Skip site navigation (1) Skip section navigation (2)

Re: RI oddness

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jan Wieck <JanWieck(at)Yahoo(dot)com>, Max Khon <fjoe(at)iclub(dot)nsu(dot)ru>, PostgreSQL HACKERS <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: RI oddness
Date: 2001-04-26 18:40:50
Message-ID: 200104261840.NAA03314@jupiter.jw.home (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)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(at)Yahoo(dot)com #



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


In response to

pgsql-hackers by date

Next:From: Joel BurtonDate: 2001-04-26 18:42:31
Subject: Re: unanswered: Schema Issue
Previous:From: V. M.Date: 2001-04-26 18:04:14
Subject: Re: unanswered: Schema Issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group