Re: doc: clarify the limitation for logical replication when REPILICA IDENTITY is FULL - Mailing list pgsql-hackers

From Amit Kapila
Subject Re: doc: clarify the limitation for logical replication when REPILICA IDENTITY is FULL
Date
Msg-id CAA4eK1LoWxuUML6CvvEZC3uY3uikMn922RRZzD8j_NiitgnzPw@mail.gmail.com
Whole thread Raw
In response to Re: doc: clarify the limitation for logical replication when REPILICA IDENTITY is FULL  (Sergei Kornilov <sk@zsrv.org>)
Responses RE: doc: clarify the limitation for logical replication when REPILICA IDENTITY is FULL
List pgsql-hackers
On Mon, Jul 10, 2023 at 7:26 PM Sergei Kornilov <sk@zsrv.org> wrote:
>
> >> Is this restriction only for the subscriber?
> >>
> >> If we have not changed the replica identity and there is no primary key, then we forbid update and delete on the
publicationside (a fairly common usage error at the beginning of using publications). 
> >> If we have replica identity FULL (the table has such a column), then on the subscription side, update and delete
willbe performed. 
> >
> > In the above sentence, do you mean the publisher side?
>
> Yep, sorry.
>
> > But we will not be able to apply them on a subscription. Right?
> >
> > If your previous sentence talks about the publisher and this sentence
> > about the subscriber then what you are saying is correct. You can see
> > the example in the email [1].
>
> Thank you
>
> >> This is an important difference for real use, when the subscriber is not necessarily postgresql - for example,
debezium.
> >
> > Can you explain the difference and problem you are seeing? As per my
> > understanding, this is the behavior from the time logical replication
> > has been introduced.
>
> The difference is that if it's a subscriber-only restriction, then it won't automatically apply to anyone with a
non-postgresqlsubscriber. 
> But if suddenly this would be a limitation of the publisher - then it will automatically apply to everyone,
regardlessof which subscriber is used. 
> (and it's a completely different problem if the restriction affects the update/delete themselves, not only their
replication.Like as default replica identity on table without primary key, not in this case) 
>
> So, I suggest to mention subscriber explicitly:
>
> +     class of Btree, then <literal>UPDATE</literal> and <literal>DELETE</literal>
> -      operations cannot be replicated.
> +     operations cannot be applied on subscriber.
>
> Another example of difference:
> Debezium users sometimes ask to set identity to FULL to get access to old values:
https://stackoverflow.com/a/59820210/10983392
> However, identity FULL is described in the documentation as:
https://www.postgresql.org/docs/current/logical-replication-publication.html
>

After seeing this, I am thinking about whether we add this restriction
on the Subscription page [1] or Restrictions page [2] as proposed. Do
you others have any preference?

[1] - https://www.postgresql.org/docs/devel/logical-replication-subscription.html
[2] - https://www.postgresql.org/docs/devel/logical-replication-restrictions.html

--
With Regards,
Amit Kapila.



pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: doc: improve the restriction description of using indexes on REPLICA IDENTITY FULL table.
Next
From: "Drouvot, Bertrand"
Date:
Subject: Re: Autogenerate some wait events code and documentation