From: Richard Huxton <dev@archonet.com>
To: Denis Zaitsev <zzz@anda.ru>
To: pgsql-sql@postgresql.org
Subject: Re: Very strange 'now' behaviour in nested triggers.
Date: Sat, 26 Jul 2003 15:14:16 +0100
Message-ID: <200307261514.16322.dev@archonet.com> (raw)
In-Reply-To: <20030726193947.B1636@natasha.ward.six>
References: <20030726193947.B1636@natasha.ward.six>
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
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: dev@archonet.com, zzz@anda.ru
Subject: Re: Very strange 'now' behaviour in nested triggers.
In-Reply-To: <200307261514.16322.dev@archonet.com>
* 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