Message-ID: <8dbff2ac-eada-4f32-bc4f-d7b3b807a35b@iki.fi>
Date: Fri, 1 Dec 2023 13:37:02 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pgsql: Clean up role created in new subscription test.
Content-Language: en-US
To: Daniel Gustafsson <daniel@yesql.se>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Peter Eisentraut <peter@eisentraut.org>, Tom Lane <tgl@sss.pgh.pa.us>,
 Robert Haas <robertmhaas@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <202311081232.rn3aho5ay6yx@alvherre.pgsql>
 <E0EB10CE-9B58-44C0-8CBC-807BC8E5A84A@yesql.se>
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: <E0EB10CE-9B58-44C0-8CBC-807BC8E5A84A@yesql.se>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8dbff2ac-eada-4f32-bc4f-d7b3b807a35b%40iki.fi>
Precedence: bulk

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?

> +	/*
> +	 * 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. Maybe something like this:

(SELECT 'role', rolname AS obj FROM pg_catalog.pg_roles
UNION ALL
SELECT 'tablespace', spcname AS obj FROM pg_catalog.pg_tablespace
UNION ALL
SELECT 'subscription', subname AS obj FROM pg_catalog.pg_subscription
) ORDER BY 1, 2

Is it OK to leave behind extra databases?

-- 
Heikki Linnakangas
Neon (https://neon.tech)