From: Denis Zaitsev <zzz@anda.ru>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Very strange 'now' behaviour in nested triggers.
Date: Sun, 27 Jul 2003 01:58:32 +0600
Message-ID: <20030727015832.D1636@natasha.ward.six> (raw)
In-Reply-To: <200307261514.16322.dev@archonet.com>
References: <20030726193947.B1636@natasha.ward.six>
<200307261514.16322.dev@archonet.com>
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...
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, dev@archonet.com
Subject: Re: Very strange 'now' behaviour in nested triggers.
In-Reply-To: <20030727015832.D1636@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