Thread: dump/restore with a hidden dependency?
I have a database with the following structure:
Create table bar…
Create function subset_of_bar … (which does a select on a subset of “bar”)
Create table foo…
Alter table foo add constraint mycheck check subset_of_bar(id);
I pg_dumped my database, and tried to pg_restore it on another server. When I do that, foo is being loaded before bar (and since there is no data in bar, the inserts to foo fail). I suspect that this is because the check constraint calls a function, which hides the dependency between the tables, so pg_dump does not get the required ordering of tables.
I thought (hoped?) that using –disable-triggers *might* help with that, so I tried to do the pg_restore in two steps (one with a –schema-only, and then another one with –data-only and –disable-triggers), but –disable-triggers does not seem to disable constraints (which is not surprising).
I’ve done some searching and am coming up empty. Is there a way to get pg_restore to apply constraints AFTER loading all the tables (I’d even be fine with a multi-step reload process of table structures, then data, then indexes, then constraints). I’d rather not go down the path of having to maintain an explicit ordered list of tables (but I might have to).
Every time you report an issue without creating a ticket, God kills a kitten. Please think of the kittens.
Disclaimer
THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail is attorney-client privileged and confidential, intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are here by notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail or by telephone (908) 233-8500.Thank you..
On 08/07/2014 10:00 AM, Chris Curvey wrote:
> I’ve done some searching and am coming up empty. Is there a way to get
> pg_restore to apply constraints AFTER loading all the tables
Kinda. PostgreSQL applies constraints with hidden system-level triggers.
An easy way to turn them off is to use this syntax:
ALTER TABLE foo DISABLE TRIGGER ALL;
Then you just need to generate one of these for each of your tables, and
run it between your table schema restore, and the data import. You can
generate a script that does it all with something like this:
COPY (
SELECT 'ALTER TABLE ' || schemaname || '.' || quote_ident(tablename)
|| ' DISABLE TRIGGER ALL;'
FROM pg_tables
) TO '/tmp/stop_triggers.sql'
After your data is loaded, just change DISABLE to ENABLE, and run the
script again.
--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On 08/07/2014 01:09 PM, Chris Curvey wrote: > The "disable trigger" statement runs without error, but does not seem > to have any effect. :( Apparently this trick only works for disabling foreign keys. I'm not sure how to temporarily disable check constraints. You might have to drop the constraints after initializing the schemas and re-add them at the end after the data import is complete. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
> -----Original Message----- > From: Shaun Thomas [mailto:sthomas@optionshouse.com] > Sent: Thursday, August 07, 2014 12:43 PM > To: Chris Curvey; pgsql-general@postgresql.org > Subject: Re: [GENERAL] dump/restore with a hidden dependency? > > On 08/07/2014 10:00 AM, Chris Curvey wrote: > > > I've done some searching and am coming up empty. Is there a way to > > get pg_restore to apply constraints AFTER loading all the tables > > Kinda. PostgreSQL applies constraints with hidden system-level triggers. > An easy way to turn them off is to use this syntax: > > ALTER TABLE foo DISABLE TRIGGER ALL; > > Then you just need to generate one of these for each of your tables, and run > it between your table schema restore, and the data import. You can > generate a script that does it all with something like this: > > COPY ( > SELECT 'ALTER TABLE ' || schemaname || '.' || quote_ident(tablename) > || ' DISABLE TRIGGER ALL;' > FROM pg_tables > ) TO '/tmp/stop_triggers.sql' > > After your data is loaded, just change DISABLE to ENABLE, and run the script > again. The "disable trigger" statement runs without error, but does not seem to have any effect. Here's a simple test case. -- create table and constraint create table foo (a int); alter table foo add constraint foobar check(a < 0); -- this should fail, and does insert into foo (a) values (1); -- disable trigger and try again alter table foo disable trigger all; -- this should work, but still fails. insert into foo (a) values (1); select version() EnterpriseDB 9.3.4.10, compiled by Visual C++ build 1600, 64-bit (and just for giggles, I tried it on another machine running "PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit") > > -- > Shaun Thomas > OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 > 312-676-8870 > sthomas@optionshouse.com > > ______________________________________________ > > See http://www.peak6.com/email_disclaimer/ for terms and conditions > related to this email Disclaimer THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail is attorney-client privileged and confidential,intended only for the use of the individual or entity named above. If the reader of this message is not theintended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are here by notifiedthat any dissemination, distribution or copying of this communication is strictly prohibited. If you have receivedthis communication in error, please notify us immediately by reply e-mail or by telephone (908) 233-8500.Thank you..
Chris Curvey <ccurvey@zuckergoldberg.com> writes:
> I have a database with the following structure:
> Create table bar...
> Create function subset_of_bar ... (which does a select on a subset of "bar")
> Create table foo...
> Alter table foo add constraint mycheck check subset_of_bar(id);
Basically, that's broken in any number of ways, not only the one you
tripped across. CHECK constraint conditions should never, ever, depend
on anything except the contents of the specific row being checked.
When you try to fake a foreign-key-like constraint with a CHECK, Postgres
will check it at inappropriate times (as per your pg_dump problem) and
fail to check it at other times when it really needs to be checked
(in this case, when you modify table bar).
You need to restructure so that you can describe the table relationship
as a regular foreign key. Anything else *will* bite you on the rear.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, August 07, 2014 2:50 PM > To: Chris Curvey > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] dump/restore with a hidden dependency? > > Chris Curvey <ccurvey@zuckergoldberg.com> writes: > > I have a database with the following structure: > > Create table bar... > > Create function subset_of_bar ... (which does a select on a subset of > > "bar") Create table foo... > > Alter table foo add constraint mycheck check subset_of_bar(id); > > Basically, that's broken in any number of ways, not only the one you tripped > across. CHECK constraint conditions should never, ever, depend on > anything except the contents of the specific row being checked. > When you try to fake a foreign-key-like constraint with a CHECK, Postgres > will check it at inappropriate times (as per your pg_dump problem) and fail > to check it at other times when it really needs to be checked (in this case, > when you modify table bar). > > You need to restructure so that you can describe the table relationship as a > regular foreign key. Anything else *will* bite you on the rear. > > regards, tom lane Thanks for the heads-up. Given that my requirement doesn't change (entries in foo must not only reference a row in bar,but must reference row in a subset of bar), what would be the recommended path forward? You can't reference a view. Using table inheritance feels like the wrong solution. Perhaps a pair of triggers? An insert-or-update trigger on foo, and a delete-or-update trigger on bar? Any other ideas? Disclaimer THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail is attorney-client privileged and confidential,intended only for the use of the individual or entity named above. If the reader of this message is not theintended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are here by notifiedthat any dissemination, distribution or copying of this communication is strictly prohibited. If you have receivedthis communication in error, please notify us immediately by reply e-mail or by telephone (908) 233-8500.Thank you..
Chris Curvey-3 wrote >> -----Original Message----- >> From: Tom Lane [mailto: > tgl@.pa > ] >> Sent: Thursday, August 07, 2014 2:50 PM >> To: Chris Curvey >> Cc: > pgsql-general@ >> Subject: Re: [GENERAL] dump/restore with a hidden dependency? >> >> Chris Curvey < > ccurvey@ > > writes: >> > I have a database with the following structure: >> > Create table bar... >> > Create function subset_of_bar ... (which does a select on a subset of >> > "bar") Create table foo... >> > Alter table foo add constraint mycheck check subset_of_bar(id); >> >> Basically, that's broken in any number of ways, not only the one you >> tripped >> across. CHECK constraint conditions should never, ever, depend on >> anything except the contents of the specific row being checked. >> When you try to fake a foreign-key-like constraint with a CHECK, Postgres >> will check it at inappropriate times (as per your pg_dump problem) and >> fail >> to check it at other times when it really needs to be checked (in this >> case, >> when you modify table bar). >> >> You need to restructure so that you can describe the table relationship >> as a >> regular foreign key. Anything else *will* bite you on the rear. >> >> regards, tom lane > > Thanks for the heads-up. Given that my requirement doesn't change > (entries in foo must not only reference a row in bar, but must reference > row in a subset of bar), what would be the recommended path forward? You > can't reference a view. Using table inheritance feels like the wrong > solution. > > Perhaps a pair of triggers? An insert-or-update trigger on foo, and a > delete-or-update trigger on bar? > > Any other ideas? In no particular order: Triggers A Compound FK that applies the check of the of the first field to the subset defined by the second. i.e., ... FOREIGN KEY (b_id, b_scope) REFERENCES bar (b_id, b_scope) David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/dump-restore-with-a-hidden-dependency-tp5814072p5814118.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Chris Curvey <ccurvey@zuckergoldberg.com> wrote: > Perhaps a pair of triggers? An insert-or-update trigger on foo, and a > delete-or-update trigger on bar? Using a foreign key constraint is best if that can do the right thing. If that doesn't work, triggers like you describe are probably the best option, but you need to cover race conditions. See this recent post for suggestions: http://www.postgresql.org/message-id/1406836331.34944.YahooMailNeo@web122303.mail.ne1.yahoo.com -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company