Re: RI oddness - Mailing list pgsql-hackers

Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     What'd be easy is this:
>
> >     -   We already have two entry points for INSERT/UPDATE on  FK
> >         table, but the one for UPDATE is fortunately unused.
>
> >     -   We  change  analyze.c  to  install  the RI_FKey_check_upd
> >         trigger if the constraint has an ON  DELETE  SET  DEFAULT
> >         clause.  Otherwise  it  uses RI_FKey_check_ins as it does
> >         now.
>
> Unfortunately, such a fix really isn't going to fly as a patch release.
> Not only does it not work for existing tables, but it won't work for
> tables created by dump and reload from a prior version (since they
> won't have the right set of triggers ... another illustration of why
> the lack of an abstract representation of the RI constraints was a
> Bad Move).  In fact I'm afraid that your proposed change would actively
> break tables imported from a prior version; wouldn't RI_FKey_check_ins
> do the wrong thing if applied as an update trigger?
>
> >     I  think  the  usage  of ON DELETE SET DEFAULT is a very rare
> >     case out in the field. Thus the  dump/reload  requirement  is
> >     limited  to  a small number of databases (if any).
>
> But dump/reload won't fix the tables' triggers.
   Ech - you're right. It wouldn't fix 'em.

>
> Given that ON DELETE SET DEFAULT isn't used much, I think we should
> not waste time creating an incomplete hack solution for 7.1.*, but
> just write it off as a known bug and move forward with a real solution
> for 7.2.
   It's  not  the  rarely used ON DELETE SET DEFAULT case that's   currently broken. It's ALL the other cases  that
can easily   cause  you  to end up in deadlocks if you just update another   field in a table having foreign keys and
youdon't  lock  all   referenced rows properly first.  Given the table:
 
       CREATE TABLE sample (           a integer   REFERENCES t1,           b integer   REFERENCES t2,           c
integer  REFERENCES t3,           d integer   REFERENCES t4,           data text       );
 
   you'd  have to SELECT ... FOR UPDATE tables t1, t2, t3 and t4   (while NOT having a lock on "sample") before you
can safely   update  "data". Otherwise, another transaction could lock one   of those and try to lock your "sample" row
and  you  have  a   deadlock.
 
   We  could  provide  another script fixing it. It is run after   the restore of a dump taken from a pre-7.1.1
database fixing   the  tgfoid  for  those  triggers  that use RI_FKey_check_ins   where a matching
RI_FKey_setdefault_delrow exist  with  same   arguments and constraint name.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com