Questions on RI spec (poss. bugs)

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Questions on RI spec (poss. bugs)
Date: 2000-11-16 19:10:02
Message-ID: Pine.BSF.4.21.0011151554130.75430-100000@megazone23.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


There's a message on -general about a possible
problem in the deferred RI constraints. He was doing a
sequence like:
begin
delete
insert
end
and having it fail even though the deleted key was back in
place at the end.

My understanding of the spec is that that sequence should
have succeeded, but I could very well be wrong. Changing the
noaction check to fix this is probably fairly minimal (making
sure that there isn't now a key with the old value before checking
for violated rows would probably be sufficient for match full and
unspecified). And I guess technically this could happen for
immediate constraints as well if a single update changed a key to
a new value and another to the old one so the constraint was still
satisifed.

But, this brings up a question for the referential actions.
It doesn't look like the actions are limited to whether or not the
row would be violating, but instead based on what row it was associated
with before. (Makes sense, you'd want a cascade update to keep
the same associations). But that made me wonder about exactly
*when* the actions were supposed to take place for deferred constraints.
You could say at check time, but that doesn't make sense for RESTRICT
really, and restrict doesn't have any special wording I see in its
definition. So if you had a deferred on delete cascade constraint, and you
do begin; delete from pk; select * from fk; end; do you see the fk rows
that were associated with the deleted pk rows?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2000-11-16 19:13:04 Re: RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c)
Previous Message Karl DeBisschop 2000-11-16 18:45:36 Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL