Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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
[SQL] Proposal for Clean-up of Conversion Functions
Postgres Folks, As long as we're talking about the ToDo list, I'd like to make some simple proposals regarding Postgres' conversion functions. 1) Addition of remaing to_char functions: We should add to_char functions for the following datatypes: to_char(interval, 'format string') (I believe this is already in the todo) to_char(inet, 'format string') 2) Addition of the following to-datatype conversion functions: to_inet(text, optional 'format string') to_interval(text, optional 'format string') to_time(text, optional 'format string') Without the "format string" these functions would operate like the current "Interval" and "time" functions, but not require quoting the function name. 3) Addition of datatype testing functions. All of these functions would test whether a text value is CASTable or transformable as the specific datatype and return a boolean value *without* raising an exception. The absence of analogous functions is one of many issues holding back PL/pgSQL from PL/SQL parity, as data type validation must take place outside functions. is_timestamp(text, optional 'format string') is_interval(text, optional 'format string') is_time(text, optional 'format string') is_date(text, optional 'format string') is_inet(text, optional 'format string') is_number(text, optional 'format string') -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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 that is not generally
contained in a . All s that are generally contained, without an intervening
whose subject routines do not include an
SQL function, in s that are contained either
in S without an intervening or in an
contained in the
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a 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
[EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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
[EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] arrays
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. --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Josh Berkus <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
