Re: Foreign key referential actions - Mailing list pgsql-hackers

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,
Pennsylvania19026