Thread: date/time compatible problems in 7.2
Hello,
I has pg_dump my DB in 7.1.3 and try ro pg_restore it in 7.2
version. Almost all is clear, but restore of some tables generate
messages
like this:
psql:/.../dbdump/.dbrestore.tmp:1624094: ERROR: copy: line 1, Bad
timestamp external representation 'Fri 25 Jan 23:59:59 2002 KRAT'
psql:/.../dbdump/.dbrestore.tmp:1624094: lost synchronization with
server, resetting connection
....... And in a postmaster log I have for each pg_restore error like
above:
2002-02-07 14:36:05 ERROR: copy: line 1, Bad timestamp external
representation 'Wed 06 Feb 00:00:00 2002 KRAT'
2002-02-07 14:36:05 FATAL 1: Socket command type *** unknown
....... where *** is char in (1,2,3,7,8,-,/). What this mean?
I can't upgrade PostgreSQL from 7.1.3 to 7.2 since following incompatibles exists:
1. Function time(datetime) don't exists in 7.2? SELECT time('now'); processed ok in 7.1.3, but 7.2
says: parser: parse error at or near "'".
2. CREATE TABLE akka (tm TIMESTAMP WITH TIME ZONE); SET datestyle TO postgresql,european;
INSERTINTO akka VALUES ('akka'); INSERT INTO akka SELECT tm::text FROM akka; -- * Last SQL
processedwell in 7.1.3, but in 7.2 didn't: ERROR: Bad timestamp external representation 'Thu 07 Feb
16:36:50.730499 2002 KRAT' I has tried to CREATE TABLE akka with timestamp(0) column,
but
this
does not help. When I use WITHOUT TIME ZONE query(*) proceed good, but
I can't use it since my pg_dump'ed DB saved with timezone info.
Any ideas?
> I has pg_dump my DB in 7.1.3 and try ro pg_restore it in 7.2
> version.
> psql:/.../dbdump/.dbrestore.tmp:1624094: ERROR: copy: line 1, Bad
> timestamp external representation 'Fri 25 Jan 23:59:59 2002 KRAT'
> psql:/.../dbdump/.dbrestore.tmp:1624094: lost synchronization with
> server, resetting connection
Not sure why it is crashing. But "KRAT" is a time zone not recognized by
the PostgreSQL date/time parser. In fact it could be afaik (it is
mentioned but commented-out in the parser) but it either had a screwy
definition or I couldn't figure out what the definition was. It could be
added for 7.2.1 (and I could send a patch beforehand) if I knew the
proper definition. Check src/backend/utils/adt/datetime.c and look for
"krat".
> 1. Function time(datetime) don't exists in 7.2?
> SELECT time('now'); processed ok in 7.1.3, but 7.2 says:
> parser: parse error at or near "'".
Right. 7.2 implements the SQL99 feature of time precision, so "time()"
now indicates a data type, not a function call. Same for "timestamp()".
select time 'now'
or
select cast('now' as time)
is the preferred syntax for your use case anyway.
> 2. CREATE TABLE akka (tm TIMESTAMP WITH TIME ZONE);
> SET datestyle TO postgresql,european;
> INSERT INTO akka VALUES ('akka');
> INSERT INTO akka SELECT tm::text FROM akka; -- *
> Last SQL processed well in 7.1.3, but in 7.2 didn't:
> ERROR: Bad timestamp external representation 'Thu 07 Feb
> 16:36:50.730499 2002 KRAT'
Ah! 7.1 and earlier was forgiving of junk strings in date/time values,
and just ignored them on input (this was for historical reasons only,
dating back to at least Postgres95 and probably earlier). But that would
open us up to unintended data if, for example, someone mistyped a time
zone field which would then be ignored as junk. So junk is no longer
ignored except in a few specific cases. I believe that the docs cover
the parsing rules, including the changes for 7.2.
I'm a little suprised that input completely devoid of information as in
example (2) above was actually accepted by 7.1. In fact it isn't:
lockhart=# CREATE TABLE akka (tm TIMESTAMP WITH TIME ZONE);
CREATE
lockhart=# INSERT INTO akka VALUES ('akka');
ERROR: Bad timestamp external representation 'akka'
lockhart=# select version(); version
-------------------------------------------------------------PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC
2.96
But if there is some valid info in the input then it was accepted prior
to 7.2:
lockhart=# INSERT INTO akka VALUES ('now akka');
INSERT 26953 1
hth
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> psql:/.../dbdump/.dbrestore.tmp:1624094: ERROR: copy: line 1, Bad
>> timestamp external representation 'Fri 25 Jan 23:59:59 2002 KRAT'
>> psql:/.../dbdump/.dbrestore.tmp:1624094: lost synchronization with
>> server, resetting connection
> Not sure why it is crashing. But "KRAT" is a time zone not recognized by
> the PostgreSQL date/time parser.
The "crash" is totally expected behavior after any error during a COPY
IN. There isn't any other way to recover except to reset the
connection. Yes, this sucks, it's broken, etc, but there's no way to
fix it except to redesign the frontend/backend COPY protocol :-(
Trust me, this *will* get changed next time we have occasion to make
incompatible changes in the FE/BE protocol. But I'm not sure that it's
a sufficient reason to create a protocol incompatibility all by itself.
As to the specific changes in datetime datatype behavior that cause
the error report, I bow to Thomas' superior knowledge...
regards, tom lane
(back on list)
> > Not sure why it is crashing. But "KRAT" is a time zone not recognized by
> > the PostgreSQL date/time parser. In fact it could be afaik (it is
> > mentioned but commented-out in the parser) but it either had a screwy
> > definition or I couldn't figure out what the definition was. It could be
> > added for 7.2.1 (and I could send a patch beforehand) if I knew the
> > proper definition. Check src/backend/utils/adt/datetime.c and look for
> > "krat".
> KRAT,KRAST is timezone code generated by FreeBSD automatically.
> You can check up /usr/share/zoneinfo - it have all timezones.
> You can see timezones KRAT,KRAST in file
> /usr/share/zoneinfo/Asia/Krasnoyarsk.
Nope. You will have to *please* give me more details. On my Linux
(Mandrake) systems the zoneinfo data is included in the glibc package,
and the Asia/Krasnoyarsk entries refer to "Krasnoyarsk" not to "KRAT" or
any other abbreviation. They also seem to be empty of any other useful
information. I'm not sure where I got the original reference to "krat"
to include as a placeholder in the code.
> I already break idea to pg_dump in 7.1.3 and pg_restore in 7.2 and
> tried to remove ' KRAT' substring from all my *.dat files, created by
> pg_dump and change schema to fields without timezone. After I tried
> pg_restore only data from dbdump-file, but pg_restore says, that
> can't initialize header from TOC-file, but I not even touched it.
> TOC - is only one binary file in dbdump-file. I think that it also have
> smth like CRC code about all other files, and this is reason why they
> say that can't initialize TOC-file?
Not sure.
> How to patch datetime.c to 7.2 permit my 'KRAT' timezone?
Look in src/backend/utils/adt/datetime.c and search for "krat". Add a
line outside of the #if 0 block which looks like the other enabled time
zones, including your time zone offset in *minutes* from UTC. Recompile
and reinstall and you should be ready to go. initdb not required.
Send me details on the krat time zone and another zone you see disabled
in datetime.c and it will be in 7.2.1...
- Thomas
Thomas Lockhart wrote: > > I has pg_dump my DB in 7.1.3 and try ro pg_restore it in 7.2 > > version. > > psql:/.../dbdump/.dbrestore.tmp:1624094: ERROR: copy: line 1, Bad > > timestamp external representation 'Fri 25 Jan 23:59:59 2002 KRAT' > > psql:/.../dbdump/.dbrestore.tmp:1624094: lost synchronization with > > server, resetting connection > > Not sure why it is crashing. But "KRAT" is a time zone not recognized by > the PostgreSQL date/time parser. In fact it could be afaik (it is > mentioned but commented-out in the parser) but it either had a screwy > definition or I couldn't figure out what the definition was. It could be > added for 7.2.1 (and I could send a patch beforehand) if I knew the > proper definition. Check src/backend/utils/adt/datetime.c and look for > "krat". KRAT,KRAST is timezone code generated by FreeBSD automatically. You can check up /usr/share/zoneinfo - it have alltimezones. You can see timezones KRAT,KRAST in file /usr/share/zoneinfo/Asia/Krasnoyarsk. I already break idea to pg_dump in 7.1.3 and pg_restore in 7.2 and tried to remove ' KRAT' substring from all my *.datfiles, created by pg_dump and change schema to fields without timezone. After I tried pg_restore only data from dbdump-file, but pg_restoresays, that can't initialize header from TOC-file, but I not even touched it. TOC - is only one binary filein dbdump-file. I think that it also have smth like CRC code about all other files, and this is reason why they say that can't initialize TOC-file? How to patch datetime.c to 7.2 permit my 'KRAT' timezone? Greatly appreciate, Ruslan A Dautkhanov
Hi Thomas, > > > > Not sure why it is crashing. But "KRAT" is a time zone not recognized by > > > the PostgreSQL date/time parser. In fact it could be afaik (it is > > > mentioned but commented-out in the parser) but it either had a screwy > > > definition or I couldn't figure out what the definition was. It could be > > > added for 7.2.1 (and I could send a patch beforehand) if I knew the > > > proper definition. Check src/backend/utils/adt/datetime.c and look for > > > "krat". > > KRAT,KRAST is timezone code generated by FreeBSD automatically. > > You can check up /usr/share/zoneinfo - it have all timezones. > > You can see timezones KRAT,KRAST in file > > /usr/share/zoneinfo/Asia/Krasnoyarsk. > > Nope. You will have to *please* give me more details. On my Linux > (Mandrake) systems the zoneinfo data is included in the glibc package, > and the Asia/Krasnoyarsk entries refer to "Krasnoyarsk" not to "KRAT" or > any other abbreviation. They also seem to be empty of any other useful > information. I'm not sure where I got the original reference to "krat" > to include as a placeholder in the code. Check out, please http://www.weltzeituhr.com/laender/zeitzonen_e.shtml. KRAT figurate in this list as Krasnoyarsk time,and KRAST as Krasnoyarsk Summertime. You can try also http://www.worldtimezone.com/wtz-names/timezonenames.html or http://www.htmlcompendium.org/reference-notes/7timzone.htm or (binary zoneinfo files) http://lrp1.steinkuehler.net/files/kernels/zoneinfo/ . Thanks, Ruslan A Dautkhanov