[SQL] Very strange 'now' behaviour in nested triggers.
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.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
> 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.
I'm not sure I'd use this approach for very long strings, but we can sort out
your timestamp problem.
> create
> table xxx (
> s text,
> t timestamp
> default 'now'
^^^
Note the quoted 'now'.
[snip recursive before trigger - final element gets inserted by the actual SQL
below - abcd get inserted by the trigger]
> 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
> 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?
Not exactly a bug. The crucial thing is that 'now' gets evaluated when the
query is parsed and the plan built. For the main INSERT that's at the start
of the transaction (which is what you want).
For the trigger function, what happens is the plan for that insert gets
compiled the first time the function is called and 'now' gets frozen.
Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you
expect.
PS - I think this is mentioned in the manuals somewhere, but it's not
surprising you missed it. Interesting example.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: 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: [SQL] Very strange 'now' behaviour in nested triggers.
Denis Zaitsev <[EMAIL PROTECTED]> writes:
> create table xxx (
> s text,
> t timestamp
> default 'now'
> );
That's a dangerous way to define the default --- 'now' is taken as a
literal of type timestamp, which means it will be reduced to a timestamp
constant as soon as a statement that requires the default is planned.
You lose in plpgsql because of plan caching, but you'd also lose if you
tried to PREPARE the insert command. Example:
regression=# insert into xxx values('a');
INSERT 154541 1
regression=# insert into xxx values('b');
INSERT 154542 1
regression=# prepare s(text) as insert into xxx values($1);
PREPARE
regression=# execute s('q1');
EXECUTE
regression=# execute s('q2');
EXECUTE
regression=# select * from xxx;
s | t
+
a | 2003-07-26 10:18:51.364913
b | 2003-07-26 10:18:53.519648
q1 | 2003-07-26 10:19:21.795415
q2 | 2003-07-26 10:19:21.795415
(4 rows)
The default would work the way you want with almost any other way of
doing it. For instance
default now()
default current_timestamp
default localtimestamp
default 'now'::text
Given that you want timestamp without time zone, I'd probably use
"default localtimestamp".
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: > On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > > 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. > > I'm not sure I'd use this approach for very long strings Of course not a very deep recursion, the strings are expected to consist of less than 10 pieces. > Not exactly a bug. The crucial thing is that 'now' gets evaluated when the > query is parsed and the plan built. For the main INSERT that's at the start > of the transaction (which is what you want). > > For the trigger function, what happens is the plan for that insert gets > compiled the first time the function is called and 'now' gets frozen. Ok, thanks a much. I've realized... > Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you > expect. > > PS - I think this is mentioned in the manuals somewhere, but it's not > surprising you missed it. Interesting example. As I remember, namely 'now' is mentioned in the manuals, as the best approach to keep the same value thru the whole transaction. That is why I used it here. For now I've tested that now() does the thing. Why? I remember that now() is changing thru the transaction, just showing the current time... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: > Denis Zaitsev <[EMAIL PROTECTED]> writes: > > create table xxx ( > > s text, > > t timestamp > > default 'now' > > ); > > That's a dangerous way to define the default --- 'now' is taken as a > literal of type timestamp, which means it will be reduced to a timestamp > constant as soon as a statement that requires the default is planned. > You lose in plpgsql because of plan caching, but you'd also lose if you > tried to PREPARE the insert command. Example: Aaa... So, the INSERT inside a trigger will use the 'now' for the time this trigger is compiled (i.e. called first time)? Do I understand right? And the only outer trigger uses the right 'now' as its value goes from the top-level INSERT... Thank you very much. By the way, do you think this method with nested triggers has some 'moral weakness' vs. just cycling left-to-right on the comma-separated string in the 'do instead' rule for some view of xxx? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
