Re: [GENERAL] CURRENT_TIMESTAMP - Mailing list pgsql-sql

On Tue, Sep 24, 2002 at 10:33:51AM +0200, Manfred Koizar wrote:
> 
> The people who wrote the specification knew about transactions.  If
> they had wanted what you describe above, they would have written:
> 
>   3) If a transaction generally contains more than one reference
>      to one or more <datetime value function>s, then all such ref-
>      erences are effectively evaluated simultaneously. The time of
>      evaluation of the <datetime value function> during the execution
>      of the transaction is implementation-dependent.
> 
> But they wrote "SQL-statement", not "transaction".
> 
> >And hence Postgresql is perfectly compliant.
> 
> I'm not so sure.
> 
> >The current definition is, I would say, the most useful definition. Can you
> >give an example where your definition would be more useful?
> 
> I did not write the standard, I'm only reading it.  I have no problem
> with an implementation that deviates from the standard "because we
> know better".  But we should users warn about this fact and not tell
> them it is compliant.

At first, I also found the idea of now() freezing during a transaction
odd. But now I seems the right thing to do - I can't really come up with
a use-case for current_timestamp to vary. 

For the relational algebra and transactional logic purists out there,
having current_timetamp be a fixed transaction time reinforces the
'atomicity' of a transaction - it's _supposed_ to happen all at once,
as far as the rest of the system is concerned. Many parts of the the
standard deviate from the ideals, however, probably due to the desire
of those with existing software to make it 'standards compliant' by
bending the standard, instead of fixing the software. There are places
in SQL92, especially, where if you know the exact feature set of some of
the big DBs from that era, you can imagine the conversation that lead
to inserting specific ambiguities into the document.

As you've probably noticed, SQL92 (and '99, from what I've look at in it)
are _not_ examples of the clearest, most pristine english in the world.
I sometimes wonder if the committee was actually an early attempt at
machine generated natural language, then I realize if that were true,
it would be clearer and more self-consistent. ;-)

All this is a very longwinded way for me to say leave now() as transaction
time, and get Peter to interpret this passage, to see what should happen
with current_timestamp. He seems to be one of the best at disentagling
the standards verbiage.

Ross