Re: pg_dump/restore and functions/triggers/trigger functions

The original pg_dump used --schema="public" .

Could the fact that pg_catalog or information_schema weren't included cause these kinds of issues? (I can't imagine why)

<becauseimjeff(at)yahoo(dot)com> wrote: did a pg_dump --format=c for a production database (on a 8.1.2 server) and attempted to pg_restore on a 8.2.0 server.

Things seemed to go fine with the exception of functions, triggers and trigger functions.

It was apparently doing a bunch of ACL work towards the end and spewed a slew of errors like this:

Feb 6 16:23:02 devl-app1 postgres[22552]: [624-1] ERROR: function add_entity(text, text, integer, text) does not exist
Feb 6 16:23:02 devl-app1 postgres[22552]: [624-2] STATEMENT: REVOKE ALL ON FUNCTION add_entity(text, text, integer, text) FROM PUBLIC;
so I checks the TOC to ensure that the function and ACLs were listed....

root(at)devl-app1# pg_restore -l prod-app-1.pgsql-prod.pgsql | grep add_entity
320; 1255 16412 FUNCTION public add_entity(text, text, integer, text) pgsql
3318; 0 0 ACL public add_entity(text, text, integer, text) pgsql

sure enough....the function and the ACLs were there.
Hmmm...When all was said and done, the only functions in the database were ones that originally came from pg_crypto (from template0 when I created the new database to load the data into)

Also...similar problem with triggers/trigger functions

Feb 6 16:07:27 devl-app1 postgres[22552]: [509-1] ERROR: function batch_stat_populate() does not exist
Feb 6 16:07:27 devl-app1 postgres[22552]: [509-2] STATEMENT: CREATE TRIGGER batch_stats_updater
Feb 6 16:07:27 devl-app1 postgres[22552]: [509-3] AFTER INSERT ON batch_audit
Feb 6 16:07:27 devl-app1 postgres[22552]: [509-4] FOR EACH ROW
Feb 6 16:07:27 devl-app1 postgres[22552]: [509-5] EXECUTE PROCEDURE batch_stat_populate();

What up? is this a version inconsistency issue?
Please tell me I don't have to -l into a listfile and manually re-order items and the use -L ?

---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.

---------------------------------
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out.