From: | Rachit Siamwalla <rachit(at)ensim(dot)com> |
---|---|
To: | "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | inserts on a transaction blocking other inserts |
Date: | 2001-05-12 01:51:14 |
Message-ID: | 9AC41B8C4781464695BB013F106FCA31D2BD73@nasdaq.ms.ensim.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I am having problems with transactions and foreign key constraints in
postgres 7.0-3 (RPM distribution). . The foreign key constraints were
blocking concurrent transactions. Here is an example where something blocked
but shouldn't have blocked:
create table hello10 (myid serial primary key, myvalue int4);
create table hello11(myvalue int4, foreign key (myvalue) references
hello10);
insert into hello10 (myvalue) values (1);
---- ok, now everything is set up for the blocking problem.
Now have two logins to psql:
psql1# begin;
psql1# insert into hello11 (myvalue) values (1)
psql1#
switch to the other login
psql2# begin;
psql2# insert into hello11 (myvalue) values (1)
*** block ***
It shouldn't block there. Basically it happens when two transactions try to
insert something into tables (doesn't have to be the same one) which both
have a foreign key constraint to a common key. I did some poking around and
luckily did find something in the archives that was similar here:
http://fts.postgresql.org/db/mw/msg.html?mid=30149
It was mentioned that it was a problem, and there was a workaround (add
INITIALLY DEFFERED to the constraint). The workaround works. My question is,
is this fixed in Postgres 7.1 (i don't have a spare machine to test, sorry)?
-rchit
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2001-05-12 04:09:07 | Re: Rtree on custom data types; type conversion stops index use. |
Previous Message | A.V.Shutko | 2001-05-12 01:48:55 | Postgres bug (working with iserverd) |