17 mars 2020 kl. 15:05 skrev Tom Lane <t...@sss.pgh.pa.us>: > > =?utf-8?Q?Bj=C3=B6rn_Lundin?= <b.f.lun...@gmail.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@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.lun...@gmail.com
