Re: TRIGGER BEFORE INSERT

Uuups... That's what I feared of. I was a bit hasty and nervous after
I've discovered, that the old schema doesn't work. Sory for that.

An yet, the original question remain. After I've change the TRIGGER to
"FOR EACH ROW", I get:
-------------------------------------------------------
database=# CREATE TRIGGER mocarny BEFORE INSERT ON test_days for each
row EXECUTE PROCEDURE prado();
CREATE TRIGGER
database=# INSERT INTO test_days (dnia) VALUES ('2007-01-06');
ERROR:  insert or update on table "test_utarg" violates foreign key
constraint "test_utarg_dnia_fkey"
DETAIL:  Key (dnia)=(1) is not present in table "test_days".
CONTEXT:  SQL statement "UPDATE test_utarg SET dnia= $1  WHERE tm
BETWEEN  $2  AND  $3 +'1day'::interval"
PL/pgSQL function "prado" line 1 at SQL statement
--------------------------------------------------------

From "DETAIL: Key (dnia)=(1)" above, I get that "test_days" is already
populated by the "DEFAULT nextval" from relevant SEQUENCE. So it looks
like:
1. either the new value of "test_days.dnia" as already present in the
NEW row, is not visible to "UPDATE test_utarg" sub-statement of the same
transaction. But earlier versions of Postgres did allow for that
visibility.
2. or the constrainets in earlier postgres were checked on trigger
transaction COMMIT, not along the way; so the constraint violation
didn't occure then.

May be option (2) is less likely....

In any case I'm a bit stuck with finding a workaround .... and I'm
wondering if the change is intentional (like: the standard requires
different semantics); or it was accidental, and came as a side effect of
some other changes.

Any ideas?


On Mon, 2007-01-08 at 09:15 -0500, Jerry Sievers wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
>
> > Hi All!
> >
> > I have some old piece of code, that worked two years ago (Postgres
> > version 7.2, I think), but doesn't work within Postgres 8.1.4 now.
> >
> > The story is, that I have a trigger on a table (business day
> > statistics), that is fired before insert; it updates another table
> > (detailed transaction log), and saves statistics from that update within
> > the freshly inserted record.
> >
> > Cutting down much larger (and obfuscated) schema to its critical
> > section, I've came with the following snippet:
> > ----------------------------------------------------------------
> > CREATE TABLE test_days (id serial unique, dnia date not null default
> > current_date-'1day'::interval, total int not null);
> > CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not
> > null, dnia int references test_days(id));
> >
> > INSERT INTO test_utarg (tm,nic, amount) SELECT current_timestamp -
> > interval_mul('1min'::interval, (random()*10000)::integer),
> > generate_series(1,88), (random()*10000)::integer;
> >
> > CREATE FUNCTION prado() RETURNS trigger AS $$ DECLARE wydano INTEGER;
> > BEGIN UPDATE test_utarg SET dnia=new.id WHERE tm BETWEEN new.dnia AND
> > new.dnia+'1day'::interval; GET DIAGNOSTICS wydano := ROW_COUNT;
> > new.total := wydano; RETURN new; END; $$ LANGUAGE plpgsql;
> > CREATE TRIGGER mocarny BEFORE INSERT ON test_days EXECUTE PROCEDURE
> > prado();
>
> Did you want a statement level trigger here?
>
> Try adding for each row' to your create trigger statement above.
>
> HTH
>
> > INSERT INTO test_days (dnia) VALUES ('2007-01-06');
> > ERROR:  record "new" is not assigned yet
> > DETAIL:  The tuple structure of a not-yet-assigned record is
> > indeterminate.
> > CONTEXT:  PL/pgSQL function "prado" line 1 at SQL statement
> > ------------------------------------------------------------
> >
> > And to my ultimate surprise, this one breaks with yet another ERROR.
> >
> > In the original schema, the ERROR was abount TEST_UTARG.DNIA referencing
> > a "not yet available" NEW.ID. ... as if constraints within transactions
> > (inside trigger) were checked on each step, and not at the end of
> > transaction .... as it looks was the case of postgres v7.2.
> >
> > But the ERROR quoted abobe warries me even more. Is it true, that NEW is
> > really "not-yet-assigned" in BEFORE INSERT trigger?? Or may be I'm not
> > seeing some other obvious mistake I've done in the code above?
> >
> > Help, pls!
> >
> > --
> > -R
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >                http://archives.postgresql.org/
> >
>