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

Hi Chris,

I have the same problem with foreign keys. Short answer: AFAIK, without
changing Postgres code, there's no valid workaround but dropping foreign
keys or fixing your code to avoid/handle the resulting
contention/deadlocks.
Our application was originally developed using Oracle, where this
problem is not existing (foreign key checks place less restrictive
locks).
The only quick solution I found is a kludge: I've changed the source
code of Postgres so it doesn't place locks when checking foreign keys...
this (mostly) works with our code, but it doesn't fully warrant you
anymore the referential integrity of the data (there are a few
situations when parallel transactions can leave child records without
parent record, e.g. when one transaction updates a child record while
the other transaction deletes the referenced row). In our code these
situations are very unlikely, so it is acceptable risk.
In the long run, there is a development effort to make Postgres handle
the foreign key checks with better locking, but I don't know how far it
got, and I don't have the time to participate myself.

HTH,
Csaba

On Wed, 2003-03-26 at 06:32, 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)
>