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

RI status report #1

From: wieck(at)debis(dot)com (Jan Wieck)
To: pgsql-hackers(at)postgreSQL(dot)org (PostgreSQL HACKERS)
Subject: RI status report #1
Date: 1999-09-27 18:42:07
Message-ID: (view raw or flat)
Lists: pgsql-hackers
Hi all,

    just  to  give  anyone  a  chance  to  complain,  I'd like to
    describe what I plan to implement for v6.6 in the referential
    integrity (RI) corner.

    1. What will be supported

       I'm concentrating on FOREIGN KEY support. From the general
       definition (thanks to Vadim for the SQL3 draft):

           [ CONSTRAINT constraint-name ] FOREIGN KEY ( column [, ...] )
                     REFERENCES [ PENDANT ] table-name [ ( column [, ...] ) ]
                     [ MATCH { FULL | PARTIAL } ]
                     [ ON DELETE <referential-action> ]
                     [ ON UPDATE <referential-action> ]
                     [ [ NOT ] DEFERRABLE ]
                     [ INITIALLY { IMMEDIATE | DEFERRED } ]

           <referential-action> ::=
                     | SET NULL
                     | SET DEFAULT
                     | RESTRICT
                     | NO ACTION

       I'll omit the following parts on the first go:

           MATCH               (match type is allways FULL)

       The implementation  of  referential-actions  will  require
       that  the  columns  in the referenced table build a unique
       key. It will not be guaranteed, that an appropriate unique
       index exists.

       The  support  for  the  SET  DEFAULT action depends on the
       smartness of the generic trigger procedure described later
       - so that detail might be left unsupported too in v6.6.

    2. Implementation

       As  previous discussions turned out, the rule system isn't
       adequate  for  implementing  deferred   constraints   with
       respect  to  all their side effects.  Thus, RI constraints
       will be implemented by specialized trigger procedures.

       Therefore, a bunch of new attributes and some indices  are
       added to the pg_trigger system catalog. These are required
       to tell constraints from real triggers, automatically drop
       referential-action  constraints  from referenced tables if
       the referencing table is dropped and to  hold  information
       about  deferrability and initially deferred states for the

       The procedures will finally get implemented as builtin,  C
       language, generic functions.

    3. What I have so far

       I've added the following attributes to pg_trigger:

       tgenabled           A  bool that is designed to switch off
                           a  regular  trigger  with  the   ALTER
                           TRIGGER  command.  This is not related
                           to RI and I'm not actually planning on
                           implementing the parser/utility stuff.

       tgisconstraint      A bool that tells a constraint from  a

       tgconstrname        The   NAME   of   the  constraint.  RI
                           constraint    triggers     will     be
                           automatically  inserted  during CREATE
                           TABLE     with      trigger      names
                           _RI_Fkey_Constraint_<n>  so  that they
                           are unique "triggers" per table.  This
                           attribute  (indexed)  holds  the  real
                           constraint name for SET CONSTRAINT.

       tgconstrrelid       The  OID   of   the   opposite   table
                           (indexed).  In  the  constraints  that
                           check foreign key existance, it's  the
                           Oid  of  the  referenced table. In the
                           constraints that do  the  referential-
                           actions,   it's   the   Oid   of   the
                           referenced table.  This Oid is used to
                           quickly   drop   triggers   from   the
                           opposite table in  the  case  of  DROP

       tgdeferrable        A  bool  telling if the constraint can
                           be set to DEFERRED checking.

       tginitdeferred      A bool telling if the constraint is in
                           DEFERRED state by default.

       To  commands/trigger.c  I've  added a few hundred lines of
       code. All AFTER ROW IMMEDIATE triggers are executed  after
       the  entire  query.  DEFERRED triggers are executed at SET

       To the time qualification  code  I've  added  SnapshotAny.
       Since  I know the exact CTID of the tuple WHICH IS OLD/NEW
       for the event in question, this  new  snapshot  completely
       ignores any time qualification and fetches it.

       What I see so far from the tests, anything (except for the
       damned funny_dup17  reported  earlier)  still  works.  And
       setting  triggers  to deferred execution solves the cyclic
       integrity problems  which  are  the  reason  for  deferred
       execution. So I assume I'm on the right track.

    4. Next steps

       First  I  need to implement the SET CONSTRAINTS command in
       the parser and utility stuff now.

       Second I'll write the generic trigger procs in PL/Tcl that
       don't  use  prepared SPI plans (it's easiest to do it this
       way). When they work as needed by the implementation, I'll
       write   down  the  specs  and  ask  the  co-developers  to
       implement  their  high  quality,  plan  saving  C-language
       equivalents. Sorry, but all co-developers therefore should
       at least compile in PL/Tcl support.

       All the parser/utility stuff must be written that  handles
       constraint  trigger  creation/dropping  during CREATE/DROP
       table.  And  all  the  new  features/definitions  must  be
       adapted to pg_dump and psql.

       Finally  the  deferred  trigger  manager  must buffer huge
       amounts of trigger events (actually collected  in  memory)
       out onto disk.

       Most  of  the  activities  after "Second" next step can be
       done parallel.  I'll commit my changes after that, because
       then  I'm  able to run a full test of deferred constraints
       to be  sure  I'm  really  on  the  right  track.  All  co-
       developers can join then using the CURRENT tree.

       Any comments?



# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #


pgsql-hackers by date

Next:From: Bruce MomjianDate: 1999-09-27 18:52:06
Subject: Re: [GENERAL] Error during 'vacuum analyze'
Previous:From: Bruce MomjianDate: 1999-09-27 18:33:06
Subject: Re: [HACKERS] vacuum process size

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