Hello,
We recently noticed some behavior that seems reasonable but also
surprised our engineers based on the docs.
If we have this setup:
create table items(i int);
insert into items(i) values (1);
create publication test_pub for all tables;
Then when we:
delete from items where i = 1;
we get:
ERROR: cannot delete from table "items" because it does not have a
replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using
ALTER TABLE.
Fair enough. But if we do this:
alter table items replica identity nothing;
because the docs [1] say that NOTHING means "Records no information
about the old row." We still get the same error when we try the DELETE
again.
The publication docs [2] say "A published table must have a replica
identity configured in order to be able to replicate UPDATE and DELETE
operations, so that appropriate rows to update or delete can be
identified on the subscriber side."
We interpreted the intersection of these two docs to imply that if you
explicitly configured NOTHING that the publication would simply not
log anything about the original row. Part of the confusion I think was
fed by reading "must have a replica identity set" as "have selected
one of the options via ALTER TABLE REPLICA IDENTITY" -- i.e., as
meaning that a setting has been configured rather than being about a
subset of those possible configuration values/a specific key existing
on the table.
I'm wondering if this might be a surprise to anyone else, and if so,
is there a minor docs tweak that might avoid the confusion?
Thanks,
James Coleman
1: https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
2: https://www.postgresql.org/docs/current/logical-replication-publication.html