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

From: Andres Freund <andres(at)anarazel(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: dump/restore doesn't preserve row ordering?
Date: 2016-08-23 21:41:43
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

> 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,
len + saveFreeSpace);
that looks like it's even possible without a concurrent autovacuum.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2016-08-23 21:48:17 Re: pg_dump with tables created in schemas created by extensions
Previous Message Stephen Frost 2016-08-23 21:22:43 Remove superuser() checks from pgstattuple