Re: Referential integrity problem postgresql 7.2 ?

On Tue, 11 Jun 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > As a related side note.  The other part of the original patch (the NOT
> > EXISTS in the upd/del no action trigger) was rejected.  For match
> > full and match unspecified the same result can be reached by doing another
> > query which may be better than the subquery.  Do you think that'd be
> > better?
>
> No opinion offhand; can you show examples of the alternatives you have
> in mind?

[guessing that -bugs is probably not appropriate anymore, moving to
-hackers]

An additional query of the form...
SELECT 1 FROM ONLY <pktable> WHERE pkatt=<keyval1> [AND ...]

to the upd/del no action triggers.  Right now in either deferred
constraints or when multiple statements are run in a function
we can sometimes raise an error where there shouldn't be one
if a pk row is modified and a new pk row that has the old values
is added.  The above should catch this (and in fact the first versions
of the patch that I did which were only sent to a couple of people
who were having problems did exactly that).  When I did the
later patch, I changed it to a NOT EXISTS() subquery because
for match partial, the new row might not need to exactly match,
but the details of how it needs to match are based on what
matching rows there are in the fk table.  I'm not sure in general
how else (apart from doing a lower level scan of the table) how
to tell if another unrelated row with the same values has been
added to the table between the point of the action that caused
this trigger to be added to the queue and the point the trigger
runs.