Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 80D40475CB1; Mon, 23 Sep 2002 21:21:04 -0400 (EDT)
Received: from svana.org (t1-1-076.dialup.apex.net.au [203.20.62.76])
	by postgresql.org (Postfix) with ESMTP
	id 646A7475CA9; Mon, 23 Sep 2002 21:21:03 -0400 (EDT)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 17teM4-0004dO-00; Tue, 24 Sep 2002 11:19:12 +1000
Date: Tue, 24 Sep 2002 11:19:12 +1000
From: Martijn van Oosterhout <kleptog@svana.org>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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
Message-ID: <20020924011912.GA16430@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
Mail-Followup-To: Manfred Koizar <mkoi-pg@aon.at>,
	Tom Lane <tgl@sss.pgh.pa.us>, 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
References: <21724.1032793386@sss.pgh.pa.us>
	<200209231506.g8NF6JP13779@candle.pha.pa.us>
	<0meuousc373fdokv3bbke116jsbkpakno2@4ax.com>
	<22827.1032800742@sss.pgh.pa.us>
	<kgmuouku2imliobrrck45ugkc0hlrkbo1v@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <kgmuouku2imliobrrck45ugkc0hlrkbo1v@4ax.com>
User-Agent: Mutt/1.3.28i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/1044
X-Sequence-Number: 30625

On Mon, Sep 23, 2002 at 09:02:00PM +0200, Manfred Koizar wrote:
> On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> >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.

Well, what I would suggest is that when you wrap several statements into a
single transaction with begin/commit, the whole lot could be considered a
single statement (since they form an atomic transaction so in a sense they
are all executed simultaneously). And hence Postgresql is perfectly
compliant.

My second point would be: what is the point of a timestamp that keeps
changing during a transaction? If you want that, there are other functions
that serve that purpose.

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

The current definition is, I would say, the most useful definition. Can you
give an example where your definition would be more useful?
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.