Re: Is Patch Ok for deferred trigger disk queue?

On Mon, 30 Jun 2003, Stephan Szabo wrote:

> On Mon, 30 Jun 2003, deststar wrote:
>
> > Hi,
> > I noticed the patch:
> > http://archives.postgresql.org/pgsql-patches/2003-06/msg00366.php
> > isn't in the patch queue. Is the patch OK?
>
> I think it was just that Bruce hasn't gotten to it.
>
> > If not please say what is wrong with it.
>
> I just checked out a new cvs copy and applied the patch, and did something
> like the following:
> create table a1(a int unique, b int, c int, unique(b,c));
> insert into a1 values (1,1,1);
> create table a2(a int references a1(a), b int, c int, foreign
> key(b,c) references a1(b,c) initially deferred );
> begin;
> insert into a2 values (1,1,1);
> insert into a2 select * from a2;
> [repeated a bunch of times until it'd be inserting 64k rows]
>
> and got an error on writing the disk event handle and a signal 11:
>
> ERROR: Can not open first disk event file handle for
> /usr/local/pgsql/data/base/17139/pgsql_tmp/pgsql_tmpdeftrig_555-000000001
> The connection to the server was lost. Attempting reset: LOG: server
> process (pid 32125) was terminated by signal 11
>
> The backtrace from the core looked like:
> #0 0x42062867 in fclose@@GLIBC_2.1 () from /lib/i686/libc.so.6
> #1 0x080d61a8 in deferredTriggerClean () at trigger.c:1864
> #2 0x080d728e in DeferredTriggerAbortXact () at trigger.c:2642
> #3 0x0808c214 in AbortTransaction () at xact.c:1042
> #4 0x08141332 in PostgresMain (argc=4, argv=0x826a2f0, username=0x826a2c0
> "sszabo") at postgres.c:2610
> #5 0x0812320e in BackendFork (port=0x8277080) at postmaster.c:2471
> #6 0x08122d1e in BackendStartup (port=0x8277080) at postmaster.c:2118
> #7 0x081218ab in ServerLoop () at postmaster.c:1090
> #8 0x08121358 in PostmasterMain (argc=3, argv=0x82693e0) at
> postmaster.c:872
> #9 0x080f9e30 in main (argc=3, argv=0xbffffa94) at main.c:211
> #10 0x420158f7 in __libc_start_main () from /lib/i686/libc.so.6
>
> looks like it was passing a NULL file handle if it couldn't be opened.

The open error seems to have been errno=13 (EACCES). I was able to get
past that by changing pgsql_tmp's permissions to 700 rather than 600.
The 64k insert one worked, but the next insert ... select failed with:

ERROR: Attempt to read from disk deferred trigger queue before
initialisation.

---

As a side question, it looks to me that the code stores the first trigger
records in memory and then after some point starts storing all new records
on disk. Is this correct? I'd wonder if that's really what you want in
general, since I'd think that the earliest ones are the ones you're least
likely to need until end of transaction (or set constraints in the fk
case) whereas the most recent ones are possibly going to be immediate
triggers which you're going to need as soon as the statement is done.