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

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
----------