Re: RI oddness

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