Re: dump/restore doesn't preserve row ordering?

On 2016-08-23 17:22:03 -0400, Tom Lane wrote:
> 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.

That's
http://archives.postgresql.org/message-id/CAE2gYzxv8YKEd4O%2B9HUYuQ%3DQMH4pwt9n9cmU-OchV-%3DN8Q7yXQ%40mail.gmail.com
?
> 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.

It seems possible that a larger row didn't fit on a page anymore, then
later when a new page was is needed for a smaller row, the earlier page
is found again. Due to RelationGetBufferForTuple() updating the fsm
when an old target buffer is present:
/*
* Update FSM as to condition of this page, and ask for another page
* to try.
*/
targetBlock = RecordAndGetPageWithFreeSpace(relation,
targetBlock,
pageFreeSpace,
len + saveFreeSpace);
that looks like it's even possible without a concurrent autovacuum.

Andres