
Right now, referential actions get deferred along with normal
checks and run against the state of the database at that time.
I think this violates SQL92 11.8 General Rules 4-6 and have some
reasoning and proposed ideas towards making it more complient
although I don't actually have an implementation in mind for
the most correct version. :(

Here are my interpretations:

	GR 4 says that the matching rows (unique and non-unique)
are determined immediately before the execution of an SQL
statement.  We can ignore the fluff about non-unique matching
rows for now because I believe that applies to match partial only.
	GR 5 says when there's a delete rule and a row of the
referenced table is marked for deletion (if it's not already
marked such) then do something based on the action, for example
mark matching rows for deletion if it is cascade.  This seems
to imply the action is supposed to occur immediately, since
AFAICS the rows aren't marked for deletion on the commit but
rather on the delete itself.
	GR 6 seems to be pretty much the same for update.

I think the correct course of action would be if I'm right:
*Make referential actions (other than no action) not deferrable
  and thus initially immediate.  This means that you see the
  cascaded (or nulled or defaulted) results immediately, but
  I think that satisfies GRs 5 and 6.  It also makes the
  problems of what we can see a little less problematic, but
  doesn't quite cure them.
*To fix the visibility issues I think we'd need to be able to
  see what rows matched immediately before the statement and
  then reference those rows later, even if the values that we're
  keying on have changed.  I'm really not sure how we'd do
  this without a great deal of extra work.
  An intermediate step towards complience would probably
  be making sure the row existed before this statement
  (I think for the fk constraints this means if it was
   created by another statement or a command before this
   one) which is wrong if a row that matched before this
  statement was modified by this statement to a new value
  that we won't match.  Most of these cases would be errors
  by sql anyway (I think these'd probably be real triggered
  data change violations) and would be wrong by our current
  implementation as well.

I'm not sure that the intermediate step on the second is
actually worthwhile over just waiting and trying to do it
right, but if I'm right in what it takes, it's reasonably
minimal.


Mon, 12 Nov 2001 18:50:17 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Foreign key referential actions 
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Right now, referential actions get deferred along with normal
> checks and run against the state of the database at that time.
> I think this violates SQL92 11.8 General Rules 4-6 and have some
> reasoning and proposed ideas towards making it more complient
> although I don't actually have an implementation in mind for
> the most correct version. :(

I'm not convinced.  11.8 GR 1 refers to clause 10.6 as specifying
when the referential constraint is to be checked.  10.6 says that
immediate-mode constraints are checked "on completion" of each SQL
statement.  (It doesn't say anything about deferred-mode constraints,
but I suppose those are checked at end of transaction.)

I think the intended meaning is that the actions caused by the
constraint are taken when the constraint is checked, which is
either end of statement or end of transaction.  Which is what
we're doing now.

			regards, tom lane

Tue, 13 Nov 2001 18:26:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign key referential actions 
On Tue, 13 Nov 2001, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Right now, referential actions get deferred along with normal
> > checks and run against the state of the database at that time.
> > I think this violates SQL92 11.8 General Rules 4-6 and have some
> > reasoning and proposed ideas towards making it more complient
> > although I don't actually have an implementation in mind for
> > the most correct version. :(
>
> I'm not convinced.  11.8 GR 1 refers to clause 10.6 as specifying
> when the referential constraint is to be checked.  10.6 says that
> immediate-mode constraints are checked "on completion" of each SQL
> statement.  (It doesn't say anything about deferred-mode constraints,
> but I suppose those are checked at end of transaction.)
> I think the intended meaning is that the actions caused by the
> constraint are taken when the constraint is checked, which is either
> end of statement or end of transaction.  Which is what we're doing
> now.

But checking the constraint and the actions are not necessarily the
same thing, I believe they're meant as two components.  There's a
constraint which says what is a legal state of the database and
there are actions which make modifications to the state of the
database based on the deletes and updates.

For example, in GR 5, it uses the present tense.  "and a row
of the referenced table that has not previously marked for
deletion *is* marked for deletion..." (emph. mine).  I'd
read that to mean that the following occurs at the time.  If
they wanted it to be at the constraint check time, that should
be "has been" or "was" because other places it says things about
how rows that are marked for deletion are effectively deleted
prior to the checking of any integrity constraint (13.7 GR 4
for example) so there'd be no rows remaining that were marked
for deletion at that point.  I guess I'm just reading it with a
different set of semantic filters for the language.

Behaviorally I would think that a sequence like:
begin;
 insert into pk
 insert into fk
 delete from pk
 insert into pk
 insert into fk
end;
would leave you with one row in each, rather than a row in pk
and none in fk or one in pk and two in fk.



Tue, 13 Nov 2001 15:55:04 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Foreign key referential actions 

Are there any TODO items here?

---------------------------------------------------------------------------

> 
> Right now, referential actions get deferred along with normal
> checks and run against the state of the database at that time.
> I think this violates SQL92 11.8 General Rules 4-6 and have some
> reasoning and proposed ideas towards making it more complient
> although I don't actually have an implementation in mind for
> the most correct version. :(
> 
> Here are my interpretations:
> 
> 	GR 4 says that the matching rows (unique and non-unique)
> are determined immediately before the execution of an SQL
> statement.  We can ignore the fluff about non-unique matching
> rows for now because I believe that applies to match partial only.
> 	GR 5 says when there's a delete rule and a row of the
> referenced table is marked for deletion (if it's not already
> marked such) then do something based on the action, for example
> mark matching rows for deletion if it is cascade.  This seems
> to imply the action is supposed to occur immediately, since
> AFAICS the rows aren't marked for deletion on the commit but
> rather on the delete itself.
> 	GR 6 seems to be pretty much the same for update.
> 
> I think the correct course of action would be if I'm right:
> *Make referential actions (other than no action) not deferrable
>   and thus initially immediate.  This means that you see the
>   cascaded (or nulled or defaulted) results immediately, but
>   I think that satisfies GRs 5 and 6.  It also makes the
>   problems of what we can see a little less problematic, but
>   doesn't quite cure them.
> *To fix the visibility issues I think we'd need to be able to
>   see what rows matched immediately before the statement and
>   then reference those rows later, even if the values that we're
>   keying on have changed.  I'm really not sure how we'd do
>   this without a great deal of extra work.
>   An intermediate step towards complience would probably
>   be making sure the row existed before this statement
>   (I think for the fk constraints this means if it was
>    created by another statement or a command before this
>    one) which is wrong if a row that matched before this
>   statement was modified by this statement to a new value
>   that we won't match.  Most of these cases would be errors
>   by sql anyway (I think these'd probably be real triggered
>   data change violations) and would be wrong by our current
>   implementation as well.
> 
> I'm not sure that the intermediate step on the second is
> actually worthwhile over just waiting and trying to do it
> right, but if I'm right in what it takes, it's reasonably
> minimal.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 21 Nov 2001 21:28:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Foreign key referential actions

On Wed, 21 Nov 2001, Bruce Momjian wrote:

> Are there any TODO items here?

No.  Tom and I don't agree on the spec's meaning for this and noone
else has really jumped in that I saw.


Wed, 21 Nov 2001 23:00:02 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Foreign key referential actions