pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Add ALTER SUBSCRIPTION ... SKIP.
9+ messages / 6 participants
[nested] [flat]

* pgsql: Add ALTER SUBSCRIPTION ... SKIP.
@ 2022-03-22 01:56 Amit Kapila <akapila@postgresql.org>
  2022-03-22 02:06 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Tom Lane <tgl@sss.pgh.pa.us>
  2022-03-22 23:59 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Andres Freund <andres@anarazel.de>
  2022-09-04 08:18 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 3 replies; 9+ messages in thread

From: Amit Kapila @ 2022-03-22 01:56 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Add ALTER SUBSCRIPTION ... SKIP.

This feature allows skipping the transaction on subscriber nodes.

If incoming change violates any constraint, logical replication stops
until it's resolved. Currently, users need to either manually resolve the
conflict by updating a subscriber-side database or by using function
pg_replication_origin_advance() to skip the conflicting transaction. This
commit introduces a simpler way to skip the conflicting transactions.

The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),
which allows the apply worker to skip the transaction finished at
specified LSN. The apply worker skips all data modification changes within
the transaction.

Author: Masahiko Sawada
Reviewed-by: Takamichi Osumi, Hou Zhijie, Peter Eisentraut, Amit Kapila, Shi Yu, Vignesh C, Greg Nancarrow, Haiying Tang, Euler Taveira
Discussion: https://postgr.es/m/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/208c5d65bbd60e33e272964578cb74182ac726a8

Modified Files
--------------
doc/src/sgml/catalogs.sgml                      |  10 +
doc/src/sgml/logical-replication.sgml           |  27 +--
doc/src/sgml/ref/alter_subscription.sgml        |  42 +++++
src/backend/catalog/pg_subscription.c           |   1 +
src/backend/catalog/system_views.sql            |   2 +-
src/backend/commands/subscriptioncmds.c         |  73 ++++++++
src/backend/parser/gram.y                       |   9 +
src/backend/replication/logical/worker.c        | 233 +++++++++++++++++++++++-
src/bin/pg_dump/pg_dump.c                       |   4 +
src/bin/psql/describe.c                         |   8 +-
src/bin/psql/tab-complete.c                     |   5 +-
src/include/catalog/catversion.h                |   2 +-
src/include/catalog/pg_subscription.h           |   5 +
src/include/nodes/parsenodes.h                  |   3 +-
src/test/regress/expected/subscription.out      | 126 +++++++------
src/test/regress/sql/subscription.sql           |  11 ++
src/test/subscription/t/029_disable_on_error.pl |  94 ----------
src/test/subscription/t/029_on_error.pl         | 183 +++++++++++++++++++
18 files changed, 665 insertions(+), 173 deletions(-)



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
@ 2022-03-22 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-03-22 02:53   ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2022-03-22 02:06 UTC (permalink / raw)
  To: Amit Kapila <akapila@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

Amit Kapila <akapila@postgresql.org> writes:
> The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),
> which allows the apply worker to skip the transaction finished at
> specified LSN. The apply worker skips all data modification changes within
> the transaction.

Hmm ... this seems like a really poor choice of syntax.
I would expect ALTER to be used for changes of persistent
object properties, which surely this is not?

An alternative perhaps could be to invoke the operation
via a function.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
  2022-03-22 02:06 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-03-22 02:53   ` Amit Kapila <amit.kapila16@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Amit Kapila @ 2022-03-22 02:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Amit Kapila <akapila@postgresql.org>; pgsql-committers <pgsql-committers@lists.postgresql.org>

On Tue, Mar 22, 2022 at 7:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Amit Kapila <akapila@postgresql.org> writes:
> > The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),
> > which allows the apply worker to skip the transaction finished at
> > specified LSN. The apply worker skips all data modification changes within
> > the transaction.
>
> Hmm ... this seems like a really poor choice of syntax.
> I would expect ALTER to be used for changes of persistent
> object properties, which surely this is not?
>

We have discussed this syntax and discussed the point that this is
different from other properties of subscription like slot_name, binary
etc. and that is why we used SKIP for it rather than the usual way by
using SET [1][2]. There could also be other such options in future
like XID or other attributes, so we thought it would be easier to
extend it.

> An alternative perhaps could be to invoke the operation
> via a function.
>

I agree that is another alternative but could be inconvenient if there
are multiple such functions. We already have one
pg_replication_origin_advance().

[1] - https://www.postgresql.org/message-id/CAA4eK1KD_C_0LSxaYB0UbG59VOgjf4mXBeSYbVWCLXAnnuqnPw%40mail.gma...
[2] - https://www.postgresql.org/message-id/f716f584-65d0-fe83-2e84-53426631739a%40enterprisedb.com

-- 
With Regards,
Amit Kapila.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
@ 2022-03-22 23:59 ` Andres Freund <andres@anarazel.de>
  2022-03-23 01:56   ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Andres Freund @ 2022-03-22 23:59 UTC (permalink / raw)
  To: Amit Kapila <akapila@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

