Re: Order by and timestamp SOLVED

[Björn Lundin <b.f.lundin@xxxxxxxxx>]

17 mars 2020 kl. 15:05 skrev Tom Lane <tgl@xxxxxxxxxxxxx>:

=?utf-8?Q?Bj=C3=B6rn_Lundin?= <b.f.lundin@xxxxxxxxx> 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@ibm2:~$ locate .psqlrc

/home/bnl/.psqlrc

bnl@ibm2:~$ 

bnl@ibm2:~$ cat .psqlrc

\timing 

\set AUTOCOMMIT 'off' 

\echo 'AUTOCOMMIT' :AUTOCOMMIT

But I do notice that in .psqlrc_history I see strange ’040’ - sometimes 

bnl@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@ibm2:~$ find / -name psql

/usr/lib/postgresql/9.4/bin/psql

/usr/lib/postgresql/9.6/bin/psql

^C

bnl@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@ibm2:~$ cat .psqlrc

\timing 

\set AUTOCOMMIT 'off' 

\echo 'AUTOCOMMIT' :AUTOCOMMIT

bnl@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@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@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@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.f.lundin@xxxxxxxxx