timestamp bug 7.4beta3
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Nayib Kiuhan
Your email address : nayib@onlinetec.net
System Configuration
---------------------
Architecture (example: Intel Pentium) : Sun Sparc IIIi
Operating System (example: Linux 2.0.26 ELF) : Solaris 9
PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4beta3
Compiler used (example: gcc 2.95.2) : gcc 3.3
Please enter a FULL description of your problem:
------------------------------------------------
In versions before 7.4beta3 I use to have tables with
"date" timestamp DEFAULT 'now'
It use to works properly, placing the actual date at the moment a new record was inserted. Now it always have the same date which correspond to the date at creating the table.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
On Oct-15 2003, Wed, 02:08 -0400
Nayib Kiuhan <nayib@onlinetec.net> wrote:
"date" timestamp DEFAULT 'now'
I believe you should be using
"date" timestampt DEFAULT CURRENT_TIMESTAMP(0)
--
Tomas Szepe <szepe@pinerecords.com>
On Wed, 2003-10-15 at 02:08, Nayib Kiuhan wrote:
In versions before 7.4beta3 I use to have tables with
"date" timestamp DEFAULT 'now'
It use to works properly, placing the actual date at the moment a new
record was inserted. Now it always have the same date which correspond
to the date at creating the table.
From the 7.4 HISTORY file:
'now' will no longer work as a column default, use now() (change
required for prepared statements) (Tom)
Admittedly, this change should also be noted in the 'migration to 7.4
section' of the release notes -- I'll send a patch to this effect to
pgsql-patches.
-Neil
Thank you for your answer. I tried before with 'now()' (inside the single aspen) and didn't work . Now it is working very well without the aspens as you already mentioned. It is a good idea to through out an error during the table creation if the format is not as indicated (now()), because when I created my tables with the old format, it did not show any problem, I just figure out that something was wrong with my tables once my java program start to do weird things.
Again, many thanks,
Nayib
----- Original Message -----
From: "Neil Conway" <neilc@samurai.com>
To: "Nayib Kiuhan" <nayib@onlinetec.net>
Cc: <pgsql-bugs@postgresql.org>
Sent: Wednesday, October 15, 2003 10:50 AM
Subject: Re: [BUGS] timestamp bug 7.4beta3
Show quoted text
On Wed, 2003-10-15 at 02:08, Nayib Kiuhan wrote:
In versions before 7.4beta3 I use to have tables with
"date" timestamp DEFAULT 'now'
It use to works properly, placing the actual date at the moment a new
record was inserted. Now it always have the same date which correspond
to the date at creating the table.From the 7.4 HISTORY file:
'now' will no longer work as a column default, use now() (change
required for prepared statements) (Tom)Admittedly, this change should also be noted in the 'migration to 7.4
section' of the release notes -- I'll send a patch to this effect to
pgsql-patches.-Neil
On Wed, 2003-10-15 at 13:29, Nayib Kiuhan wrote:
It is a good idea to through out an error during the table creation if
the format is not as indicated (now()), because when I created my
tables with the old format, it did not show any problem
I agree that this kind of silent backward-incompatibility isn't good,
but I don't think that we can reject 'now' or its variants outright:
it's legal syntax, it just doesn't do what you might expect.
Since Tom implemented this change, perhaps he can contribute some
insight -- Tom, can we improve the upgrade experience in this case?
-Neil
Neil Conway <neilc@samurai.com> writes:
I agree that this kind of silent backward-incompatibility isn't good,
It's unpleasant, but we were going to have to bite this bullet sooner or
later. Allowing 'now' to work like a non-constant in this context was
always a fragile hack.
Tom, can we improve the upgrade experience in this case?
I don't see any very simple way; certainly not anything I'd want to
stick in in a last-minute fashion. There are too many possible
representations of 'now', and if we did try to substitute now() we'd
be disabling a legal (if possibly useless) behavior.
It's not like it's hard to fix post-upgrade (or pre-upgrade, for that
matter): a simple "ALTER TABLE ... SET DEFAULT now()" will do it.
regards, tom lane
It's not like it's hard to fix post-upgrade (or pre-upgrade, for that
matter): a simple "ALTER TABLE ... SET DEFAULT now()" will do it.
Like 'now' and now() are both valid, at least will be good to have a note when a table that includes 'now' is created, bringing the attention to people to review 'now' and now() functionalities.
Regards,
Nayib