Menu
Our Postgres 9.2.4 server is taking 0.69s on average (15000 statements) to execute
SET CONSTRAINTS ALL DEFERRED
Why should this be?
Would using INITIALLY DEFERRED in the table definition and not using SET CONSTRAINTS .. eliminate this time or just hide it somewhere else?
568
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.
DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode. Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED , DEFERRABLE INITIALLY IMMEDIATE , or NOT DEFERRABLE .
The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on an existing table.
A deferred constraint is one that is enforced when a transaction is committed. A deferrable constraint is specified by using DEFERRABLE clause. Once you've added a constraint, you cannot change it to DEFERRABLE. You must drop and recreate the constraint.
I looked through the source code. Essentially, SET CONSTRAINTS ALL DEFERRED does nothing except set a global variable noting "all appropriate constraints are now deferred". The only nontrivial work that is done is when there are subtransactions in play. If that applies to your case, try it without. (Note that subtransactions include PL/pgSQL exception blocks.)
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
