Hi,

    I  just  got  trapped  by  one  of  my  own  features  in the
    referential integrity area.

    The problem is, that the trigger run on the FK row at  UPDATE
    allways  checks  and  locks the referenced PK, even if the FK
    attributes didn't change. That's because if there'd be an  ON
    DELETE  SET  DEFAULTS  and someone deletes a PK consisting of
    all the FK's column defaults, we wouldn't notice and  let  it
    pass through.

    The bad thing on it is now, if I have one XACT that locks the
    PK row first, then locks the FK row, and I have another  XACT
    that  just want's to update another field in the FK row, that
    second XACT must lock the PK row in the first place  or  this
    entire  thing leads to deadlocks. If one table has alot of FK
    constraints, this causes not really wanted lock contention.

    The clean way to get out of it would be to skip non-FK-change
    events in the UPDATE trigger and do alot of extra work in the
    SET DEFAULTS trigger.  Actually it'd be  to  check  if  we're
    actually  deleting  the FK defaults values from the PK table,
    and if so we'd have to check if  references  exist  by  doing
    another NO ACTION kinda test.

    Any other smart idea?


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


Mon, 23 Apr 2001 14:55:01 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
RI oddness
hi, there!

On Mon, 23 Apr 2001, Jan Wieck wrote:

>     I  just  got  trapped  by  one  of  my  own  features  in the
>     referential integrity area.
> 
>     The problem is, that the trigger run on the FK row at  UPDATE
>     allways  checks  and  locks the referenced PK, even if the FK
>     attributes didn't change. That's because if there'd be an  ON
>     DELETE  SET  DEFAULTS  and someone deletes a PK consisting of
>     all the FK's column defaults, we wouldn't notice and  let  it
>     pass through.
> 
>     The bad thing on it is now, if I have one XACT that locks the
>     PK row first, then locks the FK row, and I have another  XACT
>     that  just want's to update another field in the FK row, that
>     second XACT must lock the PK row in the first place  or  this
>     entire  thing leads to deadlocks. If one table has alot of FK
>     constraints, this causes not really wanted lock contention.
> 
>     The clean way to get out of it would be to skip non-FK-change
>     events in the UPDATE trigger and do alot of extra work in the
>     SET DEFAULTS trigger.  Actually it'd be  to  check  if  we're
>     actually  deleting  the FK defaults values from the PK table,
>     and if so we'd have to check if  references  exist  by  doing
>     another NO ACTION kinda test.
> 
>     Any other smart idea?

read-write locks?

/fjoe


Tue, 24 Apr 2001 19:34:08 +0700 (NSS)
Max Khon <fjoe@iclub.nsu.ru>
Re: RI oddness
Max Khon wrote:
> hi, there!
>
> On Mon, 23 Apr 2001, Jan Wieck wrote:
>
> >     I  just  got  trapped  by  one  of  my  own  features  in the
> >     referential integrity area.
> >
> >     The problem is, that the trigger run on the FK row at  UPDATE
> >     allways  checks  and  locks the referenced PK, even if the FK
> >     attributes didn't change. That's because if there'd be an  ON
> >     DELETE  SET  DEFAULTS  and someone deletes a PK consisting of
> >     all the FK's column defaults, we wouldn't notice and  let  it
> >     pass through.
> >
> >     The bad thing on it is now, if I have one XACT that locks the
> >     PK row first, then locks the FK row, and I have another  XACT
> >     that  just want's to update another field in the FK row, that
> >     second XACT must lock the PK row in the first place  or  this
> >     entire  thing leads to deadlocks. If one table has alot of FK
> >     constraints, this causes not really wanted lock contention.
> >
> >     The clean way to get out of it would be to skip non-FK-change
> >     events in the UPDATE trigger and do alot of extra work in the
> >     SET DEFAULTS trigger.  Actually it'd be  to  check  if  we're
> >     actually  deleting  the FK defaults values from the PK table,
> >     and if so we'd have to check if  references  exist  by  doing
> >     another NO ACTION kinda test.
> >
> >     Any other smart idea?
>
> read-write locks?

    Just  discussed  it  with  Tom  Lane  while he'd been here in
    Norfolk and it's even more ugly. We couldn't  even  pull  out
    the  FK's  column  defaults  at  this time to check if we are
    about to delete the corresponding PK because they might  call
    all  kinds  of  functions  with tons of side effects we don't
    want.

    Seems the only way to do it cleanly is  to  have  the  parser
    putting  the  information  which TLEs are *OLD* and which are
    *NEW* somewhere and pass it all  down  through  the  executor
    (remembering it per tuple in the deferred trigger queue) down
    into the triggers.


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


Tue, 24 Apr 2001 15:40:37 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: RI oddness
Jan Wieck wrote:
>     Just  discussed  it  with  Tom  Lane  while he'd been here in
>     Norfolk and it's even more ugly. We couldn't  even  pull  out
>     the  FK's  column  defaults  at  this time to check if we are
>     about to delete the corresponding PK because they might  call
>     all  kinds  of  functions  with tons of side effects we don't
>     want.
>
>     Seems the only way to do it cleanly is  to  have  the  parser
>     putting  the  information  which TLEs are *OLD* and which are
>     *NEW* somewhere and pass it all  down  through  the  executor
>     (remembering it per tuple in the deferred trigger queue) down
>     into the triggers.

    While  we  know about the *right* way to fix it, that's a far
    too big of a change for 7.1.1.   But  I'd  like  to  fix  the
    likely deadlocks caused by referential integrity constraints.

    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.

    -   We change ri_triggers.c so  that  RI_FKey_check_ins  will
        skip  the  PK  check  if the FK attributes did not change
        while RI_FKey_check_upd will enforce the check allways.

    This way it'll  automatically  gain  a  performance  win  for
    everyone using referential integrity.

    The  bad  side  effect  is, that these changes will require a
    dump/reload FOR DATABASES, where ON  DELETE  SET  DEFAULT  is
    used.  If  they don't dump/reload, it'll open the possibility
    of violating constraints  that  are  defined  ON  DELETE  SET
    DEFAULT  by  deleting  the  PK  that  consists  of the column
    defaults of  an  existing  FK  reference.  The  DELETE  would
    succeed and the stall references remain.

    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). It is easy
    to detect if a DB's schema contains this clause by looking up
    pg_trigger  for  usage  of  RI_FKey_setdefault_del.  We could
    provide  a  small  script  telling   which   databases   need
    dump/reload.

    Comments?


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


Thu, 26 Apr 2001 10:58:38 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: RI oddness
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.

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.

			regards, tom lane

Thu, 26 Apr 2001 12:41:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RI oddness 
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 you don'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_del row 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


Thu, 26 Apr 2001 13:40:50 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: RI oddness