Thread: pgsql: Add ALTER SUBSCRIPTION ... SKIP.
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(-)
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
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.gmail.com [2] - https://www.postgresql.org/message-id/f716f584-65d0-fe83-2e84-53426631739a%40enterprisedb.com -- With Regards, Amit Kapila.
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
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.