On 09/14/2012 11:56 PM, McKinzie, Alan (Alan) wrote:
My underlying question/concern is "will this change have any adverse
affects (on performance) during normal operations when the foreign keys
are set to deferrable initially immediate" .vs. the foreign keys being
defined as NOT DEFERRABLE.
AFAIK in PostgreSQL DEFERRABLE INITIALLY IMMEDIATE is different to NOT
DEFERRABLE.
DEFERRABLE INITIALLY IMMEDIATE is executed at the end of the statement,
while NOT DEFERRABLE is executed as soon as it arises.
http://www.postgresql.org/docs/current/static/sql-set-constraints.html
http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred
Again from memory there's a performance cost to deferring constraint
checks to the end of the statement rather than doing them as soon as
they arise, so NOT DEFERRED can potentially perform better or at least
not hit limits that DEFERRABLE INITIALLY DEFERRED might hit in Pg.
This seems under-documented and I haven't found much good info on it, so
the best thing to do is test it.
--
Craig Ringer
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
