Subject: Re: ACCESS EXCLUSIVE lock
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Atesz <atesz@ritek.hu>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4536391E.7040604@ritek.hu>
References: <4536391E.7040604@ritek.hu>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1161185300.8706.82.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 18 Oct 2006 10:28:20 -0500

On Wed, 2006-10-18 at 09:24, Atesz wrote:
> Hi!
> 
> I have a problem with ACCESS EXCLUSIVE lock when I drop a reference in 
> transaction. I have 2 tables: 
> create table  a(id SERIAL primary key);
> create table  b(id SERIAL primary key references a(id));
> 
> After that I have 2 processes: P1, P2
> In P1:
> begin;
> ALTER TABLE b DROP CONSTRAINT  b_id_fkey;
> 
> In P2:
> SELECT * FROM a;
> 
> And I'm waiting for the result, but I don't get until P1 finishes.
> I know the DROP CONSTRAINT put an ACCESS EXCLUSIVE table LOCK into the 
> TABLE a, and the SELECT is stopped by this LOCK in P2.
> Note: I cannot commit the P1 earlier, because it's a very long 
> transaction (more hours, data conversion transaction)
> My question:  Why need this strict locking?
> 
> In my opinion there isn't exclusion between the DROP CONSTRAINT and the 
> SELECT.

What if, a minute or two after the drop contraint, you issue a rollback?