I happened to notice, while experimenting with the data set used
in the SPGIST-for-inet thread, that loading the supplied pg_dump
script and immediately dumping it does not reproduce the row order
appearing in the original dump script. I thought maybe this had
something to do with the heap_multi_insert() optimization that
COPY uses nowadays, but disabling that didn't change it. Further
experimentation says it's been like that since 8.4; 8.3 is the
last version that reproduces the source row order in this test.
I can't immediately think of a reason for this. In everyday
updates you could theorize about effects like autovacuum
asynchonously updating the FSM, but surely the FSM should have no
impact on where COPY puts stuff when loading into an empty table.
I know we've had complaints in the past about dump/reload failing
to preserve row order; that's the reason why pg_dump takes care
to turn off synchronize_seqscans. And it seems bad from a testing
standpoint too. So this bothers me. But I have no idea what's
causing it. Does this ring any bells for anyone?
regards, tom lane