Re: 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