Обсуждение: time stops within transaction

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

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,
Pennsylvania19026
 

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

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

"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

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

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/

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

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/

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

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/

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
Serviceand other goodies at http://donb.photo.net.
 

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

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

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

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

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.

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

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

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

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

> 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

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

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