Re: begin transaction locks out other connections

Dennis Brakhane wrote:
> On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee
>  wrote:
>   
>> On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti  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