Date: Sun, 16 Jun 2024 11:27:35 +0200
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: DROP COLLATION vs pg_collation question
Message-ID: <Zm6wB76XFH2Za1SC@hermes.hilbert.loc>
References: <Zmi-gljgKdjQmY8B@hermes.hilbert.loc>
 <d3384b9f1e870eda5f6a01b008209398b18b36d4.camel@cybertec.at>
 <ZmnzY3uqoiBrFOoO@hermes.hilbert.loc>
 <26f717988471c4d672cce8c495a2c8e68446486a.camel@cybertec.at>
 <ZmyjJA0jGU5TL64I@hermes.hilbert.loc>
 <56da532b44bc02a7648796041ffa5da0a85c06df.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <56da532b44bc02a7648796041ffa5da0a85c06df.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:MvgsYR6+ZUKRKL6ZF3HPaJA2Ekw6oxL2ACGEInabJkF1CUYIQVE
 kcNOnszSNwtj+FRFBpAKV4p8xNfBFekuZ83uUTkQOVHBMF9+Fm0OQxbtz7Jns17URIjHw8X
 0BETyvHeTNYL2+iXnDU/dCpJMw00MfOxYmZdLoKMZpn04BXoVfl1DO5BFtSaVzuuhNSdkHr
 NaKvFmRFAvu6YfY/LPCXg==
X-Spam-Flag: NO
UI-OutboundReport: notjunk:1;M01:P0:xYwluJbjlAs=;bKa4zkWj5O8Rke/XAPyzLKsdWNs
 1ByKs+KibCMU5SAMtXNkGKygFd0hCP4FsZ1SnuHqyO3HjlqRlzP8cVnDlvw85qUB1KWtNIcmc
 v/UeJDrg83+FOY8sGYASXJIGU604vLeyiTfUrjxsdV/uQZYG7tlX7EUH1W6YMs2dcXMBfHpmM
 V5+q09k0Y4v3QPGB2TFvXA8B8boCqC4fRNdEKC9QwZEL/xb9dq6tauA6uiGUXryJWwOIewJk5
 uHA1jcTD6SrY2UrjDRvSoKc7CDaOE557bOO3ujAmXAyYMaQYKABfFq2IG5D0LrtOhvQQU1Tw8
 y07Ios9vjPJAbjnenXoJTQ2o/yogNOFFC3bDrZkcdsEN/wiF6hFABa52SClWjD4CliQ2m4s88
 XBGq609ATyumAiWJivGZ4FiGmuZwY0Iiz9xy9/4VPpUFw+C55MW5aE84ZDe5k6ag5rRNgvinN
 XtlhsXIThxKTraRu7Ox0SGM5wjZvh+7XIfS7mfod6J7k4079qAiJe+rI47g7RHDXhTlMBh3VP
 H4kmsUAdZ174OZoa5qrk2+rJb/RQKW7t99ax9MejW79lJ8enhsDlSbarJLhM8Hl/AJYw3/RdV
 be3JGS7Pz/C5jnIwfYUIvGeSKdrcJH36l5NeWWnSyvSYVnX1o5Gh8SUowkxNYJLg6VMl7S/F/
 VHmL6V23Nztvp3WP2IEuw5mVMz47iV4UxNREOqoudqXIFHoYk6ZEFsNqpp6CFeQ6Ow3LzxRDv
 ZYiRod1WlEbVBERh8wzmkm5xf0nkBvXSd9eeFhm7HiIBD3T6dE53EcVqVgIGtURLhcwwMCFpC
 tknLZRtvz0YodAGFibi6dA5eAeAg65NmvK3nVmuBuB1Cw=
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/Zm6wB76XFH2Za1SC%40hermes.hilbert.loc>
Precedence: bulk

Am Sun, Jun 16, 2024 at 06:53:31AM +0200 schrieb Laurenz Albe:

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

I would have thought so, thanks for confirming.

> but the collations themselves
> are defined by an external library, so the implementation is shared.

Which in turn means I cannot at all truly _remove_ collations
from a cluster at the SQL level, only make them invisible
(and thereby not-to-be-used) inside a particular database by
removing them from pg_collations via DROP COLLATION, right ?

> > 3) update collation version information in pg_collations for
> > =A0=A0 collations intended for an encoding different from the
> > =A0=A0 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

The error I got was to the effect of insufficient permissions
(the connected user wasn't the owner of the collation).
ALTERing as superuser updated collation version information
just fine, so PEBKAC.

For the record: Yes, collation versions CAN be updated
regardless of whether a given collation applies to the
database's encoding.

> Does the following give you the same error?
>
>   ALTER DATABASE ... REFRESH COLLATION VERSION

That did not show the same error because I ran it as database
owner (?). The database owner does not own the collations,
however, which made the above fail.

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