Re: subscription disable_on_error not working after ALTER SUBSCRIPTION set bad conninfo

On Thu, Jan 18, 2024 at 8:54 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Jan 18, 2024 at 11:15 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > On Thu, Jan 18, 2024 at 12:55 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > ...
> > >
> > > Although we can improve it to handle this case too, I'm not sure it's
> > > a bug. The doc says[1]:
> > >
> > > Specifies whether the subscription should be automatically disabled if
> > > any errors are detected by subscription workers during data
> > > replication from the publisher.
> > >
> > > When an apply worker is trying to establish a connection, it's not
> > > replicating data from the publisher.
> > >
> > > Regards,
> > >
> > > [1] https://www.postgresql.org/docs/devel/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR
> > >
> > > --
> > > Masahiko Sawada
> > > Amazon Web Services: https://aws.amazon.com
> >
> > Yeah, I had also seen that wording of those docs. And I agree it
> > leaves open some room for doubts because strictly from that wording it
> > can be interpreted that establishing the connection is not actually
> > "data replication from the publisher" in which case maybe there is no
> > bug.
> >
>
> As far as I remember that was the intention. The idea was if there is
> any conflict during apply that users manually need to fix, they have
> the provision to stop repeating the error. If we wish to extend the
> purpose of this option for another valid use case and there is a good
> way to achieve the same then we can discuss but I don't think we need
> to change it in back-branches.
>
> --

In case we want to proceed with this, here is a simple POC patch that
seems to do the job.

~~~

RESULT:

test_sub=# create subscription sub1 connection 'dbname=test_pub'
publication pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
2024-01-19 11:50:33.385 AEDT [17905] LOG: logical replication apply
worker for subscription "sub1" has started
2024-01-19 11:50:33.398 AEDT [17907] LOG: logical replication table
synchronization worker for subscription "sub1", table "t1" has started
2024-01-19 11:50:33.481 AEDT [17907] LOG: logical replication table
synchronization worker for subscription "sub1", table "t1" has
finished

test_sub=# alter subscription sub1 set (disable_on_error);
ALTER SUBSCRIPTION

test_sub=# alter subscription sub1 connection 'port=-1';
2024-01-19 11:51:00.696 AEDT [17905] LOG: logical replication worker
for subscription "sub1" will restart because of a parameter change
ALTER SUBSCRIPTION
2024-01-19 11:51:00.704 AEDT [18649] LOG: logical replication apply
worker for subscription "sub1" has started
2024-01-19 11:51:00.704 AEDT [18649] ERROR: could not connect to the
publisher: invalid port number: "-1"
2024-01-19 11:51:00.705 AEDT [18649] LOG: subscription "sub1" has
been disabled because of an error

======
Kind Regards,
Peter Smith.
Fujitsu Australia