Re: Test instability when pg_dump orders by OID

On Mon, Aug 25, 2025 at 05:15:55PM -0400, Andres Freund wrote:
> On 2025-08-24 09:08:16 -0700, Noah Misch wrote:
> > On Sun, Aug 24, 2025 at 11:50:01AM -0400, Tom Lane wrote:
> > > Andres Freund <andres(at)anarazel(dot)de> writes:
> > > > I wonder if it's worth adding support to CI to perform the cross-version
> > > > upgrade test. It'd be pretty easy to install all pgdg apt postgres packages to
> > > > the debian image, which then could be used as the source version...
> >
> > I think catching this particular case would take more than that. It entails
> > running the latest v16 src/test/regress suite, capturing the dump of that into
> > $animal_root/upgrade.$animal/REL_16_STABLE/*.sql, and seeing the upgrade
> > failure of that dump having the latest v16 regression objects. I don't know
> > how to get there without a v16 source tree.
>
> Ah, ok, that does make it less worthwhile to go after.
>
> > > I feel that that's the wrong tradeoff. CI should be expected to be
> > > fairly cheap, not to catch everything the buildfarm could catch.
>
> I think it's also about removing painful manual testing - and imo manually
> running cross-version pg_upgrade tests is really rather painful.

I make the buildfarm client drive it. That was painful to set up the first
time[1], but the per-run manual pain isn't bad. A run of all supported
branches takes hours of wall time, though. There are some optimization
opportunities, but it hasn't come up often enough to make those compelling for
me to implement.

[1] For example, there's no one OpenSSL version compatible with all of v9.2 -
v19. Disabling SSL doesn't solve that: some versions then disable pgcrypto,
and the upgrade test fails for pgcrypto being absent on one side of the
upgrade. I settled on SSL only for the versions where pgcrypto requires it.
Version-dependent LD_LIBRARY_PATH etc. likely would have been an alternative.