public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: James Coleman <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING
Date: Wed, 07 Feb 2024 21:22:52 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAaqYe_=7qFSqW7qavvhVy58mmzk1uSQ0RReRiUHyKO5znvr7g@mail.gmail.com>
References: <CAAaqYe_=7qFSqW7qavvhVy58mmzk1uSQ0RReRiUHyKO5znvr7g@mail.gmail.com>

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

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox