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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026