NOT DEFERRABLE vs. DEFERRABLE INITIALLY IMMEDIATE constraints
I've been plagued several times by NOT DEFERRABLE constraints. Is there
any good reason to define a constraint as NOT DEFERRABLE rather
than DEFERRABLE INITIALLY IMMEDIATE? For example, is there performance
penalty for PostgreSQL being prepared to defer a constraint even though it
is not currently being deferred?
The only downside I see to DEFERRABLE INITIALLY IMMEDIATE is that a naive
user could needless set it to deferred, and thus use more memory/time than
they otherwise would. But there are so many ways for naive users to shoot
themselves in the foot, I fail to see the point in foreclosing this one
possibility.
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
I've been plagued several times by NOT DEFERRABLE constraints. Is there
any good reason to define a constraint as NOT DEFERRABLE rather
than DEFERRABLE INITIALLY IMMEDIATE? For example, is there performance
penalty for PostgreSQL being prepared to defer a constraint even though it
is not currently being deferred?
There's a substantial performance difference between deferrable and
nondeferrable uniqueness constraints (ie, indexes). For foreign keys
I don't believe it matters. We don't implement deferrability for
other types of constraints such as CHECK.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general