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

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

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

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

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