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

Re: RI oddness

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: 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 15:58:38
Message-ID: 200104261558.KAA02838@jupiter.jw.home (view raw, whole thread or download thread mbox)
Lists: 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

    -   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




# 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 address at

In response to


pgsql-hackers by date

Next:From: Marko KreenDate: 2001-04-26 16:13:59
Subject: Re: crypt(table.field) ?
Previous:From: Tom LaneDate: 2001-04-26 15:53:56
Subject: Re: scaling multiple connections

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