ATTENTION: catalog changes - initdb required!

    General  support for deferred constraint triggers is finished
    and committed to CURRENT tree.


    Implemented so far:

        CREATE CONSTRAINT TRIGGER <constraint_name>
            AFTER <event> ON <relation_name>
            [ FROM <referencing_relation_name> ]
            [ [ NOT ] DEFERRABLE ]
            [ INITIALLY { IMMEDIATE | DEFERRED } ]
            FOR EACH ROW EXECUTE PROCEDURE <procedure_name> ( <args> )

        SET CONSTRAINTS { <constraint_names> | ALL } { IMMEDIATE | DEFERRED }

    Details on CREATE CONSTRAINT TRIGGER:

        <constraint_name>

            Can  be  a  usual  identifier  or  ""   for   unnamed
            constraints.  Since the same constraint can result in
            multiple pg_trigger  entries  for  different  tables,
            there's  no check for duplicates. This is the name to
            later identify constraints in SET CONSTRAINTS.

        FROM <referencing_relation_name>

            If given, causes that this trigger are  automatically
            removed  when  the  referencing  relation is dropped.
            This is useful for referential action triggers  (like
            ON DELETE CASCADE), which are fired on changes to the
            PK table. Dropping the FK table without removing  the
            triggers from the PK table would make it unusable.

        [ NOT ] DEFERRABLE

            Specifies  if  the  trigger  is  deferrable  or  not.
            Defaults to NOT DEFERRABLE if INITIALLY is IMMEDIATE.
            Defaults to DEFERRABLE if INITIALLY is DEFERRED.

        INITIALLY { IMMEDIATE | DEFERRED }

            Specifies  the  deferred  state  of  the  trigger  at
            session start.  Defaults to IMMEDIATE.

        <procedure_name> ( <args> )

            The usual trigger procedure definition.

        The trigger itself in pg_trigger is created with a tgname
        of  RI_ConstraintTrigger_<newoid>, which should be unique
        enough.

    Details on SET CONSTRAINTS:

        <constraint_names>

            A comma separated list of constraint identifiers.  An
            attempt to set named constraints to DEFERRED where at
            least one of the pg_trigger entries  with  this  name
            isn't deferrable raises an ERROR.

            Using   ALL   with   DEFERRED   sets  all  deferrable
            constraint triggers (named and unnamed) to  deferred,
            leaving not deferrable ones immediate.

        If SET CONSTRAINTS is used outside of a transaction block
        (BEGIN/COMMIT), it sets the default behaviour on  session
        level.  All  constraint  triggers  begin each transaction
        (explicit block or implicit single  statement)  in  these
        states.

        All  AFTER  ROW  triggers (regular ones) are treated like
        IMMEDIATE constraint triggers now so they  are  fired  at
        the  end  of  the  entire statement instead of during it.
        This  interfered  with  the  funny_dup17  test   in   the
        regression suite which is commented out now.

        Trigger events for deferred triggers are condensed during
        a  transaction.   That  means,  that  executing  multiple
        UPDATE  commands  affecting  the  same  row would finally
        invoke only one trigger call which receives the  original
        tuple  (before  BEGIN)  as OLD and the final tuple (after
        last UPDATE) as NEW. Similar INSERT/DELETE  of  same  row
        will fire no trigger at all.

        There are checks done if IMMEDIATE or BEFORE ROW triggers
        have already been fired when a row  is  touched  multiple
        times in the same transaction.  In that case, an error is
        raised because this might violate referential  integrity.

        Needless  to  say  that  COMMIT  causes  an  implicit SET
        CONSTRAINTS ALL IMMEDIATE.  All deferred triggers are run
        then, so COMMIT could raise trigger generated errors now!

    Next we need:

    1.  Generic trigger procs that are argument driven. I'll make
        a separate thread for this topic.

    2.  Support  in  CREATE  TABLE  that  issues  the appropriate
        CREATE CONSTRAINT TRIGGER statements for FOREIGN  KEY  in
        the  same manner as CREATE INDEX for PRIMARY KEY is done.
        This must wait until we have an accepted  call  interface
        for the generic trigger procs from 1..

    3.  Support for pg_dump to emit the correct CREATE CONSTRAINT
        TRIGGER statements.  Who wants to pick up this topic?

    4.  Add the ability to swap  out  huge  amounts  of  deferred
        trigger  events  to disk (actually I'm collecting them in
        memory - so large transactions affecting millions of rows
        of  a table where triggers are defined are likely to blow
        up the backend).  This is my topic - sorry.

    5.  Write a regression test for the new FOREIGN KEY  support.
        Surely an important thing but one of the last steps after
        anything else works properly.

    6.  Remove the "not supported yet" note for FOREIGN KEY  from
        the  docs  along  with  correcting  to  the  full  syntax
        supported finally :-)

    Hmmmm - the more I work on it the longer the TODO becomes.


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) #



