I just ran into a strangest thing: within transaction, select now() will
always return time when transaction started. Same happens with select
'now'::timestamp.

This is with 7.0. I have not tested it with CVS.

I am not sure what causes this. I assume that result of now() is cached by
fmgr. Is there a way to declare functions 'not-cacheable-ever'? If there
is, such should be applied to now().

-alex


Tue, 17 Oct 2000 22:20:10 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
time stops within transaction
Wow, that is strange.


> I just ran into a strangest thing: within transaction, select now() will
> always return time when transaction started. Same happens with select
> 'now'::timestamp.
> 
> This is with 7.0. I have not tested it with CVS.
> 
> I am not sure what causes this. I assume that result of now() is cached by
> fmgr. Is there a way to declare functions 'not-cacheable-ever'? If there
> is, such should be applied to now().
> 
> -alex
> 
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 17 Oct 2000 23:21:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: time stops within transaction
Alex Pilosov <alex@pilosoft.com> writes:
> I just ran into a strangest thing: within transaction, select now() will
> always return time when transaction started.

That is what now() is defined to return: transaction start time.
Perhaps the documentation needs improvement...

			regards, tom lane

Tue, 17 Oct 2000 23:33:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 

----- Original Message -----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Alex Pilosov <alex@pilosoft.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Wednesday, 18 October 2000 16:21
Subject: Re: [HACKERS] time stops within transaction


> Wow, that is strange.
>
>
> > I just ran into a strangest thing: within transaction, select now() will
> > always return time when transaction started. Same happens with select
> > 'now'::timestamp.
> >


Actually, thats useful since you can put now()  into multiple fields in one
transaction.

The alternative is that CURRENT_TIMESTAMP  (??? is that the one) which isn't a
function
and stuffs up when trying to use it as a field default or as part of an
expression in a view.

(Comment true for 6.5.3 at least)

 Documentation on time constants and how to misuse them is weak...

Regards


Wed, 18 Oct 2000 16:48:43 +1300
"John Huttley" <John@mwk.co.nz>
Re: time stops within transaction
"John Huttley" <John@mwk.co.nz> writes:
>  Documentation on time constants and how to misuse them is weak...

You can say that again!  Who's up for submitting documentation patches?

			regards, tom lane

Wed, 18 Oct 2000 00:01:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 
On Tue, 17 Oct 2000, Alex Pilosov wrote:

> I just ran into a strangest thing: within transaction, select now() will
> always return time when transaction started. Same happens with select
> 'now'::timestamp.

 It's feature, not bug. IMHO good feature, an example I use it for rows 
identification during table filling :-)

					Karel


Wed, 18 Oct 2000 10:38:01 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: time stops within transaction
Tom Lane writes:

> That is what now() is defined to return: transaction start time.
> Perhaps the documentation needs improvement...

Then CURRENT_TIMESTAMP is in violation of SQL.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Wed, 18 Oct 2000 17:38:22 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: time stops within transaction 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> That is what now() is defined to return: transaction start time.

> Then CURRENT_TIMESTAMP is in violation of SQL.

Au contraire, if it did not behave that way it would violate the spec.
See SQL92 6.8 general rule 3:

         3) If an SQL-statement 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 SQL-statement is implementation-dependent.

			regards, tom lane

Wed, 18 Oct 2000 11:41:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 
Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> >> That is what now() is defined to return: transaction start time.
> 
> > Then CURRENT_TIMESTAMP is in violation of SQL.
> 
> Au contraire, if it did not behave that way it would violate the spec.
> See SQL92 6.8 general rule 3:
> 
>          3) If an SQL-statement 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 SQL-statement is implementation-dependent.

statement != transaction

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Wed, 18 Oct 2000 18:10:04 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: time stops within transaction 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Au contraire, if it did not behave that way it would violate the spec.
>> See SQL92 6.8 general rule 3:
>> 
>> 3) If an SQL-statement 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 SQL-statement is implementation-dependent.

> statement != transaction

So?  It also says that the choice of exactly when to evaluate now()
is implementation-dependent.  Doing so at start of transaction is
an allowed behavior AFAICS.  Actually calling time(2) at each use
of now(), which is what the original poster seemed to want, is
clearly *not* an allowed behavior.

I think what you are advocating is recomputing now() at each statement
boundary within a transaction, but that's not as simple as it looks
either.  Consider statement boundaries in an SQL function --- the
function is probably being called from some outer statement, so
advancing now() within the function would violate the spec constraint
with respect to the outer statement.

			regards, tom lane

Wed, 18 Oct 2000 12:39:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 
Tom Lane writes:

