Re: begin transaction locks out other connections

Dennis Brakhane wrote:
> On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee
> <pavan.deolasee@gmail.com> wrote:
>
>> On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote:
>>
>>  I think that's because Postgres does not have deferred constraint checks.
>>
>
> I believe it does. See
> http://www.postgresql.org/docs/8.3/interactive/sql-set-constraints.html
> and the DEFERRABLE keyword in CREATE TABLE.
>
> Or am I missing something here?
>
>
As far as I know UNIQUE and CHECK constraints cannot be deferrable; only
FOREIGN KEY constraints can be deferrable. You can use a CONSTRAINT
TRIGGER to emulate others though.

I'm told some other databases can defer UNIQUE constraint checks, but I
haven't the foggiest how that can work in a remotely sane way. Wouldn't
a deferred UNIQUE constraint be useless to the query planner (which
can't trust that the data is really unique right now) and cause
confusing behaviour with scalar subqueries (that might suddenly not
return a single result) and stored procedures that rely on the unique
constraint?

I guess the same thing applies to a deferred foreign key constraint,
really - you can't actually trust it in any context where you're
modifying the data involved. It just seems a lot simpler to think about
the effects of deferred foreign key constraints.

--
Craig Ringer