Re: Add an option to skip loading missing publication to avoid logical replication failure

On Thu, Mar 13, 2025 at 7:38 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, 12 Mar 2025 at 16:15, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > Thanks, Vignesh, for adding the test. I believe you've tested the
> > effect of DROP PUBLICATION. However, I think we should also test the
> > behavior of ALTER SUBSCRIPTION...SET PUBLICATION before creating the
> > PUBLICATION, and then create the PUBLICATION at a later stage.
>
> I felt having only one test case for this is enough, I have removed
> the DROP PUBLICATION test and added the SET PUBLICATION test. The
> attached v8 version patch has the changes for the same.

Thanks looks good to me.

While looking at the patch, I have a few comments/questions

+ if (pub)
+ result = lappend(result, pub);
+ else
+ {
+ /*
+ * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
+ * apply worker continues using the existing replication slot and
+ * origin after restarting. If the replication origin is not
+ * updated before the restart, the WAL start location may point to
+ * a position before the specified publication exists, causing
+ * persistent apply worker restarts and errors.
+ *
+ * This ensures that the publication is skipped if it does not
+ * exist and is loaded when the corresponding WAL record is
+ * encountered.
+ */
+ ereport(WARNING,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("skipped loading publication: %s", pubname),
+ errhint("If the publication already exists, ignore it as it will be
loaded upon reaching the corresponding WAL record; otherwise, create
it."));
+ }

This comment focuses on a specific use case regarding the problem with
'ALTER SUBSCRIPTION ... SET PUBLICATION,' but in reality, we are
addressing a more general case where the user is trying to SET
PUBLICATION or even CREATE SUBSCRIPTION, and some publications are
missing. Wouldn't it be better to rephrase the comment?

2. + errhint("If the publication already exists, ignore it as it will
be loaded upon reaching the corresponding WAL record; otherwise,
create it."));

Is this hint correct? This is a question rather than a comment: When
we reach a particular WAL where the publication was created, will the
publication automatically load, or does the user need to REFRESH the
publications?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com