Re: RI oddness - Mailing list pgsql-hackers

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
notchange       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
thisclause 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