Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

On Wed, 2024-02-07 at 15:12 -0500, James Coleman wrote:
> 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.

Well, "REPLICA IDENTITY NOTHING" is the same as "has no replica identity".
So is "REPLICA IDENTITY DEFAULT" if there is no primary key, or
"REPLICA IDENTITY USING INDEX ..." if the index is dropped.

See "pg_class": the column "relreplident" is not nullable.

Yours,
Laurenz Albe