pg_dump generates invalid SQL command

Question

When I run pg_dump on my database, it's converting one of my views (orders_plus) to a table, and then creating a rule that I guess puts it back to the equivalent of a view. That seems to be OK (though strange). However, right after the 'CREATE RULE' it puts this line:

ALTER VIEW orders_plus SET ();

That's not a valid command, and so I get an error when recovering the database.

Answer 1

pg_dump should never do this. This is a problem with your server or your client. Since you got this from Apple and it may be customized follow up with them.

My guess is that one of the pg_get_* functions is failing. This could be an issue with data in your system tables or it could be issues with the server-side functions. But I have never seen anything like this before anywhere else so Apple would be the place to start.

Answer 2

... 3 years later ...

Seems you stumbled upon a bug in v9.2.1, which was fixed in v9.2.2.

See v9.2.2 release notes:

Fix pg_dump for views with circular dependencies and no relation options (Tom Lane)

The previous fix to dump relation options when a view is involved in a circular dependency didn't work right for the case that the view has no options; it emitted ALTER VIEW foo SET () which is invalid syntax.

I recently noticed the exact same issue when restoring/migrating a production database. I used an older version of pg_dump than the database by mistake;)

psql:dump.sql:27821784: ERROR:  syntax error at or near ")"
LINE 1: ALTER VIEW <viewname> SET ();

The problem went away when using pg_dump v9.2.6.

For completeness, here's some software info: Postgres version 9.2.6 GNU/Linux 2.6.18-406.el5 #1 SMP Fri May 1 10:37:57 EDT 2015 x86_64. Red Hat Enterprise release 5.11 (Tikanga).