Re: transaction blocking inserts in postgresql 7.3 - Mailing list pgsql-general

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)