From: Denis Zaitsev <zzz@anda.ru>
To: pgsql-sql@postgresql.org
Subject: Very strange 'now' behaviour in nested triggers.
Date: Sat, 26 Jul 2003 19:39:47 +0600
Message-ID: <20030726193947.B1636@natasha.ward.six> (raw)
In short, the idea this example is to test for is to split a
comma-separated value of some text attribute (given to the INSERT
operator) and then insert a row for each of the parts of that text
value. I've tried to do this thru a nested triggers approach.
create
table xxx (
s text,
t timestamp
default 'now'
);
create
function xxx () returns trigger
language plpgsql
as '
declare
tail text;
head integer;
begin
tail:= substring(new.s, \'[^,]+$\');
head:= length(new.s)-
length(tail) -1;
if head > 0 then
insert into xxx values (
substring(new.s for head)
--,new.t
);
end if;
new.s:= trim(tail);
raise notice \'"%"\', new.s;
raise notice \'"%"\', new.t;
return new;
end;
';
create
trigger xxx
before insert on xxx
for each row execute procedure
xxx ();
Then:
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:26.514217"
INSERT 223886 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:28.300914"
INSERT 223891 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:30.948737"
INSERT 223896 1
zzz=> SELECT * from xxx;
s | t
---+----------------------------
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:26.514217
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:28.300914
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:30.948737
(15 rows)
So, all the timestamps except those for the last 'x' field are the
same! These "the same" timestamps are really the timestamp of the
first top-level INSERT. And the timestamps for the last field of the
comma-separated string are the correct things. This last field is
cultivated by the top-level trigger's call.
If to set new.t for nested triggers explicitly (commented in the
trigger code above), then all will be ok. But this is not a cure, of
course.
So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
misunderstand something?
Thanks in advance.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zzz@anda.ru
Subject: Re: Very strange 'now' behaviour in nested triggers.
In-Reply-To: <20030726193947.B1636@natasha.ward.six>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox