From: | Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Order by and timestamp |
Date: | 2020-03-16 21:28:54 |
Message-ID: | 4C6CD383-ADC0-4529-82FF-2141568C09BB@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 16 mars 2020 kl. 17:40 skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> =?utf-8?Q?Bj=C3=B6rn_Lundin?= <b(dot)f(dot)lundin(at)gmail(dot)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
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(at)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(at)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(at)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(at)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(at)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(at)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(at)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(dot)f(dot)lundin(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Björn Lundin | 2020-03-16 21:50:44 | Re: Order by and timestamp |
Previous Message | Adrian Klaver | 2020-03-16 21:09:14 | Re: psql crash on 9.6.16 |