Hi, On Mon, Nov 16, 2026 at 03:46:25PM +0530, Amit Kapila wrote: > On Tue, Nov 10, 2026 at 7:44 PM Bertrand Drouvot > > For failover, the value should match exactly. > > > > For two_phase, the expected value depends on subtwophasestate: > > > > • DISABLED: the slot must have two_phase = false. > > • PENDING, before the tables are ready: it must remain false. > > • PENDING, once the tables are ready: either value can be accepted before > > enabling > > it and recording the state as ENABLED. This also makes the transition safe > > if the > > worker previously exited between those two operations. > > • ENABLED: the slot must have two_phase = true. > > > > Instead of PENDING state checks, how about checking mismatch in > begin_prepared kind of functions whether the subscription has reached > ENABLED state? I am asking this because if the slot is initially > created with two_phase = true and the subscription is in pending state > then the apply worker will start receiving prepared transactions and > will lead to problems described under "TWO_PHASE TRANSACTIONS" in > worker.c.
Thanks for looking at it! I think that could be too late. With parallel streaming, a parallel apply worker may already have executed the transaction's changes. So, in the attached, I went with checking the slot property after acquiring the slot, before decoding starts. A PENDING subscription requires two_phase to remain disabled until table synchronization is complete. Once ready, START_REPLICATION can enable it. The patches therefore add slot_failover and slot_two_phase pgoutput options to validate the current slot properties without changing them. For older publishers, the patches preserve the existing behavior and leave the user responsible for ensuring that the values match. I also considered querying pg_replication_slots before START_REPLICATION. That would provide useful best effort validation, but the slot could be changed or recreated between the query and its acquisition. I decided not to include it. What do you think? > If we want to proceed with this, then we should consider whether the > mismatch in 'failover' property should be an ERROR or a simple > 'WARNING' as that won't immediately lead to data inconsistency or such > issues. Yeah, a failover mismatch does not have the same immediate consequences as a two_phase mismatch, but I think an ERROR is still preferable. For example, it can prevent replication from resuming after promotion. Also, since the documentation requires the values to match, I don't think we should let replication continue with a mismatched configuration. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From a1f9fe26045296921f002fc8999ba4bc1ba9b8bb Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 17 Nov 2026 10:44:17 +0000 Subject: [PATCH v1 1/2] Detect mismatched subscription and slot failover states A subscription can use a manually managed slot whose failover property does not match its failover parameter, for example after changing its slot or connection, or recreating the slot. For PostgreSQL 20 and later publishers, pass the expected value to pgoutput and validate it after acquiring the slot, before decoding starts. Older publishers retain the existing behavior and rely on the documented requirement that users ensure the values match. Route START_REPLICATION failures through apply error handling so disable_on_error and apply_error_count work as expected. Document the behavior and test both mismatch directions. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/avMmtxMsgqKy4IQq@bdtpg --- doc/src/sgml/logicaldecoding.sgml | 11 ++ doc/src/sgml/monitoring.sgml | 8 +- doc/src/sgml/ref/alter_subscription.sgml | 8 + doc/src/sgml/ref/create_subscription.sgml | 8 + .../libpqwalreceiver/libpqwalreceiver.c | 8 + src/backend/replication/logical/worker.c | 82 +++++---- src/backend/replication/pgoutput/pgoutput.c | 26 +++ src/include/replication/logicalproto.h | 4 + src/include/replication/pgoutput.h | 2 + src/include/replication/walreceiver.h | 2 + src/test/subscription/meson.build | 1 + .../subscription/t/039_slot_properties.pl | 156 ++++++++++++++++++ 12 files changed, 284 insertions(+), 32 deletions(-) create mode 100644 src/test/subscription/t/039_slot_properties.pl diff --git a/doc/src/sgml/logicaldecoding.sgml b/doc/src/sgml/logicaldecoding.sgml index 6dc49108997..79397c7a3f1 100644 --- a/doc/src/sgml/logicaldecoding.sgml +++ b/doc/src/sgml/logicaldecoding.sgml @@ -740,6 +740,17 @@ DETAIL: Synchronization could lead to data loss, because the remote slot needs </listitem> </varlistentry> + <varlistentry id="pgoutput-options-slot-failover"> + <term><varname>slot_failover</varname> (<type>boolean</type>)</term> + <listitem> + <para> + Reports an error unless the selected replication slot's + <literal>failover</literal> property matches the specified value. + The slot property is checked only when this option is specified. + </para> + </listitem> + </varlistentry> + <varlistentry id="pgoutput-options-origin"> <term><varname>origin</varname> (<type>enum</type>)</term> <listitem> diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 445fbc9232c..15e01092d09 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2592,10 +2592,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>apply_error_count</structfield> <type>bigint</type> </para> <para> - Number of times an error occurred while applying changes. Note that any - conflict resulting in an apply error will be counted in both - <literal>apply_error_count</literal> and the corresponding conflict - count (e.g., <literal>confl_*</literal>). + Number of errors reported by the apply worker while starting replication + or applying changes. Note that any conflict resulting in an apply error + will be counted in both <literal>apply_error_count</literal> and the + corresponding conflict count (e.g., <literal>confl_*</literal>). </para></entry> </row> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index 545264e8a0a..e17516d0a65 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -327,6 +327,14 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < option is enabled. </para> + <para> + When the publisher is running + <productname>PostgreSQL</productname> 20 or later, the apply worker + reports an error when starting replication if the named slot's + <literal>failover</literal> property does not match the subscription + parameter. + </para> + <para> The <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>, <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>, and diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 92552e97009..1ee3b4d0f55 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -213,6 +213,14 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl option is disabled or could be disabled for sync even when the subscription's <literal>failover</literal> option is enabled. </para> + + <para> + When the publisher is running + <productname>PostgreSQL</productname> 20 or later, the apply worker + reports an error when starting replication if the named slot's + <literal>failover</literal> property does not match the subscription + parameter. + </para> </listitem> </varlistentry> </variablelist> diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c index dcc738f4e38..e581e60b9c3 100644 --- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c +++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c @@ -31,6 +31,7 @@ #include "miscadmin.h" #include "pgstat.h" #include "pqexpbuffer.h" +#include "replication/logicalproto.h" #include "replication/slotsync.h" #include "replication/walreceiver.h" #include "storage/latch.h" @@ -657,6 +658,13 @@ libpqrcv_startstreaming(WalReceiverConn *conn, PQserverVersion(conn->streamConn) >= 150000) appendStringInfoString(&cmd, ", two_phase 'on'"); + if (options->proto.logical.slot_failover_set && + PQserverVersion(conn->streamConn) >= + LOGICALREP_SLOT_PROPERTY_CHECK_SERVER_VERSION_NUM) + appendStringInfo(&cmd, ", slot_failover '%s'", + options->proto.logical.slot_failover ? + "true" : "false"); + if (options->proto.logical.origin && PQserverVersion(conn->streamConn) >= 160000) { diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 95044620e68..31f20703883 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -5675,6 +5675,8 @@ set_stream_options(WalRcvStreamOptions *options, } options->proto.logical.twophase = false; + options->proto.logical.slot_failover = false; + options->proto.logical.slot_failover_set = false; options->proto.logical.origin = pstrdup(MySubscription->origin); } @@ -5693,6 +5695,36 @@ cleanup_subxact_info(void) subxact_data.nsubxacts_max = 0; } +/* + * Handle an error encountered while starting or running apply. + * + * This is only for use in error handler code. + */ +static void +handle_apply_error(void) +{ + /* + * Reset the origin state to prevent the advancement of origin progress if + * we fail to apply. Otherwise, this will result in transaction loss as + * that transaction won't be sent again by the server. + */ + replorigin_xact_clear(true); + + if (MySubscription->disableonerr) + DisableSubscriptionAndExit(); + else + { + /* + * Report the worker failed while applying changes. Abort the current + * transaction so that the stats message is sent in an idle state. + */ + AbortOutOfAnyTransaction(); + pgstat_report_subscription_error(MySubscription->oid); + + PG_RE_THROW(); + } +} + /* * Common function to run the apply loop with error handling. Disable the * subscription, if necessary. @@ -5709,28 +5741,7 @@ start_apply(XLogRecPtr origin_startpos) } PG_CATCH(); { - /* - * Reset the origin state to prevent the advancement of origin - * progress if we fail to apply. Otherwise, this will result in - * transaction loss as that transaction won't be sent again by the - * server. - */ - replorigin_xact_clear(true); - - if (MySubscription->disableonerr) - DisableSubscriptionAndExit(); - else - { - /* - * Report the worker failed while applying changes. Abort the - * current transaction so that the stats message is sent in an - * idle state. - */ - AbortOutOfAnyTransaction(); - pgstat_report_subscription_error(MySubscription->oid); - - PG_RE_THROW(); - } + handle_apply_error(); } PG_END_TRY(); } @@ -5751,6 +5762,7 @@ run_apply_worker(void) TimeLineID startpointTLI; char *err; bool must_use_password; + bool enable_twophase; slotname = MySubscription->slotname; @@ -5815,6 +5827,13 @@ run_apply_worker(void) set_stream_options(&options, slotname, &origin_startpos); + enable_twophase = + MySubscription->twophasestate == LOGICALREP_TWOPHASE_STATE_PENDING && + AllTablesyncsReady(); + + options.proto.logical.slot_failover = MySubscription->failover; + options.proto.logical.slot_failover_set = true; + /* * Even when the two_phase mode is requested by the user, it remains as * the tri-state PENDING until all tablesyncs have reached READY state. @@ -5824,13 +5843,24 @@ run_apply_worker(void) * PENDING, which allows ALTER SUBSCRIPTION ... REFRESH PUBLICATION to * work. */ - if (MySubscription->twophasestate == LOGICALREP_TWOPHASE_STATE_PENDING && - AllTablesyncsReady()) + if (enable_twophase) { /* Start streaming with two_phase enabled */ options.proto.logical.twophase = true; + } + + PG_TRY(); + { walrcv_startstreaming(LogRepWorkerWalRcvConn, &options); + } + PG_CATCH(); + { + handle_apply_error(); + } + PG_END_TRY(); + if (enable_twophase) + { StartTransactionCommand(); /* @@ -5844,10 +5874,6 @@ run_apply_worker(void) PopActiveSnapshot(); CommitTransactionCommand(); } - else - { - walrcv_startstreaming(LogRepWorkerWalRcvConn, &options); - } ereport(DEBUG1, (errmsg_internal("logical replication apply worker for subscription \"%s\" two_phase is %s", diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 57b3ccbed60..6258b96b11d 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -296,6 +296,7 @@ parse_output_parameters(List *options, PGOutputData *data) bool messages_option_given = false; bool streaming_given = false; bool two_phase_option_given = false; + bool slot_failover_option_given = false; bool origin_option_given = false; /* Initialize optional parameters to defaults */ @@ -303,6 +304,8 @@ parse_output_parameters(List *options, PGOutputData *data) data->streaming = LOGICALREP_STREAM_OFF; data->messages = false; data->two_phase = false; + data->slot_failover = false; + data->slot_failover_set = false; data->publish_no_origin = false; foreach(lc, options) @@ -396,6 +399,17 @@ parse_output_parameters(List *options, PGOutputData *data) data->two_phase = defGetBoolean(defel); } + else if (strcmp(defel->defname, "slot_failover") == 0) + { + if (slot_failover_option_given) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + slot_failover_option_given = true; + + data->slot_failover = defGetBoolean(defel); + data->slot_failover_set = true; + } else if (strcmp(defel->defname, "origin") == 0) { char *origin; @@ -550,6 +564,18 @@ pgoutput_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt, else ctx->twophase_opt_given = true; + if (data->slot_failover_set && + data->slot_failover != MyReplicationSlot->data.failover) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("replication slot \"%s\" has failover %s", + NameStr(MyReplicationSlot->data.name), + MyReplicationSlot->data.failover ? + "enabled" : "disabled"), + errdetail("The expected failover setting is %s.", + data->slot_failover ? + "enabled" : "disabled"))); + /* Init publication state. */ data->publications = NIL; publications_valid = false; diff --git a/src/include/replication/logicalproto.h b/src/include/replication/logicalproto.h index 058a955e20c..c1aaaf6789a 100644 --- a/src/include/replication/logicalproto.h +++ b/src/include/replication/logicalproto.h @@ -36,6 +36,9 @@ * LOGICALREP_PROTO_STREAM_PARALLEL_VERSION_NUM is the minimum protocol version * where we support applying large streaming transactions in parallel. * Introduced in PG16. + * + * LOGICALREP_SLOT_PROPERTY_CHECK_SERVER_VERSION_NUM is the minimum server + * version that can check slot properties during START_REPLICATION. */ #define LOGICALREP_PROTO_MIN_VERSION_NUM 1 #define LOGICALREP_PROTO_VERSION_NUM 1 @@ -43,6 +46,7 @@ #define LOGICALREP_PROTO_TWOPHASE_VERSION_NUM 3 #define LOGICALREP_PROTO_STREAM_PARALLEL_VERSION_NUM 4 #define LOGICALREP_PROTO_MAX_VERSION_NUM LOGICALREP_PROTO_STREAM_PARALLEL_VERSION_NUM +#define LOGICALREP_SLOT_PROPERTY_CHECK_SERVER_VERSION_NUM 200000 /* * Logical message types diff --git a/src/include/replication/pgoutput.h b/src/include/replication/pgoutput.h index 774ebe53fa4..41d2be1be3c 100644 --- a/src/include/replication/pgoutput.h +++ b/src/include/replication/pgoutput.h @@ -33,6 +33,8 @@ typedef struct PGOutputData char streaming; bool messages; bool two_phase; + bool slot_failover; + bool slot_failover_set; bool publish_no_origin; } PGOutputData; diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h index 5cfe90f9989..36afc96553d 100644 --- a/src/include/replication/walreceiver.h +++ b/src/include/replication/walreceiver.h @@ -186,6 +186,8 @@ typedef struct char *streaming_str; /* Streaming of large transactions */ bool twophase; /* Streaming of two-phase transactions at * prepare time */ + bool slot_failover; /* Expected slot failover state */ + bool slot_failover_set; /* Check slot failover state */ char *origin; /* Only publish data originating from the * specified origin */ } logical; diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build index e71e95c6297..8b15df814c8 100644 --- a/src/test/subscription/meson.build +++ b/src/test/subscription/meson.build @@ -48,6 +48,7 @@ tests += { 't/036_sequences.pl', 't/037_except.pl', 't/038_walsnd_shutdown_timeout.pl', + 't/039_slot_properties.pl', 't/100_bugs.pl', ], }, diff --git a/src/test/subscription/t/039_slot_properties.pl b/src/test/subscription/t/039_slot_properties.pl new file mode 100644 index 00000000000..b771b5b493e --- /dev/null +++ b/src/test/subscription/t/039_slot_properties.pl @@ -0,0 +1,156 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test validation of subscription and replication slot properties. +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $publisher = PostgreSQL::Test::Cluster->new('publisher'); +$publisher->init(allows_streaming => 'logical'); +$publisher->start; + +my $subscriber = PostgreSQL::Test::Cluster->new('subscriber'); +$subscriber->init; +$subscriber->start; + +my $publisher_connstr = $publisher->connstr . ' dbname=postgres'; + +# Recreate a logical slot with the requested failover property. +sub recreate_slot +{ + my ($slot_name, $failover) = @_; + + $publisher->safe_psql( + 'postgres', qq[ + SELECT pg_drop_replication_slot('$slot_name') + WHERE EXISTS ( + SELECT FROM pg_replication_slots WHERE slot_name = '$slot_name'); + SELECT pg_create_logical_replication_slot( + '$slot_name', 'pgoutput', false, false, $failover); + ]); +} + +# Wait until the apply worker has released a slot. +sub wait_for_slot_inactive +{ + my ($slot_name) = @_; + + $publisher->poll_query_until( + 'postgres', qq[ + SELECT active_pid IS NULL + FROM pg_replication_slots + WHERE slot_name = '$slot_name'; + ]) or die "timed out waiting for slot $slot_name to become inactive"; +} + +$publisher->safe_psql( + 'postgres', q[ + CREATE TABLE property_tab (a integer PRIMARY KEY, payload text); + CREATE PUBLICATION property_pub FOR TABLE property_tab; +]); + +$subscriber->safe_psql( + 'postgres', q[ + CREATE TABLE property_tab (a integer PRIMARY KEY, payload text); +]); + +# A failover-enabled subscription requires a failover-enabled slot. +recreate_slot('failover_slot', 'true'); +$subscriber->safe_psql( + 'postgres', qq[ + CREATE SUBSCRIPTION failover_sub + CONNECTION '$publisher_connstr application_name=failover_sub' + PUBLICATION property_pub + WITH (slot_name = 'failover_slot', create_slot = false, + copy_data = false, enabled = false, failover = true, + disable_on_error = true); + ALTER SUBSCRIPTION failover_sub ENABLE; +]); + +$publisher->poll_query_until( + 'postgres', q[ + SELECT active AND failover + FROM pg_replication_slots + WHERE slot_name = 'failover_slot'; +]) or die 'timed out waiting for the failover slot to become active'; + +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION failover_sub DISABLE'); +wait_for_slot_inactive('failover_slot'); +recreate_slot('failover_slot', 'false'); + +my $subscriber_log_offset = -s $subscriber->logfile; +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION failover_sub ENABLE'); +$subscriber->wait_for_log( + qr/replication slot "failover_slot" has failover disabled/, + $subscriber_log_offset); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT NOT subenabled + FROM pg_subscription + WHERE subname = 'failover_sub'; +]) or die 'timed out waiting for failover_sub to be disabled'; + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT apply_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = 'failover_sub'; +]) or die 'timed out waiting for the apply error to be counted'; + +wait_for_slot_inactive('failover_slot'); +$subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION failover_sub'); + +# A subscription without failover enabled must reject a failover-enabled slot. +recreate_slot('extra_failover_slot', 'true'); +$subscriber->safe_psql( + 'postgres', qq[ + CREATE SUBSCRIPTION extra_failover_sub + CONNECTION '$publisher_connstr application_name=extra_failover_sub' + PUBLICATION property_pub + WITH (slot_name = 'extra_failover_slot', create_slot = false, + copy_data = false, enabled = false, failover = false, + disable_on_error = true); +]); + +$subscriber_log_offset = -s $subscriber->logfile; +$subscriber->safe_psql('postgres', + 'ALTER SUBSCRIPTION extra_failover_sub ENABLE'); +$subscriber->wait_for_log( + qr/replication slot "extra_failover_slot" has failover enabled/, + $subscriber_log_offset); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT NOT subenabled + FROM pg_subscription + WHERE subname = 'extra_failover_sub'; +]) or die 'timed out waiting for extra_failover_sub to be disabled'; + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT apply_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = 'extra_failover_sub'; +]) or die 'timed out waiting for the apply error to be counted'; + +is( $subscriber->safe_psql( + 'postgres', q[ + SELECT NOT subenabled AND apply_error_count > 0 + FROM pg_subscription + JOIN pg_stat_subscription_stats + ON subid = pg_subscription.oid + WHERE pg_subscription.subname = 'extra_failover_sub'; + ]), + 't', + 'failover mismatch disabled the subscription and counted an apply error'); + +wait_for_slot_inactive('extra_failover_slot'); +$subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION extra_failover_sub'); + +$subscriber->stop; +$publisher->stop; + +done_testing(); -- 2.47.3
>From 2294b256c7b7c70697eb617b7f3cdae2be201d3c Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 17 Nov 2026 10:44:25 +0000 Subject: [PATCH v1 2/2] Detect mismatched subscription and slot two_phase states A subscription can use a manually managed slot whose two_phase property does not match its state, for example after changing its slot or connection, or recreating the slot. For PostgreSQL 20 and later publishers, pass the expected state to pgoutput and validate it after acquiring the slot, before decoding starts. A pending subscription requires two_phase to remain disabled until table synchronization is complete, after which START_REPLICATION may enable it. Older publishers retain the existing behavior and rely on the documented requirement that the values match. Checking before decoding avoids detecting a mismatch only after parallel streaming has produced nontransactional effects on the subscriber. Use the startup error handling so disable_on_error and apply_error_count work as expected. Document the behavior and test both mismatch directions and the pending state. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/avMmtxMsgqKy4IQq@bdtpg --- doc/src/sgml/logicaldecoding.sgml | 11 ++ doc/src/sgml/ref/alter_subscription.sgml | 30 ++- doc/src/sgml/ref/create_subscription.sgml | 16 ++ .../libpqwalreceiver/libpqwalreceiver.c | 7 + src/backend/replication/logical/worker.c | 14 ++ src/backend/replication/pgoutput/pgoutput.c | 27 +++ src/include/replication/pgoutput.h | 2 + src/include/replication/walreceiver.h | 2 + .../subscription/t/039_slot_properties.pl | 183 +++++++++++++++++- 9 files changed, 278 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/logicaldecoding.sgml b/doc/src/sgml/logicaldecoding.sgml index 79397c7a3f1..780ea092cc9 100644 --- a/doc/src/sgml/logicaldecoding.sgml +++ b/doc/src/sgml/logicaldecoding.sgml @@ -740,6 +740,17 @@ DETAIL: Synchronization could lead to data loss, because the remote slot needs </listitem> </varlistentry> + <varlistentry id="pgoutput-options-slot-two-phase"> + <term><varname>slot_two_phase</varname> (<type>boolean</type>)</term> + <listitem> + <para> + Reports an error unless the selected replication slot's + <literal>two_phase</literal> property matches the specified value. + The slot property is checked only when this option is specified. + </para> + </listitem> + </varlistentry> + <varlistentry id="pgoutput-options-slot-failover"> <term><varname>slot_failover</varname> (<type>boolean</type>)</term> <listitem> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index e17516d0a65..087094435a2 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -310,13 +310,16 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <para> When altering the <link linkend="sql-createsubscription-params-with-slot-name"><literal>slot_name</literal></link>, - the <literal>failover</literal> and <literal>two_phase</literal> property - values of the named slot may differ from the counterpart + the named slot's <literal>failover</literal> property may differ from the + counterpart <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link> - and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link> - parameters specified in the subscription. When creating the slot, ensure - the slot properties <literal>failover</literal> and <literal>two_phase</literal> - match their counterpart parameters of the subscription. + parameter, and its <literal>two_phase</literal> property may differ from + the state required by the subscription's + <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link> + parameter. When creating the slot, ensure the + <literal>failover</literal> property matches the corresponding parameter + and the <literal>two_phase</literal> property matches the required + subscription state. Otherwise, the slot on the publisher may behave differently from what these subscription options say: for example, the slot on the publisher could either be synced to the standbys even when the subscription's @@ -327,12 +330,27 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < option is enabled. </para> + <para> + While the subscription's internal two-phase state is + <quote>pending</quote> and not all tables are synchronized and ready, the + required slot <literal>two_phase</literal> property is + <literal>false</literal>, even if the subscription's + <literal>two_phase</literal> parameter is <literal>true</literal>. This + also applies to a subscription with no tables, which remains in the + <quote>pending</quote> state. Once all tables are ready, + <command>START_REPLICATION</command> can enable the slot property, after + which the apply worker updates the subscription state. + </para> + <para> When the publisher is running <productname>PostgreSQL</productname> 20 or later, the apply worker reports an error when starting replication if the named slot's <literal>failover</literal> property does not match the subscription parameter. + With such a publisher, the apply worker also reports an error when + starting replication if the named slot's <literal>two_phase</literal> + property does not match the state required by the subscription. </para> <para> diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 1ee3b4d0f55..23a080e7231 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -221,6 +221,22 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <literal>failover</literal> property does not match the subscription parameter. </para> + + <para> + The named slot's <literal>two_phase</literal> property may likewise + differ from the state required by the subscription. Ensure these + values match as well. While the subscription's internal two-phase + state is <quote>pending</quote> and not all tables are synchronized + and ready, the required slot property is <literal>false</literal>, + even if the subscription's <literal>two_phase</literal> parameter is + <literal>true</literal>. This also applies to a subscription with no + tables, which remains in the <quote>pending</quote> state. Once all + tables are ready, <command>START_REPLICATION</command> can enable the + slot property, after which the apply worker updates the subscription + state. With a <productname>PostgreSQL</productname> 20 or later + publisher, the apply worker also reports an error when starting + replication if they do not match. + </para> </listitem> </varlistentry> </variablelist> diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c index e581e60b9c3..eed1a164f83 100644 --- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c +++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c @@ -658,6 +658,13 @@ libpqrcv_startstreaming(WalReceiverConn *conn, PQserverVersion(conn->streamConn) >= 150000) appendStringInfoString(&cmd, ", two_phase 'on'"); + if (options->proto.logical.slot_twophase_set && + PQserverVersion(conn->streamConn) >= + LOGICALREP_SLOT_PROPERTY_CHECK_SERVER_VERSION_NUM) + appendStringInfo(&cmd, ", slot_two_phase '%s'", + options->proto.logical.slot_twophase ? + "true" : "false"); + if (options->proto.logical.slot_failover_set && PQserverVersion(conn->streamConn) >= LOGICALREP_SLOT_PROPERTY_CHECK_SERVER_VERSION_NUM) diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 31f20703883..322107b79ae 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -5675,6 +5675,8 @@ set_stream_options(WalRcvStreamOptions *options, } options->proto.logical.twophase = false; + options->proto.logical.slot_twophase = false; + options->proto.logical.slot_twophase_set = false; options->proto.logical.slot_failover = false; options->proto.logical.slot_failover_set = false; options->proto.logical.origin = pstrdup(MySubscription->origin); @@ -5831,6 +5833,18 @@ run_apply_worker(void) MySubscription->twophasestate == LOGICALREP_TWOPHASE_STATE_PENDING && AllTablesyncsReady(); + /* + * A pending subscription that is ready can enable two-phase decoding as + * part of START_REPLICATION. Otherwise, require the slot to match the + * subscription state before decoding starts. + */ + if (!enable_twophase) + { + options.proto.logical.slot_twophase = + MySubscription->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED; + options.proto.logical.slot_twophase_set = true; + } + options.proto.logical.slot_failover = MySubscription->failover; options.proto.logical.slot_failover_set = true; diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 6258b96b11d..e39e22cafa8 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -27,6 +27,7 @@ #include "replication/logicalproto.h" #include "replication/origin.h" #include "replication/pgoutput.h" +#include "replication/slot.h" #include "rewrite/rewriteHandler.h" #include "utils/builtins.h" #include "utils/inval.h" @@ -296,6 +297,7 @@ parse_output_parameters(List *options, PGOutputData *data) bool messages_option_given = false; bool streaming_given = false; bool two_phase_option_given = false; + bool slot_two_phase_option_given = false; bool slot_failover_option_given = false; bool origin_option_given = false; @@ -304,6 +306,8 @@ parse_output_parameters(List *options, PGOutputData *data) data->streaming = LOGICALREP_STREAM_OFF; data->messages = false; data->two_phase = false; + data->slot_two_phase = false; + data->slot_two_phase_set = false; data->slot_failover = false; data->slot_failover_set = false; data->publish_no_origin = false; @@ -399,6 +403,17 @@ parse_output_parameters(List *options, PGOutputData *data) data->two_phase = defGetBoolean(defel); } + else if (strcmp(defel->defname, "slot_two_phase") == 0) + { + if (slot_two_phase_option_given) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + slot_two_phase_option_given = true; + + data->slot_two_phase = defGetBoolean(defel); + data->slot_two_phase_set = true; + } else if (strcmp(defel->defname, "slot_failover") == 0) { if (slot_failover_option_given) @@ -564,6 +579,18 @@ pgoutput_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt, else ctx->twophase_opt_given = true; + if (data->slot_two_phase_set && + data->slot_two_phase != MyReplicationSlot->data.two_phase) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("replication slot \"%s\" has two_phase %s", + NameStr(MyReplicationSlot->data.name), + MyReplicationSlot->data.two_phase ? + "enabled" : "disabled"), + errdetail("The expected two_phase setting is %s.", + data->slot_two_phase ? + "enabled" : "disabled"))); + if (data->slot_failover_set && data->slot_failover != MyReplicationSlot->data.failover) ereport(ERROR, diff --git a/src/include/replication/pgoutput.h b/src/include/replication/pgoutput.h index 41d2be1be3c..0d0e8e8ecfb 100644 --- a/src/include/replication/pgoutput.h +++ b/src/include/replication/pgoutput.h @@ -33,6 +33,8 @@ typedef struct PGOutputData char streaming; bool messages; bool two_phase; + bool slot_two_phase; + bool slot_two_phase_set; bool slot_failover; bool slot_failover_set; bool publish_no_origin; diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h index 36afc96553d..6d3615b0c80 100644 --- a/src/include/replication/walreceiver.h +++ b/src/include/replication/walreceiver.h @@ -186,6 +186,8 @@ typedef struct char *streaming_str; /* Streaming of large transactions */ bool twophase; /* Streaming of two-phase transactions at * prepare time */ + bool slot_twophase; /* Expected slot two_phase state */ + bool slot_twophase_set; /* Check slot two_phase state */ bool slot_failover; /* Expected slot failover state */ bool slot_failover_set; /* Check slot failover state */ char *origin; /* Only publish data originating from the diff --git a/src/test/subscription/t/039_slot_properties.pl b/src/test/subscription/t/039_slot_properties.pl index b771b5b493e..9403996934c 100644 --- a/src/test/subscription/t/039_slot_properties.pl +++ b/src/test/subscription/t/039_slot_properties.pl @@ -9,18 +9,25 @@ use Test::More; my $publisher = PostgreSQL::Test::Cluster->new('publisher'); $publisher->init(allows_streaming => 'logical'); +$publisher->append_conf('postgresql.conf', + "max_prepared_transactions = 10\nlogical_decoding_work_mem = 64kB"); $publisher->start; my $subscriber = PostgreSQL::Test::Cluster->new('subscriber'); $subscriber->init; +$subscriber->append_conf('postgresql.conf', + "max_prepared_transactions = 10\n" + . "max_worker_processes = 20\n" + . "max_logical_replication_workers = 10\n" + . "max_parallel_apply_workers_per_subscription = 4"); $subscriber->start; my $publisher_connstr = $publisher->connstr . ' dbname=postgres'; -# Recreate a logical slot with the requested failover property. +# Recreate a logical slot with the requested properties. sub recreate_slot { - my ($slot_name, $failover) = @_; + my ($slot_name, $two_phase, $failover) = @_; $publisher->safe_psql( 'postgres', qq[ @@ -28,7 +35,7 @@ sub recreate_slot WHERE EXISTS ( SELECT FROM pg_replication_slots WHERE slot_name = '$slot_name'); SELECT pg_create_logical_replication_slot( - '$slot_name', 'pgoutput', false, false, $failover); + '$slot_name', 'pgoutput', false, $two_phase, $failover); ]); } @@ -53,11 +60,171 @@ $publisher->safe_psql( $subscriber->safe_psql( 'postgres', q[ - CREATE TABLE property_tab (a integer PRIMARY KEY, payload text); + CREATE SEQUENCE local_seq; + CREATE TABLE property_tab ( + a integer PRIMARY KEY, + payload text, + local_value bigint DEFAULT nextval('local_seq')); +]); + +my $subscriber_log_offset; + +# A pending two-phase subscription requires two-phase decoding to remain +# disabled until all table synchronization workers have finished. +recreate_slot('pending_slot', 'true', 'false'); +$subscriber->safe_psql( + 'postgres', qq[ + CREATE SUBSCRIPTION pending_sub + CONNECTION '$publisher_connstr application_name=pending_sub' + PUBLICATION property_pub + WITH (slot_name = 'pending_slot', create_slot = false, + copy_data = true, enabled = false, two_phase = true, + disable_on_error = true); +]); + +is( $subscriber->safe_psql( + 'postgres', q[ + SELECT subtwophasestate = 'p' AND EXISTS ( + SELECT FROM pg_subscription_rel + WHERE srsubid = pg_subscription.oid + AND srsubstate <> 'r') + FROM pg_subscription + WHERE subname = 'pending_sub'; + ]), + 't', + 'two-phase subscription is pending while table synchronization is incomplete' +); + +$subscriber_log_offset = -s $subscriber->logfile; +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION pending_sub ENABLE'); +$subscriber->wait_for_log( + qr/replication slot "pending_slot" has two_phase enabled/, + $subscriber_log_offset); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT NOT subenabled + FROM pg_subscription + WHERE subname = 'pending_sub'; +]) or die 'timed out waiting for pending_sub to be disabled'; + +is( $subscriber->safe_psql( + 'postgres', q[ + SELECT subtwophasestate = 'p' AND EXISTS ( + SELECT FROM pg_subscription_rel + WHERE srsubid = pg_subscription.oid + AND srsubstate <> 'r') + FROM pg_subscription + WHERE subname = 'pending_sub'; + ]), + 't', + 'two-phase subscription remains pending after the slot mismatch'); + +wait_for_slot_inactive('pending_slot'); +$subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION pending_sub'); + +# A subscription without two-phase replication must reject a slot that can +# send prepared transactions. In particular, it must do so before parallel +# apply can execute subscriber-side defaults. +recreate_slot('disabled_slot', 'true', 'false'); +$subscriber->safe_psql( + 'postgres', qq[ + CREATE SUBSCRIPTION disabled_sub + CONNECTION '$publisher_connstr application_name=disabled_sub' + PUBLICATION property_pub + WITH (slot_name = 'disabled_slot', create_slot = false, + copy_data = false, enabled = false, two_phase = false, + streaming = parallel, disable_on_error = true); +]); + +$publisher->safe_psql( + 'postgres', q[ + BEGIN; + INSERT INTO property_tab + SELECT i, repeat(i::text, 1000) + FROM generate_series(1, 5000) AS i; + PREPARE TRANSACTION 'disabled_gid'; ]); +$subscriber_log_offset = -s $subscriber->logfile; +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION disabled_sub ENABLE'); +$subscriber->wait_for_log( + qr/replication slot "disabled_slot" has two_phase enabled/, + $subscriber_log_offset); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT NOT subenabled + FROM pg_subscription + WHERE subname = 'disabled_sub'; +]) or die 'timed out waiting for disabled_sub to be disabled'; +$subscriber->poll_query_until( + 'postgres', q[ + SELECT apply_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = 'disabled_sub'; +]) or die 'timed out waiting for the apply error to be counted'; + +is( $subscriber->safe_psql( + 'postgres', 'SELECT last_value, is_called FROM local_seq'), + '1|f', + 'subscriber-side defaults did not run before the mismatch error'); +is($subscriber->safe_psql('postgres', 'SELECT count(*) FROM property_tab'), + '0', 'no prepared transaction changes were applied'); + +$publisher->safe_psql('postgres', "ROLLBACK PREPARED 'disabled_gid'"); +wait_for_slot_inactive('disabled_slot'); +$subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION disabled_sub'); + +# Once a subscription has enabled two-phase replication, it must reject a +# replacement slot without two-phase decoding enabled. +recreate_slot('enabled_slot', 'true', 'false'); +$subscriber->safe_psql( + 'postgres', qq[ + CREATE SUBSCRIPTION enabled_sub + CONNECTION '$publisher_connstr application_name=enabled_sub' + PUBLICATION property_pub + WITH (slot_name = 'enabled_slot', create_slot = false, + copy_data = false, enabled = false, two_phase = true, + disable_on_error = true); + ALTER SUBSCRIPTION enabled_sub ENABLE; +]); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT subtwophasestate = 'e' + FROM pg_subscription + WHERE subname = 'enabled_sub'; +]) or die 'timed out waiting for two-phase replication to be enabled'; + +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION enabled_sub DISABLE'); +wait_for_slot_inactive('enabled_slot'); +recreate_slot('enabled_slot', 'false', 'false'); + +$subscriber_log_offset = -s $subscriber->logfile; +$subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION enabled_sub ENABLE'); +$subscriber->wait_for_log( + qr/replication slot "enabled_slot" has two_phase disabled/, + $subscriber_log_offset); + +$subscriber->poll_query_until( + 'postgres', q[ + SELECT NOT subenabled + FROM pg_subscription + WHERE subname = 'enabled_sub'; +]) or die 'timed out waiting for enabled_sub to be disabled'; +$subscriber->poll_query_until( + 'postgres', q[ + SELECT apply_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = 'enabled_sub'; +]) or die 'timed out waiting for the apply error to be counted'; + +wait_for_slot_inactive('enabled_slot'); +$subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION enabled_sub'); + # A failover-enabled subscription requires a failover-enabled slot. -recreate_slot('failover_slot', 'true'); +recreate_slot('failover_slot', 'false', 'true'); $subscriber->safe_psql( 'postgres', qq[ CREATE SUBSCRIPTION failover_sub @@ -78,9 +245,9 @@ $publisher->poll_query_until( $subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION failover_sub DISABLE'); wait_for_slot_inactive('failover_slot'); -recreate_slot('failover_slot', 'false'); +recreate_slot('failover_slot', 'false', 'false'); -my $subscriber_log_offset = -s $subscriber->logfile; +$subscriber_log_offset = -s $subscriber->logfile; $subscriber->safe_psql('postgres', 'ALTER SUBSCRIPTION failover_sub ENABLE'); $subscriber->wait_for_log( qr/replication slot "failover_slot" has failover disabled/, @@ -104,7 +271,7 @@ wait_for_slot_inactive('failover_slot'); $subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION failover_sub'); # A subscription without failover enabled must reject a failover-enabled slot. -recreate_slot('extra_failover_slot', 'true'); +recreate_slot('extra_failover_slot', 'false', 'true'); $subscriber->safe_psql( 'postgres', qq[ CREATE SUBSCRIPTION extra_failover_sub -- 2.47.3
