Order by and timestamp

On Sun, 15 Mar 2020 22:33:35 +0100:wq
Björn Lundin <b.f.lundin@gmail.com> wrote:

And to my surprise i get a result like this (note the order of
column STARTTS)

(1) Suggest using "pastebin.com" for this kind of data. It may not
look very pretty -- or readable at all -- on the viewer's end
depending on their settings (see below for example).

(2) I think you are refering to one section where the date goes
from 2016-10-01 to 2016-09-30; suggest describing the
transition in your text and flag the rows with '*' or
something similar.

| 2016-10-01 15:35:00 |
| 2016-10-01 16:10:00 |
* | 2016-09-30 13:00:00 |
* | 2016-09-30 13:00:00 |

(3) "Old database" might mean anyting. Provide the PG version
it was created in and the one you are using along with the
result of "\d+" in the current database.

(4) Classic causes of this are a botched index. Depending on the
size you might just want to either drop and re-add the
indexes or export and reload the table (e.g., \copy to ...
+ truncate + \copy from ...). The point there would be
fully rebuilding the table and index structure.

If that doesn't work perhaps drop and re-add the table with
whatever version of PG you are using and then \copy the data
back in using the current version.

(5) If you've tried any of the above then bloody well describe it
(along with any migration steps taken) in the message so you
don't have to re-read what you've already done :-)

(6) Don't gamble on horses, play the stock market instead: It
sounds fancier and you can loose much more money much more
quickly... er... yeah.

What this looks like on my end. Feel free to try and make sense
of it yourself.

marketid | marketname | startts |
eventid ….
….

1.127253880 | To Be Placed | 2016-09-29 16:10:00 |
27951325 | 1.127275624 | 1m4f Hcap | 2016-09-30
16:20:00 | 27953169 | 1.127275625 | To Be Placed |
2016-09-30 16:20:00 | 27953169 | 1.127275629 | 1m2f
Hcap | 2016-09-30 16:50:00 | 27953169 | 1.127275634 |
1m2f Hcap | 2016-09-30 17:20:00 | 27953169 |
1.127275635 | To Be Placed | 2016-09-30 17:20:00 |
27953169 | 1.127275639 | 1m Nursery | 2016-09-30
17:50:00 | 27953169 | 1.127275640 | To Be Placed |
2016-09-30 17:50:00 | 27953169 | 1.127275645 | To Be
Placed | 2016-09-30 18:20:00 | 27953169 | 1.127275649 |
6f Mdn Stks | 2016-09-30 18:50:00 | 27953169 |
1.127275650 | To Be Placed | 2016-09-30 18:50:00 |
27953169 | 1.127275654 | 5f Hcap | 2016-09-30
19:20:00 | 27953169 | 1.127275655 | To Be Placed |
2016-09-30 19:20:00 | 27953169 | 1.127275659 | 5f
Hcap | 2016-09-30 19:50:00 | 27953169 | 1.127275660
| To Be Placed | 2016-09-30 19:50:00 | 27953169 |
1.127275677 | 1m Mdn Stks | 2016-10-01 12:45:00 |
27953172 | 1.127275680 | To Be Placed | 2016-10-01
12:45:00 | 27953172 | 1.127275684 | 6f Hcap |
2016-10-01 13:15:00 | 27953172 | 1.127275687 | To Be
Placed | 2016-10-01 13:15:00 | 27953172 | 1.127275691 |
1m Hcap | 2016-10-01 13:50:00 | 27953172 |
1.127275694 | To Be Placed | 2016-10-01 13:50:00 |
27953172 | 1.127275698 | 1m2f Hcap | 2016-10-01
14:25:00 | 27953172 | 1.127275701 | To Be Placed |
2016-10-01 14:25:00 | 27953172 | 1.127275705 | 1m
Grp1 | 2016-10-01 15:00:00 | 27953172 | 1.127275708
| To Be Placed | 2016-10-01 15:00:00 | 27953172 |
1.127275715 | To Be Placed | 2016-10-01 15:35:00 |
27953172 | 1.127275722 | To Be Placed | 2016-10-01
16:10:00 | 27953172 | 1.127278857 | 7f Hcap |
2016-09-30 13:00:00 | 27953255 | 1.127278858 | To Be
Placed | 2016-09-30 13:00:00 | 27953255 | 1.127278862 |
1m Class Stks | 2016-09-30 13:35:00 | 27953255 |
1.127278863 | To Be Placed | 2016-09-30 13:35:00 |
27953255 | 1.127278867 | 6f Hcap | 2016-09-30
14:10:00 | 27953255 | … ….

1.130630452 | 2m INHF | 2017-03-30 16:00:00 |
28172518 | 1.130630453 | To Be Placed | 2017-03-30
16:00:00 | 28172518 | 1.130645203 | 1m2f Mdn Stks |
2017-04-01 12:30:00 | 28173548 | 1.130645204 | To Be
Placed | 2017-04-01 12:30:00 | 28173548 | 1.130645213 |
6f Hcap | 2017-04-01 13:40:00 | 28173548 |
1.130645214 | To Be Placed | 2017-04-01 13:40:00 |
28173548 | 1.130645218 | 1m3f Hcap | 2017-04-01
14:15:00 | 28173548 | 1.130645219 | To Be Placed |
2017-04-01 14:15:00 | 28173548 | 1.130645223 | 7f Mdn
Stks | 2017-04-01 14:50:00 | 28173548 | 1.130645224 | To
Be Placed | 2017-04-01 14:50:00 | 28173548 | 1.130645228
| 1m3f Hcap | 2017-04-01 15:25:00 | 28173548 |
1.130645229 | To Be Placed | 2017-04-01 15:25:00 |
28173548 | 1.130645233 | 2m Hcap | 2017-04-01
16:00:00 | 28173548 | 1.130645234 | To Be Placed |
2017-04-01 16:00:00 | 28173548 | 1.130645400 | 2m3f Nov
Hrd | 2017-03-31 13:10:00 | 28173582 | 1.130645401 | To
Be Placed | 2017-03-31 13:10:00 | 28173582 | 1.130645405
| 2m5f Hcap Chs | 2017-03-31 13:40:00 | 28173582 |
1.130645415 | 2m1f Hcap Chs | 2017-03-31 14:40:00 |
28173582 | 1.130645416 | To Be Placed | 2017-03-31
14:40:00 | 28173582 | 1.130645420 | 2m5f Hcap Hrd |
2017-03-31 15:10:00 | 28173582 | 1.130645421 | To Be
Placed | 2017-03-31 15:10:00 | 28173582 | 1.130645425 |
2m3f Hcap Chs | 2017-03-31 15:40:00 | 28173582 |
1.130645426 | To Be Placed | 2017-03-31 15:40:00 |
28173582 | 1.130645430 | 1m5f Stks NHF | 2017-03-31
16:10:00 | 28173582 | 1.130645431 | To Be Placed |
2017-03-31 16:10:00 | 28173582 | 1.130645436 | 1m4f
Hcap | 2017-03-31 16:45:00 | 28173583 | 1.130645437 |
To Be Placed | 2017-03-31 16:45:00 | 28173583 |
1.130645441 | 1m Hcap | 2017-03-31 17:15:00 |
28173583 | 1.130645442 | To Be Placed | 2017-03-31
17:15:00 | 28173583 | 1.130645447 | To Be Placed |
2017-03-31 17:45:00 | 28173583 | 1.130645451 | 7f
Hcap | 2017-03-31 18:15:00 | 28173583 | 1.130645452
| To Be Placed | 2017-03-31 18:15:00 | 28173583 |
1.130645456 | 1m Hcap | 2017-03-31 18:45:00 |
28173583 | 1.130645457 | To Be Placed | 2017-03-31
18:45:00 | 28173583 | 1.130645461 | 5f Hcap |
2017-03-31 19:15:00 | 28173583 | 1.130645462 | To Be
Placed | 2017-03-31 19:15:00 | 28173583 | 1.130645466 |
5f Hcap | 2017-03-31 19:45:00 | 28173583 |
1.130645471 | 1m Hcap | 2017-03-31 13:00:00 |
28173584 | 1.130645472 | To Be Placed | 2017-03-31
13:00:00 | 28173584 | 1.130645476 | 6f Hcap |
2017-03-31 13:30:00 | 28173584 | 1.130645477 | To Be
Placed | 2017-03-31 13:30:00 | 28173584 | 1.130645481 |
1m5f Stks | 2017-03-31 14:00:00 | 28173584 |
1.130645482 | To Be Placed | 2017-03-31 14:00:00 |
28173584 | 1.130645486 | 5f Hcap | 2017-03-31
14:30:00 | 28173584 | 1.130645487 | To Be Placed |
2017-03-31 14:30:00 | 28173584 | 1.130645491 | 2m
Hcap | 2017-03-31 15:00:00 | 28173584 | 1.130645492
| To Be Placed | 2017-03-31 15:00:00 | 28173584 |
1.130645496 | 6f Hcap | 2017-03-31 15:30:00 |
28173584 | 1.130645497 | To Be Placed | 2017-03-31
15:30:00 | 28173584 | 1.130645501 | 1m2f Mdn Stks |
2017-03-31 16:00:00 | 28173584 | 1.130645502 | To Be
Placed | 2017-03-31 16:00:00 | 28173584 | 1.130645507 |
2m Nov Hrd | 2017-03-31 13:20:00 | 28173585 |
1.130645508 | To Be Placed | 2017-03-31 13:20:00 |
28173585 | 1.130645512 | 1m7f Hcap Chs | 2017-03-31
13:50:00 | 28173585 | 1.130645513 | To Be Placed |
2017-03-31 13:50:00 | 28173585 | 1.130645517 | 2m5f Hcap
Hrd | 2017-03-31 14:20:00 | 28173585 | 1.130645518 | To Be
Placed | 2017-03-31 14:20:00 | 28173585 | 1.130645801 |
To Be Placed | 2017-03-31 20:00:00 | 28173591 |
1.130662612 | 6f Listed Stks | 2017-04-01 12:50:00 |
28174115 | 1.130662613 | To Be Placed | 2017-04-01
12:50:00 | 28174115 | 1.130662617 | 1m Hcap |
2017-04-01 13:25:00 | 28174115 | 1.130662618 | To Be
Placed | 2017-04-01 13:25:00 | 28174115 | 1.130662622 |
1m Listed Stks | 2017-04-01 14:00:00 | 28174115 |
1.130662623 | To Be Placed | 2017-04-01 14:00:00 |
28174115 | 1.130662627 | 1m Hcap | 2017-04-01
14:35:00 | 28174115 | 1.130662628 | To Be Placed |
2017-04-01 14:35:00 | 28174115 | 1.130662632 | 5f Cond
Stks | 2017-04-01 15:10:00 | 28174115 | 1.130662633 | To
Be Placed | 2017-04-01 15:10:00 | 28174115 | 1.130662637
| 5f Cond Stks | 2017-04-01 15:45:00 | 28174115 |
1.130662638 | To Be Placed | 2017-04-01 15:45:00 |
28174115 | 1.130662643 | To Be Placed | 2017-04-01
16:20:00 | 28174115 | 1.130662647 | 1m2f Hcap |
2017-04-01 16:50:00 | 28174115 |

