public inbox for [email protected]help / color / mirror / Atom feed
Re: DROP COLLATION vs pg_collation question 6+ messages / 4 participants [nested] [flat]
* Re: DROP COLLATION vs pg_collation question @ 2024-06-16 09:27 Karsten Hilbert <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Karsten Hilbert @ 2024-06-16 09:27 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-16 17:37 Laurenz Albe <[email protected]> parent: Karsten Hilbert <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Laurenz Albe @ 2024-06-16 17:37 UTC (permalink / raw) To: Karsten Hilbert <[email protected]>; +Cc: [email protected] On Sun, 2024-06-16 at 11:27 +0200, Karsten Hilbert wrote: > > 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 ? 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-16 20:38 Tom Lane <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2024-06-16 20:38 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Karsten Hilbert <[email protected]>; [email protected] Laurenz Albe <[email protected]> writes: > On Sun, 2024-06-16 at 11:27 +0200, Karsten Hilbert wrote: >> 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 ? > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-18 13:02 Karsten Hilbert <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Karsten Hilbert @ 2024-06-18 13:02 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] Am Sun, Jun 16, 2024 at 04:38:49PM -0400 schrieb Tom Lane: > 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. 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 -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-18 13:32 Karsten Hilbert <[email protected]> parent: Karsten Hilbert <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Karsten Hilbert @ 2024-06-18 13:32 UTC (permalink / raw) To: [email protected] Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert: > 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, 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 -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DROP COLLATION vs pg_collation question @ 2024-06-18 14:44 Adrian Klaver <[email protected]> parent: Karsten Hilbert <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Adrian Klaver @ 2024-06-18 14:44 UTC (permalink / raw) To: Karsten Hilbert <[email protected]>; [email protected] On 6/18/24 06:32, Karsten Hilbert wrote: > Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert: > >> 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, > > 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. 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. > > Karsten > -- > GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-18 14:44 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-16 09:27 Re: DROP COLLATION vs pg_collation question Karsten Hilbert <[email protected]> 2024-06-16 17:37 ` Laurenz Albe <[email protected]> 2024-06-16 20:38 ` Tom Lane <[email protected]> 2024-06-18 13:02 ` Karsten Hilbert <[email protected]> 2024-06-18 13:32 ` Karsten Hilbert <[email protected]> 2024-06-18 14:44 ` Adrian Klaver <[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