Re: Disallow UPDATE/DELETE on table with unpublished generated column as REPLICA IDENTITY

On 2024-Nov-12, Amit Kapila wrote:

> On Tue, Nov 12, 2024 at 2:15 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:

> > So, another option is to do nothing for stable branches.
>
> Fair enough. The other point in favor of that option is that nobody
> has reported this problem yet but my guess is that they would have
> probably not used such a combination at least with pgoutput plugin
> otherwise, they would have faced the ERRORs on the subscriber. So, we
> can do this only for HEAD and decide on the fix if anyone ever reports
> this problem.

Right.

> > Well, maybe we can restrict the change of REPLICA IDENTITY if the table
> > is already in a pgoutput publication?
>
> What about the PRIMARY KEY case as shared in my later email? Even
> apart from that the plugin is decided via slot, so we won't be able to
> detect from table<->publication relationship.

I responded to both emails together, my response is what you quoted
below:

> > On 2024-Nov-12, Amit Kapila wrote:
> >
> > > Also, another point against restricting defining REPLICA IDENTITY on
> > > generated columns is that we do allow generated columns to be PRIMARY
> > > KEY which is a DEFAULT for REPLICA IDENTITY, so that also needs to be
> > > restricted. That won't be a good idea.
> >
> > Oh, that's a good point too.

(I was acknowledging this as a problem case.)

> > It's not clear to me why doesn't pgoutput cope with generated columns in
> > replica identities. Maybe that can be reconsidered?
>
> In stable branches, we intentionally skip publishing generated columns
> as we assumed that the subscriber side also had a generated column.
> So, sending it would be a waste of network bandwidth. OTOH, when one
> tries to replicate the changes to some other database that didn't have
> the generated columns concept, it would create a problem. So we
> developed a new feature for HEAD as part of commits 745217a051 and
> 7054186c4e which allows the publication of generated columns when
> explicitly specified by the users.

Ah, I think it's good then, we don't need to do anything further on
this. It's just not supported on earlier branches (and it doesn't work
with pgoutput, though it does with other plugins); and master has a
mechanism for it to work with any output plugin.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/