Thread: 'Default' troubles again. This time with time :)))
Hello! Look at this: bdb=> create table dd (aa int4, gg timestamp default 'now'); CREATE bdb=> insert into dd (aa) values (5); INSERT 581771 1 bdb=> select * from dd; aa|gg --+---------------------- 5|1999-07-18 14:40:02+05 (1 row) So far so good. But some seconds later: bdb=> insert into dd (aa) values (6); INSERT 581772 1 bdb=> select * from dd; aa|gg --+---------------------- 5|1999-07-18 14:40:02+05 6|1999-07-18 14:40:02+05 (2 rows) See? Time haven't changed! And again: bdb=> insert into dd (aa) values (123); INSERT 581773 1 bdb=> select * from dd; aa|gg ---+---------------------- 5|1999-07-18 14:40:02+05 6|1999-07-18 14:40:02+05 123|1999-07-18 14:40:02+05 (3 rows) Seems there is lot'a trouble with default values :) -- Leon.
Leon <leon@udmnet.ru> writes:
> bdb=> create table dd (aa int4, gg timestamp default 'now');
> [ default value doesn't change over time ]
This oughta be in the FAQ I think ...
When you write a simple constant default, it gets coerced to the target
data type before the default information is stored. So, what you
effectively did was to create table dd with a default value for gg
of the time at which you executed 'create table'.
To get the effect you want, you need the text string 'now' to be
converted to timestamp type *at the time an INSERT uses the default*.
Any expression more complex than a simple constant will do, but the
usual idiom for this task is:
create table dd (aa int4, gg timestamp default text 'now');
> Seems there is lot'a trouble with default values :)
There *are* some known bugs with defaults: if you write a default
for a fixed-length character field (ie, char(n)) it's a good idea
to make sure the default value is exactly n characters. 6.4 messes
up badly with a wrong-length default. 6.5 cures the simplest case
(constant default value) and I'm currently working on a more general
fix for 6.6. That's got nothing to do with the timestamp question,
though.
regards, tom lane
Tom Lane wrote: > > > [ default value doesn't change over time ] > > This oughta be in the FAQ I think ... > > When you write a simple constant default, it gets coerced to the target > data type before the default information is stored. So, what you > effectively did was to create table dd with a default value for gg > of the time at which you executed 'create table'. > Tom! I tested your method of creating table with create table ww (aa int4, bb timestamp default text 'now'), and it didn't work either! (BTW, this is exactly the way docs suggest doing it.) See? I promised to deliver a real bug and I did it! :))) Yes, docs mumble something about 'cacheable' and 'non-cacheable' functions, but it is not clear to me how Postgres discerns them. It is complete puzzle to me why 'USER' is cacheable and 'CURRENT_TIMESTAMP' is not. This distinction, I think, should be made clearer. Maybe in the sensible form of two-column table in the docs. :) One column is function name, the other is 'cacheability'. -- Leon.
Leon <leon@udmnet.ru> writes:
> Tom! I tested your method of creating table with
> create table ww (aa int4, bb timestamp default text 'now'),
> and it didn't work either! (BTW, this is exactly the way docs suggest
> doing it.) See? I promised to deliver a real bug and I did it! :)))
By golly, you're right. It works as advertised for a DATETIME field,
which is the case I'd been testing. But for a TIMESTAMP field the
constant gets pre-coerced anyway :-(. Wonder why ... will look into
it, since I'm busy hacking on that part of the system now.
> Yes, docs mumble something about 'cacheable' and 'non-cacheable'
> functions, but it is not clear to me how Postgres discerns them.
The proiscachable field in table pg_proc is presumably supposed to
tell this. Doesn't look like it's set in an intelligent manner
for most of the built-in functions though. I don't think it has
anything to do with the bug for TIMESTAMP...
regards, tom lane
Added to FAQ as:
4.22) How do I create a column that will default to the current time?
The tempation is to do:
create table test (x int, modtime timestamp default 'now');
but this makes the column default to the time of table creation, not the
time of row insertion.
Instead do:
create table test (x int, modtime timestamp default text 'now');
The casting of the value to text prevents the default value from being
computed at table
creation time, and delays it until insertion time.
> Leon <leon@udmnet.ru> writes:
> > bdb=> create table dd (aa int4, gg timestamp default 'now');
>
> > [ default value doesn't change over time ]
>
> This oughta be in the FAQ I think ...
>
> When you write a simple constant default, it gets coerced to the target
> data type before the default information is stored. So, what you
> effectively did was to create table dd with a default value for gg
> of the time at which you executed 'create table'.
>
> To get the effect you want, you need the text string 'now' to be
> converted to timestamp type *at the time an INSERT uses the default*.
> Any expression more complex than a simple constant will do, but the
> usual idiom for this task is:
>
> create table dd (aa int4, gg timestamp default text 'now');
>
>
> > Seems there is lot'a trouble with default values :)
>
> There *are* some known bugs with defaults: if you write a default
> for a fixed-length character field (ie, char(n)) it's a good idea
> to make sure the default value is exactly n characters. 6.4 messes
> up badly with a wrong-length default. 6.5 cures the simplest case
> (constant default value) and I'm currently working on a more general
> fix for 6.6. That's got nothing to do with the timestamp question,
> though.
>
> regards, tom lane
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Added to FAQ as:
> 4.22) How do I create a column that will default to the current time?
> Instead do:
> create table test (x int, modtime timestamp default text 'now');
Actually, Leon's complaint was specifically that that doesn't work!
Try it with current sources:
regression=> create table test (x int, modtime timestamp default text 'now');
CREATE
regression=> insert into test values (1);
INSERT 545995 1
regression=> insert into test values (2);
INSERT 545996 1
regression=> insert into test values (3);
INSERT 545997 1
regression=> select * from test;
x|modtime
-+----------------------
1|1999-09-28 09:53:03-04
2|1999-09-28 09:53:03-04
3|1999-09-28 09:53:03-04
(3 rows)
(and no, I don't type that fast :-)) It does work for a datetime column,
but not for type timestamp. I looked into this a while back and found
that it's caused by StoreAttrDefault's roundabout way of making defaults
plus lack of a full set of pg_proc entries for type timestamp --- the
conversion ends up happening anyway when the default expression is
parsed a second time.
I think the FAQ ought to recommend
create table test (x int, modtime timestamp default now());
which does work as desired for both data types.
One of the end results of this constant folding + proiscachable work
should be that the whole problem goes away, because the parser will
be aware that text-to-datetime is a noncachable function and will not
try to simplify 'now'::datetime (or ::timestamp) at parse time.
But until everyone is using 6.6 or later, we had better recommend
workarounds like the above.
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Added to FAQ as:
> > 4.22) How do I create a column that will default to the current time?
>
> > Instead do:
> > create table test (x int, modtime timestamp default text 'now');
>
> Actually, Leon's complaint was specifically that that doesn't work!
> Try it with current sources:
>
> regression=> create table test (x int, modtime timestamp default text 'now');
> CREATE
> regression=> insert into test values (1);
> INSERT 545995 1
> regression=> insert into test values (2);
> INSERT 545996 1
> regression=> insert into test values (3);
> INSERT 545997 1
> regression=> select * from test;
> x|modtime
> -+----------------------
> 1|1999-09-28 09:53:03-04
> 2|1999-09-28 09:53:03-04
> 3|1999-09-28 09:53:03-04
> (3 rows)
>
> (and no, I don't type that fast :-)) It does work for a datetime column,
> but not for type timestamp. I looked into this a while back and found
> that it's caused by StoreAttrDefault's roundabout way of making defaults
> plus lack of a full set of pg_proc entries for type timestamp --- the
> conversion ends up happening anyway when the default expression is
> parsed a second time.
>
> I think the FAQ ought to recommend
>
> create table test (x int, modtime timestamp default now());
>
> which does work as desired for both data types.
>
> One of the end results of this constant folding + proiscachable work
> should be that the whole problem goes away, because the parser will
> be aware that text-to-datetime is a noncachable function and will not
> try to simplify 'now'::datetime (or ::timestamp) at parse time.
> But until everyone is using 6.6 or later, we had better recommend
> workarounds like the above.
New text is:
4.22) How do I create a column that will default to the current time?
The tempation is to do:
create table test (x int, modtime timestamp default 'now');
but this makes the column default to the time of table creation, not the
time of row insertion. Instead do:
CREATE TABLE test (x int, modtime timestamp default now() );
The calling of the function now() prevents the default value from being
computed at table creation time, and delays it until insertion time. We
believe this will not be a problem in post-6.5.* releases.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026