I've got a question about the foreign key constraint behavior.
It looks to me that inserts within transactions into a child table, which have the same FK value back to the parent
willblock until the first txn will commit or rollback. (see example below)
This seems to be based on the fact that the RI_FKey_check function will lock the parent row for update, so any other
childrow referring the same row will be locked out.
I've added a debug stmt into the RI_FKey_check function to see the query it does:
NOTICE: RI_FKey_check: PLAN2: SELECT 1 FROM ONLY "public"."parent" x WHERE "id" = $1 FOR UPDATE OF x
I think I basically understand, why this is done. To make sure that the parent row can't be deleted before the child
rowis committed and there would have an orphan reference.
But, if a lot of inserts happens into the child table and there is a mix of short and long running transactions, the
likelihoodof blocking is very high, even the inserts are independent and everything is ok (prim. key etc.). This is
evenmore extreme, the smaller parent table is.
FYI, I've tried the same with Oracle and there is no such problem. The insert in the second session will come back
immediatelywithout blocking, though it will still maintain the integrity from other txns.
I wonder if there is a lower level way to maintain the locking and having the same behavior as oracle.
So, instead of using a "SELECT ... FOR UPDATE", using some pg function to lock a row with a different mode?
Overall, I find this restriction pretty bad and renders the use of foreign key constraints almost useless from the
performancepoint of view as that leads to real serialization of transaction, even they don't have any overlaps.
in session1:
============
drop table child;
drop table parent;
create table parent (id integer not null);
ALTER TABLE parent ADD CONSTRAINT parent_PK PRIMARY KEY(ID);
create table child (id integer not null, parent_id integer not null);
ALTER TABLE child ADD CONSTRAINT child_PK PRIMARY KEY(ID);
ALTER TABLE child ADD CONSTRAINT child_parent_id FOREIGN KEY (parent_id) REFERENCES parent (ID);
insert into parent values (1);
insert into parent values (2);
begin;
insert into child values (1,1);
<this will be ok>
in session2 after the last insert in session1:
==============================================
begin;
insert into child values (2,1);
<this will block now until the session1 does commit or rollback>
--
Best regards,
Peter Schindler
