Thread: Deferred trigger queue
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) #
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
> 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
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
> 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 fora 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) #
> 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 starton 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) #
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, Pennsylvania19026
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 #
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, Pennsylvania19026