Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Tue, 24 Sep 2002 11:19:12 +1000, Martijn van Oosterhout <[EMAIL PROTECTED]> 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 s, then all such ref- erences are effectively evaluated simultaneously. The time of evaluation of the 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 ---(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
On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 16:55:48 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
On Mon, 23 Sep 2002 23:35:13 -0400, Tom Lane <[EMAIL PROTECTED]> 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 ---(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] CURRENT_TIMESTAMP
> > Christoph Haller wrote: > > Hi pgsql-sql list, > > I did some testing around tables using a column > > timestamp with time zone not null default now(). > > I have noticed a valuable feature: > > As long as being inside a transaction initiated by > > begin; > > the return value of the now() function does not change. > > Is this intended - as written in the documentation? > > Can I rely on it, so future releases of PostgreSQL > > will act the same. > > Yes, this is intended and will not change. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > I vote the now() behaviour not to change. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Manfred Koizar <[EMAIL PROTECTED]> writes: > On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <[EMAIL PROTECTED]> > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] arrays (was untitled)
On Tue, 24 Sep 2002, Madhavi wrote:
> How do I pass an array as an aruments in a postgres function? And how do I
> use it in the function??
Well, I've only got a 7.3devel machine to test, but:
create function f(int[]) returns int as
'select $1[1];' language 'sql';
select f('{3,4}');
seems to work for example.
What are you actually trying to do and in what language?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] select case problem
i have a table with the fields empno (not null) and division_no (null allowed) what i wanted to do is to do a select case statement such that when division_no is null itll output 'No division' if its not null itll output 'with division' unfortunately this statement doesnt work ... i need help on the correct syntax select case division_no when null then 'no division' else 'with division' end from employee; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] select case problem
Hi, select case when division_no is null then 'no division' else 'with division' end from employee; Is the right syntax Regards -- Matteo Beccati http://www.phpadsnew.com http://phppgads.sourceforge.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> "Martijn" == Martijn van Oosterhout <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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
---(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
On Tue, Sep 24, 2002 at 10:55:41AM -0400, Roland Roberts wrote: > > "Martijn" == Martijn van Oosterhout <[EMAIL PROTECTED]> 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; SELECT current_timestamp; END; will give two identical timestamps? Ross ---(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
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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
[EMAIL PROTECTED]
Dancing Horse Hill
Elmwood, Wisconsin
---(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] CURRENT_TIMESTAMP
"Josh Berkus" <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> "Ross" == Ross J Reedstrom <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(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
Roland Roberts wrote: > > "Ross" == Ross J Reedstrom <[EMAIL PROTECTED]> 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 [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 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Timestamp Error - 7.2
We have moved from 7.1 to 7.2 and get the following error when
extracting dates.
Bad timestamp external representation ' '
eg. INSERT INTO mytable VALUES('1', '2001-09-24')
Seems to accept dd/mm/
(What about ISO default?)
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(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] CURRENT_TIMESTAMP
On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian <[EMAIL PROTECTED]> 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. SQL> select current_timestamp from rdb$database; = 2002-09-24 22:30:18. SQL> commit; Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