Hi,

On 2022-03-22 01:56:03 +0000, Amit Kapila wrote:
> Add ALTER SUBSCRIPTION ... SKIP.
> 
> This feature allows skipping the transaction on subscriber nodes.
> 
> If incoming change violates any constraint, logical replication stops
> until it's resolved. Currently, users need to either manually resolve the
> conflict by updating a subscriber-side database or by using function
> pg_replication_origin_advance() to skip the conflicting transaction. This
> commit introduces a simpler way to skip the conflicting transactions.
> 
> The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),
> which allows the apply worker to skip the transaction finished at
> specified LSN. The apply worker skips all data modification changes within
> the transaction.

This was missing an include of xlogdefs.h in pg_subscription.h, thus failing
in headerscheck. See e.g.
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2022-03-22%2022%3A22%3A05

I've pushed the trivial fix for that. I'll propose adding headerscheck to CI /
cfbot.

Greetings,

Andres Freund





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
  2022-03-22 23:59 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Andres Freund <andres@anarazel.de>
@ 2022-03-23 01:56   ` Amit Kapila <amit.kapila16@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Amit Kapila @ 2022-03-23 01:56 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: Amit Kapila <akapila@postgresql.org>; pgsql-committers <pgsql-committers@lists.postgresql.org>

On Wed, Mar 23, 2022 at 5:29 AM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2022-03-22 01:56:03 +0000, Amit Kapila wrote:
> > Add ALTER SUBSCRIPTION ... SKIP.
> >
> > This feature allows skipping the transaction on subscriber nodes.
> >
> > If incoming change violates any constraint, logical replication stops
> > until it's resolved. Currently, users need to either manually resolve the
> > conflict by updating a subscriber-side database or by using function
> > pg_replication_origin_advance() to skip the conflicting transaction. This
> > commit introduces a simpler way to skip the conflicting transactions.
> >
> > The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),
> > which allows the apply worker to skip the transaction finished at
> > specified LSN. The apply worker skips all data modification changes within
> > the transaction.
>
> This was missing an include of xlogdefs.h in pg_subscription.h, thus failing
> in headerscheck. See e.g.
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2022-03-22%2022%3A22%3A05
>
> I've pushed the trivial fix for that. I'll propose adding headerscheck to CI /
> cfbot.
>

Thanks.

-- 
With Regards,
Amit Kapila.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
@ 2022-09-04 08:18 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  2022-09-04 11:38   ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Alvaro Herrera @ 2022-09-04 08:18 UTC (permalink / raw)
  To: Amit Kapila <akapila@postgresql.org>; Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org

On 2022-Mar-22, Amit Kapila wrote:

> Add ALTER SUBSCRIPTION ... SKIP.

There are two messages here that seem oddly worded.

msgid "start skipping logical replication transaction finished at %X/%X"
msgid "done skipping logical replication transaction finished at %X/%X"

Two complaints here.  First, the phrases "start / finished" and "done /
finished" look very strange.  It took me a while to realize that
"finished" refers to the LSN, not to the skipping operation.  Do we ever
talk about a transaction "finished at XYZ" as opposed to a transaction
whose LSN is XYZ?  (This became particularly strange when I realized
that the LSN might come from a PREPARE.)

Second, "logical replication transaction".  Is it not a regular
transaction that we happen to be processing via logical replication?

I think they should say something like

"logical replication starts skipping transaction with LSN %X/%X"
"logical replication completed skipping transaction with LSN %X/%X"

Other ideas?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
  2022-09-04 08:18 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-09-04 11:38   ` Amit Kapila <amit.kapila16@gmail.com>
  2022-09-06 14:10     ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Masahiko Sawada <sawada.mshk@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Amit Kapila @ 2022-09-04 11:38 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Amit Kapila <akapila@postgresql.org>; Masahiko Sawada <sawada.mshk@gmail.com>; pgsql-hackers@lists.postgresql.org

On Sun, Sep 4, 2022 at 1:48 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2022-Mar-22, Amit Kapila wrote:
>
> > Add ALTER SUBSCRIPTION ... SKIP.
>
> There are two messages here that seem oddly worded.
>
> msgid "start skipping logical replication transaction finished at %X/%X"
> msgid "done skipping logical replication transaction finished at %X/%X"
>
> Two complaints here.  First, the phrases "start / finished" and "done /
> finished" look very strange.  It took me a while to realize that
> "finished" refers to the LSN, not to the skipping operation.  Do we ever
> talk about a transaction "finished at XYZ" as opposed to a transaction
> whose LSN is XYZ?  (This became particularly strange when I realized
> that the LSN might come from a PREPARE.)
>

The reason to add "finished at ..." was to be explicit about whether
it is a starting LSN or an end LSN of a transaction. We do have such
differentiation in ReorderBufferTXN (first_lsn ... end_lsn).

> Second, "logical replication transaction".  Is it not a regular
> transaction that we happen to be processing via logical replication?
>
> I think they should say something like
>
> "logical replication starts skipping transaction with LSN %X/%X"
> "logical replication completed skipping transaction with LSN %X/%X"
>

This looks better to me. If you find the above argument to
differentiate between the start and end LSN convincing then we can
think of replacing "with" in the above messages with "finished at". I
see your point related to using "finished at" for PREPARE may not be a
good idea but I don't have better ideas for the same.

-- 
With Regards,
Amit Kapila.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
  2022-09-04 08:18 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Alvaro Herrera <alvherre@alvh.no-ip.org>
  2022-09-04 11:38   ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
@ 2022-09-06 14:10     ` Masahiko Sawada <sawada.mshk@gmail.com>
  2022-09-07 03:46       ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Masahiko Sawada @ 2022-09-06 14:10 UTC (permalink / raw)
  To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Amit Kapila <akapila@postgresql.org>; pgsql-hackers@lists.postgresql.org

On Sun, Sep 4, 2022 at 8:39 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Sun, Sep 4, 2022 at 1:48 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >
> > On 2022-Mar-22, Amit Kapila wrote:
> >
> > > Add ALTER SUBSCRIPTION ... SKIP.
> >
> > There are two messages here that seem oddly worded.
> >
> > msgid "start skipping logical replication transaction finished at %X/%X"
> > msgid "done skipping logical replication transaction finished at %X/%X"
> >
> > Two complaints here.  First, the phrases "start / finished" and "done /
> > finished" look very strange.  It took me a while to realize that
> > "finished" refers to the LSN, not to the skipping operation.  Do we ever
> > talk about a transaction "finished at XYZ" as opposed to a transaction
> > whose LSN is XYZ?  (This became particularly strange when I realized
> > that the LSN might come from a PREPARE.)
> >
>
> The reason to add "finished at ..." was to be explicit about whether
> it is a starting LSN or an end LSN of a transaction. We do have such
> differentiation in ReorderBufferTXN (first_lsn ... end_lsn).
>
> > Second, "logical replication transaction".  Is it not a regular
> > transaction that we happen to be processing via logical replication?
> >
> > I think they should say something like
> >
> > "logical replication starts skipping transaction with LSN %X/%X"
> > "logical replication completed skipping transaction with LSN %X/%X"
> >
>
> This looks better to me.

+1

> If you find the above argument to
> differentiate between the start and end LSN convincing then we can
> think of replacing "with" in the above messages with "finished at". I
> see your point related to using "finished at" for PREPARE may not be a
> good idea but I don't have better ideas for the same.

Given that the user normally doesn't need to be aware of the
difference between start LSN and end LSN in the context of using this
feature, I think we can use "with LSN %X/%X".

Regards,

-- 
Masahiko Sawada





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
  2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
  2022-09-04 08:18 ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Alvaro Herrera <alvherre@alvh.no-ip.org>
  2022-09-04 11:38   ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <amit.kapila16@gmail.com>
  2022-09-06 14:10     ` Re: pgsql: Add ALTER SUBSCRIPTION ... SKIP. Masahiko Sawada <sawada.mshk@gmail.com>
@ 2022-09-07 03:46       ` Amit Kapila <amit.kapila16@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Amit Kapila @ 2022-09-07 03:46 UTC (permalink / raw)
  To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Amit Kapila <akapila@postgresql.org>; pgsql-hackers@lists.postgresql.org

On Tue, Sep 6, 2022 at 7:40 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> > > I think they should say something like
> > >
> > > "logical replication starts skipping transaction with LSN %X/%X"
> > > "logical replication completed skipping transaction with LSN %X/%X"
> > >
> >
> > This looks better to me.
>
> +1
>
> > If you find the above argument to
> > differentiate between the start and end LSN convincing then we can
> > think of replacing "with" in the above messages with "finished at". I
> > see your point related to using "finished at" for PREPARE may not be a
> > good idea but I don't have better ideas for the same.
>
> Given that the user normally doesn't need to be aware of the
> difference between start LSN and end LSN in the context of using this
> feature, I think we can use "with LSN %X/%X".
>

Fair enough.

Alvaro, would you like to push your proposed change? Otherwise, I am
happy to take care of this.

-- 
With Regards,
Amit Kapila.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2022-09-07 03:46 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-22 01:56 pgsql: Add ALTER SUBSCRIPTION ... SKIP. Amit Kapila <akapila@postgresql.org>
2022-03-22 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-22 02:53   ` Amit Kapila <amit.kapila16@gmail.com>
2022-03-22 23:59 ` Andres Freund <andres@anarazel.de>
2022-03-23 01:56   ` Amit Kapila <amit.kapila16@gmail.com>
2022-09-04 08:18 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-09-04 11:38   ` Amit Kapila <amit.kapila16@gmail.com>
2022-09-06 14:10     ` Masahiko Sawada <sawada.mshk@gmail.com>
2022-09-07 03:46       ` Amit Kapila <amit.kapila16@gmail.com>

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