Thread: pg_restore: [archiver (db)] could not execute query: ERROR: zero-length delimited identifier at or near """" at character 60

pg_restore: [archiver (db)] could not execute query: ERROR: zero-length delimited identifier at or near """" at character 60

From

"Shahryar G. Hashemi"

Date:

02 January 2007, 01:24:33

Hello,

We are running PostgreSQL 7.4.7 on a Linux system. We have a primary DB and hot-standby [via pg_dump/pg_restore]. A few weeks ago we had a database corruption and had to go back to a dump from 12/20/2006. Everything restored fine [on the primary and standby DB's] however everytime we perform a pg_dump/pg_restore we get this error:

pg_restore: creating TABLE account_password
pg_restore: creating FUNCTION trig_account_insert()
pg_restore: creating FUNCTION trig_account_password_insert()
pg_restore: creating ACL trig_account_password_insert()
pg_restore: [archiver (db)] could not execute query: ERROR: zero-length delimited identifier at or near """" at character 60
pg_restore: *** aborted because of error

Here is how we run the pg_dump command:
pg_dump -C -v -U hydra -f <backup_file> -Fc hydra
Here is how we run the pg_restore command:
pg_restore -v -C -h bvmgdb2a -U hydra -d template1 <backup_file>

I can get the pg_restore to complete if I add the -x [ignore ACL [grant/revoke]. But my question is what is wrong with the ACL trig_account_password_insert() and how can I fix it on the production DB so this error can go away? The error makes zero sense. The documentation leaves a lot to be desired.

BTW I am a Sybase/SQL Server DBA so PostgreSQL is another world to me.

Thanks,

Shahryar

--
Shahryar G. Hashemi                      DBA
InfoSpace Inc.
601 108th Ave NE Suite 1200
Bellevue, WA 98004 U.S.A.
Office: +1 425.201.8853                  Fax: +1 425.201.6160
Alpha Pager: page-shashem@infospace.com Numeric Pager: +1 877.252.5631
E-Mail: shashem@infospace.com            WEB: http://infospaceinc.com

This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Re: pg_restore: [archiver (db)] could not execute query: ERROR: zero-length delimited identifier at or near """" at character 60

From

Tom Lane

Date:

02 January 2007, 01:42:22

"Shahryar G. Hashemi" <Shahryar.Hashemi@infospace.com> writes:
> pg_restore: creating ACL trig_account_password_insert()
> pg_restore: [archiver (db)] could not execute query: ERROR:  zero-length
> delimited identifier at or near """" at character 60

Since 8.0, pg_dump/pg_restore have been careful to include the failing
SQL command in messages like this, but 7.x didn't always do so.  You could
enable statement logging on the server to find out what the failed
command was.  Offhand it looks like a quoting problem but without more
detail one can't tell.

            regards, tom lane