Thread
-
Deferred trigger queue
Jan Wieck <wieck@debis.com> — 2000-02-08T15:54:31Z
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) # -
Re: [HACKERS] Deferred trigger queue
Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-08T16:41:58Z
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
-
Re: [HACKERS] Deferred trigger queue
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-08T17:55:31Z
> 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 -
Re: [HACKERS] Deferred trigger queue
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-08T18:17:41Z
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 -
Re: [HACKERS] Deferred trigger queue
Jan Wieck <wieck@debis.com> — 2000-02-08T19:33:16Z
> 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) # -
Re: [HACKERS] Deferred trigger queue
Jan Wieck <wieck@debis.com> — 2000-02-08T19:45:37Z
> 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) # -
Re: Deferred trigger queue
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-09T12:12:52Z
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
-
Re: Deferred trigger queue
Jan Wieck <janwieck@t-online.de> — 2000-06-10T19:30:21Z
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 # -
Re: Deferred trigger queue
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-10T22:11:20Z
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