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