I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error:

SQL Error [XX000]: ERROR: unexpected attlen: -2
  Where: SQL statement "SELECT newdata FROM pg_temp_25.pg_temp_2489701 newdata WHERE newdata IS NOT NULL AND EXISTS (SELECT 1 FROM pg_temp_25.pg_temp_2489701 newdata2 WHERE newdata2 IS NOT NULL AND newdata2 OPERATOR(pg_catalog.*=) newdata AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)"

I've made sure a unique index exists, and I've tried running the refresh without concurrency first, which works fine.

I'm running PostgreSQL 9.6 under CloudSQL from Google Cloud.

Can anyone provide any insight?

  • attlen -2 looks like data corruption. – Laurenz Albe Jan 14 at 19:20
  • thanks @LaurenzAlbe. What'd you suggest to further debug this, or perhaps address the corruption? Please note that if I run REFRESH MATERIALIZED VIEW(with no concurrency) the data is updated fine... – jimijazz Jan 15 at 13:49
  • Oh. Perhaps the materialized view is corrupted. If you drop and re-create it, does the error persist? – Laurenz Albe Jan 15 at 14:27
  • @LaurenzAlbe yes, it does persist. I did discover the source of the error over the weekend though: A former colleague mentioned that when the materialized view was created, a dummy field was declared as '' as field, but postrges doesn't know how to interpret its type. When we change the line to 0::varchar as field the problem went away. I have no clue on why the refresh statement worked though without the concurrency, and the error only appeared when using the concurrency parameter. – jimijazz Jan 18 at 14:16
  • 1
    If you can compose a self-contained test case that exhibits that problem with the latest patch for 9.6, you should send a bug report to PostgreSQL. – Laurenz Albe Jan 18 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.