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

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@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.

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@yahoo.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@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 EACHROW
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.

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> 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.

Seems pretty strange.  Can you strip this down to a reproducible test
case?

            regards, tom lane

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> The original pg_dump used --schema="public" .

I think that would have excluded anything that didn't demonstrably
belong to schema public, such as procedural languages.  Is it possible
that *all* your functions failed to load, and you only noted the ensuing
GRANT/REVOKE failures?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think that would have excluded anything that didn't demonstrably
belong to schema public, such as procedural languages. Is it possible
that *all* your functions failed to load, and you only noted the ensuing
GRANT/REVOKE failures?

yes...ALL my functions did indeed fail to load.
Weirdness is that I only saw errors on the GRANT/REVOKE failures.
Huh.

I created a simple test case with a single function...got the same results.
I removed the --schema='public' and it worked fine.
I compared the TOCs on the 2 different files and sure enough, there is an entry on the one that DIDN'T only use public that had the plpgsql entry.

248; 2612 90212 PROCEDURAL LANGUAGE - plpgsql

Huh.  I guess if I added plpgsql to template1 before I created the empty shell db to restore into, I would never have seen this issue.
Ok....
I guess the real question is (other than related to this issue), it there any need to dump the catalog/informational schemas?

---

Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.

Jeff Amiel <becauseimjeff@yahoo.com> writes:
>   I guess the real question is (other than related to this issue), it there any need to dump the
catalog/informationalschemas?  

There isn't, but pg_dump won't dump them anyway; you have no need to
specify switches for that.

The whole business of partial dumps and selective restores is still
pretty messy :-(.  IIRC pg_dump doesn't have any concept of dumping
or restoring all the objects that a desired object depends on; but
without that, any kind of selectivity is hard to use.  My recommendation
at the moment is to always do complete dumps --- you can filter during
pg_restore if you have to, but if your back is against the wall and your
only up-to-date dump is critically incomplete, you're screwed.

            regards, tom lane