Re: Odd Locking Problem

On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
> I'm having an odd case where my system is locking such that if I insert
> into a table during a transaction, if I start a new connection and
> transaction, it blocks while trying to do a similar insert until the
> first transaction is committed or rolled back.

Are there foreign keys here? I can duplicate the problem easily with
them:

-- session 1
create table a (a serial primary key);
create table b (a int references a);
insert into a values (1);

begin;
insert into b values (1);

-- session 2
insert into b values (1);
-- hangs

If I commit on session 1, session 2 is unlocked.

This is a known problem, solved in 8.1. A workaround for previous
releases is to defer FK checks until commit:

create table b (a int references a initially deferred);

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.