I do this fairly often, but after talking to some other people I realized it might be a good idea to share a couple of quick SQL scripts. The idea is: you have some large operations you are doing on your database. Of the kind where you load or update lots of data - which means that it will run a lot faster with the constraints in the database turned off. All of them, or maybe just the FOREIGN KEYs, depending on exactly what you are doing. The following simple queries will generate SQL scripts that drop all your constraints, and then re-create them. Adding filters for just a single table/namespace/constrainttype is trivial, but left as an exercise for the reader...
To generate a script to drop constraints:
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname
Note that the order is important - we must drop FOREIGN KEYs before we drop PRIMARY KEYs and UNIQUE constraints, since they depend on each other.
To generate a script to reload the constraints:
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;
Again, note the reversed order that is necessary so we create PRIMARY KEYs and UNIQUE constraints before we create the FOREIGN KEYs.
The easiest way to turn these commands into proper scripts is to just use the psql output feature:
mydb=# \t
Showing only tuples.
mydb=# \o drop_constraints.sql
mydb=# SELECT ...
mydb=# \o create_constraints.sql
mydb=# SELECT ...
You can then just load those files as include files in psql. Obviously, you need to script both the DROP and CREATE statements before you actually drop something.
And the second obvious note - always run these things in a transaction. Take advantage of the transactional DDL in PostgreSQL - as always. I often run the whole thing - drop, bulk work, recreat - inside a single transaction. That way, applications may not even need to know there is service happening...
Finally, in some databases you may need to add further restrictions, and always verify the scripts before you run them :)
Not sure what you mean. AFAIK, it should only do implicit commit if you're running psql in single transaction mode?
There's a major "flaw" in this thing.
As soon as you drop the contraints (1st query) you won't be able to reload them (2nd one).
Infact, once you ALTER a TABLE to DROP a CONSTRAINT of its, that constraint won't show up anymore in the pg_constraint system table, thus making the 2nd query useless!
So you need to save some data from pg_constraint into a table for later restoration.
Nonetheless, this post is very interesting.
Yeah, that's why I said "Obviously, you need to script both the DROP and CREATE statements before you actually drop something."
The easiest way is to simply save the script...
But it's certainly true that if you run the script generated by the first query, the system tables will be empty of the information, and there is no way to recover it (other than backups).
What I prefer in this case is to create some extra structure (a table and a couple of functions) to make the bookkeeping of constraints. What actually happens in my experience is that the application needs to do some bulk load. In that case the pre-generated scripts don't fit well and need some maintenance, while the table+functions solution is "automatic". In any case your posting shows a hands-on view of some system tables, which is always a good thing! Thanks.
How about indexes as well?
SELECT 'DROP INDEX "'||nspname||'"."'||relname||'" RESTRICT;' FROM pg_index INNER JOIN pg_class ON indexrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE indisprimary=FALSE and indisvalid=TRUE AND nspname NOT LIKE 'pg_%25' ORDER BY nspname,relname;
SET search_path=public; SELECT pg_get_indexdef(pg_index.indexrelid)||';' FROM pg_index INNER JOIN pg_class ON indexrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE indisprimary=FALSE and indisvalid=TRUE AND nspname NOT LIKE 'pg_%25' ORDER BY nspname,relname;
THANK YOU. I don't know why this functionatliy isn't in Postgresql in the first place, seeing as how apparently Oracle and MySQL have it.
New comments can no longer be posted on this entry.
Just a warning that if you pipe these two commands to two separate scripts, and then try to run those scripts within a transaction, that \i in psql will cause implicit commits.