Date: Wed, 12 Jun 2024 21:13:39 +0200
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: DROP COLLATION vs pg_collation question
Message-ID: <ZmnzY3uqoiBrFOoO@hermes.hilbert.loc>
References: <Zmi-gljgKdjQmY8B@hermes.hilbert.loc>
 <d3384b9f1e870eda5f6a01b008209398b18b36d4.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <d3384b9f1e870eda5f6a01b008209398b18b36d4.camel@cybertec.at>
Ma_X_il-Followup-to: d
Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net
Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net
X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net
X-Pri_X_ority: 2 (High)
Sender: <karsten.hilbert@gmx.net>
X-Provags-ID: V03:K1:Ycd1ws3MERLLWpbO3vGoBGnaWtVv5D05ShFvgwDDbTQBd1HAJ1B
 vLO2rgpSJQXq8x7NIpOIwSNueuMujlVP7m6ruObOcI6YJII/0g3htG1ObhKcdfvb1X4s2j+
 9B4sZHb3wiKjNHZSxMeHwy65JwiPI6EZfa/VHvvuNz55/ABE+npKmu50+yADJM1np0GCQmL
 Rw+7y/CAxOhCFDzlZKsaA==
X-Spam-Flag: NO
UI-OutboundReport: notjunk:1;M01:P0:+xNA8/m+FjA=;Ah6+DS1nDvGsWicGlelSqglirxC
 U47tfY3Mr/hXTL3G7ETIXkwy4ayAj2od05STOUZrm/yDXgUDPODk6ANC2pRiV48YR4tT70/ol
 o2QeE7B1pL02ntDJnbuuIy8eSr9wzJW0q2nxp7J8g4MVakzR/Pq3W7TO3S9QHGvu1zTWkyITf
 diICy0GoaJ/SaZXIjAyKdqHyGMzYOdEeVfjwIQAci7ypdmcoFpPXOOzbCiEtVvC70m0FId84s
 5UA1oEIwrOcnBD5Da46ibw41Wyhm8r/8wB8tqDocujU8OtIYOl5ab1pnOwhulMVEb6Er1SE/f
 QUwNzyBpGLrpszK61obIyHae4+yS2/0rLuHni6qNd+h0b4Tb2S0YV9WCLFQep5HhvxJSqAQeI
 n+7vy54/DBOHk1X8RJzhBnu/7ghO+/Ul1dQn0mCwa9WDVf/TP8i+zv18b1FWBPqbidXU64ZyE
 gyd1YEyhYFOglKbSt3iI3tk4CvG1EAMMAj7f/a2moUaDIl0+4ex7axOdsMOPOHkWOI5Eo7CMv
 X1vf0piwk6/1dqgSdfCW58epGJqd+2JcdTTrfgElE8TltYhhyIx87dl6t+qKNEruXNdd20SAs
 3oTFFrTAs2wUckiIC1maqql20SZizjNoNqmDtInyiYWMhutS7nB57jPIWwqDo6QvsXaYjmOWH
 VcsvAUyx3tNYrQ8QZ1x0St8Tx+lKypa62GazbobXJZ6WSPiMfN6rbZKRJhaF4wYVhw7l5Heyj
 NFrQrse6fj2NXZKwaQQnMvWwTdng+36zFDxxXmOg3qFj2wgBaAOL4SBR7o/tZbSZA8CSElQen
 5mG62w3zJoFRjgKOTkVYMsCpReBGKK0HPjHtI4zb3UHgA=
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/ZmnzY3uqoiBrFOoO%40hermes.hilbert.loc>
Precedence: bulk

> > 	DROP COLLATION IF EXISTS pg_catalog."...."
>
> Yes, that will delete a row from "pg_collation".

Many thanks.

> Note that with DROP COLLATION you can only remove collations
> that belong to the encoding of your current database.

A-ha !  Can that bit be found anywhere in the docs ?

IOW, the following code is exactly useless ?

(because of the "collencoding <> _db_encoding" business ;-)

	create function gm.remove_unneeded_collations()
		returns void
		language plpgsql
		security definer
		as '
	DECLARE
		_rec record;
		_db_name text;
		_db_encoding integer;
	BEGIN
		SELECT pg_catalog.current_database() INTO _db_name;
		SELECT encoding INTO _db_encoding FROM pg_database WHERE datname =3D _db=
_name;
		RAISE NOTICE ''database [%]: removing collations for encodings other tha=
n the database encoding [%]'', _db_name, pg_catalog.pg_encoding_to_char(_d=
b_encoding);
		FOR _rec IN (
			SELECT oid, collnamespace, collname, collencoding
			FROM pg_collation
			WHERE
				oid > 1000
					AND
				collencoding IS NOT NULL
					AND
				collencoding <> -1
					AND
				collencoding <> _db_encoding
		) LOOP
			RAISE NOTICE ''dropping collation #% "%.%" (encoding: %)'', _rec.oid, _=
rec.collnamespace::regnamespace, _rec.collname, pg_catalog.pg_encoding_to_=
char(_rec.collencoding);
			BEGIN
				EXECUTE ''DROP COLLATION IF EXISTS '' || _rec.collnamespace::regnamesp=
ace || ''."'' || _rec.collname || ''"'';
			EXCEPTION
				WHEN undefined_object THEN RAISE NOTICE ''collation does not seem to e=
xist (perhaps for the DB encoding ?)'';
			END;
		END LOOP;
	END;';


The reason for this being the wish to reduce the risk surface
for locale version information changes at the OS level by
removing collations not relevant to a given database.

Thanks,
Karsten
=2D-
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B