PostgreSQL – How to Drop Table Ignoring Dependencies

postgresqlpostgresql-9.6

I had a table from which I wanted to delete a lot of rows but that was failing because of memory constraints. I saw a suggestion to "simply" copy all the rows that I don't want to delete into a new table, drop the old table, then rename the new table to the old table's name. That would be fine except for other objects that depend on the original table. From what I can tell that means I'd have to drop cascade and then recreate everything that depends on the original table.

Is there a way to either script everything that depends on the original table so I can easily recreate them in pgadmin or psql?

Alternatively, is there a way to ignore the dependency long enough to delete the old table and rename the new table to the old table's name?

I believe this is how pg_repack works so there must be a way.

Best Answer

DROP TABLE example CASCADE;

will drop the table example. and drop all REFERENCES constraints that point into that table.

so after replacing the table you will also need to rebuild the references.

to get the dependencies you could use pg_dump --schema-only and then grep for REFERENCES example(

You will not be able to rebuild the references until the needed data is present in the table.

Related Solutions

Postgresql – Drop and restore PostgresSQL database without losing settings

If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.

If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.

As the configuration files are just plain text (as are the logfiles) you can simply copy the backup over into the new data directory after running initdb.

(Note: I don't know what pg_dropcluster does. It is not a standard Postgres tool)

PostgreSQL – How to Duplicate a Huge Table

Generally the fastest way to duplicate a table is simply:

CREATE TABLE table2 AS SELECT * FROM table1;

Parallel INSERTs may be faster, but only with a very fast disk subsystem (when data is interleaved on many drives). Otherwise this will be slower.

Once you're done with modifying table2, it can take the new name with:

BEGIN;
DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;

The DROP TABLE command needs an exclusive lock, which affects concurrent readers in a way you may want to anticipate:

DROP will wait for any pending read on the table from other transactions to finish.
Any new transaction attempting to read that table in the meantime will be put it in wait, and then fail since the original table1 no longer exists. The error would look like "could not open relation with OID oid"

To avoid the second issue, you may rename table1 to old_table1 instead of dropping it, and then drop it only later outside of the transaction, when these readers are done with it. So the sequence above would become:

BEGIN;
ALTER TABLE table1 RENAME TO old_table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;
...
DROP TABLE old_table1;

Best Answer

Related Solutions

Postgresql – Drop and restore PostgresSQL database without losing settings

PostgreSQL – How to Duplicate a Huge Table

Related Question