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> ::=
                       CASCADE
                     | SET NULL
                     | SET DEFAULT
                     | RESTRICT
                     | NO ACTION

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

           PENDANT
           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
       constraints.

       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
                           trigger.

       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
                           TABLE.

       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
       CONSTRAINTS ... IMMEDIATE or at COMMIT.

       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?


Jan

--

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



Mon, 27 Sep 1999 20:42:07 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
RI status report #1
>        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?

Great.  How's that for a comment?  :-)


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 27 Sep 1999 15:31:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RI status report #1
wieck@debis.com (Jan Wieck) writes:
>     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.

Jan, I have no comments about the RI features, but I am a little worried
about not creating big headaches in merging different changes.  Can we
work out a schedule that will minimize tromping on each others' toes?

I am in the middle of some fairly extensive revisions in
rewriteManip.c, rewriteHandler.c, and ruleutils.c --- basically,
fixing all the routines that recurse through expression trees to use
expression_tree_walker and expression_tree_mutator, for a big space
savings and elimination of a bunch of routine-X-doesn't-handle-node-
type-Y bugs.  Also I'm going to fix the rule deparser to use a
stringinfo buffer so it doesn't have any hardwired limits on the textual
length of a rule.  And I think I know how to fix some of the problems
with aggregates in subselects, like this one:

create table t1 (name text, value float8);
CREATE
select name from t1 where name IN
(select name from t1 group by name having 2 = count(*));
ERROR:  SELECT/HAVING requires aggregates to be valid

(It looks to me like some of the routines recurse into subselects when
they shouldn't.  It's a lot easier to see that sort of issue when
there's no code left except the actual Var manipulation and the
nonstandard recursion decisions ;-).)

I intended to finish these up in the next few days and commit them,
but if you've already started major hacking in these files then maybe
we should find another way.

Also, I believe Thomas is in the middle of wide-ranging revisions in
the parser, so you'd better coordinate with him on touching that area.

			regards, tom lane

Mon, 27 Sep 1999 20:06:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] RI status report #1 
Tom Lane wrote:

> wieck@debis.com (Jan Wieck) writes:
> >     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.
>
> Jan, I have no comments about the RI features, but I am a little worried
> about not creating big headaches in merging different changes.  Can we
> work out a schedule that will minimize tromping on each others' toes?

    To  avoid  this  kind of trouble is one of my reasons for the
    status report. So others can see which areas will be affected
    and we can coordinate a little.

> I am in the middle of some fairly extensive revisions in
> rewriteManip.c, rewriteHandler.c, and ruleutils.c --- basically,

    My changes absolutely don't touch the rule system. Except for
    a few lines in tcop, transam and tqual all the work  is  done
    in trigger.c.

> I intended to finish these up in the next few days and commit them,
> but if you've already started major hacking in these files then maybe
> we should find another way.

    Do  it  -  I'll wait for you (would you please give me a sign
    then). But I'm 97.5% sure our work has no collision areas.

> Also, I believe Thomas is in the middle of wide-ranging revisions in
> the parser, so you'd better coordinate with him on touching that area.

    Ah - that's more critical.  I  just  began  to  add  the  SET
    CONSTRAINTS  command  and  am through with thinking about the
    CREATE CONSTRAINT TRIGGER too. We all know that our parser is
    a  very delicate peace of software.  Thomas, could you please
    comment on this?


Jan

--

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



Tue, 28 Sep 1999 13:58:09 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] RI status report #1
> > Also, I believe Thomas is in the middle of wide-ranging revisions in
> > the parser, so you'd better coordinate with him on touching that area.
>     Ah - that's more critical.  I  just  began  to  add  the  SET
>     CONSTRAINTS  command  and  am through with thinking about the
>     CREATE CONSTRAINT TRIGGER too. We all know that our parser is
>     a  very delicate peace of software.  Thomas, could you please
>     comment on this?

At the moment I am working on join *syntax*, so my changes are
isolated to gram.y, analyze.c, parse_clause.c, and parse_target.c.
Don't wait for me; I'll bet that we don't collide much, and if we do I
don't mind doing the merge.

Sometime later, once I understand the syntax and have it coded for
inner joins, I'll want to start modifying the parser and planner to
handle outer joins. At that point, I'll be asking for help and advice,
and look forward to your input. But I'm not there yet.

I'm hoping to have some updates committed in a week or so, but things
have been going very slowly with little time to work on this :(

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Tue, 28 Sep 1999 13:43:00 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] RI status report #1
On Mon, 27 Sep 1999, Bruce Momjian wrote:

> >        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?
> 
> Great.  How's that for a comment?  :-)

Damn, I was gonna say that but figured it wasn't enough...:)


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 29 Sep 1999 18:15:35 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] RI status report #1
wieck@debis.com (Jan Wieck) writes:
> Tom Lane wrote:
>> I am in the middle of some fairly extensive revisions in
>> rewriteManip.c, rewriteHandler.c, and ruleutils.c --- ...
>> I intended to finish these up in the next few days and commit them,
>> but if you've already started major hacking in these files then maybe
>> we should find another way.

>     Do  it  -  I'll wait for you (would you please give me a sign
>     then). But I'm 97.5% sure our work has no collision areas.

OK, I'm done with rewriter/ruleutils cleanups, at least for now.

			regards, tom lane

Sat, 02 Oct 1999 14:24:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] RI status report #1 