> So?  It also says that the choice of exactly when to evaluate now()
> is implementation-dependent.  Doing so at start of transaction is
> an allowed behavior AFAICS.

But it's only talking about statements.  You can't reuse things that you
calculated for previous statements unless it says so.  (Of course
implementation-dependent means that you can do anything you want to, but
let's not go there. :-)

> Actually calling time(2) at each use
> of now(), which is what the original poster seemed to want, is
> clearly *not* an allowed behavior.

What this covers is doing things like

SELECT CURRENT_TIMESTAMP as "Today", CURRENT_TIMESTAMP + 1 DAY AS "Tomorrow";

But keep in mind that other/correct SQL implementations don't have
autocommit, so if you're in some interactive SQL shell and you keep
entering

select current_timestamp;

then it won't ever advance unless you do commits in between.  This doesn't
make much sense to me, as CURRENT_TIMESTAMP is defined to return the
"current time" .  The point of a transaction is all data or no data, not
all the same data.

> I think what you are advocating is recomputing now() at each statement
> boundary within a transaction, but that's not as simple as it looks
> either.  Consider statement boundaries in an SQL function --- the
> function is probably being called from some outer statement, so
> advancing now() within the function would violate the spec constraint
> with respect to the outer statement.

Good point.  There are probably special rules for SQL functions.

I'm not saying that this thing is a priority to me, but it's something to
consider.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Wed, 18 Oct 2000 23:22:27 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: time stops within transaction 
At 12:39 PM 10/18/00 -0400, Tom Lane wrote:
>Peter Eisentraut <peter_e@gmx.net> writes:
>> Tom Lane writes:
>>> Au contraire, if it did not behave that way it would violate the spec.
>>> See SQL92 6.8 general rule 3:
>>> 
>>> 3) If an SQL-statement 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 SQL-statement is implementation-dependent.
>
>> statement != transaction
>
>So?  It also says that the choice of exactly when to evaluate now()
>is implementation-dependent. 

Note the phrase "during the execution of the SQL-STATEMENT" above.  It
says that exactly when it will be evaluated within the statement is
implementation-defined, BUT THAT IT IS EVALUATED WITHIN THE STATEMENT,
not beforehand.

At least, that's how I read it :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 18 Oct 2000 14:36:18 -0700
Don Baccus <dhogaza@pacifier.com>
Re: time stops within transaction 
On Wed, 18 Oct 2000, Tom Lane wrote:

> I think what you are advocating is recomputing now() at each statement
> boundary within a transaction, but that's not as simple as it looks
> either.  Consider statement boundaries in an SQL function --- the
> function is probably being called from some outer statement, so
> advancing now() within the function would violate the spec constraint
> with respect to the outer statement.
Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
statement as submitted by a client (libpq)?

-alex


Wed, 18 Oct 2000 22:24:04 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: time stops within transaction 
Alex Pilosov <alex@pilosoft.com> writes:
>> Consider statement boundaries in an SQL function --- the
>> function is probably being called from some outer statement, so
>> advancing now() within the function would violate the spec constraint
>> with respect to the outer statement.
> Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
> statement as submitted by a client (libpq)?

There's never been any reason to make such a distinction.
Nor am I entirely convinced that that's the right definition...

			regards, tom lane

Wed, 18 Oct 2000 23:02:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 


Tom Lane wrote:

> Alex Pilosov <alex@pilosoft.com> writes:
> >> Consider statement boundaries in an SQL function --- the
> >> function is probably being called from some outer statement, so
> >> advancing now() within the function would violate the spec constraint
> >> with respect to the outer statement.
> > Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
> > statement as submitted by a client (libpq)?
>
> There's never been any reason to make such a distinction.

There's already a distinction.
Snapshot is made per top-level statement and functions/subqueries
use the same snapshot as that of top-level statement.

Regards.

Hiroshi Inoue



Thu, 19 Oct 2000 12:48:09 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: time stops within transaction
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>>>> Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
>>>> statement as submitted by a client (libpq)?
>> 
>> There's never been any reason to make such a distinction.

> There's already a distinction.
> Snapshot is made per top-level statement and functions/subqueries
> use the same snapshot as that of top-level statement.

Not so.  SetQuerySnapshot is executed per querytree, not per top-level
statement --- for example, if a rule generates multiple queries from
a user statement, SetQuerySnapshot is called again for each query.

With the current structure of pg_exec_query_string(), an operation
executed in the outer loop, rather than the inner, would more or less
correspond to one "top level" query --- if you want to assume that
pg_exec_query_string() is only called from PostgresMain.  That's
true today but hasn't always been true --- I believe it used to be
used to parse SPI commands, and someday it may be again.

			regards, tom lane

