Hi,

    looking at all the complications about dealing with segmented
    files etc., I wonder if it's really worth the efford  to  add
    file buffering to the trigger queue.

    The  memory  footprint left by modifying a row where triggers
    have to be run is about 40 + 8 * num_triggers bytes.  So  for
    one   PK/FP   relationship,  it  will  be  48  bytes  per  FK
    inserted/updated or 48 bytes per PK updated/deleted.  If  one
    PK  table  has  multiple references to it, this will only add
    another 8 bytes to the  footprint.  Same  if  one  table  has
    multiple foreign keys defined.

    The  question now is, who ever attempts to act on millions of
    rows in one transaction, if referential integrity constraints
    are set up?

    Of  course,  if  someone  updates  millions  of rows in an RI
    scenario during one  transaction,  it  could  blow  away  the
    backend. But I'd prefer to leave this as a well known problem
    for 7.1 and better start on creating a good  regression  test
    and some documentation for it.

    Thomas, where should the documentation for FOREIGN KEY go?


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, 8 Feb 2000 16:54:31 +0100 (CET)
wieck@debis.com (Jan Wieck)
Deferred trigger queue
wieck@debis.com (Jan Wieck) writes:
>     looking at all the complications about dealing with segmented
>     files etc., I wonder if it's really worth the efford  to  add
>     file buffering to the trigger queue.

You shouldn't be thinking about that.  Use a BufFile (see
src/include/storage/buffile.h), and you have temp file creation,
file segmentation and auto cleanup at xact abort with no more work
than fopen/fwrite would be.  See nodeHash.c/nodeHashjoin.c for an
example of use.

>     Of  course,  if  someone  updates  millions  of rows in an RI
>     scenario during one  transaction,  it  could  blow  away  the
>     backend. But I'd prefer to leave this as a well known problem
>     for 7.1 and better start on creating a good  regression  test
>     and some documentation for it.

However, if you think that there are other tasks that are higher
priority than this one, I won't argue.

			regards, tom lane

Tue, 08 Feb 2000 11:41:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Deferred trigger queue 
>     Thomas, where should the documentation for FOREIGN KEY go?

Depends on what the docs look like. There should be some mention of
foreign keys in the CREATE TABLE reference page
(doc/sgml/ref/create_table.sgml) and there should be some mention of
it in the User's Guide. Eventually, we will probably have a full
chapter on it (and if you want just make a file doc/sgml/foreign.sgml
and we will start). If you don't want to do that yet, plop something
in syntax.sgml.

                  - Thomas

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

Tue, 08 Feb 2000 17:55:31 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Deferred trigger queue
And btw, I've got most of the regression tests passing with a first
cut at outer join syntax, but the rules system has breakage. Should be
OK after another pass through to clean up code, which is likely to
touch many files since a bit of the RTE structure changes.

I'd have gone ahead and committed, but figured that breaking foreign
keys would not be a step ahead for Jan ;)

                     - Thomas

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

Tue, 08 Feb 2000 18:17:41 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Deferred trigger queue
> And btw, I've got most of the regression tests passing with a first
> cut at outer join syntax, but the rules system has breakage. Should be
> OK after another pass through to clean up code, which is likely to
> touch many files since a bit of the RTE structure changes.
>
> I'd have gone ahead and committed, but figured that breaking foreign
> keys would not be a step ahead for Jan ;)

    FOREIGN  KEYs  aren't  related  to rules in any way. They are
    implemented as triggers. So break the rule system for a while
    if you feel the need.


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, 8 Feb 2000 20:33:16 +0100 (CET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Deferred trigger queue
> wieck@debis.com (Jan Wieck) writes:
> >     looking at all the complications about dealing with segmented
> >     files etc., I wonder if it's really worth the efford  to  add
> >     file buffering to the trigger queue.
>
> You shouldn't be thinking about that.  Use a BufFile (see
> src/include/storage/buffile.h), and you have temp file creation,
> file segmentation and auto cleanup at xact abort with no more work
> than fopen/fwrite would be.  See nodeHash.c/nodeHashjoin.c for an
> example of use.

    You  already  pointed  me to that long ago. Surely, something
    the like would be what to use in this case.

> However, if you think that there are other tasks that are higher
> priority than this one, I won't argue.

    It's not that I totally want to forget about  it.  It's  just
    that  I  think  with 7 days left until BETA I better start on
    stressing the code and providing some docs instead of  taking
    care for possible abuse.

    There  are  details that MUST be documented IMHO. For example
    FOREIGN KEY needs that there is a UNIQUE  constraint  defined
    on  the  set of referenced columns. Actually this requirement
    is not checked in any way, so it MUST  be  mentioned  in  the
    docs.


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, 8 Feb 2000 20:45:37 +0100 (CET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Deferred trigger queue
Jan, I have added to the TODO list:

	* Add deferred trigger queue file? (Jan)

Do you want this in there?

> Hi,
> 
>     looking at all the complications about dealing with segmented
>     files etc., I wonder if it's really worth the efford  to  add
>     file buffering to the trigger queue.
> 
>     The  memory  footprint left by modifying a row where triggers
>     have to be run is about 40 + 8 * num_triggers bytes.  So  for
>     one   PK/FP   relationship,  it  will  be  48  bytes  per  FK
>     inserted/updated or 48 bytes per PK updated/deleted.  If  one
>     PK  table  has  multiple references to it, this will only add
>     another 8 bytes to the  footprint.  Same  if  one  table  has
>     multiple foreign keys defined.
> 
>     The  question now is, who ever attempts to act on millions of
>     rows in one transaction, if referential integrity constraints
>     are set up?
> 
>     Of  course,  if  someone  updates  millions  of rows in an RI
>     scenario during one  transaction,  it  could  blow  away  the
>     backend. But I'd prefer to leave this as a well known problem
>     for 7.1 and better start on creating a good  regression  test
>     and some documentation for it.
> 
>     Thomas, where should the documentation for FOREIGN KEY go?
> 
> 
> 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
  pgman@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

Fri, 9 Jun 2000 08:12:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Deferred trigger queue
Bruce Momjian wrote:
> Jan, I have added to the TODO list:
> 
> 	* Add deferred trigger queue file? (Jan)
> 
> Do you want this in there?

    Yes.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Sat, 10 Jun 2000 21:30:21 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Deferred trigger queue
Added.

> Bruce Momjian wrote:
> > Jan, I have added to the TODO list:
> > 
> > 	* Add deferred trigger queue file? (Jan)
> > 
> > Do you want this in there?
> 
>     Yes.
> 
> 
> Jan
> 
> -- 
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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

Sat, 10 Jun 2000 18:11:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Deferred trigger queue