public inbox for [email protected]  

help / color / mirror / Atom feed

From: Karsten Hilbert <[email protected]>
To: [email protected]
Subject: Re: DROP COLLATION vs pg_collation question
Date: Wed, 12 Jun 2024 21:13:39 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

> > 	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 = _db_name;
		RAISE NOTICE ''database [%]: removing collations for encodings other than the database encoding [%]'', _db_name, pg_catalog.pg_encoding_to_char(_db_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::regnamespace || ''."'' || _rec.collname || ''"'';
			EXCEPTION
				WHEN undefined_object THEN RAISE NOTICE ''collation does not seem to exist (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
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: DROP COLLATION vs pg_collation question
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox