Re: Order by and timestamp SOLVED

17 mars 2020 kl. 15:05 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:
>>> What happens if you use psql(9.4.15) to do sort query against 9.4.15 server?
>
>> However AFTER I checked the bad sorting - sep/oct 2016 and early apr 2017
>> With psql 9.4 I connected with psql 9.6 again.
>> And now the sorting error is gone her too!
>
> Boy ... I don't have any confidence in that answer.

It is not an answer. It is an observation.

> psql does not re-sort
> data, nor does it have any way to affect what the server does.

I did not say that. I do not think that.
But - still I got the above result
* bad order with psql 9.6 towards db 9.4
* good order with psql 9.4 towards db 9.4
* good order with psql 9.6 towards db 9.4

> It seems
> to me that if this actually is a version inconsistency problem, that's
> a bug in itself.
> I am starting to wonder though if you had psql's FETCH_COUNT option
> active in one configuration and not the other, and if so whether that
> could explain anything.

FETCH_COUNT is a variable set in runtime / read by .psqlrc?
Then it is the same.
I have 1 .psqlrc on ibm2.

bnl(at)ibm2:~$ locate .psqlrc
/home/bnl/.psqlrc
bnl(at)ibm2:~$

bnl(at)ibm2:~$ cat .psqlrc
\timing
\set AUTOCOMMIT 'off'
\echo 'AUTOCOMMIT' :AUTOCOMMIT

But I do notice that in .psqlrc_history I see strange ’040’ - sometimes
bnl(at)ibm2:~$ cat .psql_history
_HiStOrY_V2_
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040amarkets\040order\040by\040startts;
select * from amarkets order by startts;
select\040*\040from\040amarkets\040order\040by\040startts;

So I now did
bnl(at)ibm2:~$ find / -name psql
/usr/lib/postgresql/9.4/bin/psql
/usr/lib/postgresql/9.6/bin/psql
^C
bnl(at)ibm2:~$ /usr/lib/postgresql/9.4/bin/psql
Timing is on.
AUTOCOMMIT off
psql (9.4.15)
Type "help" for help.

bnl=> select * from AEVENTS order by OPENTS;
<output omitted, was ok. Different statement too see if that is added to .psql_history>
Time: 278,207 ms

bnl(at)ibm2:~$ cat .psqlrc
\timing
\set AUTOCOMMIT 'off'
\echo 'AUTOCOMMIT' :AUTOCOMMIT

bnl(at)ibm2:~$ cat .psql_history
_HiStOrY_V2_
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040amarkets\040order\040by\040startts;\040\040
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040AEVENTS\040order\040by\040OPENTS;

And yes it is
Trying 9.6

bnl(at)ibm2:~$ /usr/lib/postgresql/9.6/bin/psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> select * from AEVENTS order by OPENTS limit 1;
<output omitted, was ok. Different statement again too see if that is added to .psql_history>
Tid: 19,240 ms
bnl=> ^D\q

bnl(at)ibm2:~$ cat .psql_history
_HiStOrY_V2_
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040amarkets\040order\040by\040startts;\040\040
select\040*\040from\040amarkets\040order\040by\040startts;
select\040*\040from\040AEVENTS\040order\040by\040OPENTS;
select\040*\040from\040AEVENTS\040order\040by\040OPENTS\040limit\0401;
bnl(at)ibm2:~$

And now that one too.

I will move from this machine.

Thanks for the suggestions, both Tom and Adrian

I will keep the machine if you are interested of pursuing it,
Otherwise I’ll stop his thread.
That is, I am convinced enough that mixing versions combined with perhaps old hardware
together did something strange

--
Björn Lundin
b(dot)f(dot)lundin(at)gmail(dot)com