9.3 reference constraint regression

From: Daniel Wood <dwood(at)salesforce(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 9.3 reference constraint regression
Date: 2013-12-11 19:32:33
Message-ID: CAPweHKe5QQ1747X2c0tA=5zf4YnS2xcvGf13Opd-1Mq24rF1cQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In 9.3 I can delete the parent of a parent-child relation if the child row
is an uncommitted insert and I first update the parent.

USER1:
drop table child;
drop table parent;
create table parent (i int, c char(3));
create unique index parent_idx on parent (i);
insert into parent values (1, 'AAA');
create table child (i int references parent(i));

USER2:
BEGIN;
insert into child values (1);

USER1:
BEGIN;
update parent set c=lower(c);
delete from parent;
COMMIT;

USER2:
COMMIT;

Note that the problem also happens if the update is "set i=i". I was
expecting this update to block as the UPDATE is on a "unique index" "that
can be used in a foreign key". The "i=i" update should get a UPDATE lock
and not a "NO KEY UPDATE" lock as I believe the c=... update does.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gavin Flower 2013-12-11 19:33:08 Re: ANALYZE sampling is too good
Previous Message Kevin Grittner 2013-12-11 19:31:44 Re: ANALYZE sampling is too good