Date: Sun, 27 Jul 2003 01:58:32 +0600
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.
Message-ID: <20030727015832.D1636@natasha.ward.six>
References: <20030726193947.B1636@natasha.ward.six>
	<200307261514.16322.dev@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200307261514.16322.dev@archonet.com>;
	from dev@archonet.com on Sat, Jul 26, 2003 at 03:14:16PM +0100

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...