Is it safe to drop and then create the foreign key constraints inside a transaction?

Question

I have a table A that references a table B. Table B needs to be populated with updated data from an external source and for efficiency I use TRUNCATE followed by a COPY. This is done even when the application is live.

To further improve efficiency, as suggested in the documentation, I want to drop and then recreate the foreign keys.

However I have some doubts.

If I drop the FKs, COPY and then recreate FKs inside the same transaction, can I be sure that the constraint is preserved even on data inserted in table A during the transaction? I ask this because in theory a transaction is atomic, but in the docs, about the temporary removal of FKs say:

there is a trade-off between data load speed and loss of error checking while the constraint is missing.

If there's a chance that a wrong reference is inserted in the meantime, what happens when you try to recreate the FK constraints?

Answer 1

TRUNCATE is not allowed on any table referenced by a foreign key, unless you use TRUNCATE CASCADE, which will also truncate the referencing tables. The DEFERRABLE status of the constraint does not affect this. I don't think there is any way around this; you will need to drop the constraint.

However, there is no risk of an integrity violation in doing so. ALTER TABLE ... ADD CONSTRAINT locks the table in question (as does TRUNCATE), so your import process is guaranteed to have exclusive access to the table for the duration of its transaction. Any attempts at concurrent inserts will simply hang until the import has committed, and by the time they are allowed to proceeed, the constraint will be back in place.