I see this on some dates, but most are in order
Actually it looks like ’order by MARKETID'

The data is collected on Amazon cloud, Ireland, and in Sweden. time
diff is 1 hour between the countries, GMT / CET

Hmm, I now realise that daylight saving time starts stops
ended 2016-10-30, and started again 2017-03-26
so it is not on the bad dates.

I vaguely recall that I have once defined the column as
Timestamp with timezone, and changed it to without.
May that have an impact?

I realize that I should migrate to 11 or 12, but hmm, I still wonder
over this sort order.

TZ=Europe/Stockholm

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# \d amarkets
Table "public.amarkets"
Column | Type |
Modifiers
------------------+--------------------------------+-----------------------------------------
marketid | character varying(11) | not null default
' '::character varying marketname | character
varying(50) | not null default ' '::character varying
startts | timestamp(3) without time zone | not null
eventid | character varying(11) | not null default
' '::character varying markettype | character
varying(25) | not null default ' '::character varying
status | character varying(50) | not null default
' '::character varying betdelay |
integer | not null default 1 numwinners
| integer | not null default 1
numrunners | integer | not null default
1 numactiverunners | integer | not null
default 1 totalmatched | numeric(15,2) | not
null default 0.0 totalavailable | numeric(15,2) |
not null default 0.0 ixxlupd | character
varying(15) | not null default ' '::character varying
ixxluts | timestamp(3) without time zone | not null Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid) "amarketsi2" btree
(eventid) "amarketsi3" btree (markettype) "amarketsi4" btree (status)
"amarketsi5" btree (numwinners) "amarketsi6" btree (ixxluts)

bnl=#

regards
--
Björn Lundin
b.f.lundin@gmail.com

--
Steven Lembark 3646 Flora Place
Workhorse Computing St. Louis, MO 63110
lembark@wrkhors.com +1 888 359 3508

On 3/15/20 2:48 PM, Steven Lembark wrote:

On Sun, 15 Mar 2020 22:33:35 +0100:wq
Björn Lundin <b.f.lundin@gmail.com> wrote:

And to my surprise i get a result like this (note the order of
column STARTTS)

(1) Suggest using "pastebin.com" for this kind of data. It may not
look very pretty -- or readable at all -- on the viewer's end
depending on their settings (see below for example).

(2) I think you are refering to one section where the date goes
from 2016-10-01 to 2016-09-30; suggest describing the
transition in your text and flag the rows with '*' or
something similar.

| 2016-10-01 15:35:00 |
| 2016-10-01 16:10:00 |
* | 2016-09-30 13:00:00 |
* | 2016-09-30 13:00:00 |

(3) "Old database" might mean anyting. Provide the PG version
it was created in and the one you are using along with the
result of "\d+" in the current database.

That was at the bottom of the post. Version 9.6.10 and a \d for amarkets.

(4) Classic causes of this are a botched index. Depending on the
size you might just want to either drop and re-add the
indexes or export and reload the table (e.g., \copy to ...
+ truncate + \copy from ...). The point there would be
fully rebuilding the table and index structure.

If that doesn't work perhaps drop and re-add the table with
whatever version of PG you are using and then \copy the data
back in using the current version.

(5) If you've tried any of the above then bloody well describe it
(along with any migration steps taken) in the message so you
don't have to re-read what you've already done :-)

(6) Don't gamble on horses, play the stock market instead: It
sounds fancier and you can loose much more money much more
quickly... er... yeah.

What this looks like on my end. Feel free to try and make sense
of it yourself.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 3/15/20 2:33 PM, Björn Lundin wrote:

Hi!
I have an old database that behaves a bit strange.
I keeps horse races in UK/IE.
I have a program that continuously* adds record into a market table ,
described as below.

*continuously means ’after each race’ which is ca 12:00 --> 23:00.

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

If so what is search_path?

I could not replicate the below.

What does below show?:

select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';

select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';

And to my surprise i get a result like this  (note the order of column
STARTTS)

  marketid   |        marketname        |       startts       | eventid
….
….

 1.127275701 | To Be Placed             | 2016-10-01 14:25:00 | 27953172 |
 1.127275705 | 1m Grp1                  | 2016-10-01 15:00:00 | 27953172 |
 1.127275708 | To Be Placed             | 2016-10-01 15:00:00 | 27953172 |
 1.127275715 | To Be Placed             | 2016-10-01 15:35:00 | 27953172 |
 1.127275722 | To Be Placed             | 2016-10-01 16:10:00 | 27953172 |
 1.127278857 | 7f Hcap                  | 2016-09-30 13:00:00 | 27953255 |
 1.127278858 | To Be Placed             | 2016-09-30 13:00:00 | 27953255 |
 1.127278862 | 1m Class Stks            | 2016-09-30 13:35:00 | 27953255 |
 1.127278863 | To Be Placed             | 2016-09-30 13:35:00 | 27953255 |
 1.127278867 | 6f Hcap                  | 2016-09-30 14:10:00 | 27953255 |
…
….

regards
--
Björn Lundin
b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?

Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.

regards, tom lane

(1) Suggest using "pastebin.com <http://pastebin.com/&gt;&quot; for this kind of data. It may not
look very pretty -- or readable at all -- on the viewer's end
depending on their settings (see below for example).

Ok, sorry about that.

https://pastebin.com/2XANMcF6 <https://pastebin.com/2XANMcF6&gt;
Odds rows market with ** at the end of the rows

(3) "Old database" might mean anyting. Provide the PG version
it was created in and the one you are using along with the
result of "\d+" in the current database.

version
------------------------------------------------------------------------------------------------
PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 rad)

bnl@ibm2:~$ uname -a
Linux ibm2 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64 GNU/Linux

bnl=> \d+
Lista med relationer
Schema | Namn | Typ | Ägare | Storlek | Beskrivning
--------+----------------+---------+-------+------------+----------------------------
public | abets | tabell | bnl | 62 MB | Bets
public | abets_found | tabell | bnl | 702 MB |
public | adiff | tabell | bnl | 0 bytes | Price diff between 1 and 2
public | aevents | tabell | bnl | 968 kB | collected runners
public | amarkets | tabell | bnl | 15 MB | collected runners
public | aprices | tabell | bnl | 122 MB | runners odds
public | apriceshistory | tabell | bnl | 95 GB | runners odds during race
public | arunners | tabell | bnl | 124 MB | collected runners
public | bet_id_serial | sekvens | bnl | 8192 bytes |
(9 rader)

(4) Classic causes of this are a botched index. Depending on the
size you might just want to either drop and re-add the
indexes or export and reload the table (e.g., \copy to ...
+ truncate + \copy from ...). The point there would be
fully rebuilding the table and index structure.

But there is no index on startts
I’ll try that later on.

(6) Don't gamble on horses, play the stock market instead: It
sounds fancier and you can loose much more money much more
quickly... er... yeah.

I can guarantee you that you can loose on horses in any rate you prefer. :-)

What this looks like on my end. Feel free to try and make sense
of it yourself.

Ok - point taken.

--
Björn Lundin
b.f.lundin@gmail.com

16 mars 2020 kl. 01:37 skrev Adrian Klaver <adrian.klaver@aklaver.com>:

On 3/15/20 2:33 PM, Björn Lundin wrote:

Hi!
I have an old database that behaves a bit strange.
I keeps horse races in UK/IE.
I have a program that continuously* adds record into a market table ,
described as below.
*continuously means ’after each race’ which is ca 12:00 --> 23:00.
I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well

bnl@ibm2:~$ psql -l
Tidtagning är på.
AUTOCOMMIT off
Lista med databaser
Namn | Ägare | Kodning | Jämförelse | Ctype | Åtkomsträttigheter
-----------+----------+---------+-------------+-------------+-----------------------
bnl | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
dry | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
imports | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
postgres | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
template0 | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rader)

bnl@ibm2:~$ \c dry
-bash: c: kommandot finns inte
bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \c dry
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "dry" som användare "bnl".
dry=> \d amarkets
                                   Tabell "public.amarkets"
      Kolumn      |              Typ               |               Modifierare                
------------------+--------------------------------+------------------------------------------
 marketid         | character varying(11)          | inte null default ' '::character varying
 marketname       | character varying(50)          | inte null default ' '::character varying
 startts          | timestamp(3) without time zone | inte null
 eventid          | character varying(11)          | inte null default ' '::character varying
 markettype       | character varying(25)          | inte null default ' '::character varying
 status           | character varying(50)          | inte null default ' '::character varying
 betdelay         | integer                        | inte null default 1
 numwinners       | integer                        | inte null default 1
 numrunners       | integer                        | inte null default 1
 numactiverunners | integer                        | inte null default 1
 totalmatched     | numeric(15,2)                  | inte null default 0.0
 totalavailable   | numeric(15,2)                  | inte null default 0.0
 ixxlupd          | character varying(15)          | inte null default ' '::character varying
 ixxluts          | timestamp(3) without time zone | inte null
Index:
    "amarketsp1" PRIMARY KEY, btree (marketid)

dry=> select count('a') from amarkets;
count
-------
0
(1 rad)

Tid: 2,059 ms
dry=>

dry=> \c imports
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "imports" som användare "bnl".
imports=> \d
Lista med relationer
Schema | Namn | Typ | Ägare
--------+--------------------+--------+-------
public | abets | tabell | bnl
public | aevents | tabell | bnl
public | aevents_tmp | tabell | bnl
public | amarkets | tabell | bnl
public | amarkets_tmp | tabell | bnl
public | aprices | tabell | bnl
public | aprices_tmp | tabell | bnl
public | apriceshistory | tabell | bnl
public | apriceshistory_tmp | tabell | bnl
public | arunners | tabell | bnl
public | arunners_tmp | tabell | bnl
(11 rader)

imports=> select count('a') from amarkets;
count
-------
0
(1 rad)

If so what is search_path?

bnl=> show search_path;
search_path
----------------
"$user",public
(1 rad)

I could not replicate the below.

What does below show?:

select '2016-09-30 13:00:00'::timestamp at time zone 'UTC’;

bnl=> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';
timezone
------------------------
2016-09-30 15:00:00+02

select '2016-10-01 15:35:00'::timestamp at time zone ’UTC’

bnl=> select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';
timezone
------------------------
2016-10-01 17:35:00+02

--
Björn Lundin
b.f.lundin@gmail.com

16 mars 2020 kl. 01:41 skrev Tom Lane <tgl@sss.pgh.pa.us>:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?

Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty

Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.

bnl=> explain select * from amarkets order by startts;
QUERY PLAN
-----------------------------------------------------------------------
Sort (cost=10702.57..10939.29 rows=94691 width=106)
Sort Key: startts
-> Seq Scan on amarkets (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)

--
Björn Lundin
b.f.lundin@gmail.com

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?

I realize that I have (basically) the same dataset on another machine.

bnl=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

bnl@tp:~$ uname -a
Linux tp 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

It misses som later record (from 2020) but otherwise contains the same data, and same definition
It is also the only user-database on the system

bnl=# \d amarkets
Table "public.amarkets"
Column | Type | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+------------------------
marketid | character varying(11) | | not null | ' '::character varying
marketname | character varying(50) | | not null | ' '::character varying
startts | timestamp(3) without time zone | | not null |
eventid | character varying(11) | | not null | ' '::character varying
markettype | character varying(25) | | not null | ' '::character varying
status | character varying(50) | | not null | ' '::character varying
betdelay | integer | | not null | 1
numwinners | integer | | not null | 1
numrunners | integer | | not null | 1
numactiverunners | integer | | not null | 1
totalmatched | numeric(15,2) | | not null | 0.0
totalavailable | numeric(15,2) | | not null | 0.0
ixxlupd | character varying(15) | | not null | ' '::character varying
ixxluts | timestamp(3) without time zone | | not null |
Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)

This gets it correctly.

So it points to something on the first machine.
Recreating indexes is a possibility, but (to me) a bit unintuitive since there are no index on startts
I’ll do that tomorrow.

--
Björn Lundin
b.f.lundin@gmail.com

On 2020-03-15 16:48:35 -0500, Steven Lembark wrote:

On Sun, 15 Mar 2020 22:33:35 +0100:wq
Björn Lundin <b.f.lundin@gmail.com> wrote:

And to my surprise i get a result like this (note the order of
column STARTTS)

(1) Suggest using "pastebin.com" for this kind of data. It may not
look very pretty -- or readable at all -- on the viewer's end
depending on their settings (see below for example).

[...]

marketid | marketname | startts |
eventid ….
….

1.127253880 | To Be Placed | 2016-09-29 16:10:00 |
27951325 | 1.127275624 | 1m4f Hcap | 2016-09-30
16:20:00 | 27953169 | 1.127275625 | To Be Placed |

This is weird. The output is correctly formatted in the text/plain part
and the HTML part looks reasonable, too: Every line is in a div of its
own, so it shouldn't be jumbled together like that (Out of curiosity:
What mail program did use to read this?). The only obvious problem I see
is the use of the "Menlo" font. Non-Mac Users won't have that and won't
know that they should substitute a monospace font, so the alignment will
be off.

As far as pasted output on this list goes, Björn's message looked
actually quite good.

Some problems I see frequently and find annoying:

* Wide output (especially explain plans) with wrapped lines. I find
these almost impossible to read, so I have to save the mail to a file
and manually undo the line breaks to read it. I rarely bother to do
that.

