Обсуждение: bug in timestamp and out of range values
We found the problem on one of our production 8.1 servers; a big concern is
that it breaks dumps (allows you to dump a table with a value that can't be
restored). Seems to be a problem in 8.2 as well. Here is the test case:
pagila=# create table t1 (c1 timestamp);
CREATE TABLE
pagila=# select to_date('3232098', 'MM/DD/YYYY');
to_date
---------------
4568-06-26 BC
(1 row)
pagila=# insert into t1 select to_date('3232098', 'MM/DD/YYYY');
INSERT 0 1
pagila=# select * from t1;
ERROR: timestamp out of range
pagila=#
pagila=#
pagila=# truncate t1;
TRUNCATE TABLE
pagila=# alter table t1 add check (c1 > '1900-01-01'::timestamp and c1
< '2100-01-01'::timestamp);
ALTER TABLE
pagila=# insert into t1 select to_date('3232098', 'MM/DD/YYYY');
ERROR: new row for relation "t1" violates check constraint "t1_c1_check"
pagila=#
pagila=#
here are some relevant irc logs discussing this:
16:41 < xzilla_> anyone have a copy of 8.2 handy ?
16:42 < AndrewSN> xzilla: yes, but it's a couple of weeks old
16:42 < xzilla> AndrewSN: s'ok... can you try and reproduce the following
error http://rafb.net/paste/results/PsPZDf57.html
16:44 < AndrewSN> xzilla: yes, I get that same result
16:45 < xzilla> guess i am obligated to send it to -bugs now :-)
16:46 < Primer> indeed
16:46 < AndrewSN> xzilla: is it a bug in to_date, the date->timestamp cast, or
timestamp_out, or all three?
16:52 < xzilla> AndrewSN: i dont think it is in to_date
16:53 < xzilla> AndrewSN: nor date->timestamp cast
16:53 < AndrewSN> xzilla: the timestamp value actually being stored in the
table is: -214839043200
16:53 < xzilla> AndrewSN: was thinking that it should have tossed an error on
insert of the invalid timestamp value
16:54 < xzilla> AndrewSN: how did you determine that ?
16:54 < AndrewSN> postgres=# select encode(timestamp_send(c1),'hex') from x1;
16:54 < AndrewSN> encode
16:54 < AndrewSN> ------------------
16:54 < AndrewSN> c24902b3d2400000
16:55 < AndrewSN> compare:
16:55 < AndrewSN> postgres=# select encode(float8send(-214839043200),'hex')
from x1;
16:55 < AndrewSN> encode
16:55 < AndrewSN> ------------------
16:55 < AndrewSN> c24902b3d2400000
16:55 < AndrewSN> (1 row)
16:55 < xzilla> AndrewSN: interesting
16:56 < AndrewSN> xzilla: what do you think should have tossed the error if
not the date->timestamp cast function?
16:57 < xzilla> AndrewSN: a check constraint on a timestamp range will cause
an error to be raised, which made me think it wasnt the
date->timestamp cast
16:58 < AndrewSN> xzilla: the "timestamp out of range" error is coming from
timestamp_out
16:58 < xzilla> http://rafb.net/paste/results/cTLlv583.html
16:59 < AndrewSN> xzilla: well, of course, because the result you're getting
from to_date is not in that range
17:00 < xzilla> AndrewSN: sure, but it also implies the result i am getting is
a valid timestamp
17:02 < AndrewSN> xzilla: ok, I think I see
17:02 < AndrewSN> xzilla: what happens is that timestamp_out barfs if the
Julian day would be negative.
17:02 < AndrewSN> xzilla: but the internal representation of timestamp is
quite happy to represent values earlier than that
17:05 < AndrewSN> xzilla: so I think, in fact, the problem is in to_date
17:06 < AndrewSN> xzilla: because to_date should not be returning negative
julian dates
17:06 -!- leafw [n=Any@cbg-off-client.mpi-cbg.de] has joined #postgresql
17:08 < AndrewSN> xzilla: note that date_out's display of '4568-06-26 BC' is
bogus, the date value being returned is much earlier
17:09 < xzilla> AndrewSN: if the internal representation of timestamp is happy
to represent the value, then isnt the problem in timestamp_out
(and the effects of to_date are only a side effect of how i
got the data in there)
17:09 < AndrewSN> eevar: select * from pg_language;
17:10 < AndrewSN> xzilla: arguably all of to_date, the date->timestamp cast
and timestamp_out are broken
17:11 < xzilla> AndrewSN: ok, i could see that :-) btw, if i do create
table t2 as select * from t1 i now have a table with the bad
timestamp that didnt directly require to_date
17:12 < AndrewSN> xzilla: of course, nothing is stopping you passing that
un-displayable timestamp value around once you got it in from
anywhere
17:12 < xzilla> AndrewSN: right
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> pagila=# select to_date('3232098', 'MM/DD/YYYY');
> to_date
> ---------------
> 4568-06-26 BC
> (1 row)
to_date's absymal lack of error checking is well known. It should
surely refuse that input altogether, given that format string.
Feel free to send a patch ...
As for the range issue, date_in does refuse negative Julian dates:
regression=# select '4714-01-27 BC'::date;
ERROR: date out of range: "4714-01-27 BC"
but again to_date doesn't:
regression=# select to_date('4714-01-27 BC', 'YYYY-MM-DD BC');
to_date
---------------
4714-01-27 BC
(1 row)
regards, tom lane
On Thursday 02 November 2006 17:48, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > pagila=# select to_date('3232098', 'MM/DD/YYYY');
> > to_date
> > ---------------
> > 4568-06-26 BC
> > (1 row)
>
> to_date's absymal lack of error checking is well known. It should
> surely refuse that input altogether, given that format string.
> Feel free to send a patch ...
>
> As for the range issue, date_in does refuse negative Julian dates:
>
> regression=# select '4714-01-27 BC'::date;
> ERROR: date out of range: "4714-01-27 BC"
>
> but again to_date doesn't:
>
> regression=# select to_date('4714-01-27 BC', 'YYYY-MM-DD BC');
> to_date
> ---------------
> 4714-01-27 BC
> (1 row)
>
I'm not concerned about to_date so much as I am that timestamp_in lets you
store values you can't read with timestamp_out. Once the value is in there
you can happily move it around with create table as and such...
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> I'm not concerned about to_date so much as I am that timestamp_in lets you
> store values you can't read with timestamp_out.
Your example does not demonstrate any such thing. What it demonstrates
is that to_date will let an out-of-range date into the system, not that
timestamp_in will. Counterexample:
regression=# select '4714-01-27 BC'::timestamp;
ERROR: timestamp out of range: "4714-01-27 BC"
regards, tom lane
>
> postgres=# select '4714-01-27 BC'::date;
> ERROR: date out of range: "4714-01-27 BC"
> postgres=# select cast(test as date) from timetest;
> test
> ---------------
> 4714-01-27 BC
> (1 row)
>
> postgres=#
> postgres=# select cast('4714-01-27 BC' as date);
> ERROR: date out of range: "4714-01-27 BC"
> postgres=#
>
> This seems pretty broken.
>
> Joshua D. Drake
And further this with timestamp instead of date:
postgres=# create table timestamptest(test timestamp);
CREATE TABLE
postgres=# insert into timestamptest values (to_date('4714-01-27 BC', 'YYYY-MM-DD BC'));
INSERT 159911988 1
postgres=# select * from timestamptest;
ERROR: timestamp out of range
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>> but again to_date doesn't:
>>
>> regression=# select to_date('4714-01-27 BC', 'YYYY-MM-DD BC');
>> to_date
>> ---------------
>> 4714-01-27 BC
>> (1 row)
>>
>
> I'm not concerned about to_date so much as I am that timestamp_in lets you
> store values you can't read with timestamp_out. Once the value is in there
> you can happily move it around with create table as and such...
Hmmm... if that is the case, I would also have a pretty significant
concern. We have basically created an environment that is unreliable
during a restore. Not to mention violating data type constraints.
postgres=# create table timetest (test date);
CREATE TABLE
postgres=# insert into timetest values (to_date('4714-01-27 BC', 'YYYY-MM-DD BC'));
INSERT 159911984 1
postgres=# select '4714-01-27 BC'::date;
ERROR: date out of range: "4714-01-27 BC"
postgres=# select cast(test as date) from timetest; test
---------------4714-01-27 BC
(1 row)
postgres=#
postgres=# select cast('4714-01-27 BC' as date);
ERROR: date out of range: "4714-01-27 BC"
postgres=#
This seems pretty broken.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate