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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 &lt;

ccurvey@

&gt; 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/1406836331.34944.YahooMailNeo@web122303.mail.ne1.yahoo.com

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general