Re: transaction blocking inserts in postgresql 7.3

I'm really curious how you got these two transactions to occur
simultaneously, i.e. how does one DO the test that you DID?

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;
> 
>  organisations      | 20810985 | 20810982 |             | 3603 |
> AccessShareLock  | t
>  organisations      | 20810985 | 20810982 |             | 3603 |
> RowShareLock     | t
>  trials             | 20810996 | 20810982 |             | 3603 |
> AccessShareLock  | t
>  trials             | 20810996 | 20810982 |             | 3603 |
> RowExclusiveLock | t
>  trials_trialid_seq | 20810994 | 20810982 |             | 3603 |
> AccessShareLock  | t
>  organisations      | 20810985 | 20810982 |             | 3605 |
> AccessShareLock  | t
>  organisations      | 20810985 | 20810982 |             | 3605 |
> RowShareLock     | t
>  organisations_pkey | 20810991 | 20810982 |             | 3605 |
> AccessShareLock  | t
>  trials             | 20810996 | 20810982 |             | 3605 |
> AccessShareLock  | t
>  trials             | 20810996 | 20810982 |             | 3605 |
> RowExclusiveLock | t
>  trials_trialid_seq | 20810994 | 20810982 |             | 3605 |
> AccessShareLock  | t
>  pg_class           |     1259 | 20810982 |             | 3607 |
> AccessShareLock  | t
>  pg_locks           |    16757 | 20810982 |             | 3607 |
> AccessShareLock  | t
> ----------
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)