Subject: Re: DROP COLLATION vs pg_collation question

From: Karsten Hilbert <[email-masked]>
Date: Sun, 16 Jun 2024 11:27:35 +0200

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

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

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 16 Jun 2024 19:37:08 +0200

On Sun, 2024-06-16 at 11:27 +0200, Karsten Hilbert wrote:
> > the collations themselves

As far as PostgreSQL is concerned, you can remove them.

You cannot remove the C library, but a PostgreSQL user can only use
the collation if there is a collation defined in PostgreSQL.

(Actually, that's not quite true: in CREATE DATABASE, you can use
ICU collations.  That does not depend on the collations defined in
pg_collation.)

Also, there is nothing that keeps a user from running CREATE COLLATION
to create a collation in a schema where the user can CREATE objects.

I wouldn't try too hard to make it impossible for users to use a
collation you don't want.  Dropping the collations is good enough to
keep a user from using the wrong collation by mistake.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Sun, 16 Jun 2024 16:38:49 -0400

> As far as PostgreSQL is concerned, you can remove them.

It's really kind of moot, since you can't change the encoding
of an existing database.  So any pg_collation entries that are
for an incompatible encoding cannot be used for anything in that
database, and they might as well not be there.  The reason they
are there is merely an implementation detail: CREATE DATABASE clones
those catalogs from the single copy of pg_collation in template0,
which therefore had better include all collations that might be
needed.

			regards, tom lane

---

From: Karsten Hilbert <[email-masked]>
Date: Tue, 18 Jun 2024 15:02:56 +0200

> which therefore had better include all collations that might be

I see, and since any database can be used as a template for
more databases, which can be create with an encoding
different from the template, it doesn't really make too much
sense to be able to remove even pg_collation entries.

So, DROP COLLATION is somewhat of a smoking gun pointed at my
foot :-)

Thanks,
Karsten

---

From: Karsten Hilbert <[email-masked]>
Date: Tue, 18 Jun 2024 15:32:55 +0200

Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert:

> different from the template,

Proving myself wrong:

root@hermes:~/tmp# sudo -u postgres psql -e -f /tmp/db.sql
Output format is wrapped.
drop database if exists new_tmpl;
DROP DATABASE
create database new_tmpl with encoding = 'utf8';
CREATE DATABASE
You are now connected to database "new_tmpl" as user "postgres".
select current_database();
 current_database
------------------
 new_tmpl
(1 row)

drop database if exists new_latin1;
psql:/tmp/db.sql:8: HINWEIS:  Datenbank »new_latin1« existiert nicht, wird übersprungen
DROP DATABASE
create database new_latin1 with template = new_tmpl encoding = 'latin1' locale = 'de_DE@latin1';
psql:/tmp/db.sql:9: FEHLER:  neue Kodierung (LATIN1) ist inkompatibel mit der Kodierung der Template-Datenbank (UTF8)
TIP:  Verwenden Sie die gleiche Kodierung wie die Template-Datenbank oder verwenden Sie template0 als Template.

Karsten

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 18 Jun 2024 07:44:23 -0700

> select current_database();

That is covered here:

https://www.postgresql.org/docs/16/manage-ag-templatedbs.html

"Another common reason for copying template0 instead of template1 is 
that new encoding and locale settings can be specified when copying 
template0, whereas a copy of template1 must use the same settings it 
does. This is because template1 might contain encoding-specific or 
locale-specific data, while template0 is known not to."

Substitute <db_name other then template0> for template1 and you can get 
the error you received.

-- 
Adrian Klaver
[email-masked]