* ASCII graphics which only line up in a certain proportional font

* text/plain messages with very long lines which really should be
paragraphs.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

On 3/16/20 1:49 AM, Björn Lundin wrote:

16 mars 2020 kl. 01:37 skrev Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:

On 3/15/20 2:33 PM, Björn Lundin wrote:

Hi!
I have an old database that behaves a bit strange.
I keeps horse races in UK/IE.
I have a program that continuously* adds record into a market table ,
described as below.
*continuously means ’after each race’ which is ca 12:00 --> 23:00.
I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well

Actually the below indicates it is in other databases. A schema would be
a namespace within a database, see here:

https://www.postgresql.org/docs/12/sql-createschema.html

In your original example the 'public' in public.amarkets.
So just to be complete \dn in psql will show you the schemas in a
database. Given the search_path("$user",public) shown below I suspect
you have only a public schema. $user matches a schema named for the
current user and generally is not there.

The times returned below match, so I am at a loss for an explanation at
the moment.

bnl@ibm2:~$ psql -l
Tidtagning är på.
AUTOCOMMIT off
                                Lista med databaser
   Namn    |  Ägare   | Kodning | Jämförelse  |    Ctype    |
Åtkomsträttigheter
-----------+----------+---------+-------------+-------------+-----------------------
 bnl       | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
 dry       | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
 imports   | bnl      | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
 postgres  | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
 template0 | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
=c/postgres          +
           |          |         |             |             |
postgres=CTc/postgres
 template1 | postgres | UTF8    | sv_SE.UTF-8 | sv_SE.UTF-8 |
=c/postgres          +
           |          |         |             |             |

If so what is search_path?

bnl=> show search_path;
  search_path
----------------
 "$user",public
(1 rad)

I could not replicate the below.

What does below show?:

select '2016-09-30 13:00:00'::timestamp at time zone 'UTC’;

bnl=> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';
        timezone
------------------------
 2016-09-30 15:00:00+02

select '2016-10-01 15:35:00'::timestamp at time zone ’UTC’

bnl=> select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';
        timezone
------------------------
 2016-10-01 17:35:00+02

--
Björn Lundin
b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 3/16/20 1:51 AM, Björn Lundin wrote:

16 mars 2020 kl. 01:41 skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>>:

Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?

Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty

Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.

bnl=> explain select * from amarkets order by startts;

Can you run as:

explain analyze select * from amarkets order by startts;

                              QUERY PLAN
-----------------------------------------------------------------------
 Sort  (cost=10702.57..10939.29 rows=94691 width=106)
   Sort Key: startts
   ->  Seq Scan on amarkets  (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)

--
Björn Lundin
b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 3/16/20 3:03 AM, Björn Lundin wrote:

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?

I realize that I have (basically) the same dataset on another machine.

Which brings me back to your first post where you had:

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

Then you said the database was:

version

------------------------------------------------------------------------------------------------
PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.9.2-10) 4.9.2, 64-bit
(1 rad)

Which seemed to be confirmed by:

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

That leaves me wondering how you got to the output in the first post?

In other words different psql version and no server version listed which
indicates the server is 9.6.

bnl=# select version();
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

*bnl@tp*:*~*$ uname -a
Linux tp 4.15.0-39-generic #42-Ubuntu SMP Tue Oct 23 15:48:01 UTC 2018
x86_64 x86_64 x86_64 GNU/Linux

It misses som later record (from 2020) but otherwise contains the same
data, and same definition
It is also the only user-database on the system

bnl=# \d amarkets
                                      Table "public.amarkets"
      Column      |              Type              | Collation |
Nullable |        Default
------------------+--------------------------------+-----------+----------+------------------------
 marketid         | character varying(11)          |           | not
null | ' '::character varying
 marketname       | character varying(50)          |           | not
null | ' '::character varying
 startts          | timestamp(3) without time zone |           | not
null |
 eventid          | character varying(11)          |           | not
null | ' '::character varying
 markettype       | character varying(25)          |           | not
null | ' '::character varying
 status           | character varying(50)          |           | not
null | ' '::character varying
 betdelay         | integer                        |           | not
null | 1
 numwinners       | integer                        |           | not
null | 1
 numrunners       | integer                        |           | not
null | 1
 numactiverunners | integer                        |           | not
null | 1
 totalmatched     | numeric(15,2)                  |           | not
null | 0.0
 totalavailable   | numeric(15,2)                  |           | not
null | 0.0
 ixxlupd          | character varying(15)          |           | not
null | ' '::character varying
 ixxluts          | timestamp(3) without time zone |           | not
null |
Indexes:
    "amarketsp1" PRIMARY KEY, btree (marketid)
    "amarketsi2" btree (eventid)
    "amarketsi3" btree (markettype)
    "amarketsi4" btree (status)
    "amarketsi5" btree (numwinners)
    "amarketsi6" btree (ixxluts)

This gets it correctly.

