Re: [SQL] CURRENT_TIMESTAMP

Hello!

i'm using PostgreSQL 7.2.1 and got strange parse errors..
could somebody tell me what's wrong with this timestamp query example?

PostgreSQL said: ERROR: parser: parse error at or near "date"
Your query:

select timestamp(date '1998-02-24', time '23:07')

example is from PostgreSQL help and certainly worked in previous versions of
pgsql.. but in 7.2.1 it does not. had anything changed and not been updated
in pgsql manuals or is it a bug?

thanx for any help

Tomas Lehuta

On Fri, 20 Sep 2002, Tomas Lehuta wrote:

> Hello!
>
> i'm using PostgreSQL 7.2.1 and got strange parse errors..
> could somebody tell me what's wrong with this timestamp query example?
>
> PostgreSQL said: ERROR: parser: parse error at or near "date"
> Your query:
>
> select timestamp(date '1998-02-24', time '23:07')
>
> example is from PostgreSQL help and certainly worked in previous versions of
> pgsql.. but in 7.2.1 it does not. had anything changed and not been updated
> in pgsql manuals or is it a bug?

Presumably it's a manual example that didn't get changed. Timestamp(...)
is now a specifier for the type with a given precision. You can use
"timestamp"(date '1998-02-24', time '23:07') or datetime math (probably
something like date '1998-02-24' + time '23:07' and possibly a cast)

"Tomas Lehuta" <lharp(at)aurius(dot)sk> writes:
> could somebody tell me what's wrong with this timestamp query example?

> select timestamp(date '1998-02-24', time '23:07')
> PostgreSQL said: ERROR: parser: parse error at or near "date"

> example is from PostgreSQL help

From where exactly? I don't see any such example in current sources.

Although you could make this work by double-quoting the name "timestamp"
(which is a reserved word now, per SQL spec), I'd recommend sidestepping
the problem by using the equivalent + operator instead:

regression=# select "timestamp"(date '1998-02-24', time '23:07');
timestamp
---------------------
1998-02-24 23:07:00
(1 row)

regression=# select date '1998-02-24' + time '23:07';
?column?
---------------------
1998-02-24 23:07:00
(1 row)

regards, tom lane

Is there any way to monitor a long running query?

I have stats turned on and I can see my queries, but is there any better
measure of the progress?

Thanks,
-Aaron Held

select current_query from pg_stat_activity;
current_query

<IDLE>
<IDLE>
<IDLE>
<IDLE>
<IDLE> in transaction
FETCH ALL FROM PgSQL_470AEE94
<IDLE> in transaction
select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
'7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
<IDLE>
<IDLE>
<IDLE>

There is pgmonitor:

http://gborg.postgresql.org/project/pgmonitor

---------------------------------------------------------------------------

Aaron Held wrote:
> Is there any way to monitor a long running query?
>
> I have stats turned on and I can see my queries, but is there any better
> measure of the progress?
>
> Thanks,
> -Aaron Held
>
> select current_query from pg_stat_activity;
> current_query
>
> <IDLE>
> <IDLE>
> <IDLE>
> <IDLE>
> <IDLE> in transaction
> FETCH ALL FROM PgSQL_470AEE94
> <IDLE> in transaction
> select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
> '7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
> <IDLE>
> <IDLE>
> <IDLE>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Aaron Held wrote:
> Is there any way to monitor a long running query?
>
> I have stats turned on and I can see my queries, but is there any better
> measure of the progress?

Oh, sorry, you want to know how far the query has progressed. Gee, I
don't think there is any easy way to do that. Sorry.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hi all developpers,

This is just a idea.

How about making available the MVCC last version number just like oid is
available. This would simplify a lot of table design. You know, having
to add a field "updated::timestamp" to detect when a record was updated
while viewing it (a la pgaccess).

That way, if the version number do not match, one would know that the
reccord was updated since last retrieved.

What do think?

JLL

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Aaron Held wrote:
> > Is there any way to monitor a long running query?
>
> Oh, sorry, you want to know how far the query has progressed. Gee, I
> don't think there is any easy way to do that.

Would it be a good idea to add the time that the current query began
execution at to pg_stat_activity?

Cheers,

Neil

--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC

Jean-Luc Lachance <jllachan(at)nsd(dot)ca> writes:
> How about making available the MVCC last version number just like oid is
> available. This would simplify a lot of table design. You know, having
> to add a field "updated::timestamp" to detect when a record was updated
> while viewing it (a la pgaccess).
> That way, if the version number do not match, one would know that the
> reccord was updated since last retrieved.

> What do think?

I think it's already there: see xmin and cmin. Depending on your needs,
testing xmin might be enough (you'd only need to pay attention to cmin
if you wanted to notice changes within your own transaction).

regards, tom lane

Neil Conway wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Aaron Held wrote:
> > > Is there any way to monitor a long running query?
> >
> > Oh, sorry, you want to know how far the query has progressed. Gee, I
> > don't think there is any easy way to do that.
>
> Would it be a good idea to add the time that the current query began
> execution at to pg_stat_activity?

What do people think about this? It seems like a good idea to me.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Neil Conway wrote:
>
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>
>>>Aaron Held wrote:
>>>
>>>>Is there any way to monitor a long running query?
>>>
>>>Oh, sorry, you want to know how far the query has progressed. Gee, I
>>>don't think there is any easy way to do that.
>>
>>Would it be a good idea to add the time that the current query began
>>execution at to pg_stat_activity?
>
>
> What do people think about this? It seems like a good idea to me.
>

My application marks the start time of each query and I have found it
very useful. The users like to see how long each query took, and the
admin can take a quick look and see how many queries are running and how
long each has been active for. Good for debugging and billing.

-Aaron Held

On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote:
> >
> > Would it be a good idea to add the time that the current query began
> > execution at to pg_stat_activity?
>
> What do people think about this? It seems like a good idea to me.

OpenACS has a package called "Developer Support" that shows you (among
other things) how long a query took to be executed. Very good to finding
out slow-running queries that need to be optimized.

-Roberto

--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +

It looks like that just timestamps things in its connection pool, that
is what I do now.

What I would like is to know about queries that have not finished yet.

-Aaron

Roberto Mello wrote:
> On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote:
>
>>>Would it be a good idea to add the time that the current query began
>>>execution at to pg_stat_activity?
>>
>>What do people think about this? It seems like a good idea to me.
>
>
> OpenACS has a package called "Developer Support" that shows you (among
> other things) how long a query took to be executed. Very good to finding
> out slow-running queries that need to be optimized.
>
> -Roberto
>

Roberto Mello wrote:
> On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote:
> > >
> > > Would it be a good idea to add the time that the current query began
> > > execution at to pg_stat_activity?
> >
> > What do people think about this? It seems like a good idea to me.
>
> OpenACS has a package called "Developer Support" that shows you (among
> other things) how long a query took to be executed. Very good to finding
> out slow-running queries that need to be optimized.

7.3 will have GUC 'log_duration' which will show query duration.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Aaron Held wrote:
> It looks like that just timestamps things in its connection pool, that
> is what I do now.
>
> What I would like is to know about queries that have not finished yet.

OK, added to TODO:

* Add start time to pg_stat_activity

Should we supply the current duration too? That value would change on
each call. Seems redundant.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, added to TODO:
> * Add start time to pg_stat_activity

It would be nearly free to include the start time of the current
transaction, because we already save that for use by now(). Is
that good enough, or do we need start time of the current query?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, added to TODO:
> > * Add start time to pg_stat_activity
>
> It would be nearly free to include the start time of the current
> transaction, because we already save that for use by now(). Is
> that good enough, or do we need start time of the current query?

Current query, I am afraid. We could optimize it so single-query
transactions wouldn't need to call that again.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

That is great! Thanks for the info.

Tom Lane wrote:
>
> Jean-Luc Lachance <jllachan(at)nsd(dot)ca> writes:
> > How about making available the MVCC last version number just like oid is
> > available. This would simplify a lot of table design. You know, having
> > to add a field "updated::timestamp" to detect when a record was updated
> > while viewing it (a la pgaccess).
> > That way, if the version number do not match, one would know that the
> > reccord was updated since last retrieved.
>
> > What do think?
>
> I think it's already there: see xmin and cmin. Depending on your needs,
> testing xmin might be enough (you'd only need to pay attention to cmin
> if you wanted to notice changes within your own transaction).
>
> regards, tom lane

Hi all,

I just read it's possible to get the MVCC last version numbers. Is it also
possible to get the current transaction id? Would it be possible to check
later if that transaction has been commited? This would be nice for a distributed
application to enforce an "exactly once" semantics for transactions (even if
there are network related errors while the server sends ack for commiting a
transaction).
And if it's possible, how long would that information be valid, i.e. when do
transaction id's get reused?
If it's not working I will have to implement my own transactions table.

Thanks in advance,
Michael Paesold

--
Werden Sie mit uns zum "OnlineStar 2002"! Jetzt GMX wählen -
und tolle Preise absahnen! http://www.onlinestar.de

On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>Tom Lane wrote:
>> It would be nearly free to include the start time of the current
>> transaction, because we already save that for use by now(). Is
>> that good enough, or do we need start time of the current query?
>
>Current query, I am afraid. We could optimize it so single-query
>transactions wouldn't need to call that again.

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.

SQL92 says in 6.8 <datetime value function>:

General Rules

1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP respectively return the current date, current
time, and current timestamp [...]
^^^^^^^

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.

SQL99 says in 6.19 <datetime value function>:

3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
activated triggers is implementation-dependent.

I cannot say that I fully understand the second sentence (guess I have
to read it for another 100 times), but "during the execution of S"
seems to mean "not before the start and not after the end of S".

What do you think?

Servus
Manfred

Manfred Koizar <mkoi-pg(at)aon(dot)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. We concluded that the spec
defines the behavior as implementation-dependent, and therefore we
can pretty much do what we want.

If you want exact current time, there's always timeofday().

regards, tom lane

On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>Manfred Koizar <mkoi-pg(at)aon(dot)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

Manfred,

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

I, for one, would judge that the start time of the statement is "during the
execution"; it would only NOT be "during the execution" if it was a value
*before* the start time of the statement. It's a semantic argument.

The spec is, IMHO, rather vague on how this would relate to transactions. I
do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a
transaction to be an extension of an individual SQL statement for this
purpose (at least, that's what I guess they did).

Thus, if you accept the postulates that:
1) "During" a SQL statement includes the start time of the statement, and
2) A Transaction is the equivalent of a single SQL statement for many
purposes,
Then the current behavior is a logical conclusion.

Further, we could not change that behaviour without breaking many people's
applications.

Ideally, since we get this question a lot, that a compile-time or
execution-time switch to change the behavior of current_timestamp
contextually would be nice. We just need someone who;s interested enough in
writing one.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> I, for one, would judge that the start time of the statement is "during the
> execution"; it would only NOT be "during the execution" if it was a value
> *before* the start time of the statement. It's a semantic argument.
>
> The spec is, IMHO, rather vague on how this would relate to transactions. I
> do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a
> transaction to be an extension of an individual SQL statement for this
> purpose (at least, that's what I guess they did).
>
> Thus, if you accept the postulates that:
> 1) "During" a SQL statement includes the start time of the statement, and
> 2) A Transaction is the equivalent of a single SQL statement for many
> purposes,
> Then the current behavior is a logical conclusion.
>
> Further, we could not change that behaviour without breaking many people's
> applications.

I don't see how we can defend returning the start of the transaction as
the current_timestamp. In a multi-statement transaction, that doesn't
seem very current to me. I know there are some advantages to returning
the same value for all queries in a transaction, but is that value worth
returning such stale time information?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

> I don't see how we can defend returning the start of the transaction as
> the current_timestamp. In a multi-statement transaction, that doesn't
> seem very current to me. I know there are some advantages to returning
> the same value for all queries in a transaction, but is that value worth
> returning such stale time information?

Then what *was* the reasoning behind the current behavior?

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

Josh Berkus wrote:
>
> Bruce,
>
> > I don't see how we can defend returning the start of the transaction as
> > the current_timestamp. In a multi-statement transaction, that doesn't
> > seem very current to me. I know there are some advantages to returning
> > the same value for all queries in a transaction, but is that value worth
> > returning such stale time information?
>
> Then what *was* the reasoning behind the current behavior?

I thought the spec required it, but now that I see it doesn't, I don't
know why it was done that way.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I don't see how we can defend returning the start of the transaction as
> the current_timestamp.

Here's an example:

CREATE RULE foo AS ON INSERT TO mytable DO
( INSERT INTO log1 VALUES (... , now(), ...);
INSERT INTO log2 VALUES (... , now(), ...) );

I think it's important that these commands store the same timestamp in
both log tables (not to mention that any now() being stored into mytable
itself generate that same timestamp).

If you scale that up just a little bit, you can devise scenarios where
successive client-issued commands (within a single transaction) want to
store the same timestamp. After all, it's only a minor implementation
detail that you chose to fire these logging operations via a rule and
not by client-side logic.

In short, there are plenty of situations where it's critical for
application correctness that a series of commands all be able to operate
with the same value of now(). I don't think that it's wise for Postgres
to try to decide where within a transaction it's safe to advance now().
That will inevitably break some applications, and it's not obvious what
the benefit is.

In short: if you want exact current time, there's timeofday(). If you
want start of transaction time, we've got that. If you want start of
current statement time, I have two questions: why, and exactly how do
you want to define current statement, considering functions, rules,
triggers, and all that other stuff that makes it interesting?

ISTM that if a client or function wants to record intratransaction
times, it can call timeofday() at the appropriate points for itself.

regards, tom lane

On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote:
> > > >
> > > > Would it be a good idea to add the time that the current query began
> > > > execution at to pg_stat_activity?
> > >
> > > What do people think about this? It seems like a good idea to me.
> >
> > OpenACS has a package called "Developer Support" that shows you (among
> > other things) how long a query took to be executed. Very good to finding
> > out slow-running queries that need to be optimized.
>
> 7.3 will have GUC 'log_duration' which will show query duration.

Forgive my ignorance here, but what is GUC? And how would I access the
query duration?

-Roberto

--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
Q: What is purple and commutes?
A: A boolean grape.

I see what you are saying now --- that even single user statements can
trigger multiple statements, so you would have to say transaction start
time is time the user query starts. I can see how that seems a little
arbitrary. However, don't we have separate paths for user queries and
queries sent as part of a rule?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I don't see how we can defend returning the start of the transaction as
> > the current_timestamp.
>
> Here's an example:
>
> CREATE RULE foo AS ON INSERT TO mytable DO
> ( INSERT INTO log1 VALUES (... , now(), ...);
> INSERT INTO log2 VALUES (... , now(), ...) );
>
> I think it's important that these commands store the same timestamp in
> both log tables (not to mention that any now() being stored into mytable
> itself generate that same timestamp).
>
> If you scale that up just a little bit, you can devise scenarios where
> successive client-issued commands (within a single transaction) want to
> store the same timestamp. After all, it's only a minor implementation
> detail that you chose to fire these logging operations via a rule and
> not by client-side logic.
>
> In short, there are plenty of situations where it's critical for
> application correctness that a series of commands all be able to operate
> with the same value of now(). I don't think that it's wise for Postgres
> to try to decide where within a transaction it's safe to advance now().
> That will inevitably break some applications, and it's not obvious what
> the benefit is.
>
> In short: if you want exact current time, there's timeofday(). If you
> want start of transaction time, we've got that. If you want start of
> current statement time, I have two questions: why, and exactly how do
> you want to define current statement, considering functions, rules,
> triggers, and all that other stuff that makes it interesting?
>
> ISTM that if a client or function wants to record intratransaction
> times, it can call timeofday() at the appropriate points for itself.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Roberto Mello wrote:
> On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote:
> > > > >
> > > > > Would it be a good idea to add the time that the current query began
> > > > > execution at to pg_stat_activity?
> > > >
> > > > What do people think about this? It seems like a good idea to me.
> > >
> > > OpenACS has a package called "Developer Support" that shows you (among
> > > other things) how long a query took to be executed. Very good to finding
> > > out slow-running queries that need to be optimized.
> >
> > 7.3 will have GUC 'log_duration' which will show query duration.
>
> Forgive my ignorance here, but what is GUC? And how would I access the
> query duration?

GUC is postgresql.conf and SET commands. They are variables that can be
set.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I see what you are saying now --- that even single user statements can
> trigger multiple statements, so you would have to say transaction start
> time is time the user query starts. I can see how that seems a little
> arbitrary. However, don't we have separate paths for user queries and
> queries sent as part of a rule?

We could use "time of arrival of the latest client command string",
if we wanted to do something like this. My point is that that very
arbitrarily assumes that those are the significant points within a
transaction, and that the client has no need to send multiple commands
that want to insert the same timestamp into different tables. This is
an unwarranted assumption about the client's control structure, IMHO.

A possible compromise is to dissociate now() and current_timestamp,
allowing the former to be start of transaction and the latter to be
start of client command.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I see what you are saying now --- that even single user statements can
> > trigger multiple statements, so you would have to say transaction start
> > time is time the user query starts. I can see how that seems a little
> > arbitrary. However, don't we have separate paths for user queries and
> > queries sent as part of a rule?
>
> We could use "time of arrival of the latest client command string",
> if we wanted to do something like this. My point is that that very
> arbitrarily assumes that those are the significant points within a
> transaction, and that the client has no need to send multiple commands
> that want to insert the same timestamp into different tables. This is
> an unwarranted assumption about the client's control structure, IMHO.
>
> A possible compromise is to dissociate now() and current_timestamp,
> allowing the former to be start of transaction and the latter to be
> start of client command.

I was thinking 'transaction_timestamp' for the transaction start time, and
current_timestamp for the statement start time. I would equate now()
with current_timestamp.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)sss(dot)pgh(dot)pa(dot)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(at)svana(dot)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.

Tom, Bruce,

> > A possible compromise is to dissociate now() and current_timestamp,
> > allowing the former to be start of transaction and the latter to be
> > start of client command.
>
> I was thinking 'transaction_timestamp' for the transaction start time, and
> current_timestamp for the statement start time. I would equate now()
> with current_timestamp.

May I point out that this will break compatibility for those used to the
current behavior?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
>
> Tom, Bruce,
>
> > > A possible compromise is to dissociate now() and current_timestamp,
> > > allowing the former to be start of transaction and the latter to be
> > > start of client command.
> >
> > I was thinking 'transaction_timestamp' for the transaction start time, and
> > current_timestamp for the statement start time. I would equate now()
> > with current_timestamp.
>
> May I point out that this will break compatibility for those used to the
> current behavior?

I am not saying we have to make that change. My point is that our
current behavior may not be the most intuitive, and that most people may
prefer a change. Any such change would be documented in the release
notes.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian dijo:

> Roberto Mello wrote:

> > Forgive my ignorance here, but what is GUC? And how would I access the
> > query duration?
>
> GUC is postgresql.conf and SET commands. They are variables that can be
> set.

Just for the record, GUC is an acronym for "Grand Unified
Configuration".

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)

Bruce Momjian writes:
> My point is that our current behavior may not be the most intuitive, and
> that most people may prefer a change.

I would prefer a change.
--
John Hasler
john(at)dhh(dot)gt(dot)org
Dancing Horse Hill
Elmwood, Wisconsin

John Hasler wrote:
> Bruce Momjian writes:
> > My point is that our current behavior may not be the most intuitive, and
> > that most people may prefer a change.
>
> I would prefer a change.

Yes, I guess that is my point, that we want to make transaction _and_
statement timestamp values available, but most people are going to use
current_timestamp, and most people are going to assume it is statement
time, not transaction time.

Can I add TODO items for this:

o Make CURRENT_TIMESTAMP/now() return statement start time
o Add TRANSACTION_TIMESTAMP to return transaction start time

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Alvaro Herrera wrote:
> Bruce Momjian dijo:
>
> > Roberto Mello wrote:
>
> > > Forgive my ignorance here, but what is GUC? And how would I access the
> > > query duration?
> >
> > GUC is postgresql.conf and SET commands. They are variables that can be
> > set.
>
> Just for the record, GUC is an acronym for "Grand Unified
> Configuration".

Thanks. I couldn't remember that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I was thinking 'transaction_timestamp' for the transaction start time, and
> current_timestamp for the statement start time. I would equate now()
> with current_timestamp.

So you want to both (a) invent even more nonstandard syntax than we
already have, and (b) break as many traditional-Postgres applications
as you possibly can?

'transaction_timestamp' has no reason to live. It's not in the spec.
And AFAIK the behavior of now() has been well-defined since the
beginning of Postgres. If you want to change 'current_timestamp' to
conform to a rather debatable reading of the spec, then fine --- but
keep your hands off of now().

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I was thinking 'transaction_timestamp' for the transaction start time, and
> > current_timestamp for the statement start time. I would equate now()
> > with current_timestamp.
>
> So you want to both (a) invent even more nonstandard syntax than we
> already have, and (b) break as many traditional-Postgres applications
> as you possibly can?

No, but I would like to see you stop makeing condescending replies to
emails. How is that!

> 'transaction_timestamp' has no reason to live. It's not in the spec.
> And AFAIK the behavior of now() has been well-defined since the
> beginning of Postgres. If you want to change 'current_timestamp' to
> conform to a rather debatable reading of the spec, then fine --- but
> keep your hands off of now().

Oh, really. When you get down off your chair we can vote on it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Can I add TODO items for this:
> o Make CURRENT_TIMESTAMP/now() return statement start time
> o Add TRANSACTION_TIMESTAMP to return transaction start time

I object to both of those as phrased. If you have already unilaterally
determined the design of this feature change, then go ahead and put that
in. But I'd suggest

o Revise current-time functions to allow access to statement
start time

which doesn't presuppose the vote about how to do it.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Can I add TODO items for this:
> > o Make CURRENT_TIMESTAMP/now() return statement start time
> > o Add TRANSACTION_TIMESTAMP to return transaction start time
>
> I object to both of those as phrased. If you have already unilaterally
> determined the design of this feature change, then go ahead and put that
> in. But I'd suggest
>
> o Revise current-time functions to allow access to statement
> start time
>
> which doesn't presuppose the vote about how to do it.

OK, I am still just throwing out ideas. I am not sure we even have
enough people who want statement_timestamp to put it in TODO. I do think
we have a standards issue.

My personal opinion is that most people think current_timestamp and
now() are statement start time, not transaction start time. In the past
we have told them the standard requires that but now I think we are not
even sure if that is correct.

So, I have these concerns:

our CURRENT_TIMESTAMP may not be standards compliant
even if it is, it is probably not returning the value most people want
most people don't know it is returning the transaction start time

So, we can just throw the TODO item you mentioned above with a question
mark, or we can try to figure out what to return for CURRENT_TIMESTAMP,
now(), and perhaps create a TRANSACTION_TIMESTAMP.

So, do people want to discuss it or should we just throw it in TODO with
a question mark?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 24 Sep 2002 11:19:12 +1000, Martijn van Oosterhout
<kleptog(at)svana(dot)org> wrote:
>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).

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.

Servus
Manfred

On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh(at)agliodbs(dot)com>
wrote:
>I, for one, would judge that the start time of the statement is "during the
>execution"; it would only NOT be "during the execution" if it was a value
>*before* the start time of the statement. It's a semantic argument.

Josh, you're right, I meant closed interval.

>Further, we could not change that behaviour without breaking many people's
>applications.
>
>Ideally, since we get this question a lot, that a compile-time or
>execution-time switch to change the behavior of current_timestamp
>contextually would be nice.

Yes, GUC!

>We just need someone who;s interested enough in
>writing one.

First we need someone who decyphers SQL99's wording.

Servus
Manfred

On Mon, 23 Sep 2002 16:55:48 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>Here's an example:
>
>CREATE RULE foo AS ON INSERT TO mytable DO
>( INSERT INTO log1 VALUES (... , now(), ...);
> INSERT INTO log2 VALUES (... , now(), ...) );
>
>I think it's important that these commands store the same timestamp in
>both log tables (not to mention that any now() being stored into mytable
>itself generate that same timestamp).

I agree. SQL99 mentions this requirement for triggers and I think we
can apply it to rules as well.

Here is another example:

BEGIN;
INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...);
-- wait a few seconds
INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...);
COMMIT;

Please don't ask me, why I would want that, but the standard demands
the timestamps to be different.

>After all, it's only a minor implementation
>detail that you chose to fire these logging operations via a rule and
>not by client-side logic.

No, it's fundamentally different whether you do something in one
SQL-statment or per a sequence of statements.

Servus
Manfred

On Mon, 23 Sep 2002 23:35:13 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>If you want to change 'current_timestamp' to
>conform to a rather debatable reading of the spec, [...]

Well the spec may be debatable, but could you please explain why my
reading of the spec is debatable. The spec says "during the execution
of the SQL-statement". You know English is not my first language, but
as far as I have learned "during" does not mean "at any time before".

Servus
Manfred

Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh(at)agliodbs(dot)com>
> wrote:
>> Ideally, since we get this question a lot, that a compile-time or
>> execution-time switch to change the behavior of current_timestamp
>> contextually would be nice.

> Yes, GUC!

I think a GUC variable is overkill, in fact potentially dangerous
(what if it's been changed without your app noticing)? I'm fine with
changing current_timestamp to be start-of-current-interactive-command,
though I'd not want to try to chop it more finely than that, for the
reasons already discussed. But I strongly feel that we should leave
the historical behavior of now() alone. There is no spec-based argument
for changing now(), since it isn't in the spec, and its behavior has
been set *and documented* in Postgres since Berkeley days.

If we leave now() alone then there's no need to create another
non-spec-compliant syntax like 'transaction_timestamp', either.
(I really don't want to see us do that, because without parens
it would mean making a new, not-in-the-spec fully-reserved word.)

BTW, as long as we are dorking with the current-time family, does
anyone want to vote for changing timeofday() to return a timestamptz
instead of a text string? There's no good argument except slavish
backward compatibility for having it return text, and we seem to be
quite willing to ignore backwards compatibility in this thread ...

regards, tom lane

>>>>> "Martijn" == Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:

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

FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret
this the same way when using a "select sysdate from dual" inside a
transaction.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Tom,

> If we leave now() alone then there's no need to create another
> non-spec-compliant syntax like 'transaction_timestamp', either.
> (I really don't want to see us do that, because without parens
> it would mean making a new, not-in-the-spec fully-reserved word.)

So, if I've got this straight:

-- current_timestamp will return the timestamp for the beginning of the
SQL statement.

-- now() will return the timestamp for the beginning of the
transaction.

-- timeofday() will return the timestamp of the exact time the function
is called.

... thus changing only current_timestamp.

This looks fine to me, as a search-and-replace on current_timestamp is
easy. However, we need to do a better job of warning people about the
change than we did with interval() to "interval"().

Actually, can I make the proposal that *any* change that breaks
backward compatibility be mentioned in both the new version
announcement and on the download page? This would prevent a lot of
grief. If I'm kept informed of these changes, I'll be happy to write
up a user-friendly announcement/instructions on how to cope with the
change.

> BTW, as long as we are dorking with the current-time family, does
> anyone want to vote for changing timeofday() to return a timestamptz
> instead of a text string? There's no good argument except slavish
> backward compatibility for having it return text, and we seem to be
> quite willing to ignore backwards compatibility in this thread ...

No, I don't see any reason to do this. It's not like timeofday() is a
particularly logical name, anyway. Why not introduce a new function,
rightnow(), that returns timestamptz?

Better yet, how about we introduce a parameter to now()? Example:

now() or now('transaction') returns the transaction timestamp.
now('statement') returns the statement timestamp
now('immediate') returns the timestamp at the exact time the function
is called.

This would seem to me much more consistent than having 3 different
time-calls, whose names have nothing to do with the difference between
them. And it has the advantage of not breaking backward compatibility.

We could introduce the new version of now() in 7.4, encourage everyone
to use it instead of other timestamp calls, and then in 7.5 change the
behavior of current_timestamp for SQL92 compliance.

-Josh Berkus

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

On Tue, Sep 24, 2002 at 10:55:41AM -0400, Roland Roberts wrote:
> >>>>> "Martijn" == Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>
> Martijn> Well, what I would suggest is that when you wrap several
> Martijn> statements into a single transaction with begin/commit,
> Martijn> the whole lot could be considered a single statement
> Martijn> (since they form an atomic transaction so in a sense they
> Martijn> are all executed simultaneously). And hence Postgresql is
> Martijn> perfectly compliant.
>
> FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret
> this the same way when using a "select sysdate from dual" inside a
> transaction.

Oh, interesting datapoint. Let me get this clear - on oracle, the
equivalent of:

BEGIN;
SELECT current_timestamp;
<go off to lunch, come back>
SELECT current_timestamp;
END;

will give two identical timestamps?

Ross

On Tue, Sep 24, 2002 at 08:05:59AM -0700, Josh Berkus wrote:
>
> This looks fine to me, as a search-and-replace on current_timestamp is
> easy. However, we need to do a better job of warning people about the
> change than we did with interval() to "interval"().
>
> Actually, can I make the proposal that *any* change that breaks
> backward compatibility be mentioned in both the new version
> announcement and on the download page? This would prevent a lot of
> grief. If I'm kept informed of these changes, I'll be happy to write
> up a user-friendly announcement/instructions on how to cope with the
> change.

I'd suggest we (for values of we that probably resolve to Bruce
or a Bruce triggered Josh ;-) start a new doc, right now, for
7.4_USER_VISIBLE_CHANGES, or some other, catchy title. In it, document,
with example SQL snippets, if need be, the change from previous behavior,
_when the patch is committed_. In fact, y'all could be hardnosed about
not accepting a user visible syntax changing patch without it touching
this file. Such a document would be invaluable for database migration.

On another note, this discussion is happening on GENERAL and SQL, but
is getting pretty technical - should someone more it to HACKERS to get
input from developers who don't hang out here?

Ross

Josh Berkus writes:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp now('immediate') returns
> the timestamp at the exact time the function is called.

I like that.

IMHO "the exact time the function is called" is what most people would
expect to get from now(), but it's too late for that.
--
John Hasler
john(at)dhh(dot)gt(dot)org
Dancing Horse Hill
Elmwood, Wisconsin

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> So, if I've got this straight:
> [ snip ]
> ... thus changing only current_timestamp.

Yeah, that's more or less what I was thinking. The argument for
changing current_timestamp seems to be really just spec compliance;
that doesn't apply to now() or timeofday().

> Better yet, how about we introduce a parameter to now()? Example:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp
> now('immediate') returns the timestamp at the exact time the function
> is called.

I like this.

> We could introduce the new version of now() in 7.4, encourage everyone
> to use it instead of other timestamp calls, and then in 7.5 change the
> behavior of current_timestamp for SQL92 compliance.

I'd be inclined to just do it; we have not been very good about
following through on multi-version sequences of changes. And the
folks who want a standard-compliant current_timestamp aren't going
to want to migrate to now('statement') instead ...

regards, tom lane

>>>>> "Ross" == Ross J Reedstrom <reedstrm(at)rice(dot)edu> writes:

Ross> Oh, interesting datapoint. Let me get this clear - on
Ross> oracle, the equivalent of:

Well, I've never gone off to lunch in the middle, but in Oracle 7, I
had transactions which definitely took as much as a few minutes to
complete where the timestamp on every row committed was the same.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Roland Roberts wrote:
> >>>>> "Ross" == Ross J Reedstrom <reedstrm(at)rice(dot)edu> writes:
>
> Ross> Oh, interesting datapoint. Let me get this clear - on
> Ross> oracle, the equivalent of:
>
> Well, I've never gone off to lunch in the middle, but in Oracle 7, I
> had transactions which definitely took as much as a few minutes to
> complete where the timestamp on every row committed was the same.

Can you run a test:

BEGIN;
SELECT CURRENT_TIMESTAMP;
wait 5 seconds
SELECT CURRENT_TIMESTAMP;

Are the two times the same?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>Can you run a test:
>
> BEGIN;
> SELECT CURRENT_TIMESTAMP;
> wait 5 seconds
> SELECT CURRENT_TIMESTAMP;
>
>Are the two times the same?

MS SQL 7:
begin transaction
insert into tst values (CURRENT_TIMESTAMP)
-- wait
insert into tst values (CURRENT_TIMESTAMP)
commit
select * from tst

t
---------------------------
2002-09-24 09:49:58.777
2002-09-24 09:50:14.100

Interbase 6:
SQL> select current_timestamp from rdb$database;

=========================
2002-09-24 22:30:13.0000

SQL> select current_timestamp from rdb$database;

=========================
2002-09-24 22:30:18.0000

SQL> commit;

Servus
Manfred

Michael Paesold <mpaesold(at)gmx(dot)at> writes:
> I just read it's possible to get the MVCC last version numbers. Is it also
> possible to get the current transaction id?

Well, there's the brute force way: insert a tuple in some table and look
at its xmin. Offhand I don't think we provide a SQL function to read
current transaction id, though it'd surely be a trivial addition.

> Would it be possible to check
> later if that transaction has been commited? This would be nice for a distributed
> application to enforce an "exactly once" semantics for transactions (even if
> there are network related errors while the server sends ack for commiting a
> transaction).

Again, it's not an exported operation, though you could add a SQL function
that called TransactionIdDidCommit().

> And if it's possible, how long would that information be valid, i.e. when do
> transaction id's get reused?

That would be the tricky part. The ID would be reused after 4 billion
transactions, which is long enough that you probably don't care ... but
the segment of the transaction log that has the associated commit bit
will be recycled as soon as the server has no internal use for it
anymore, which could be as early as the next database-wide VACUUM.
If you tried to call TransactionIdDidCommit() after that, you'd get the
infamous "can't open pg_clog/nnnn" error.

> If it's not working I will have to implement my own transactions table.

That's what I'd recommend. Transaction IDs are internal to the database
and are not designed for users to rely on.

regards, tom lane

Tom Lane wrote:

> Michael Paesold <mpaesold(at)gmx(dot)at> writes:
[snip]
> > If it's not working I will have to implement my own transactions table.
>
> That's what I'd recommend. Transaction IDs are internal to the database
> and are not designed for users to rely on.
>
> regards, tom lane

Well, after reading your explanation I agree with you that it is better
to have my own transaction table. I appreciate your detailed response.

Thanks very much!

Best Regards,
Michael Paesold

SQL> create table rbr_foo (a date);

Table created.

SQL> begin
2 insert into rbr_foo select sysdate from dual;
[...wait about 10 seconds...]
3 insert into rbr_foo select sysdate from dual;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> select * from rbr_foo;

A
---------------------
SEP 27, 2002 12:57:27
SEP 27, 2002 12:57:27

Note that, as near as I can tell, Oracle 8 does NOT have timestamp or
current_timestamp. Online docs say both are present in Oracle 9i.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

OK, we have two db's returning statement start time, and Oracle 8 not
having CURRENT_TIMESTAMP.

Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
transaction start? Is this an open item or TODO item?

---------------------------------------------------------------------------

Manfred Koizar wrote:
> On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >Can you run a test:
> >
> > BEGIN;
> > SELECT CURRENT_TIMESTAMP;
> > wait 5 seconds
> > SELECT CURRENT_TIMESTAMP;
> >
> >Are the two times the same?
>
> MS SQL 7:
> begin transaction
> insert into tst values (CURRENT_TIMESTAMP)
> -- wait
> insert into tst values (CURRENT_TIMESTAMP)
> commit
> select * from tst
>
> t
> ---------------------------
> 2002-09-24 09:49:58.777
> 2002-09-24 09:50:14.100
>
> Interbase 6:
> SQL> select current_timestamp from rdb$database;
>
> =========================
> 2002-09-24 22:30:13.0000
>
> SQL> select current_timestamp from rdb$database;
>
> =========================
> 2002-09-24 22:30:18.0000
>
> SQL> commit;
>
> Servus
> Manfred
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote:
>
> OK, we have two db's returning statement start time, and Oracle 8 not
> having CURRENT_TIMESTAMP.
>
> Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
> transaction start? Is this an open item or TODO item?

Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
any case, if it does get changed we'll have to go through the documentation
and work out whether we mean current_timestamp or now(). I think most people
actually want now().

Fortunatly where I work we only use now() so it won't really matter too
much. Is there a compelling reason to change?

> ---------------------------------------------------------------------------
>
> Manfred Koizar wrote:
> > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
> > <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> > >Can you run a test:
> > >
> > > BEGIN;
> > > SELECT CURRENT_TIMESTAMP;
> > > wait 5 seconds
> > > SELECT CURRENT_TIMESTAMP;
> > >
> > >Are the two times the same?
> >
> > MS SQL 7:
> > begin transaction
> > insert into tst values (CURRENT_TIMESTAMP)
> > -- wait
> > insert into tst values (CURRENT_TIMESTAMP)
> > commit
> > select * from tst
> >
> > t
> > ---------------------------
> > 2002-09-24 09:49:58.777
> > 2002-09-24 09:50:14.100
> >
> > Interbase 6:
> > SQL> select current_timestamp from rdb$database;
> >
> > =========================
> > 2002-09-24 22:30:13.0000
> >
> > SQL> select current_timestamp from rdb$database;
> >
> > =========================
> > 2002-09-24 22:30:18.0000
> >
> > SQL> commit;
> >
> > Servus
> > Manfred
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.

Martijn van Oosterhout wrote:
> On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote:
> >
> > OK, we have two db's returning statement start time, and Oracle 8 not
> > having CURRENT_TIMESTAMP.
> >
> > Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
> > transaction start? Is this an open item or TODO item?
>
> Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
> any case, if it does get changed we'll have to go through the documentation
> and work out whether we mean current_timestamp or now(). I think most people
> actually want now().

Well, I think we have to offer statement start time somewhere, and it
seems the standard probably requires that. Two other databases do it
that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone
test on 9.X?

> Fortunatly where I work we only use now() so it won't really matter too
> much. Is there a compelling reason to change?

Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
happy with STATEMENT_TIMESTAMP, but because the standard requires it we
may just have to fix CURRENT_TIMESTAMP.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> > Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
> > any case, if it does get changed we'll have to go through the documentation
> > and work out whether we mean current_timestamp or now(). I think most people
> > actually want now().
>
> Well, I think we have to offer statement start time somewhere, and it
> seems the standard probably requires that. Two other databases do it
> that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone
> test on 9.X?

Hmm, well having a statement start time could be conceivably useful.

> > Fortunatly where I work we only use now() so it won't really matter too
> > much. Is there a compelling reason to change?
>
> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
> happy with STATEMENT_TIMESTAMP, but because the standard requires it we
> may just have to fix CURRENT_TIMESTAMP.

Well, my vote would be for STATEMENT_TIMESTAMP. Is there really no other
database that does it the way we do? Perhaps it could be matched with a
TRANSACTION_TIMESTAMP and we can sort out CURRENT_TIMESTAMP some other way.

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote:
>> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
>> happy with STATEMENT_TIMESTAMP, but because the standard requires it we
>> may just have to fix CURRENT_TIMESTAMP.

> Well, my vote would be for STATEMENT_TIMESTAMP.

One problem with inventing STATEMENT_TIMESTAMP is that (if spelled that
way, without parens) it would have to become a fully-reserved keyword,
thus possibly breaking some applications that use that name now.

But the real point, I think, is that the folks pushing for this think
that the standard requires CURRENT_TIMESTAMP to be statement timestamp.
Inventing some other keyword isn't going to satisfy them.

I don't personally find the "it's required by the spec" argument
compelling, because the spec specifically says that the exact behavior
is implementation-dependent --- so anyone who assumes CURRENT_TIMESTAMP
will behave as start-of-statement timestamp is going to have portability
problems anyway. Oracle didn't seem to find the argument compelling
either; at last report they have no statement-timestamp function.

I'd be happier with the whole thing if anyone had exhibited a convincing
use-case for statement timestamp. So far I've not seen any actual
examples of situations that are not better served by either transaction
timestamp or true current time. And the spec is perfectly clear that
CURRENT_TIMESTAMP does not mean true current time...

regards, tom lane

Tom,

> I'd be happier with the whole thing if anyone had exhibited a convincing
> use-case for statement timestamp. So far I've not seen any actual
> examples of situations that are not better served by either transaction
> timestamp or true current time. And the spec is perfectly clear that
> CURRENT_TIMESTAMP does not mean true current time...

Are we still planning on putting the three different versions of now() on the
TODO? I.e.,
now('transaction'),
now('statement'), and
now('immediate')
With now() = now('transaction')?

I still think it's a good idea, provided that we have some easy means to
determine now('statement').

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').

I did a little more research on CURRENT_TIMESTAMP. I read the Oracle
docs, and while they mention it, they don't say if the date is xact,
statement, or timeofday. They do mention it was only added in their
newest product, 9.X, so it isn't surpising no one is using it.

I also researched the SQL99 standards and found a much more specific
definition:

3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
activated triggers is implementation-dependent.

They basically seem to be saying that CURRENT_TIMESTAMP has to be the
same for all triggers as it is for the submitted SQL statement. When
they say "the time of evaluation ... is implementation-dependent" they
mean that is can be the beginning of the statement, or the end of the
statement. In fact, you can make a strong argument that it should be
the statement end time that is the proper time, but for implementation
reasons, it is certainly easier to make it start.

Now, they are _not_ saying the statement can't have the same time as
other statements in the transaction, but I don't see why they would
explicitly have to state that. They say statement, so I think we need
to follow that if we want to be standard-compliant. We already have two
other databases who are doing this timing at statement level.

If we change CURRENT_TIMESTAMP to statement time, I don't think we need
now(""), but if we don't change it, I think we do --- somehow we should
allow users to access statement time.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

> If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> now(""), but if we don't change it, I think we do --- somehow we should
> allow users to access statement time.

I'd argue that we need the 3 kinds of now() regardless, just to limit user
confusion. If we set things up as:

now() = transaction time
current_timestamp = statement time
timeofday() = exact time

That does give users access to all 3 timestamps, but using a competely
non-intuitive nomenclature. It's likely that the three types of now() would
just be pointers to other time functions, but would provide nomenative
clarity.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> Bruce,
>
> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I'd argue that we need the 3 kinds of now() regardless, just to limit user
> confusion. If we set things up as:
>
> now() = transaction time
> current_timestamp = statement time
> timeofday() = exact time
>
> That does give users access to all 3 timestamps, but using a competely
> non-intuitive nomenclature. It's likely that the three types of now() would
> just be pointers to other time functions, but would provide nomenative
> clarity.

I agree, having now() as a central place for time information is a good
idea. Maybe we need to vote on these issues.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sun, 29 Sep 2002, Bruce Momjian wrote:

Apologies in advance if there is a more appropriate list.

We are currently developing a database to host some complicated, XMl
layered data. We have chosen postgres because of its ability to store
multidimensional arrays. We feel that using these will allow us to
simplify the database structure considerably by storing some data in
multidimensional arrays.

However, we currently have some dissenters who believe that using the
multidimensional arrays will make queries slower and unneccesarily
complicated. Its hard for us to evaluate in advance because none of us
have much experience with postgres (we are web based and have relied on
MySQL for most projects up to this point).

I have several questions related to the scenario above.

1) are SQL queries slower when extracting data from multidimensional
arrays
2) are table joins more difficult or unneccesarily complicated
3) can you do selects on only a portion of a multidimensional array. That
is, if you were storing multilanguage titles in a two dimensional array,

[en], "english title"
[fr], "french title"

could you select where title[0] = 'en'

I know these may sound like terribily stupid questions. but we need some
quick guidance before proceeding with a schema that relies on these
advanced data features of postgres

tia

mike

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?

I have no objection to doing that. What seems to be contentious is
whether we should change the current behavior of CURRENT_TIMESTAMP.

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Now, they are _not_ saying the statement can't have the same time as
> other statements in the transaction, but I don't see why they would
> explicitly have to state that.

Allow me to turn that around: given that they clearly do NOT state that,
how can you argue that "the spec requires it"? AFAICS the spec does not
require it. In most places they are considerably more explicit than
this about stating what is required.

> We already have two other databases who are doing this timing at
> statement level.

The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
implementation-dependent. We are under no compulsion to follow any
specific other implementation. If we were going to follow some other
lead, I'd look to Oracle first...

> If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> now(""), but if we don't change it, I think we do --- somehow we should
> allow users to access statement time.

I have no problem with providing a function to access statement time,
and now('something') seems a reasonable spelling of that function.
But I think the argument that we should change our historical behavior
of CURRENT_TIMESTAMP is very weak.

One reason why I have a problem with the notion that the spec requires
CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
command" (which is the only specific definition I've seen mentioned
here) is that the spec does not truly have a notion of interactive
command to begin with. AFAICT the spec's model of command execution
is ecpg-like: you have commands embedded in a calling language with
all sorts of opportunities for pre-planning, pre-execution, etc.
The notion of command arrival time is extremely fuzzy in this model.
It could very well be the time you compiled the ecpg application, or
the time you started the application running.

regards, tom lane

On Sun, Sep 29, 2002 at 18:12:55 -0600,
Mike Sosteric <mikes(at)athabascau(dot)ca> wrote:
> On Sun, 29 Sep 2002, Bruce Momjian wrote:
>
> 3) can you do selects on only a portion of a multidimensional array. That
> is, if you were storing multilanguage titles in a two dimensional array,
>
> [en], "english title"
> [fr], "french title"
>
> could you select where title[0] = 'en'

It is unusual to want to store arrays in a database. Normally you want to
use additional tables instead. For example multilanguage titles is something
I would expect to be in a table that had a column referencing back to
another table defining the object a title was for, a column with the
title and a column with the language.

On Mon, 30 Sep 2002, Bruno Wolff III wrote:

> > 3) can you do selects on only a portion of a multidimensional array. That
> > is, if you were storing multilanguage titles in a two dimensional array,
> >
> > [en], "english title"
> > [fr], "french title"
> >
> > could you select where title[0] = 'en'
>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.
>

The chances are very very good that in 99% of the cases we'd only ever
have a single title. multiple titles would be rare. and, to make it worse,
there are several instances of this where you need a table but its seems
overkill for the odd 1% time when you actually need teh extra row.

of course, the there'd be a language lookup table.

what about the speed and query issue?
m

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

On Mon, 30 Sep 2002, Bruno Wolff III wrote:

>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.

I think arrays are one of the cool features of postgres
(along with gist indexes).

Here are some common uses:

- Tree representation (the genealogical from child to ancestors approach)
- Storing of polynomial formulae of arbitary degree

checkout the intarray package in contrib for further info.

I think pgsql arrays provide a natural solution to certain problems
where it fits.

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

On Mon, Sep 30, 2002 at 06:38:56 -0600,
Mike Sosteric <mikes(at)athabascau(dot)ca> wrote:
> On Mon, 30 Sep 2002, Bruno Wolff III wrote:
>
> The chances are very very good that in 99% of the cases we'd only ever
> have a single title. multiple titles would be rare. and, to make it worse,
> there are several instances of this where you need a table but its seems
> overkill for the odd 1% time when you actually need teh extra row.
>
> of course, the there'd be a language lookup table.
>
> what about the speed and query issue?

The book or movie or whatever table should have an index on something
(say bookid). Then make an index on the title table on bookid. This
makes getting the titles for a specific book fairly efficient.

I think using a simpler design (i.e. tables in preference to arrays)
will make doing the project easier. This may override any speed up
you get using arrays.

Mike Sosteric <mikes(at)athabascau(dot)ca> writes:
> could you select where title[0] = 'en'

You certainly could ... but bear in mind that there's no convenient way
to make such a query be indexed, at present. So any values that you
actually want to use as search keys had better be in their own fields.

Now, if you are just using this as an extra search condition that picks
one row out of a small number that are identified by another WHERE
clause, then it's good enough to index for the other clause, and so the
lack of an index for title[0] isn't an issue. In this case, with only
a small number of possible values for title[0], it seems that an index
wouldn't be helpful anyway.

regards, tom lane

Mike,

> We are currently developing a database to host some complicated, XMl
> layered data. We have chosen postgres because of its ability to store
> multidimensional arrays. We feel that using these will allow us to
> simplify the database structure considerably by storing some data in
> multidimensional arrays.

Hmmm ... I'm curious; what kind of data do you feel could be
*simplified* by multi-dimensional arrays?

> However, we currently have some dissenters who believe that using the
> multidimensional arrays will make queries slower and unneccesarily
> complicated.

They're correct, especially about the latter.

> 1) are SQL queries slower when extracting data from multidimensional
> arrays

Yes, but this is fixable; see the Intarray package in /contrib.

> 2) are table joins more difficult or unneccesarily complicated

Yes.

> 3) can you do selects on only a portion of a multidimensional array.

Yes.

> That
> is, if you were storing multilanguage titles in a two dimensional
> array,
>
> [en], "english title"
> [fr], "french title"
>
> could you select where title[0] = 'en'

Yes.

> I know these may sound like terribily stupid questions. but we need
> some
> quick guidance before proceeding with a schema that relies on these
> advanced data features of postgres

The problem you will be facing is that Arrays are one of the
fundamentally *Non-Relational* features that Postgresql supports for a
limited set of specialized purposes (mostly buffer tables, procedures,
and porting from MySQL). As such, incorporating arrays into any kind
of complex schema will drive you to drink ... and is 95% likely more
easily done through tables and sub-tables, in any case.

Let's take your example of "title", and say we wanted to use it in a
join:

SELECT movie.name, movie.show_date, movie.title_lang, title.translation
FROM movies JOIN title_langs ON (
movie.title_lang[1] = title_langs.lang OR movie.title_lang[2] =
title_langs.lang OR movie.title_lang[3] = title_langs.lang ... )

... as you can see, the join is extremely painful. Let alone
constructing a query like "Select all movies with titles only in
English and French and one other language." (try it, really)

Then there's the not insignificant annoyance of getting data into and
out of multi-dimensional arrays, which must constantly be parsed into
text strings. And the fact that you will have to keep track, in your
middleware code, of what the ordinal numbers of arrays mean, since
array elements are fundamentally ordered. (BTW, Postgres arrays begin
at 1, not 0)

Now, I know at least one person who is using arrays to store scientific
data. However, that data arrives in his lab in the form of matrices,
and is not used for joins or query criteria beyond a simple "where"
clause.

As such, I'd reccommend one of two approaches for you:

1) Post some of your schema ideas here, and let us show you how they
are better done relationally. The relational data model has 30 years
of thought behind it -- it can solve a lot of problems.

2) Shift over to an XML database or a full-blown OODB (like Cache').

Good luck.

-Josh Berkus

On 30 Sep 2002 at 8:54, Josh Berkus wrote:

> As such, I'd reccommend one of two approaches for you:
>
> 1) Post some of your schema ideas here, and let us show you how they
> are better done relationally. The relational data model has 30 years
> of thought behind it -- it can solve a lot of problems.

Mike,

Just in case you or others think Josh is some crazed lunatic[1] who
doesn't know what he's talking about, I support his views on this
topic. Avoid arrays. Normalize your data.

[1] - Actually, I don't think I know anything about Josh, except that
he's right about normalizing your data.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

Dan Langille wrote:
> On 30 Sep 2002 at 8:54, Josh Berkus wrote:
>
> > As such, I'd reccommend one of two approaches for you:
> >
> > 1) Post some of your schema ideas here, and let us show you how they
> > are better done relationally. The relational data model has 30 years
> > of thought behind it -- it can solve a lot of problems.
>
> Mike,
>
> Just in case you or others think Josh is some crazed lunatic[1] who
> doesn't know what he's talking about, I support his views on this
> topic. Avoid arrays. Normalize your data.
>
> [1] - Actually, I don't think I know anything about Josh, except that
> he's right about normalizing your data.

Yes, arrays have a very small window of usefulness, but the window does
exist, so we haven't removed them.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 30 Sep 2002 at 12:09, Bruce Momjian wrote:

> Dan Langille wrote:
> > On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> >
> > > As such, I'd reccommend one of two approaches for you:
> > >
> > > 1) Post some of your schema ideas here, and let us show you how they
> > > are better done relationally. The relational data model has 30 years
> > > of thought behind it -- it can solve a lot of problems.
> >
> > Mike,
> >
> > Just in case you or others think Josh is some crazed lunatic[1] who
> > doesn't know what he's talking about, I support his views on this
> > topic. Avoid arrays. Normalize your data.
> >
> > [1] - Actually, I don't think I know anything about Josh, except that
> > he's right about normalizing your data.
>
> Yes, arrays have a very small window of usefulness, but the window does
> exist, so we haven't removed them.

I do not advocate removing them. I do advocate data normalization.
Let's say it's a matter of Do The Right Thing(tm) unless you know
what you're doing.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Now, they are _not_ saying the statement can't have the same time as
> > other statements in the transaction, but I don't see why they would
> > explicitly have to state that.
>
> Allow me to turn that around: given that they clearly do NOT state that,
> how can you argue that "the spec requires it"? AFAICS the spec does not
> require it. In most places they are considerably more explicit than
> this about stating what is required.

I just looked at the SQL99 spec again:

3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
^^^^^^^^^^^^^^^^^^^^^^^^^
activated triggers is implementation-dependent.

Notice the part I highlighted. The time returned is
implementation-dependent "during the execution of S". Now, if we do:

BEGIN;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP;

the time currently returned for the second query is _not_ during the
duration of S (S being an SQL procedure statement) so I don't see how we
can be viewed as spec-compliant.

> > We already have two other databases who are doing this timing at
> > statement level.
>
> The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
> implementation-dependent. We are under no compulsion to follow any
> specific other implementation. If we were going to follow some other
> lead, I'd look to Oracle first...

Only "implementation-dependent" during the execution of the statement.
We can't just return the session start time or 1970-01-01 for every
invocation of CURRENT_TIMESTAMP.

> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I have no problem with providing a function to access statement time,
> and now('something') seems a reasonable spelling of that function.
> But I think the argument that we should change our historical behavior
> of CURRENT_TIMESTAMP is very weak.

Hard to see how it is "very weak". What do you base that on?
Everything I have seen looks pretty strong that we are wrong in our
current implementation.

> One reason why I have a problem with the notion that the spec requires
> CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
> command" (which is the only specific definition I've seen mentioned
> here) is that the spec does not truly have a notion of interactive
> command to begin with. AFAICT the spec's model of command execution
> is ecpg-like: you have commands embedded in a calling language with
> all sorts of opportunities for pre-planning, pre-execution, etc.
> The notion of command arrival time is extremely fuzzy in this model.
> It could very well be the time you compiled the ecpg application, or
> the time you started the application running.

The spec says "during the execution of S" so that is what I think we
have to follow.

Hopefully we will get an Oracle 9 tester soon.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Dan,

> Just in case you or others think Josh is some crazed lunatic[1] who
> doesn't know what he's talking about, I support his views on this
> topic. Avoid arrays. Normalize your data.

And just because I'm a crazed lunatic, that doesn't mean that I don't
know what I'm talking about.

Um. I mean, "Even if I were a crazed lunatic, that wouldn't mean that
I don't know what I'm talking about."

<grin>

-Josh "Relational Mania" Berkus

On Mon, 30 Sep 2002, Josh Berkus wrote:

I have a very good sense of the strengths of relational databases. But
they are also limited when it comes to object orientaed data (like XML
records). I though arrays would be a way to simply the complexity you get
when you try and map objects to relations.

so a couple more questions then

Is Cache open source?
are the XML databases that are evolved and sophisticated enough to use in
production environments.

m

