Thread: TRIGGER BEFORE INSERT
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();
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
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/
>
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant
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/
> >
>
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> 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.
Current versions of PG check foreign keys at the end of each
insert/update/delete statement, so your before-insert trigger is in fact
erroneous: the referenced key does not yet exist in the target table.
I think 7.2 did constraint checking only when the entire interactive
command finished, but there were enough cases where that was wrong
that we changed it.
Consider declaring the foreign-key constraint as DEFERRED.
regards, tom lane
On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > 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.
>
> Current versions of PG check foreign keys at the end of each
> insert/update/delete statement, so your before-insert trigger is in fact
> erroneous: the referenced key does not yet exist in the target table.
> I think 7.2 did constraint checking only when the entire interactive
> command finished, but there were enough cases where that was wrong
> that we changed it.
>
> Consider declaring the foreign-key constraint as DEFERRED.
No luck here.
I've changed the trigger function to have triggers deferred, like the
following:
database=# CREATE OR REPLACE FUNCTION prado() RETURNS trigger AS $$
DECLARE wydano INTEGER; BEGIN SET CONSTRAINTS ALL DEFERRED ; 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;
and the results are still the same:
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)=(3) 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
------------------------------------------------------------
But I've never before used a deferred constraints - so may be I haven't
set it up correctly, in the above definition. Have I?
But actually, I've found a workaround: I've encapsulated the above
functionality inside of a function, which:
1. does an INSERT
2. subsequently does a SELECT of what i've just inserted (currently I'm
stuck with postgres v8.1.4 - so I cannot use INSERT ... RETURNING).
3. then I UPDATE the logtable
4. then I UPDATE the record INSERTED in step (1).
Originally, I had this functionality in a single "TRIGGER BEFORE"
function (OK, it fired UPDATE within - but I had the 'fresh' ROW of data
from step (1) all along with me, inside of that trigger function - no
need to SELECT/UPDATE it in separate statements).
So I get a performance panelty against my original schema.
Is there a way to optimise?
Hi!
I'm re-posting this message again in hope someone would have a look at
the case again. .. it's pending.
In postgres v7.2 I had a trigger function launched BEFORE INSERT, which
did everything I needed (like an UPDATE of other table inside of that
trigger function, and adjustment of the INSERTed ROW)
Currently (as of postgress v8.1.4) I have to put that function OUTSIDE
of a trigger and to achieve that same functionality, in addition to the
original INSERT to the original table, I have to:
1. make a separate SELECT on that table.
2. make a separate UPDATE on that table.
Does anyone have any idea how to 'optimise' that? Like folding-up the
three statements I need for this to work in v8.1.4 back to (or closer
to) the initial single statement?
I fear I lack the necesary SQL experience to optimise (I feel like lucky
to have a workaround).
Any help apreciated.
-R
On Tue, 2007-01-09 at 18:41 +0100, Rafal Pietrak wrote:
> On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
> > Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > > 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.
> >
> > Current versions of PG check foreign keys at the end of each
> > insert/update/delete statement, so your before-insert trigger is in fact
> > erroneous: the referenced key does not yet exist in the target table.
> > I think 7.2 did constraint checking only when the entire interactive
> > command finished, but there were enough cases where that was wrong
> > that we changed it.
> >
> > Consider declaring the foreign-key constraint as DEFERRED.
>
> No luck here.
>
> I've changed the trigger function to have triggers deferred, like the
> following:
>
> database=# CREATE OR REPLACE FUNCTION prado() RETURNS trigger AS $$
> DECLARE wydano INTEGER; BEGIN SET CONSTRAINTS ALL DEFERRED ; 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;
>
> and the results are still the same:
>
> 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)=(3) 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
> ------------------------------------------------------------
>
> But I've never before used a deferred constraints - so may be I haven't
> set it up correctly, in the above definition. Have I?
>
> But actually, I've found a workaround: I've encapsulated the above
> functionality inside of a function, which:
> 1. does an INSERT
> 2. subsequently does a SELECT of what i've just inserted (currently I'm
> stuck with postgres v8.1.4 - so I cannot use INSERT ... RETURNING).
> 3. then I UPDATE the logtable
> 4. then I UPDATE the record INSERTED in step (1).
>
> Originally, I had this functionality in a single "TRIGGER BEFORE"
> function (OK, it fired UPDATE within - but I had the 'fresh' ROW of data
> from step (1) all along with me, inside of that trigger function - no
> need to SELECT/UPDATE it in separate statements).
>
> So I get a performance panelty against my original schema.
>
> Is there a way to optimise?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
On Thu, Jan 11, 2007 at 01:01:24PM +0100, Rafal Pietrak wrote: > Hi! > > I'm re-posting this message again in hope someone would have a look at > the case again. .. it's pending. Well, I can't help with the details because I can't see what you're trying to do, but I'm fairly sure you can't change te "deferred state" of triggers after the transaction has started. AIUI you also have to make the foreign key constraint "deferrable" otherwise you can't defer it in any case. This is incidently what Tom suggested. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Rafal Pietrak wrote: > Hi! > > I'm re-posting this message again in hope someone would have a look at > the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Alternatively, you may want to re-think your trigger function so that it does things in the right order. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: > Rafal Pietrak wrote: > > Hi! > > > > I'm re-posting this message again in hope someone would have a look at > > the case again. .. it's pending. > > You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: "SET CONSTRAINTS ALL DEFERRED ; " *before* the UPDATE statement *within* the trigger function (just after BEGIN statement there). After doing so, notheing changes. I get the same ERROR. (all screenlogs were included in previous posts, so I'll spare those here). So may be "SET CONSTRAINTS .... DEFERRED " should be used somehow differently? I've never had any use for that construct, may be I miss something? > Alternatively, you may want to re-think your trigger function so that it > does things in the right order. I cannot see *any* way to reorder the events in the triger function. The function is short anough 'not to allow' :) for reordering - it just makes an UPDATE to some other table (where it puts a reference to the 'fresh ROW') and stores the result of that update in the newly created ROW. And the problem is, that UPDATE puts a reference to the fresh ROW and that the UPDATE statement does NOT SEE the 'freshly created ROW' - may be this is not a case of 'too early constraint check', but rather a problem of 'visibility' of data (new data) within a single transaction (an UPDATE is launched within the trigger transaction - should see already created ROW, shouldn't it?). N.B. All the code that does fail is in my previous posts. But as this is the 'second round' of my 'call for help' - I get an impression, that there may actually not be a solution. Too bad. -R
On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote: > Well. I were, but probably I'm doing something wrong with 'deferring the > trigger'. When I put: > > "SET CONSTRAINTS ALL DEFERRED ; " > > *before* the UPDATE statement *within* the trigger function (just after > BEGIN statement there). 1. Doing it within a function has no effect. 2. By default foreign key checks are not deferrable. Did you make yours deferrable? > So may be "SET CONSTRAINTS .... DEFERRED " should be used somehow > differently? I've never had any use for that construct, may be I miss > something? Only at the beginning of a transaction and it only works on foreign keys marked deferrable. > I cannot see *any* way to reorder the events in the triger function. The > function is short anough 'not to allow' :) for reordering - it just > makes an UPDATE to some other table (where it puts a reference to the > 'fresh ROW') and stores the result of that update in the newly created > ROW. A BEFORE trigger cannot see the row, nor can anything called by that trigger. If you want to call functions that want to see that row, use an AFTER trigger. > And the problem is, that UPDATE puts a reference to the fresh ROW and > that the UPDATE statement does NOT SEE the 'freshly created ROW' - may > be this is not a case of 'too early constraint check', but rather a > problem of 'visibility' of data (new data) within a single transaction > (an UPDATE is launched within the trigger transaction - should see > already created ROW, shouldn't it?). BEFORE trigger, no. AFTER trigger, yes. That's the difference between the two types... > But as this is the 'second round' of my 'call for help' - I get an > impression, that there may actually not be a solution. Too bad. It's possible, by making your foreign key INITIALLY DEFERRED. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote:
>> So may be "SET CONSTRAINTS .... DEFERRED " should be used somehow
>> differently? I've never had any use for that construct, may be I miss
>> something?
> Only at the beginning of a transaction and it only works on foreign
> keys marked deferrable.
No, you can do it mid-transaction. I think the problem is the second
part: the FK constraint has to be marked deferrable, which is not the
default I believe. Also, you could forget the SET CONSTRAINTS entirely
if you made the constraint INITIALLY DEFERRED ... then it'd always act
as a deferred check.
regards, tom lane
On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: > On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: > > Rafal Pietrak wrote: > > > Hi! > > > > > > I'm re-posting this message again in hope someone would have a look at > > > the case again. .. it's pending. > > > > You were given a solution; defer the foreign key constraint. > > Well. I were, but probably I'm doing something wrong with 'deferring the > trigger'. When I put: What previous posters have said is that you need to defer the FK. So you need to change your table definition from: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id)); to: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id) INITIALLY DEFERRED); per Toms suggestion. This eliminates the need for the SET CONSTRAINTS DEFERRED statement. -- Adrian Klaver aklaver@comcast.net
Perfect! Thenx! -R On Thu, 2007-01-11 at 12:26 -0800, Adrian Klaver wrote: > On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: > > On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: > > > Rafal Pietrak wrote: > > > > Hi! > > > > > > > > I'm re-posting this message again in hope someone would have a > look at > > > > the case again. .. it's pending. > > > > > > You were given a solution; defer the foreign key constraint. > > > > Well. I were, but probably I'm doing something wrong with 'deferring > the > > trigger'. When I put: > > What previous posters have said is that you need to defer the FK. So > you need > to change your table definition from: > CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not > null, dnia int references test_days(id)); > to: > CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not > null, dnia int references test_days(id) INITIALLY DEFERRED); > per Toms suggestion. This eliminates the need for the SET CONSTRAINTS > DEFERRED > statement. > >