Re: pgsql: Clean up role created in new subscription test.

> On 1 Dec 2023, at 12:37, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 01/12/2023 13:22, Daniel Gustafsson wrote:
>>> On 8 Nov 2023, at 13:32, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>>> I suppose you're just thinking of using PQexec() or whatever, run one
>>> query with sufficient ORDER BY, save the result, and at the end of the
>>> test run just run the same query and compare that they are cell-by-cell
>>> identical?  This sounds a lot simpler than the patch you posted.
>> I found some spare cycles for this and came up with the attached.  The idea was
>> to keep it in-line with how pg_regress already today manipulate and traverse
>> _stringlists for various things.  With the addition of the 0001 patch to clean
>> up global objects left in test_pg_dump it passes check-world.
>
> Do we want to impose this policy to all extensions too?

I don't think it would be bad, and as of today the policy holds for all of
check-world apart from this one test module.

>> +    /*
>> +     * Store the global objects before the test starts such that we can check
>> +     * for any objects left behind after the tests finish.
>> +     */
>> +    query_to_stringlist("postgres",
>> +                        "(SELECT rolname AS obj FROM pg_catalog.pg_roles ORDER BY 1) "
>> +                        "UNION ALL "
>> +                        "(SELECT spcname AS obj FROM pg_catalog.pg_tablespace ORDER BY 1) "
>> +                        "UNION ALL "
>> +                        "(SELECT subname AS obj FROM pg_catalog.pg_subscription ORDER BY 1)",
>> +                        &globals_before);
>> +
>
> Strictly speaking, the order of this query isn't guaranteed to be stable, although in practice it probably is.

Of course, will fix.  I originally had three separate query_to_stringlist calls
and had a brainfade when combining.  It seemed like pointless use of cycles
when we can get everything in one connection.

> Is it OK to leave behind extra databases?

The test suite for pg_upgrade can make use of left behind databases to seed the
old cluster, so I think that's allowed by design.

--
Daniel Gustafsson