So it points to something on the first machine.
Recreating indexes is a possibility, but (to me) a bit unintuitive since
there are no index on startts
I’ll do that tomorrow.

--
Björn Lundin
b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Is amarkets in more then one schema?

Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well

Actually the below indicates it is in other databases. A schema would be a namespace within a database, see here:

https://www.postgresql.org/docs/12/sql-createschema.html

In your original example the 'public' in public.amarkets.
So just to be complete \dn in psql will show you the schemas in a database. Given the search_path("$user",public) shown below I suspect you have only a public schema. $user matches a schema named for the current user and generally is not there.

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \dn
Lista med scheman
Namn | Ägare
--------+----------
public | postgres
(1 rad)

Yes only 1 schema

Hmm to be clear, I have the problem on a machine
Called ibm running debian
In a 9.4 database called bnl

This also have database dry and import which both contain the same table (with only PK - no index) - but both those are empty.

Then I said I have the same dataset on another another box
Called tp, running ubuntu
With a 10.6 database called bnl
Which works

I’ll reply to the other mail separately

thanks for replying

--
Björn Lundin
b.f.lundin@gmail.com

16 mars 2020 kl. 16:27 skrev Adrian Klaver <adrian.klaver@aklaver.com>:

On 3/16/20 1:51 AM, Björn Lundin wrote:

16 mars 2020 kl. 01:41 skrev Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>:

Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> writes:

On 3/15/20 2:33 PM, Björn Lundin wrote:

I then did ’select * from AMARKETS order by STARTTS’

Is amarkets in more then one schema?

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?

Yes - in other schemas - described in reply to Adrain
But the schema_path does not point to them
And those two other tables are empty

Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.

bnl=> explain select * from amarkets order by startts;

Can you run as:

explain analyze select * from amarkets order by startts;

Yes, below the first plan

QUERY PLAN
-----------------------------------------------------------------------
Sort (cost=10702.57..10939.29 rows=94691 width=106)
Sort Key: startts
-> Seq Scan on amarkets (cost=0.00..2875.91 rows=94691 width=106)
(3 rader)
—

bnl=> explain analyze select * from amarkets order by startts;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Sort (cost=10702.57..10939.29 rows=94691 width=106) (actual time=404.555..422.174 rows=97835 loops=1)
Sort Key: startts
Sort Method: quicksort Memory: 24329kB
-> Seq Scan on amarkets (cost=0.00..2875.91 rows=94691 width=106) (actual time=4.586..351.739 rows=97835 loops=1)
Planning time: 74.707 ms
Execution time: 434.785 ms
(6 rader)

Tid: 527,142 ms
bnl=>

--
Björn Lundin
b.f.lundin@gmail.com

16 mars 2020 kl. 16:46 skrev Adrian Klaver <adrian.klaver@aklaver.com>:

On 3/16/20 3:03 AM, Björn Lundin wrote:

Yeah, it's hard to think of any explanation other than "the query used a
corrupt index on startts to produce the ordering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?

I realize that I have (basically) the same dataset on another machine.

Which brings me back to your first post where you had:

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

Then you said the database was:

version
------------------------------------------------------------------------------------------------
PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 rad)

Which seemed to be confirmed by:

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

That leaves me wondering how you got to the output in the first post?

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

I did not realize that would matter when posting - did the post away from home,
I can reach the prod machine but not the history machine (ibm2) from outside.

So - from the pi - first post

bnl=# \q
bnl@pibetbot:~ $ psql
Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

bnl=# \d amarkets
Table "public.amarkets"
Column | Type | Modifiers
------------------+--------------------------------+-----------------------------------------
marketid | character varying(11) | not null default ' '::character varying
marketname | character varying(50) | not null default ' '::character varying
startts | timestamp(3) without time zone | not null
eventid | character varying(11) | not null default ' '::character varying
markettype | character varying(25) | not null default ' '::character varying
status | character varying(50) | not null default ' '::character varying
betdelay | integer | not null default 1
numwinners | integer | not null default 1
numrunners | integer | not null default 1
numactiverunners | integer | not null default 1
totalmatched | numeric(15,2) | not null default 0.0
totalavailable | numeric(15,2) | not null default 0.0
ixxlupd | character varying(15) | not null default ' '::character varying
ixxluts | timestamp(3) without time zone | not null
Indexes:
"amarketsp1" PRIMARY KEY, btree (marketid)
"amarketsi2" btree (eventid)
"amarketsi3" btree (markettype)
"amarketsi4" btree (status)
"amarketsi5" btree (numwinners)
"amarketsi6" btree (ixxluts)

bnl=# \q
bnl@pibetbot:~ $ logout
Connection to 192.168.1.7 closed.

From the machine (ibm2) with bad sort order

imac:~ bnl$ ssh 192.168.1.20
bnl@192.168.1.20's password:
Linux ibm2 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
No mail.
Last login: Mon Mar 16 16:54:56 2020 from 192.168.1.174
bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \d amarkets
                                   Tabell "public.amarkets"
      Kolumn      |              Typ               |               Modifierare                
------------------+--------------------------------+------------------------------------------
 marketid         | character varying(11)          | inte null default ' '::character varying
 marketname       | character varying(50)          | inte null default ' '::character varying
 startts          | timestamp(3) without time zone | inte null
 eventid          | character varying(11)          | inte null default ' '::character varying
 markettype       | character varying(25)          | inte null default ' '::character varying
 status           | character varying(50)          | inte null default ' '::character varying
 betdelay         | integer                        | inte null default 1
 numwinners       | integer                        | inte null default 1
 numrunners       | integer                        | inte null default 1
 numactiverunners | integer                        | inte null default 1
 totalmatched     | numeric(15,2)                  | inte null default 0.0
 totalavailable   | numeric(15,2)                  | inte null default 0.0
 ixxlupd          | character varying(15)          | inte null default ' '::character varying
 ixxluts          | timestamp(3) without time zone | inte null
Index:
    "amarketsp1" PRIMARY KEY, btree (marketid)
    "amarketsi2" btree (eventid)
    "amarketsi3" btree (markettype)
    "amarketsi4" btree (status)
    "amarketsi5" btree (numwinners)
    "amarketsi6" btree (ixxluts)

bnl=>

--
Björn Lundin
b.f.lundin@gmail.com

=?utf-8?Q?Bj=C3=B6rn_Lundin?= <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

On 3/16/20 9:15 AM, Björn Lundin wrote:

16 mars 2020 kl. 16:46 skrev Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:

On 3/16/20 3:03 AM, Björn Lundin wrote:

Yeah, it's hard to think of any explanation other than "the query
used a
corrupt index on startts to produce the ordering".  But your \d doesn't
show any index on startts.  So maybe there's more than one amarkets
table?

I realize that I have (basically) the same dataset on another machine.

Which brings me back to your first post where you had:

Timing is on.
AUTOCOMMIT off
psql (9.6.10)
Type "help" for help.

Then you said the database was:

                                    version
------------------------------------------------------------------------------------------------
PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.9.2-10) 4.9.2, 64-bit
(1 rad)

Which seemed to be confirmed by:

bnl@ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

That leaves me wondering how you got to the output in the first post?

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

To be clear the RPI version of the database sorts correctly?

I did not realize that would matter when posting - did the post away
from home,

Yes, it would be have been nice to know at the outset there where
multiple instances involved.

I can reach the prod machine but not the history machine (ibm2) from
outside.
So - from the pi - first post

--
Adrian Klaver
adrian.klaver@aklaver.com

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

To be clear the RPI version of the database sorts correctly?

Yes, but as I replied to Tom, it only contains a days worth of data, then pg_dump()ed and truncated.
Tas data is imported to
* the faulty one (ibm2/debian/9.4)
* the correct one (tp/ubuntu/pg 10.6)

I did not realize that would matter when posting - did the post away from home,

Yes, it would be have been nice to know at the outset there where multiple instances involved.

Hmm did not realize that. It’s hard to know when to leave out ’insignificant details’ and when not to.
(Ie when the details turn out to be significant)
I saw a machine - with its current data - sort in a for me strange way.
Then it struck me that I have another (semi-retired) machine with basically the same data,
Enetered the same way, with the same import files, that works

So in a sense many instances, but not really.
I mean, the pg_dump does copy-commands.
I could have inserted that by hand.

--
Björn Lundin
b.f.lundin@gmail.com

On 3/16/20 11:56 AM, Björn Lundin wrote:

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

To be clear the RPI version of the database sorts correctly?

Yes, but as I replied to Tom, it only contains a days worth of data,
then pg_dump()ed and truncated.
Tas data is imported to
* the faulty one (ibm2/debian/9.4)
* the correct one (tp/ubuntu/pg 10.6)

Per Tom's comment, what are the encodings?

Also I would point out that the problem occurs on the machine you are
dumping/restoring backwards 9.6 --> 9.4. Not sure if that is relevant or
not, but worth looking at.

How is the dump/restore done(plain text, custom format, etc) and what
are the command strings?

Also what versions of pg_dump/pg_restore are you using on the dump and
restore sides for the various Postgres versions?

More below.

I did not realize that would matter when posting - did the post away
from home,

Yes, it would be have been nice to know at the outset there where
multiple instances involved.

Hmm did not realize that. It’s hard to know when to leave out
’insignificant details’ and when not to.
(Ie when the details turn out to be significant)
I saw a machine - with its current data - sort in a for me strange way.
Then it struck me that I have another (semi-retired) machine with
basically the same data,
Enetered the same way, with the same import files, that works

So in a sense many instances, but not really.

Yes really, otherwise you would not be seeing a difference. Sorry, pet
peeve of mine, when people say these two things are not doing the same
thing but then say they are the same thing.

I mean, the pg_dump does copy-commands.

It also does a certain amount of setup at the beginning of the file.

I could have inserted that by hand.

--
Björn Lundin
b.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>

--
Adrian Klaver
adrian.klaver@aklaver.com