Wed, 29 Sep 1999 19:10:31 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
RI status report #2
Bruce Momjian wrote:
> 
> 
> Man, that's a heap of additions.

    Only the top of the iceberg :-)


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) #


Wed, 29 Sep 1999 19:30:50 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] RI status report #2

Man, that's a heap of additions.

> ATTENTION: catalog changes - initdb required!
> 
>     General  support for deferred constraint triggers is finished
>     and committed to CURRENT tree.
> 
> 
>     Implemented so far:
> 
>         CREATE CONSTRAINT TRIGGER <constraint_name>
>             AFTER <event> ON <relation_name>
>             [ FROM <referencing_relation_name> ]
>             [ [ NOT ] DEFERRABLE ]
>             [ INITIALLY { IMMEDIATE | DEFERRED } ]
>             FOR EACH ROW EXECUTE PROCEDURE <procedure_name> ( <args> )
> 
>         SET CONSTRAINTS { <constraint_names> | ALL } { IMMEDIATE | DEFERRED }
> 
>     Details on CREATE CONSTRAINT TRIGGER:
> 
>         <constraint_name>
> 
>             Can  be  a  usual  identifier  or  ""   for   unnamed
>             constraints.  Since the same constraint can result in
>             multiple pg_trigger  entries  for  different  tables,
>             there's  no check for duplicates. This is the name to
>             later identify constraints in SET CONSTRAINTS.
> 
>         FROM <referencing_relation_name>
> 
>             If given, causes that this trigger are  automatically
>             removed  when  the  referencing  relation is dropped.
>             This is useful for referential action triggers  (like
>             ON DELETE CASCADE), which are fired on changes to the
>             PK table. Dropping the FK table without removing  the
>             triggers from the PK table would make it unusable.
> 
>         [ NOT ] DEFERRABLE
> 
>             Specifies  if  the  trigger  is  deferrable  or  not.
>             Defaults to NOT DEFERRABLE if INITIALLY is IMMEDIATE.
>             Defaults to DEFERRABLE if INITIALLY is DEFERRED.
> 
>         INITIALLY { IMMEDIATE | DEFERRED }
> 
>             Specifies  the  deferred  state  of  the  trigger  at
>             session start.  Defaults to IMMEDIATE.
> 
>         <procedure_name> ( <args> )
> 
>             The usual trigger procedure definition.
> 
>         The trigger itself in pg_trigger is created with a tgname
>         of  RI_ConstraintTrigger_<newoid>, which should be unique
>         enough.
> 
>     Details on SET CONSTRAINTS:
> 
>         <constraint_names>
> 
>             A comma separated list of constraint identifiers.  An
>             attempt to set named constraints to DEFERRED where at
>             least one of the pg_trigger entries  with  this  name
>             isn't deferrable raises an ERROR.
> 
>             Using   ALL   with   DEFERRED   sets  all  deferrable
>             constraint triggers (named and unnamed) to  deferred,
>             leaving not deferrable ones immediate.
> 
>         If SET CONSTRAINTS is used outside of a transaction block
>         (BEGIN/COMMIT), it sets the default behaviour on  session
>         level.  All  constraint  triggers  begin each transaction
>         (explicit block or implicit single  statement)  in  these
>         states.
> 
>         All  AFTER  ROW  triggers (regular ones) are treated like
>         IMMEDIATE constraint triggers now so they  are  fired  at
>         the  end  of  the  entire statement instead of during it.
>         This  interfered  with  the  funny_dup17  test   in   the
>         regression suite which is commented out now.
> 
>         Trigger events for deferred triggers are condensed during
>         a  transaction.   That  means,  that  executing  multiple
>         UPDATE  commands  affecting  the  same  row would finally
>         invoke only one trigger call which receives the  original
>         tuple  (before  BEGIN)  as OLD and the final tuple (after
>         last UPDATE) as NEW. Similar INSERT/DELETE  of  same  row
>         will fire no trigger at all.
> 
>         There are checks done if IMMEDIATE or BEFORE ROW triggers
>         have already been fired when a row  is  touched  multiple
>         times in the same transaction.  In that case, an error is
>         raised because this might violate referential  integrity.
> 
>         Needless  to  say  that  COMMIT  causes  an  implicit SET
>         CONSTRAINTS ALL IMMEDIATE.  All deferred triggers are run
>         then, so COMMIT could raise trigger generated errors now!
> 
>     Next we need:
> 
>     1.  Generic trigger procs that are argument driven. I'll make
>         a separate thread for this topic.
> 
>     2.  Support  in  CREATE  TABLE  that  issues  the appropriate
>         CREATE CONSTRAINT TRIGGER statements for FOREIGN  KEY  in
>         the  same manner as CREATE INDEX for PRIMARY KEY is done.
>         This must wait until we have an accepted  call  interface
>         for the generic trigger procs from 1..
> 
>     3.  Support for pg_dump to emit the correct CREATE CONSTRAINT
>         TRIGGER statements.  Who wants to pick up this topic?
> 
>     4.  Add the ability to swap  out  huge  amounts  of  deferred
>         trigger  events  to disk (actually I'm collecting them in
>         memory - so large transactions affecting millions of rows
>         of  a table where triggers are defined are likely to blow
>         up the backend).  This is my topic - sorry.
> 
>     5.  Write a regression test for the new FOREIGN KEY  support.
>         Surely an important thing but one of the last steps after
>         anything else works properly.
> 
>     6.  Remove the "not supported yet" note for FOREIGN KEY  from
>         the  docs  along  with  correcting  to  the  full  syntax
>         supported finally :-)
> 
>     Hmmmm - the more I work on it the longer the TODO becomes.
> 
> 
> 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) #
> 
> 
> 
> ************
> 


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

Wed, 29 Sep 1999 13:30:55 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RI status report #2
> Bruce Momjian wrote:
> > 
> > 
> > Man, that's a heap of additions.
> 
>     Only the top of the iceberg :-)

Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
that I thought 6.6 would be a small, incremental release after the
changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
earlier releases.

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

Wed, 29 Sep 1999 13:51:50 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RI status report #2
>
> > Bruce Momjian wrote:
> > >
> > >
> > > Man, that's a heap of additions.
> >
> >     Only the top of the iceberg :-)
>
> Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> that I thought 6.6 would be a small, incremental release after the
> changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> earlier releases.

    Wasn't  it  YOU  who  asked  ME to become active again?  Your
    above thought is a  little  silly  if  ya  really  wanted  to
    interrupt my sleep mode ;-)

    OTOH   Vadim   is   close  to  WAL  and  I  see  activity  on
    (outer/left/right?) join support too. Maybe there wouldn't be
    a v6.6 at all.

    WAL is IMHO the only real reason not to choose PostgreSQL for
    production.  Beeing able to recover  (roll  forward)  from  a
    backup  using  transaction  log  is  a  required  feature for
    mission critical data. Thus, having all  this  (WAL,  FOREIGN
    KEY  etc.)  is  a greater step forward that that between v6.4
    and v6.5.

    If all that really materializes in  our  next  release,  it's
    time to number it v7.0 - no?


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) #



Wed, 29 Sep 1999 21:02:28 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] RI status report #2
Bruce Momjian wrote:
 
> Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> that I thought 6.6 would be a small, incremental release after the
> changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> earlier releases.

And that surprises you??  Even in the short two years I've used
PostgreSQL, I have grown accustomed to major changes every major
version.  First there was the NOT NULL (and scads of other) features to
compel me to go from 6.1.1 to 6.2, then there were subselects (and
vastly improved documentation) to get me up to 6.3, then there were
views, rules, and the new protocol to make 6.4 a must-cc event, then
MVCC....  And now I'm maintaining RPM's so I can stay on the released
bleeding edge without breaking my server policies. Whoda thunk it?

Of course, my measly list above doesn't do the development justice -- as
one look at the changelog will show.

Lamar Owen
WGCR Internet Radio

Wed, 29 Sep 1999 15:08:38 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: [HACKERS] RI status report #2
> >
> > > Bruce Momjian wrote:
> > > >
> > > >
> > > > Man, that's a heap of additions.
> > >
> > >     Only the top of the iceberg :-)
> >
> > Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> > that I thought 6.6 would be a small, incremental release after the
> > changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> > earlier releases.
> 
>     Wasn't  it  YOU  who  asked  ME to become active again?  Your
>     above thought is a  little  silly  if  ya  really  wanted  to
>     interrupt my sleep mode ;-)

I specialize in silly.  :-)

Full-featured is good, much better than small, incremental.

I certainly interrupted your sleep mode.

> 
>     OTOH   Vadim   is   close  to  WAL  and  I  see  activity  on
>     (outer/left/right?) join support too. Maybe there wouldn't be
>     a v6.6 at all.

Do I read 7.0 in there?

>     WAL is IMHO the only real reason not to choose PostgreSQL for
>     production.  Beeing able to recover  (roll  forward)  from  a
>     backup  using  transaction  log  is  a  required  feature for
>     mission critical data. Thus, having all  this  (WAL,  FOREIGN
>     KEY  etc.)  is  a greater step forward that that between v6.4
>     and v6.5.
> 
>     If all that really materializes in  our  next  release,  it's
>     time to number it v7.0 - no?

Yes, I am starting to see 7.0 too.

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

