X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 44F8DD1C92B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 26 Jul 2003 13:41:30 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 41849-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 26 Jul 2003 10:40:40 -0300 (ADT)
Received: from ward.six (unknown [212.57.173.169])
	by svr1.postgresql.org (Postfix) with ESMTP id 115CCD1C4EB
	for <pgsql-sql@postgresql.org>; Sat, 26 Jul 2003 10:41:18 -0300 (ADT)
Date: Sat, 26 Jul 2003 19:39:47 +0600
From: Denis Zaitsev <zzz@anda.ru>
To: pgsql-sql@postgresql.org
Subject: Very strange 'now' behaviour in nested triggers.
Message-ID: <20030726193947.B1636@natasha.ward.six>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/395
X-Sequence-Number: 14509

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.