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(at)Yahoo(dot)com #

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