Wed, 29 Sep 1999 17:03:43 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RI status report #2
> Bruce Momjian wrote:
>  
> > Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> > that I thought 6.6 would be a small, incremental release after the
> > changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> > earlier releases.
> 
> And that surprises you??  Even in the short two years I've used
> PostgreSQL, I have grown accustomed to major changes every major
> version.  First there was the NOT NULL (and scads of other) features to
> compel me to go from 6.1.1 to 6.2, then there were subselects (and
> vastly improved documentation) to get me up to 6.3, then there were
> views, rules, and the new protocol to make 6.4 a must-cc event, then
> MVCC....  And now I'm maintaining RPM's so I can stay on the released
> bleeding edge without breaking my server policies. Whoda thunk it?
> 
> Of course, my measly list above doesn't do the development justice -- as
> one look at the changelog will show.

Yes, it still shocks me.  I was telling Thomas, every release I think,
man, this is so great, no reason anyone should be using a prior release.
And then the next release is the same thing.

The basic issue for me is that each of the new features requsted looks
so hard, I can't imagine how it could be done, but by release time, it
does get done.  Amazing.

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

Wed, 29 Sep 1999 17:06:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RI status report #2
Bruce Momjian wrote:
> Lamr Owen wrote:
> > And that surprises you??  Even in the short two years I've used
> > PostgreSQL, I have grown accustomed to major changes every major
[snip]
 
> Yes, it still shocks me.  I was telling Thomas, every release I think,
> man, this is so great, no reason anyone should be using a prior release.
> And then the next release is the same thing.
> 
> The basic issue for me is that each of the new features requsted looks
> so hard, I can't imagine how it could be done, but by release time, it
> does get done.  Amazing.

I find the enthusiasm of this particular development quite infectious. 
While I'm only doing a very small part in packaging RPM's (thus far), I
feel quite good about it (it conjures back the same feeling that I had
at 15 years old when my Z80 disassembler first correctly disassembled
the opcodes of three-quarters of the instruction set -- no operands at
that time, but the opcode logic was WORKING... It felt uniquely
gratifying).  

Just reading the web page and the release notes doesn't do this
development justice -- until I subscribed to this hackers list, I had no
idea that PostgreSQL development was so dynamic.

This beats following the linux kernel development, IMO.

Lamar Owen
WGCR Internet Radio

Thu, 30 Sep 1999 11:46:01 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: [HACKERS] RI status report #2
On Wed, 29 Sep 1999, Bruce Momjian wrote:

> > Bruce Momjian wrote:
> > > 
> > > 
> > > Man, that's a heap of additions.
> > 
> >     Only the top of the iceberg :-)
> 
> Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> that I thought 6.6 would be a small, incremental release after the
> changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> earlier releases.

Have we ever had a "small, incremental release", other then the
minor-minor releases? :)

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


Sun, 3 Oct 1999 16:54:10 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] RI status report #2
On Wed, 29 Sep 1999, Jan Wieck wrote:

> >
> > > Bruce Momjian wrote:
> > > >
> > > >
> > > > Man, that's a heap of additions.
> > >
> > >     Only the top of the iceberg :-)
> >
> > Yikes.  I was just talking to Thomas Lockhart by phone, and was saying
> > that I thought 6.6 would be a small, incremental release after the
> > changes in 6.5.*.  Obviously, 6.6 is going to be as full-featured as
> > earlier releases.
> 
>     Wasn't  it  YOU  who  asked  ME to become active again?  Your
>     above thought is a  little  silly  if  ya  really  wanted  to
>     interrupt my sleep mode ;-)
> 
>     OTOH   Vadim   is   close  to  WAL  and  I  see  activity  on
>     (outer/left/right?) join support too. Maybe there wouldn't be
>     a v6.6 at all.
> 
>     WAL is IMHO the only real reason not to choose PostgreSQL for
>     production.  Beeing able to recover  (roll  forward)  from  a
>     backup  using  transaction  log  is  a  required  feature for
>     mission critical data. Thus, having all  this  (WAL,  FOREIGN
>     KEY  etc.)  is  a greater step forward that that between v6.4
>     and v6.5.
> 
>     If all that really materializes in  our  next  release,  it's
>     time to number it v7.0 - no?

I was kinda starting to wonder that one myself... my feeling: its time
guys.

We're still in no more rush to get it out the door...sometime 1st quarter
of year 2000, but with everything that has changed up until now, I think
its time we up'd the major version number ...


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


Sun, 3 Oct 1999 16:56:48 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] RI status report #2
On Wed, 29 Sep 1999, Bruce Momjian wrote:

> Yes, I am starting to see 7.0 too.

one motion, two second's...anyone disagree? *lifts mallet* 

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


Sun, 3 Oct 1999 16:57:30 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] RI status report #2