Re: [GENERAL] TRIGGER BEFORE INSERT
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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] TRIGGER BEFORE INSERT
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. signature.asc Description: Digital signature
Re: [GENERAL] TRIGGER BEFORE INSERT
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 [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] TRIGGER BEFORE INSERT
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TRIGGER BEFORE INSERT
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. signature.asc Description: Digital signature
Re: [GENERAL] TRIGGER BEFORE INSERT
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] TRIGGER BEFORE INSERT
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TRIGGER BEFORE INSERT
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TRIGGER BEFORE INSERT
Rafal Pietrak [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] TRIGGER BEFORE INSERT
On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
Rafal Pietrak [EMAIL PROTECTED] 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
Re: [GENERAL] TRIGGER BEFORE INSERT
Rafal Pietrak [EMAIL PROTECTED] 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()*1)::integer),
generate_series(1,88), (random()*1)::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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] 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 [EMAIL PROTECTED] 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()*1)::integer),
generate_series(1,88), (random()*1)::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/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Trigger before insert
ON.KG wrote: How could i stop Inserting record into table by some condition? RETURN null when using a before trigger. Or raise an exception to abort the whole transaction. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Trigger before insert
Hi! How could i stop Inserting record into table by some condition? RH RETURN null when using a before trigger. Or raise an exception to abort RH the whole transaction. Thanx ;) RETURN NULL works so as i need ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
