In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates

Question

ERROR: query failed: ERROR:  cannot update table "table_123" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
CONTEXT:  SQL statement "UPDATE repack.table_123 SET (id, automation_job_member_id, action_id, csm_field_id, target_type, status, external_status, status_description, valid_at, started_at, completed_at, created_at, updated_at, target_identifier) = ($2.id, $2.automation_job_member_id, $2.action_id, $2.csm_field_id, $2.target_type, $2.status, $2.external_status, $2.status_description, $2.valid_at, $2.started_at, $2.completed_at, $2.created_at, $2.updated_at, $2.target_identifier) WHERE (id) = ($1.id)"
DETAIL: query was: SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
DEBUG: No workers to disconnect.

I am using the "dbzm" publication in PostgreSQL, which is created to include all tables. However, I am unable to provide a replica identity and cannot drop the replica.

I attempted to remove the table created by "repack" from the publication, but it cannot be dropped due to the fact that the publication is configured to include all tables.

postgres=> ALTER PUBLICATION dbzm_postgres DROP TABLE repack.table_123;
ERROR:  publication "dbzm_postgres" is defined as FOR ALL TABLES
DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

Please clarify: does the table you are trying to `pg_repack` have a primary key or other replica identity? Best is to edit the question and add the putput to the `psql` command `\d+ table_123`. — Laurenz Albe, Jun 14 '23 at 21:09

score 0 · Answer 1 · answered Jun 19 '23 at 06:57

It looks like pg_repack creates tables without primary key, probably for fast loading. That won't work with logical replication. So it seems like you cannot run pg_repack on a replicated table. This also makes sense from the replication angle, because the new table is a different one from the old one, and logical decoding wouldn't work.

Either don't use pg_repack or remove the table from the publication before running pg_repack. Afterwards, you can add it again. You may have to initialize replication for the table from scratch.

In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates

1 Answers1