Re: [GENERAL] 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) [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 enoughthe 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.
Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions
Jeff Amiel [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions
Jeff Amiel [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions
Tom Lane [EMAIL PROTECTED] 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.
Re: [GENERAL] pg_dump/restore and functions/triggers/trigger functions
Jeff Amiel [EMAIL PROTECTED] writes: I guess the real question is (other than related to this issue), it there any need to dump the catalog/informational schemas? 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
