how to disable all pkey/fkey constraints globally

On 10/04/11 10:59 AM, J.V. wrote:

Maybe I do not want to drop, so is there a way to simply disable all
globally (not drop) & then enable all globally?

IF there was such a method (I don't think there is), to reenable the
constraints would require going through every single row of every single
table and verify the constraints are valid prior to enabling them.
this would require global locks on each table so they didn't get updated
between the time said validation starts and when the constraint is
actually enabled to prevent any possibility of inconsistency from
creeping in.

*OUCH*

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of J.V.
Sent: Tuesday, October 04, 2011 10:00 PM
To: pgsql-general
Subject: [GENERAL] how to disable all pkey/fkey constraints globally

Is there a generic way to drop just all primary key and foreign key constraints
on a given table?

I know how to do given the specific name of the constraint.

same question but one statement that would just disable all primary key and
foreign key constraints on a given database?

and am assuming the reverse could not be done because would have to re-
create each one individually?

Maybe I do not want to drop, so is there a way to simply disable all globally
(not drop) & then enable all globally?

You can find all foreign key constraints for a given table, save constraint definitions, drop constraints, and later re-enable them. Look into table pg_constraint and function pg_get_constraintdef.

Dmitry Epstein | Developer

Allied Testing

www.alliedtesting.com
We Deliver Quality.

On 10/05/2011 04:49 AM, depstein@alliedtesting.com wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of J.V.
Sent: Tuesday, October 04, 2011 10:00 PM
To: pgsql-general
Subject: [GENERAL] how to disable all pkey/fkey constraints globally

Is there a generic way to drop just all primary key and foreign key constraints
on a given table?

I know how to do given the specific name of the constraint.

same question but one statement that would just disable all primary key and
foreign key constraints on a given database?

and am assuming the reverse could not be done because would have to re-
create each one individually?

Maybe I do not want to drop, so is there a way to simply disable all globally
(not drop) & then enable all globally?

You can find all foreign key constraints for a given table, save
constraint definitions, drop constraints, and later re-enable them.
Look into table pg_constraint and function pg_get_constraintdef.

If you'll allow me to toot my horn, here's an alternative:

- Use dbtoyaml [1]http://www.pyrseas.org/docs/dbtoyaml.html to output your tables to a file, say, yaml1
- Edit the yaml1 file, searching for primary_key and foreign_keys and
remove those you want to drop, save the result to a different file, say,
yaml2
- Use yamltodb [2]http://www.pyrseas.org/docs/yamltodb.html with yaml2 to generate SQL to drop the primary keys
and foreign keys, in the correct dependency order (at least that's what
it's supposed to do, make sure you use the -1 option), to a file, say, sql1
- Run sql1 through psql to drop the constraints
- Use yamltodb with yaml1 to generate SQL to recreate the primary keys
and foreign keys to, say sql2
- Run sql2 through psql to recreate the constraints

Regards,

Joe

[1]: http://www.pyrseas.org/docs/dbtoyaml.html
[2]: http://www.pyrseas.org/docs/yamltodb.html

Create a temp table that will store all the foreign kez constraints 
then create a function that add an entry to this table 
then write a plpgsql function that reads the constraints and disable them 
write another function to read the constraints from the temporary tables and create them again 

have a look on pg_constraints , pg_tables 
 
regards

________________________________
From: Joe Abbate <jma@freedomcircle.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, October 5, 2011 11:27 AM
Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally

On 10/05/2011 04:49 AM, depstein@alliedtesting.com wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of J.V.
Sent: Tuesday, October 04, 2011 10:00 PM
To: pgsql-general
Subject: [GENERAL] how to disable all pkey/fkey constraints globally

Is there a generic way to drop just all primary key and foreign key constraints
on a given table?

I know how to do given the specific name of the constraint.

same question but one statement that would just disable all primary key and
foreign key constraints on a given database?

and am assuming the reverse could not be done because would have to re-
create each one individually?

Maybe I do not want to drop, so is there a way to simply disable all globally
(not drop) & then enable all globally?

You can find all foreign key constraints for a given table, save
constraint definitions, drop constraints, and later re-enable them.
Look into table pg_constraint and function pg_get_constraintdef.

If you'll allow me to toot my horn, here's an alternative:

- Use dbtoyaml [1]http://www.pyrseas.org/docs/dbtoyaml.html to output your tables to a file, say, yaml1
- Edit the yaml1 file, searching for primary_key and foreign_keys and
remove those you want to drop, save the result to a different file, say,
yaml2
- Use yamltodb [2]http://www.pyrseas.org/docs/yamltodb.html with yaml2 to generate SQL to drop the primary keys
and foreign keys, in the correct dependency order (at least that's what
it's supposed to do, make sure you use the -1 option), to a file, say, sql1
- Run sql1 through psql to drop the constraints
- Use yamltodb with yaml1 to generate SQL to recreate the primary keys
and foreign keys to, say sql2
- Run sql2 through psql to recreate the constraints

Regards,

Joe

[1]: http://www.pyrseas.org/docs/dbtoyaml.html
[2]: http://www.pyrseas.org/docs/yamltodb.html

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

Is there a simpler way than this to query the database for meta-data and
get the constraint definitions?

If I have the constraint name (which I do), I could store the constraint
definition to a file or database table and recreate them if I could get
the definition.

This seems like a very simple thing to do, but nowhere can I find the
meta-data I would need to first save the constraint, to later re-create it.

thanks

On 10/20/2011 05:10 PM, J.V. wrote:

Is there a simpler way than this to query the database for meta-data and
get the constraint definitions?

If I have the constraint name (which I do), I could store the constraint
definition to a file or database table and recreate them if I could get
the definition.

This seems like a very simple thing to do, but nowhere can I find the
meta-data I would need to first save the constraint, to later re-create it.

The metadata is all in the PostgreSQL catalogs. One could write
PL/pgSQL (or other PL) functions to select all the info from the
catalogs into a simple text (SQL statement) format (e.g., ALTER TABLE
tbl ADD CONSTRAINT ...), and also to generate or execute the disabling
(ALTER TABLE tbl DROP CONSTRAINT ...) statements.

You may think it's very simple, but I can assure you that adding or
dropping constraints is not so easy because the ADDs and DROPs have to
be issued in an order that takes into account inter-table/constraint
dependencies (including, in the general case, constraints that are
inherited between tables).

Joe

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of J.V.
Sent: Friday, October 21, 2011 1:11 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally

Is there a simpler way than this to query the database for meta-data and get
the constraint definitions?

If I have the constraint name (which I do), I could store the constraint
definition to a file or database table and recreate them if I could get the
definition.

This seems like a very simple thing to do, but nowhere can I find the meta-
data I would need to first save the constraint, to later re-create it.

thanks

If you know constraint name and schema, then I don't see how you could possibly make it any simpler than the way I already suggested:

Look into table pg_constraint and function pg_get_constraintdef.

Of course, since this involves the use of a system catalogue, this is neither portable nor very reliable in the long run. You can also get all the information about foreign key constraints from information_schema tables, but that is more complicated. You'll need to join table_constraints, referential_constraints and key_column_usage, probably more than once.