public inbox for [email protected]help / color / mirror / Atom feed
Re: DROP COLLATION vs pg_collation question 4+ messages / 2 participants [nested] [flat]
* Re: DROP COLLATION vs pg_collation question @ 2024-06-12 19:13 Karsten Hilbert <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Karsten Hilbert @ 2024-06-12 19:13 UTC (permalink / raw) To: [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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-13 07:49 Laurenz Albe <[email protected]> parent: Karsten Hilbert <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Laurenz Albe @ 2024-06-13 07:49 UTC (permalink / raw) To: Karsten Hilbert <[email protected]>; [email protected] On Wed, 2024-06-12 at 21:13 +0200, Karsten Hilbert wrote: > > 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 ? 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-14 20:08 Karsten Hilbert <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Karsten Hilbert @ 2024-06-14 20:08 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Am Thu, Jun 13, 2024 at 09:49:46AM +0200 schrieb Laurenz Albe: > > > Note that with DROP COLLATION you can only remove collations > > > that belong to the encoding of your current database. > > 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) 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 -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-16 04:53 Laurenz Albe <[email protected]> parent: Karsten Hilbert <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Laurenz Albe @ 2024-06-16 04:53 UTC (permalink / raw) To: Karsten Hilbert <[email protected]>; +Cc: [email protected] On Fri, 2024-06-14 at 22:08 +0200, Karsten Hilbert wrote: > 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. > I am asking because I seem to not be enabled to > > 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 ? 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-06-16 04:53 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-12 19:13 Re: DROP COLLATION vs pg_collation question Karsten Hilbert <[email protected]> 2024-06-13 07:49 ` Laurenz Albe <[email protected]> 2024-06-14 20:08 ` Karsten Hilbert <[email protected]> 2024-06-16 04:53 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox