From: | Saiful Muhajir <saifulmuhajir(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Find rows with "timestamp out of range" |
Date: | 2017-06-20 01:48:17 |
Message-ID: | CAA0dH_ucbGWSZheewb-oiSi3_H4+fOwjm=0ypoBmwOHCsR+_6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I tried with
SELECT comment_id FROM comments WHERE create_time < '1 Jan 1800';
But the result is same: ERROR 22008 timestamp out of range.
With the help from #postgresql community, I successfully extract the
min(create_time) with below query:
SELECT min(trim(leading '\' from
timestamp_send(create_time)::text)::bit(64)::bigint) FROM comments;
min
-------
-332024613738615000
Which is in microseconds from 2000-01-01 00:00:00 and the result is
correspond to ~10500 BC. Way out of range. CMIIW
So, while trying to figure out the "broken" rows with this query:
SELECT comment_id FROM comments WHERE trim(leading '\' from
timestamp_send(create_time)::text)::bit(64)::bigint > -31556908800000000
And there are many rows, 800+. So, I guess this looks like corrupted data
in the table.
A while ago, the server was crashed when our datacenter experienced power
outage. But I didn't checked anything until today. It seems that what's
left is how do I fix this because we don't store old backups.
For your information, I forgot to mention that this is Postgres 9.3.15 with
fsync=ON.
--
Regards,
Saiful Muhajir <http://saifulmuhajir.web.id>
On 19 June 2017 at 20:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Saiful Muhajir <saifulmuhajir(at)gmail(dot)com> writes:
> > I have a table with around *133 million rows* with two timestamp columns.
> > While trying to copy some columns for a new database, using *\COPY *, the
> > error occurred with: *timestamp out of range*
>
> > *select comment_id, create_time from comments where create_time < '1 Jan
> > 1800';*
> > ERROR: 22008: timestamp out of range
> > LOCATION: timestamp_out, timestamp.c:226
>
> As you can see, the error is occurring in timestamp_out(), ie in the
> attempt to display the specific value. You could probably do this
> successfully:
>
> select comment_id from comments where create_time < '1 Jan 1800';
>
> and to fix, maybe
>
> update comments set create_time = '-infinity' where create_time < '1 Jan
> 1800';
>
>
> As to what's actually going on, we made an effort a few years back to
> tighten up the logic concerning exactly what is the minimum legal
> timestamp value --- it's somewhere in 4714BC, but as I recall, the exact
> boundary where it failed used to depend on your TimeZone setting. (Maybe
> it still does, for you ... what PG version is this exactly?) I'm betting
> that you have a value right on the hairy edge of failure, that was
> accepted when input but is now rejected during display, either because of
> the aforesaid logic changes or because you're using a different TimeZone
> setting than it was input under.
>
> It might be entertaining to try
>
> select comment_id, create_time + interval '1 year'
> from comments where create_time < '1 Jan 1800';
>
> and see if that is able to produce output.
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ed Rouse | 2017-07-06 19:13:09 | Finding the negative |
Previous Message | Tom Lane | 2017-06-19 13:33:10 | Re: Find rows with "timestamp out of range" |