Date: Fri, 14 Jun 2024 22:08:04 +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: <ZmyjJA0jGU5TL64I@hermes.hilbert.loc>
References: <Zmi-gljgKdjQmY8B@hermes.hilbert.loc>
 <d3384b9f1e870eda5f6a01b008209398b18b36d4.camel@cybertec.at>
 <ZmnzY3uqoiBrFOoO@hermes.hilbert.loc>
 <26f717988471c4d672cce8c495a2c8e68446486a.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <26f717988471c4d672cce8c495a2c8e68446486a.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:FPQOsoMkv0CHIANarfx21FdQ2KClJX1RaMP7PIj+HLOqaK3KcJn
 WcFMGDVzKoEI3DCj+Pqe6o/0Y8cQcAQVGXh9jJfnyJ9fL0+3WpVLIM0erjrVVc/gGqS2WZ7
 JsZVXu5ejFzwCDe7GR39/lAqbf4+ynMhP5NwCrJHnp3N4QI7rjAIig0u08bUV6ko42y1Jsu
 bsVpB+rEqSxTiNkYrBG1Q==
X-Spam-Flag: NO
UI-OutboundReport: notjunk:1;M01:P0:C+ZilQWF1hQ=;Ai4IPPep/e/bahYvH1V9si+6oAx
 IZFG9BLf4WhFH2e9fXrysCL1xB9fkmXVMDtWHGTe5zTnoARPP6DU59eTvEgK8qEmxlJdzD9O+
 lk5N3ptiK9TroRR3FXsdXYX3SjK1ovu6zKI7E6fG2RaDBCjyjOfobfnQ3fZgOfKEPBGnnYEN0
 grlo07vCuO39gtnLNTnBAYjqoE1Nt4xw76f632nt2jED/WDH3WYvP0REYg35hR1pCOGj42yPU
 duf5B4K5utBYvkWXJ8ESdEdpPv4bfUoi2birfb0JUm53R6XxGddoxPK10t9kLzKMWljF91JSL
 UIhs7NP079nVz3KvOOzutzkifVW35C0rIffoVtaIwxEnv2x7ZGQjiQw6S/RPZkcTryrs/vkFr
 YsEcJ955BEXuIRcGDREb9IdN4Sdt25TCHfLqmPXhLOYSgybUj+TEzc6bC3JtbiBXdgluW2GYx
 YGM2EQPOaBeWLXE9stfT6SK/wUSz3OOoPD5reKhXm1FDwnXcJz8c5p8CqtcAFTe99KyNIbfXx
 Xb0xpUtjpdaofdzUFW9iwIMpifAH/6bB/H1tX88MJZM9Ul2ObUAt2JSzRnM0nHklxSkw64eeS
 /6GV0O5GEM3OU760BZgwynIHavQP4ZstYBlvrVDWVLuAkTN7Y08g8/jWPHQf6wSbsRSv/JAyf
 PZYAXEq7OAjnySJk7eHnQZ8paQIxndEVlsLqmy4b9hJNRc5NV/hC1cdKZPsJxJLnZ9oiFWrV9
 lIyFw4NNFHhEwNIooEdX87YMUB0PuVYIPlzM6PyCFNiUXq0KUcCPGVQVKc5J78oHIQxUjIkWD
 U7SEbImH85bdqXc3loluBRK2FSTJq7J9cKYqpOZdg1rIE=
Content-Transfer-Encoding: quoted-printable
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/ZmyjJA0jGU5TL64I%40hermes.hilbert.loc>
Precedence: bulk

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
=2D-
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B