Subject: Re: DROP COLLATION vs pg_collation question

From: Karsten Hilbert <[email-masked]>
Date: Wed, 12 Jun 2024 21:13:39 +0200

> > 	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

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 13 Jun 2024 09:49:46 +0200

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

src/backend/catalog/namespace.c:

  /*
   * get_collation_oid - find a collation by possibly qualified name
   *
   * Note that this will only find collations that work with the current
   * database's encoding.
   */
  Oid
  get_collation_oid(List *collname, bool missing_ok)

Yours,
Laurenz Albe

---

From: Karsten Hilbert <[email-masked]>
Date: Fri, 14 Jun 2024 22:08:04 +0200

>   get_collation_oid(List *collname, bool missing_ok)

Thanks.

Are collations per-database or per-cluster objects ?

I am asking because I seem to not be enabled to

1) use a collation that's intended for an encoding different
   from the database encoding

	-> makes sense

2) remove a collation that's intended for an encoding
   different from the database encoding

	-> so far so good, ignore them, but

3) update collation version information in pg_collations for
   collations intended for an encoding different from the
   database encoding (ALTER COLLATION ... REFRESH VERSION fails)

which in effect would mean that -- upon change of collation
versions in the underlying operating system (ICU update, libc
update) -- one would have to live with outdated version
information in pg_collations short of dump/sed/restore or
some such ?

I'm pretty sure I am overlooking something.

Best,
Karsten

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 16 Jun 2024 06:53:31 +0200

> Are collations per-database or per-cluster objects ?

Each database has its own "pg_collation" catalog table.

So they are local to the database, but the collations themselves
are defined by an external library, so the implementation is shared.

> information in pg_collations short of dump/sed/restore or

That should not happen.  What error do you get when you

  ALTER COLLATION ... REFRESH VERSION

Does the following give you the same error?

  ALTER DATABASE ... REFRESH COLLATION VERSION

Yours,
Laurenz Albe