Thu, 19 Oct 2000 00:22:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 


Tom Lane wrote:

> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> >>>> Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
> >>>> statement as submitted by a client (libpq)?
> >>
> >> There's never been any reason to make such a distinction.
>
> > There's already a distinction.
> > Snapshot is made per top-level statement and functions/subqueries
> > use the same snapshot as that of top-level statement.
>
> Not so.  SetQuerySnapshot is executed per querytree, not per top-level
> statement --- for example, if a rule generates multiple queries from
> a user statement, SetQuerySnapshot is called again for each query.
>
> With the current structure of pg_exec_query_string(), an operation
> executed in the outer loop, rather than the inner, would more or less
> correspond to one "top level" query --- if you want to assume that
> pg_exec_query_string() is only called from PostgresMain.  That's
> true today but hasn't always been true --- I believe it used to be
> used to parse SPI commands, and someday it may be again.
>

If there's no concept of top-level statement,there's no
concept of read consistency and MVCC isn't needed.

Regards.

Hiroshi Inoue.



Thu, 19 Oct 2000 13:42:51 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: time stops within transaction
> > > Snapshot is made per top-level statement and functions/subqueries
> > > use the same snapshot as that of top-level statement.
> >
> > Not so.  SetQuerySnapshot is executed per querytree, not per top-level
> > statement --- for example, if a rule generates multiple queries from
> > a user statement, SetQuerySnapshot is called again for each query.

This is true. I just make it to work as it was in pre-6.5 times - each
query of *top level* query list uses own snapshot (in read committed mode
only) as if they were submitted by user one by one.

But functions/subqueries called while executing query uses same snapshot
as query itself.

> > With the current structure of pg_exec_query_string(), an operation
> > executed in the outer loop, rather than the inner, would more or less
> > correspond to one "top level" query --- if you want to assume that
> > pg_exec_query_string() is only called from PostgresMain.  That's
> > true today but hasn't always been true --- I believe it used to be
> > used to parse SPI commands, and someday it may be again.

It was never used in SPI. Just look at _SPI_execute. Same parent query
snapshot is used in SPI functions. *But* SPI' queries *see* changes
made by parent query - I never was sure about this and think I've asked
other opinions. No opinions - no changes -:)

> If there's no concept of top-level statement,there's no
> concept of read consistency and MVCC isn't needed.

Except of the fact that SPI' queries see changes made by parent same
snapshot
is used all time while executing top-level query (single query, not query
list).

Vadim



Wed, 18 Oct 2000 22:51:22 -0700
"Vadim Mikheev" <vmikheev@sectorbase.com>
Re: time stops within transaction


Tom Lane wrote:

> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> >>>> Postgres doesn't have an idea of what a 'top-level' statement is? I.E.
> >>>> statement as submitted by a client (libpq)?
> >>
> >> There's never been any reason to make such a distinction.
>
> > There's already a distinction.
> > Snapshot is made per top-level statement and functions/subqueries
> > use the same snapshot as that of top-level statement.
>
> Not so.  SetQuerySnapshot is executed per querytree, not per top-level
> statement --- for example, if a rule generates multiple queries from
> a user statement, SetQuerySnapshot is called again for each query.
>

Is it possible that a rule generates multiple queries from
a read(select)-only statement ? If so,the queries must
be executed under the same snapshot in order to guaran
tee read consistency from user's POV.
As for non-select queries I'm not sure because read
consistency doesn't have much meaning for them.

I just remembered a report from Forest Wilkinson
about a month ago [SQL] SQL functions not locking
properly?

Don't we have to distiguish simple procedure calls
(select func();) and function calls as a part of a query ?
As I mentioned once before,it seems a problem that
arbitrary functions could be called from queries.

As for procedures,it seems preferable that each
statement of them is treated as a top-level query.

Regards.
Hiroshi Inoue


Thu, 19 Oct 2000 18:24:56 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: time stops within transaction
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Is it possible that a rule generates multiple queries from
> a read(select)-only statement ? If so,the queries must
> be executed under the same snapshot in order to guaran
> tee read consistency from user's POV.
> As for non-select queries I'm not sure because read
> consistency doesn't have much meaning for them.

In SERIALIZABLE mode everything is done with the first snapshot obtained
*in the transaction*, which seems correct to me.  In READ COMMITTED mode
a new snapshot is taken at every SetQuerySnapshot, which means later
commands in an xact can see data committed later than transaction start.
The issue here seems to be just how often we want to do
SetQuerySnapshot.

One thing that bothers me about the current setup is that
pg_exec_query_string delays calling SetQuerySnapshot until the last
possible moment before executing a query.  In particular, parsing and
planning of the first query in a transaction will be done with no
snapshot at all!  Is this good, and if so why?

I am inclined to think that we should do SetQuerySnapshot in the outer
loop of pg_exec_query_string, just before calling
pg_analyze_and_rewrite.  This would ensure that parse/plan accesses to
the database have a snapshot, and would eliminate the question I raised
yesterday about whether ProcessUtility is missing SetQuerySnapshot
calls.

If we did that, then SetQuerySnapshot would be called once per user-
written command (defining a command as whatever the grammar produces
a single parsetree for, which is probably OK) so long as SPI functions
don't try to use pg_exec_query_string...

Then this'd also be an appropriate place to advance now(), if people
feel that's more appropriate behavior for now() than the existing one.

> I just remembered a report from Forest Wilkinson
> about a month ago [SQL] SQL functions not locking
> properly?

Yes, that was on my to-look-at list too.  Not sure if it's related.

> Don't we have to distiguish simple procedure calls
> (select func();) and function calls as a part of a query ?

"select func()" looks like a query to me.  I don't see how you are going
to make such a distinction in a useful way.  If we had a CALL statement
distinct from function invocation in expressions, then maybe it'd make
sense for that context to act differently.

			regards, tom lane

Thu, 19 Oct 2000 10:29:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: time stops within transaction 
> I am inclined to think that we should do SetQuerySnapshot in the outer
> loop of pg_exec_query_string, just before calling
> pg_analyze_and_rewrite.  This would ensure that parse/plan accesses to
                                           ^^^^^^^^^^^^^^
Actually not - snapshot is passed as parameter to heap_beginscan...
And currently SnapshotNow is used everywhere.

> If we did that, then SetQuerySnapshot would be called once per user-
> written command (defining a command as whatever the grammar produces
> a single parsetree for, which is probably OK) so long as SPI functions
> don't try to use pg_exec_query_string...

SPI doesn't try this from its birthday in ~6.2

Vadim



Thu, 19 Oct 2000 11:55:18 -0700
"Vadim Mikheev" <vmikheev@sectorbase.com>
Re: time stops within transaction 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> 
> > I just remembered a report from Forest Wilkinson
> > about a month ago [SQL] SQL functions not locking
> > properly?
> 
> Yes, that was on my to-look-at list too.  Not sure if it's related.
> 

As I replied to his posting,the cause is obvious.
Because the queries in a function are executed under
the same snapshot,SELECT statements never
see the changes made by other backends.
OTOH SELECT .. FOR UPDATE has a different visiblity
from simple SELECT. Yes,SELECT .. FOR UPDATE
doesn't guarantee read consistency because it has to
acquire a lock on the latest tuples.
I recommended to use SELECT .. FOR UPDATE then
but it's far from being reasonable.

> > Don't we have to distiguish simple procedure calls
> > (select func();) and function calls as a part of a query ?
> 
> "select func()" looks like a query to me.  I don't see how you are going
> to make such a distinction in a useful way.  If we had a CALL statement
> distinct from function invocation in expressions, then maybe it'd make
> sense for that context to act differently.
>

As I mentioned before,calling functions which have strong side effect e.g.
   select strong_effect(column1), column2 from table1 where ...;
is a problem. IMHO the use of functions should be restricted.
Of cource,we have to call(execute)procedures which change
the database. Unfortunately we don't have a command to call
(execute) functions as procedures currently.

Regards.
Hiroshi Inoue

Fri, 20 Oct 2000 06:13:16 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: time stops within transaction 


Vadim Mikheev wrote:

> > I am inclined to think that we should do SetQuerySnapshot in the outer
> > loop of pg_exec_query_string, just before calling
> > pg_analyze_and_rewrite.  This would ensure that parse/plan accesses to
>                                            ^^^^^^^^^^^^^^
> Actually not - snapshot is passed as parameter to heap_beginscan...
> And currently SnapshotNow is used everywhere.
>

I sometimes mentioned anxieties about the use of SnapshotNow,
though I 've had no reasonable solution for it.
SnapshotNow isn't a real snapshot and so it wouldn't be able to
give us a complete consistency e.g. in the case "DDL statements
in transaction block".

However I couldn't think of any reasnoable way how to
handle the following cases.

We would have PREPARE statements in the near future.
How does PREPARE use the same snapshot as the execution ?
We would never be able to have shared catalog cache.
We coulnd't delete dropped table files immediately after commit.
...

Regards.
Hiroshi Inoue


Fri, 20 Oct 2000 08:52:14 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: time stops within transaction