> of thought behind it -- it can solve a lot of problems.
>
> 2) Shift over to an XML database or a full-blown OODB (like Cache').
>
> Good luck.
>
> -Josh Berkus
>
>
>
>

Mike Sosteric <mikes(at)athabascau(dot)ca> Managing Editor, EJS <http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/>
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

Mike,

> I have a very good sense of the strengths of relational databases. But
> they are also limited when it comes to object orientaed data (like XML
> records). I though arrays would be a way to simply the complexity you get
> when you try and map objects to relations.

In my experience, most XML records are, in fact, simple tree structures that
are actually easy to represent in SQL. But I don't know about yours.

Certainly the translation of XML --> SQL Tree Structure is no more complex
than XML --> Array, that I can see.

> Is Cache open source?

No. It's a proprietary, and probably very expensive, database. There are no
open source OODBs that I know of, partly because of the current lack of
international standards for OODBs.

> are the XML databases that are evolved and sophisticated enough to use in
> production environments.

I don't know. The last time I evaluated XML databases was a year ago, when
there was nothing production-quality in existence. But I don't know what
the situation is now.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 30 Sep 2002, Josh Berkus wrote:

thanks for this. we will stick with the relational model.

m

>
> Mike,
>
> > I have a very good sense of the strengths of relational databases. But
> > they are also limited when it comes to object orientaed data (like XML
> > records). I though arrays would be a way to simply the complexity you get
> > when you try and map objects to relations.
>
> In my experience, most XML records are, in fact, simple tree structures that
> are actually easy to represent in SQL. But I don't know about yours.
>
> Certainly the translation of XML --> SQL Tree Structure is no more complex
> than XML --> Array, that I can see.
>
> > Is Cache open source?
>
> No. It's a proprietary, and probably very expensive, database. There are no
> open source OODBs that I know of, partly because of the current lack of
> international standards for OODBs.
>
> > are the XML databases that are evolved and sophisticated enough to use in
> > production environments.
>
> I don't know. The last time I evaluated XML databases was a year ago, when
> there was nothing production-quality in existence. But I don't know what
> the situation is now.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

Mike Sosteric <mikes(at)athabascau(dot)ca> Managing Editor, EJS <http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/>
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

Mike,

> thanks for this. we will stick with the relational model.

Hey, don't make your decision entirely based on my advice. Do some
research! I'm just responding "off the cuff" to your questions.

If you do take the relational approach, post some sample problems here and
people can help you with how to represent XML data relationally.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Notice the part I highlighted. The time returned is
> implementation-dependent "during the execution of S". Now, if we do:

> BEGIN;
> SELECT CURRENT_TIMESTAMP;
> SELECT CURRENT_TIMESTAMP;

> the time currently returned for the second query is _not_ during the
> duration of S (S being an SQL procedure statement)

Not so fast. What is an "SQL procedure statement"?

Our interactive commands do not map real well to the spec's definitions.
Consider for example SQL92 section 4.17:

4.17 Procedures

A <procedure> consists of a <procedure name>, a sequence of <pa-
rameter declaration>s, and a single <SQL procedure statement>.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
A <procedure> in a <module> is invoked by a compilation unit as-
sociated with the <module> by means of a host language "call"
statement that specifies the <procedure name> of the <procedure>
and supplies a sequence of parameter values corresponding in number
and in <data type> to the <parameter declaration>s of the <proce-
dure>. A call of a <procedure> causes the <SQL procedure statement>
that it contains to be executed.

The only thing you can easily map this onto in Postgres is stored
functions; your reading would then say that each Postgres function call
requires its own evaluation of current_timestamp, which I think we are
all agreed would be a disastrous interpretation.

It would be pretty easy to make the case that an ECPG module represents
a "procedure" in the spec's meaning, in which case it is *necessary* for
spec compliance that the ECPG module be able to execute all its commands
with the same value of current_timestamp. This would look like a series
of interactive commands to the backend.

So I do not think that the spec provides clear support for your position.
The only thing that is really clear is that there is a minimum unit
of execution in which current_timestamp is not supposed to change.
It does not clearly define any maximum unit; and it is even less clear
that our interactive commands should be equated to "SQL procedure
statement".

regards, tom lane

On Mon, 30 Sep 2002, Josh Berkus wrote:

Don't worry.

Our biggest problem is that each XML data entry, say

<title=en>This is the title</title>

has an language attribute. if there are, say 67 seperate items, each with
multiple languages, then the comlexity of the table structure skyrockets
because you have to allow for multiple titles, multiple names, multiple
everything.

the resulting relational model is icky to say the least. The question, is
how to simplify that. I had thought arrays would help because you can
store the multiple language strings in a single table along with other
records..

any ideas?

m

>
> Mike,
>
> > thanks for this. we will stick with the relational model.
>
> Hey, don't make your decision entirely based on my advice. Do some
> research! I'm just responding "off the cuff" to your questions.
>
> If you do take the relational approach, post some sample problems here and
> people can help you with how to represent XML data relationally.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>

Mike Sosteric <mikes(at)athabascau(dot)ca> Managing Editor, EJS <http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/>
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

Mike,

> has an language attribute. if there are, say 67 seperate items, each with
> multiple languages, then the comlexity of the table structure skyrockets
> because you have to allow for multiple titles, multiple names, multiple
> everything.

This looks soluable several ways.

Question #1: If each record has 67 fields, and each field may appear in
several languages, is it possible for some fields to be in more languages
than others? I.e. if "title-en" and "title-de" exist, does it follow that
"content-en" and "content-de" exist as well? Or not?

Question #2: Does your XML schema allow locall defined attributes? That is,
do some records have entire attributes ("fields" ) that other records do not?

Suggestion #1: Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book
for giving you ideas on how to adapt SQL structures to odd purposes.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 30 Sep 2002, Josh Berkus wrote:

>
> Question #1: If each record has 67 fields, and each field may appear in
> several languages, is it possible for some fields to be in more languages
> than others? I.e. if "title-en" and "title-de" exist, does it follow that
> "content-en" and "content-de" exist as well? Or not?

yes.

>
> Question #2: Does your XML schema allow locall defined attributes? That is,
> do some records have entire attributes ("fields" ) that other records do not?

yes.

>
> Suggestion #1: Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book
> for giving you ideas on how to adapt SQL structures to odd purposes.

I have ordered the book from amazon.ca

m

>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Mike Sosteric <mikes(at)athabascau(dot)ca> Managing Editor, EJS <http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/>
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."

___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---

How can you make a difference between now('statement'), and
now('immediate').
To me they are the same thing. Why not simply now() for transaction, and
now('CLOCK') or better yet system_clock() or clock() for curent time.

JLL

Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

OK, forget system_clock() or clock() timeofday() will do.

Jean-Luc Lachance wrote:
>
> How can you make a difference between now('statement'), and
> now('immediate').
> To me they are the same thing. Why not simply now() for transaction, and
> now('CLOCK') or better yet system_clock() or clock() for curent time.
>
> JLL

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Notice the part I highlighted. The time returned is
> > implementation-dependent "during the execution of S". Now, if we do:
>
> > BEGIN;
> > SELECT CURRENT_TIMESTAMP;
> > SELECT CURRENT_TIMESTAMP;
>
> > the time currently returned for the second query is _not_ during the
> > duration of S (S being an SQL procedure statement)
>
> Not so fast. What is an "SQL procedure statement"?
>
> Our interactive commands do not map real well to the spec's definitions.
> Consider for example SQL92 section 4.17:
>
> 4.17 Procedures
>
> A <procedure> consists of a <procedure name>, a sequence of <pa-
> rameter declaration>s, and a single <SQL procedure statement>.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> A <procedure> in a <module> is invoked by a compilation unit as-
> sociated with the <module> by means of a host language "call"
> statement that specifies the <procedure name> of the <procedure>
> and supplies a sequence of parameter values corresponding in number
> and in <data type> to the <parameter declaration>s of the <proce-
> dure>. A call of a <procedure> causes the <SQL procedure statement>
> that it contains to be executed.
>
> The only thing you can easily map this onto in Postgres is stored
> functions; your reading would then say that each Postgres function call
> requires its own evaluation of current_timestamp, which I think we are
> all agreed would be a disastrous interpretation.
>
> It would be pretty easy to make the case that an ECPG module represents
> a "procedure" in the spec's meaning, in which case it is *necessary* for
> spec compliance that the ECPG module be able to execute all its commands
> with the same value of current_timestamp. This would look like a series
> of interactive commands to the backend.
>
> So I do not think that the spec provides clear support for your position.
> The only thing that is really clear is that there is a minimum unit
> of execution in which current_timestamp is not supposed to change.
> It does not clearly define any maximum unit; and it is even less clear
> that our interactive commands should be equated to "SQL procedure
> statement".

OK, you don't like "SQL procedure statement". Let's look at SQL92:

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

so, again, we have wording that is has to be "during" the SQL statement.

Also, we have MSSQL, Interbase, and now Oracle modifying
CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
in a few hours ago.)

Perhaps we need a vote on this.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Also, we have MSSQL, Interbase, and now Oracle modifying
> CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
> in a few hours ago.)

Weren't you dissatisfied with the specificity of that Oracle report?

> Perhaps we need a vote on this.

Perhaps, but let's wait till the facts are in.

regards, tom lane

I was wondering why is such a rage against arrays.

I posted 2 very common problems where arrays provide
the only natural (and efficient) fit. (and got no responses)
So it seems to me that:

- Arrays implementation (along with the intarray package) in postgresql
is well performing and stable.
- Some problems shout out for array usage.
- The Array interface is defined in java.sql package.
(I dont know if sql arrays is in some standard but it seems that
Java sees it that way, at least).
- The Array interface is implemented in the official postgresql java
package.
- In some problems replacing arrays according the tradition relational
paradigm would end up in a such a performance degradation, that
some applications would be unusable.
- Oleg and Teodor did a great job in intarray, making array usage
easy and efficient.

Thanx!

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

>>>>> "Josh" == Josh Berkus <josh(at)agliodbs(dot)com> writes:

Josh> Now, I know at least one person who is using arrays to store
Josh> scientific data. However, that data arrives in his lab in
Josh> the form of matrices, and is not used for joins or query
Josh> criteria beyond a simple "where" clause.

Indeed, my first attempt to use arrays was to maintain some basic
statistics about a set of data. The array elements where to be
distribution moments and would only be used in "where" clauses. The
problem was that I wanted to be about to update the statistics using
triggers whenever the main data was updated. The inability to access
a specific array element in PL/pgSQL code made this so painful I ended
up just extending a table with more columns.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Achilleus,

> I was wondering why is such a rage against arrays.
>
> I posted 2 very common problems where arrays provide
> the only natural (and efficient) fit. (and got no responses)
> So it seems to me that:

All of your points are correct.

Us "old database hands" have a knee-jerk reaction against arrays for
long-term data storage because, much of the time, developers use arrays
because they are lazy or don't understand the relational model instead
of because they are the best thing to use. This is particularly true
of people who come to database development from, say, web design.

In this thread particularly, Mike was suggesting using arrays for a
field used in JOINs, which would be a royal mess. Which was why you
heard so many arguments against using arrays.

Or, to put it another way:

1. Array data types are perfect for storing data that arrives in the
form of arrays or matricies, such as scientific data , or interface
programs that store arrays of object properties.

2. For other purposes, arrays are a very poor substitute for proper
sub-table storage of related data according to the relational model.

3. The distinguishing factor is "atomicity": ask yourself: "is this
array a discrete and undivisible unit, or is is a collection of related
but mutable elements?" If the former, use and array. If the latter,
use a sub-table.

Clearer now?

-Josh Berkus

[ Thread moved to hackers.]

OK, I have enough information from the various other databases to make a
proposal. It seems the other databases, particularly Oracle, record
CURRENT_TIMESTAMP as the time of statement start. However, it isn't the
time of statement start from the user's perspective, but rather from the
database's perspective, i.e. if you call a function that has two
statements in it, each statement could have a different
CURRENT_TIMESTAMP.

I don't think that is standards-compliant, and I don't think any of our
users want that. What they probably want is to have a fixed
CURRENT_TIMESTAMP from the time the query is submitted until it is
completed. We can call that the "statement arrival time" version of
CURRENT_TIMESTAMP. I don't know if any of the other databases support
this concept, but it seems the most useful, and is closer to the
standards and to other databases than we are now.

So, we have a couple of decisions to make:

Should CURRENT_TIMESTAMP be changed to "statement arrival time"?
Should now() be changed the same way?
If not, should now() and CURRENT_TIMESTAMP return the same type of
value?

One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
leave now() as transaction start time.

Also, should we added now("val") where val can be "transaction",
"statement", or "clock"?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, Oct 03, 2002 at 04:18:08PM -0400, Bruce Momjian wrote:
>
> So, we have a couple of decisions to make:
>
> Should CURRENT_TIMESTAMP be changed to "statement arrival time"?
> Should now() be changed the same way?
> If not, should now() and CURRENT_TIMESTAMP return the same type of
> value?
>
> One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
> leave now() as transaction start time.

A disadvantage to this, as I see it, is that users may have depended on
the traditional Postgres behaviour of time "freezing" in transaction.
You always had to select timeofday() for moving time. I can see an
advantage in making what Postgres does somewhat more like what other
people do (as flat-out silly as some of that seems to be). Still, it
looks to me like the present CURRENT_TIMESTAMP implementation is at
least as much like the spec as anyone else's implementation, and more
like the spec than many of them. So I'm still not clear on what
problem the change is going to fix, especially since it breaks with
traditional behaviour.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

Andrew Sullivan wrote:
> On Thu, Oct 03, 2002 at 04:18:08PM -0400, Bruce Momjian wrote:
> >
> > So, we have a couple of decisions to make:
> >
> > Should CURRENT_TIMESTAMP be changed to "statement arrival time"?
> > Should now() be changed the same way?
> > If not, should now() and CURRENT_TIMESTAMP return the same type of
> > value?
> >
> > One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
> > leave now() as transaction start time.
>
> A disadvantage to this, as I see it, is that users may have depended on
> the traditional Postgres behavior of time "freezing" in transaction.
> You always had to select timeofday() for moving time. I can see an
> advantage in making what Postgres does somewhat more like what other
> people do (as flat-out silly as some of that seems to be). Still, it
> looks to me like the present CURRENT_TIMESTAMP implementation is at
> least as much like the spec as anyone else's implementation, and more
> like the spec than many of them. So I'm still not clear on what
> problem the change is going to fix, especially since it breaks with
> traditional behavior.

Uh, why change? Well, we have a "tradition" issue here, and changing it
will require something in the release notes. The big reason to change
is that most people using CURRENT_TIMESTAMP are not anticipating that it
is transaction start time, and are asking/complaining. We had one only
this week. If it were obvious to users when they used it, we could just
say it is our way of doing it, but in most cases it is catching people
by surprised. Given that other DB's have CURRENT_TIMESTAMP changing
even more frequently than we think is reasonable, it would make sense to
change it so it more closely matches what people expect, both new SQL
users and users moving from other DBs.

So, in summary, reasons for the change:

more intuitive
more standard-compliant
more closely matches other db's

Reasons not to change:

PostgreSQL traditional behavior

Does that help?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> So, in summary, reasons for the change:
> more intuitive
> more standard-compliant
> more closely matches other db's

I'd give you the first and third of those. As Andrew noted, the
argument that "it's more standard-compliant" is not very solid.

> Reasons not to change:
> PostgreSQL traditional behavior

You've phrased that in a way that makes it sound like the decision
is a no-brainer. How about

Breaks existing Postgres applications in non-obvious ways

which I think is a more realistic description of the downside.

Also, it seems a lot of people who have thought about this carefully
think that the start-of-transaction behavior is just plain more useful.
The fact that it surprises novices is not a reason why people who know
the behavior shouldn't want it to work like it does. (The behavior of
nextval/currval for sequences surprises novices, too, but I haven't
heard anyone claim we should change it because of that.)

So I think a fairer summary is

Pro:

more intuitive (but still not what an unversed person would
expect, namely true current time)
arguably more standard-compliant
more closely matches other db's (but still not very closely)

Con:

breaks existing Postgres applications in non-obvious ways
arguably less useful than our traditional behavior

I've got no problem with the idea of adding a way to get at
statement-arrival time. (I like the idea of a parameterized version of
now() to provide a consistent interface to all three functionalities.)
But I'm less than enthused about changing the existing functions to give
pride of place to statement-arrival time. In the end, I think that
transaction-start time is the most commonly useful and safest variant,
and so I feel it ought to have pride of place as the easiest one to get
at.

regards, tom lane

On Thu, Oct 03, 2002 at 07:09:33PM -0400, Tom Lane wrote:

> statement-arrival time. (I like the idea of a parameterized version of
> now() to provide a consistent interface to all three functionalities.)

I like this, too. I think it'd be probably useful. But. . .

> pride of place to statement-arrival time. In the end, I think that
> transaction-start time is the most commonly useful and safest variant,

. . .I also think this is true. If I'm doing a bunch of database
operations in one transaction, there is a remarkably good argument
that they happened "at the same time". After all, the marked passage
of time is probably just an unfortunate side effect of the inability
of my database can't process things instantaneously.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > So, in summary, reasons for the change:
> > more intuitive
> > more standard-compliant
> > more closely matches other db's
>
> I'd give you the first and third of those. As Andrew noted, the
> argument that "it's more standard-compliant" is not very solid.

The standard doesn't say anything about transaction in this regard. I
actually think Oracle is closer to the standard than we are right now.

> > Reasons not to change:
> > PostgreSQL traditional behavior
>
> You've phrased that in a way that makes it sound like the decision
> is a no-brainer. How about
>
> Breaks existing Postgres applications in non-obvious ways
>
> which I think is a more realistic description of the downside.

I had used Andrew's words:

the traditional Postgres behaviour of time "freezing" in transaction.

Yes, "breaking" is a clearer description.

> Also, it seems a lot of people who have thought about this carefully
> think that the start-of-transaction behavior is just plain more useful.
> The fact that it surprises novices is not a reason why people who know
> the behavior shouldn't want it to work like it does. (The behavior of
> nextval/currval for sequences surprises novices, too, but I haven't
> heard anyone claim we should change it because of that.)

No one has suggested a more intuitive solution for sequences, or we
would have discussed it.

> So I think a fairer summary is
>
> Pro:
>
> more intuitive (but still not what an unversed person would
> expect, namely true current time)
> arguably more standard-compliant

What does "arguably" mean? That seems more like a throw-away objection.

> more closely matches other db's (but still not very closely)

Closer!

No need to qualify what I said. It is "more" of all these things, not
"exactly", of course.

> Con:
>
> breaks existing Postgres applications in non-obvious ways
> arguably less useful than our traditional behavior
>
> I've got no problem with the idea of adding a way to get at
> statement-arrival time. (I like the idea of a parameterized version of
> now() to provide a consistent interface to all three functionalities.)
> But I'm less than enthused about changing the existing functions to give
> pride of place to statement-arrival time. In the end, I think that
> transaction-start time is the most commonly useful and safest variant,
> and so I feel it ought to have pride of place as the easiest one to get
> at.

Well, let's see what others say. If no one is excited about the change,
we can just document its current behavior. Oh, I see it is already
documented in func.sgml:

It is quite important to realize that
<function>CURRENT_TIMESTAMP</function> and related functions all return
the time as of the start of the current transaction; their values do not
increment while a transaction is running. But
<function>timeofday()</function> returns the actual current time.

Seems that isn't helping enough to reduce the number of people who are
surprised by our behavior. I don't think anyone would be surprised by
statement time.

What do others think?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 2002-10-04 at 01:41, Bruce Momjian wrote:
> Well, let's see what others say. If no one is excited about the change,
> we can just document its current behavior. Oh, I see it is already
> documented in func.sgml:
>
> It is quite important to realize that
> <function>CURRENT_TIMESTAMP</function> and related functions all return
> the time as of the start of the current transaction; their values do not
> increment while a transaction is running. But
> <function>timeofday()</function> returns the actual current time.
>
> Seems that isn't helping enough to reduce the number of people who are
> surprised by our behavior. I don't think anyone would be surprised by
> statement time.
>
> What do others think?

I would prefer that CURRENT_TIME[STAMP] always produce the same time
within a transaction. If it is changed, it will certainly break one of
my applications, which explicitly depends on the current behaviour. If
you change it, please provide an alternative way of doing the same
thing.

I can see that the current behaviour might give surprising results in a
long running transaction. Surprise could be reduced by giving the time
of first use within the transaction rather than the start of the
transaction.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For the word of God is quick, and powerful, and
sharper than any twoedged sword, piercing even to the
dividing asunder of soul and spirit, and of the joints
and marrow, and is a discerner of the thoughts and
intents of the heart." Hebrews 4:12

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> I can see that the current behaviour might give surprising results in a
> long running transaction. Surprise could be reduced by giving the time
> of first use within the transaction rather than the start of the
> transaction.

[ cogitates ... ] Hmm, we could do that, and it probably would break
few if any existing apps. But would it really reduce the surprise
factor? The complaints we've heard so far all seemed to come from
people who expected multiple current_timestamp calls to show advancing
times within a transaction.

Oliver's idea might be worth doing just on performance grounds: instead
of a gettimeofday() call at the start of every transaction, we'd only
have to reset a flag variable. When and if current_timestamp is done
inside the transaction, then call the kernel to ask what time it is.
We win on every transaction that does not contain a current_timestamp
call, which is probably a good bet for most apps. But I don't think
this does much to resolve the behavioral complaints.

regards, tom lane

On Sun, 29 Sep 2002, Mike Sosteric wrote:

> On Sun, 29 Sep 2002, Bruce Momjian wrote:
>
> Apologies in advance if there is a more appropriate list.
>
> We are currently developing a database to host some complicated, XMl
> layered data. We have chosen postgres because of its ability to store
> multidimensional arrays. We feel that using these will allow us to
> simplify the database structure considerably by storing some data in
> multidimensional arrays.

the long and the short of it is that arrays are useful to store data, but
should not be used where you need to look up the data in them in a where
clause.