Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 77F47475F25
	for <pgsql-general@postgresql.org>;
	Mon, 23 Sep 2002 15:00:12 -0400 (EDT)
Received: from email02.aon.at (WARSL402PIP7.highway.telekom.at [195.3.96.94])
	by postgresql.org (Postfix) with SMTP id 708BF475C8B
	for <pgsql-general@postgresql.org>;
	Mon, 23 Sep 2002 15:00:11 -0400 (EDT)
Received: (qmail 62300 invoked from network); 23 Sep 2002 19:00:10 -0000
Received: from m160p019.dipool.highway.telekom.at (HELO cantor)
	([62.46.9.243]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail2rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <tgl@sss.pgh.pa.us>; 23 Sep 2002 19:00:10 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
	Aaron Held <aaron@MetroNY.com>, Roberto Mello <rmello@cc.usu.edu>,
	Neil Conway <neilc@samurai.com>, pgsql-sql@postgresql.org,
	pgsql-general@postgresql.org
Subject: Re: [SQL] CURRENT_TIMESTAMP
Date: Mon, 23 Sep 2002 21:02:00 +0200
Message-ID: <kgmuouku2imliobrrck45ugkc0hlrkbo1v@4ax.com>
References: <21724.1032793386@sss.pgh.pa.us>
	<200209231506.g8NF6JP13779@candle.pha.pa.us>
	<0meuousc373fdokv3bbke116jsbkpakno2@4ax.com>
	<22827.1032800742@sss.pgh.pa.us>
In-Reply-To: <22827.1032800742@sss.pgh.pa.us>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/1019
X-Sequence-Number: 30600

On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Manfred Koizar <mkoi-pg@aon.at> writes:
>> This has been discussed before and I know I'm going to get flamed for
>> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP)
>> return the start time of the current transaction is a bug, or at least
>> it is not conforming to the standard.
>
>As you say, it's been discussed before.

Yes, and I hate to be annoying.

>We concluded that the spec defines the behavior as
>implementation-dependent,

AFAICT the spec requires the returned value to meet two conditions.

C1: If a statement contains more than one <datetime value function>,
they all have to return (maybe different formats of) the same value.

C2: The returned value has to represent a point in time *during* the
execution of the SQL-statement.

The only thing an implementor is free to choose is which point in time
"during the execution of the SQL-statement" is to be returned, i.e. a
timestamp in the interval between the start of the statement and the
first time when the value is needed.

The current implementation only conforms to C1.

>and therefore we can pretty much do what we want.

Start time of the statement, ... of the transaction, ... of the
session, ... of the postmaster, ... of the century?

I understand that with subselects, functions, triggers, rules etc. it
is not easy to implement the specification.  If we can't do it now, we
should at least add a todo and make clear in the documentation that
CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant.

Servus
 Manfred