From: | Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> |
---|---|
To: | <chris(at)hutchinsonsoftware(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: transaction blocking inserts in postgresql 7.3 |
Date: | 2003-03-26 09:50:10 |
Message-ID: | 5.1.0.14.1.20030326174518.029454f0@mbox.jaring.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Hutchinson | 2003-03-26 09:59:13 | Re: transaction blocking inserts in postgresql 7.3 |
Previous Message | Csaba Nagy | 2003-03-26 09:29:15 | Re: transaction blocking inserts in postgresql 7.3 |