Re: transaction blocking inserts in postgresql 7.3

Is it blocking because orgid is a primary key?

Does it still block if you use a different orgid in each of the two
transactions?

Regards,
Link.

At 04:32 PM 3/26/03 +1100, Chris Hutchinson wrote:

>I'm trying to find a work-around for blocked inserts in transactions in
>postgres 7.3.
>It appears that inserts into tables which reference other tables block
>inserts until a transaction is committed.
>Is there any solution other than abandoning referential integrity?
>
>Any suggestions gratefully received. An example of the problem is listed
>below.
>
>Regards,
>Chris
>
>
>I've tested the following schema:
>----------------
>create table Organisations (
>     OrgID SERIAL NOT NULL PRIMARY KEY,
>     Name TEXT NOT NULL
>);
>
>create table Trials (
>     TrialID SERIAL NOT NULL PRIMARY KEY,
>     OrgID INTEGER NOT NULL REFERENCES Organisations,
>     Title TEXT NOT NULL
>);
>
>insert into organisations (name) values ('org1');
>insert into organisations (name) values ('org2');
>
>---------------
>
>in one psql instance running:
>-------
>begin;
>insert into trials(orgid,title) values(1,'test1');
>-------
>
>in a second psql instance running;
>-------
>insert into trials(orgid,title) values(1,'test2');
>-------
>
>The second insert blocks until a commit in the first instance, even though
>the inserts only require row-level and share locks. This blocking occurs
>regardless of whether the second instance runs in a transaction or not.
>
>Here's output from pg_locks. PID 3605 is running the transaction, 3603 is
>the blocked insert:
>----------
>tester# select pgc.relname,pg_locks.* from pg_class pgc,pg_locks where
>pgc.relfilenode=pg_locks.relation order by pid,relname;