public inbox for [email protected]
help / color / mirror / Atom feedRe: 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]>
2024-06-13 07:49 ` Re: DROP COLLATION vs pg_collation question Laurenz Albe <[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-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 ` Re: DROP COLLATION vs pg_collation question 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-12 19:13 Re: DROP COLLATION vs pg_collation question Karsten Hilbert <[email protected]>
2024-06-13 07:49 ` Re: DROP COLLATION vs pg_collation question Laurenz Albe <[email protected]>
@ 2024-06-14 20:08 ` Karsten Hilbert <[email protected]>
2024-06-16 04:53 ` Re: DROP COLLATION vs pg_collation question 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-12 19:13 Re: DROP COLLATION vs pg_collation question Karsten Hilbert <[email protected]>
2024-06-13 07:49 ` Re: DROP COLLATION vs pg_collation question Laurenz Albe <[email protected]>
2024-06-14 20:08 ` Re: DROP COLLATION vs pg_collation question Karsten Hilbert <[email protected]>
@ 2024-06-16 04:53 ` Laurenz Albe <[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