Re: Is a VACUUM or ANALYZE necessary after logical replication?
On Saturday, June 15, 2024, Koen De Groote wrote: > I've gone over all of https://www.postgresql.org/docs/current/logical- > replication.html and the only mentions of the word "index" I could find > was in relation to replica identity and examples of table definitions > showing primary key indexes. > > Nothing is said about indexes. Maybe for good reason, maybe they are fully > functionality immediately after replication? > > So the main question: Once a table is fully replicated, do I need to > vacuum(analyze) that table, or are the indexes on that table already > functional? > The whole point of “logical” replication is that the inserts/updates/deletes are reapplied on the secondary against the table and the whatever triggers, indexes, or whatnot exist on that table in the secondary behave just as if you connected to the server directly and issued the corresponding SQL against it. As far as the replication system is concerned none of those things on the primary matter nor does it have to care about them on the secondary. David J.
Re: DROP COLLATION vs pg_collation question
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
Re: DROP COLLATION vs pg_collation question
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
Re: DROP COLLATION vs pg_collation question
Laurenz Albe 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
