Re: Order by and timestamp
От | Adrian Klaver |
---|---|
Тема | Re: Order by and timestamp |
Дата | |
Msg-id | 4b326caa-f441-61e1-3521-1597ab83790f@aklaver.com обсуждение исходный текст |
Ответ на | Re: Order by and timestamp (Björn Lundin <b.f.lundin@gmail.com>) |
Список | pgsql-general |
On 3/16/20 2:28 PM, Björn Lundin wrote: > > >> 16 mars 2020 kl. 17:40 skrev Tom Lane <tgl@sss.pgh.pa.us >> <mailto:tgl@sss.pgh.pa.us>>: >> >> =?utf-8?Q?Bj=C3=B6rn_Lundin?= <b.f.lundin@gmail.com >> <mailto:b.f.lundin@gmail.com>> writes: >>> Ooh - terrible sorry. >>> The output from first post describing the database schema >>> Was actually from my production machine - a raspberry pi. >>> The pi hold a db on an usb-disk, which is pg_dump()ed every night and >>> imported to ibm2 history db (the bad one) >> >>> The schema is identical to the one with trouble - which is a history >>> database >>> Intended for testing >> >> Hmmm ... schema is evidently the same, but locale seemingly not. >> Is it possible this is a locale problem? I did not look closely >> at the original data, but I'm suddenly wondering if it could be >> explained by misinterpreting the date field order (month-day vs >> day-month). Check the datestyle settings on both machines. >> >> regards, tom lane > > > But most of the output is correct. Like 95+% > I found the bad ones by chance A quick look at the dump data in your other post indicates the data values themselves are not bad, just that sorting is not correct. I do not have a 9.4 instance available, so I cannot test below. > > > > The pi - keeping only data for 1 day then table exported and truncated > every night > (So there is not more than say max 60 records any given day) > Was only involved because I use it to describe the table > > *bnl@pibetbot*:*~ $*locale > LANG=en_GB.UTF-8 > LANGUAGE= > LC_CTYPE="en_GB.UTF-8" > LC_NUMERIC="en_GB.UTF-8" > LC_TIME="en_GB.UTF-8" > LC_COLLATE="en_GB.UTF-8" > LC_MONETARY="en_GB.UTF-8" > LC_MESSAGES="en_GB.UTF-8" > LC_PAPER="en_GB.UTF-8" > LC_NAME="en_GB.UTF-8" > LC_ADDRESS="en_GB.UTF-8" > LC_TELEPHONE="en_GB.UTF-8" > LC_MEASUREMENT="en_GB.UTF-8" > LC_IDENTIFICATION="en_GB.UTF-8" > LC_ALL= > > *bnl@pibetbot*:*~ $*psql > Timing is on. > AUTOCOMMIT off > psql (9.6.10) > Type "help" for help. > > bnl=# show datestyle; > DateStyle > ----------- > ISO, DMY > > > > The faulty machine > Ibm2 - linux debian pg-9.4 > bnl@ibm2:~$ locale > LANG=sv_SE.UTF-8 > LANGUAGE= > LC_CTYPE="sv_SE.UTF-8" > LC_NUMERIC="sv_SE.UTF-8" > LC_TIME="sv_SE.UTF-8" > LC_COLLATE="sv_SE.UTF-8" > LC_MONETARY="sv_SE.UTF-8" > LC_MESSAGES="sv_SE.UTF-8" > LC_PAPER="sv_SE.UTF-8" > LC_NAME="sv_SE.UTF-8" > LC_ADDRESS="sv_SE.UTF-8" > LC_TELEPHONE="sv_SE.UTF-8" > LC_MEASUREMENT="sv_SE.UTF-8" > LC_IDENTIFICATION="sv_SE.UTF-8" > LC_ALL= > > bnl@ibm2:~$ psql > Tidtagning är på. > AUTOCOMMIT off > psql (9.6.15, server 9.4.15) > Skriv "help" för hjälp. > > bnl=> show datestyle; > DateStyle > ----------- > ISO, YMD > > > The machine briefly mentioned with basically same dataset as faulty machine > Linux Ubuntu with pg-10.6 > *bnl@tp*:*~*$ locale > LANG=sv_SE.UTF-8 > LANGUAGE= > LC_CTYPE="sv_SE.UTF-8" > LC_NUMERIC="sv_SE.UTF-8" > LC_TIME="sv_SE.UTF-8" > LC_COLLATE="sv_SE.UTF-8" > LC_MONETARY="sv_SE.UTF-8" > LC_MESSAGES="sv_SE.UTF-8" > LC_PAPER="sv_SE.UTF-8" > LC_NAME="sv_SE.UTF-8" > LC_ADDRESS="sv_SE.UTF-8" > LC_TELEPHONE="sv_SE.UTF-8" > LC_MEASUREMENT="sv_SE.UTF-8" > LC_IDENTIFICATION="sv_SE.UTF-8" > LC_ALL= > > *bnl@tp*:*~*$ psql > Timing is on. > AUTOCOMMIT off > psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)) > Type "help" for help. > > bnl=# show datestyle; > DateStyle > ----------- > ISO, YMD > > > > Both faulty (ibm2) and correct(tp) are populated with the same > pg_dump()- files that r-pi produces every nigth > > And for completeness - b info from the pi > > bnl=# select version(); > version > ---------------------------------------------------------------------------------------------------------------------- > PostgreSQL 9.6.10 on armv7l-unknown-linux-gnueabihf, compiled by gcc > (Raspbian 6.3.0-18+rpi1) 6.3.0 20170516, 32-bit > (1 row) > > *bnl@pibetbot*:*~ $*uname -a > Linux pibetbot 4.14.79-v7+ #1159 SMP Sun Nov 4 17:50:20 GMT 2018 armv7l > GNU/Linux > > -- > Björn Lundin > b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com> > > > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: