From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
cc: Andres Freund <andres@anarazel.de>, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Update pg_upgrade test for reg* to include regrole and
 regnamesp
In-reply-to: <6f5edd28-e55f-7399-d89c-d2d1ed2d1e51@2ndQuadrant.com>
References: <E1gRS1V-0007eS-1p@gemulon.postgresql.org>
 <8863.1543297423@sss.pgh.pa.us>
 <20181127063226.mpyijg3jwrmmm3rr@alap3.anarazel.de>
 <6f5edd28-e55f-7399-d89c-d2d1ed2d1e51@2ndQuadrant.com>
Comments: In-reply-to Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
	message dated "Tue, 27 Nov 2018 10:32:05 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2734.1543333649.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 27 Nov 2018 10:47:29 -0500
Message-ID: <2735.1543333649@sss.pgh.pa.us>
Precedence: bulk

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> In any case, I think we will probably need to bite the bullet and have =

> pg_dump render LOs in OID order.

+1.  Looking at the relevant code:

    /*
     * Currently, we re-fetch all BLOB OIDs using a cursor.  Consider scan=
ning
     * the already-in-memory dumpable objects instead...
     */
    if (fout->remoteVersion >=3D 90000)
        blobQry =3D "DECLARE bloboid CURSOR FOR SELECT oid FROM pg_largeob=
ject_metadata";
    else
        blobQry =3D "DECLARE bloboid CURSOR FOR SELECT DISTINCT loid FROM =
pg_largeobject";

I suspect that the original expectation was that SELECT DISTINCT would
deliver the blob OIDs in sorted order, and nobody noticed when hash-based
DISTINCT broke that, and then the pg_largeobject_metadata patch figured
that it didn't need to preserve a guarantee that wasn't there.

We have taken pains in the past to ensure consistent dump ordering, and
it seems to me the lack of that here is just an oversight.  It's kinda
surprising that it hasn't bit us before.

> Not sure if that needs to be always on, =

> or an option.

We have no existing option that says "you can skip ordering my dump
consistently".  I see no need for one here.

The easy fix is to add "ORDER BY 1" to both of the above queries.
If anyone is concerned about the performance of that, they could
go try to do what the comment suggests, but I'm not personally
excited about that.

			regards, tom lane