MIME-Version: 1.0
References: 
 <CALDaNm0-n8FGAorM+bTxkzn+AOUyx5=L_XmnvOP6T24+-NcBKg@mail.gmail.com>
 <CAA4eK1+gaEyYJpsGZktfXptU4gbLcwbzFTC0TBZc1UY2SBPydw@mail.gmail.com>
 <CALDaNm2VTjKe8mQsTXFvrbgzcSO4OiuyOdw4VDBK_Ntn=ay-bQ@mail.gmail.com>
 <CAA4eK1+37NhZg+q_UdX6Bfm1ikfoZPZaS0nqPY6xtMNs4BPQnw@mail.gmail.com>
 <CALDaNm13A+KyNu47YMyu+tL4XeombTHzZt3fpD1QLxh_Mtw+WQ@mail.gmail.com>
 <CALDaNm1Lg1YOtY+Sy2his=ikHsQYYQ1pWN620QiBZDBdbsjMHw@mail.gmail.com>
 <CAA4eK1LSOUXoH+gY1OikbHajxpgbZJqs=jfYGDmckuWWXzcLMQ@mail.gmail.com>
 <CALDaNm2Xkm1M-ik2RLJZ9rMhW2zW2GRLL6ePyZJbXcAjOVwzXg@mail.gmail.com>
 <CALDaNm3Ub=T1c78kDF3y5Wcna3WrzoiEeVL8_atbSFSiVhj8FQ@mail.gmail.com>
 <CAA4eK1JF1VQftiLVwt9QbCeR956Xg8fTsfdm4tnhw=J2_jUWRg@mail.gmail.com>
 <CALDaNm0T4uP0BTQHAzsmw3EtCoq=-y4Ryww9PZTrqiZeyA80XQ@mail.gmail.com>
 <CAFiTN-sAbD1G09bn1EPh4EgEjtqxBdGaSxKYhVCBEQZwgXgdmg@mail.gmail.com>
 <CALDaNm3HYP5WJLV9cKWEXuGgWYSLFOs-=u0qf5OD4Zyb87=6gw@mail.gmail.com>
 <CAFiTN-tnDOhVdKvDR9z3+z3B+dNQFs10e48OgAdRsC9sJXqs0g@mail.gmail.com>
 <CALDaNm2gfsxQL7VrurNnJffCBcuyrZQ1LXDOhf9FY57n8=8cVw@mail.gmail.com>
 <CAFiTN-u1gKoTau54q=P+N9i8rVwAq4hjVXv6mOkxM_EiqZDMbg@mail.gmail.com>
In-Reply-To: 
 <CAFiTN-u1gKoTau54q=P+N9i8rVwAq4hjVXv6mOkxM_EiqZDMbg@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Thu, 13 Mar 2025 15:20:01 +0530
Message-ID: 
 <CAA4eK1L-4c2+v7NZB4jWDwtN-VT+UKAm9Vp2dzO2Tyg0HTJvbQ@mail.gmail.com>
Subject: Re: Add an option to skip loading missing publication to avoid
 logical replication failure
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000084af020630363ff5"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1L-4c2%2Bv7NZB4jWDwtN-VT%2BUKAm9Vp2dzO2Tyg0HTJvbQ%40mail.gmail.com>
Precedence: bulk

--00000000000084af020630363ff5
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 13, 2025 at 11:43=E2=80=AFAM Dilip Kumar <dilipbalaut@gmail.com=
> wrote:
>
> Looks fine, shall we add the missing publication point as well
> something like below
>
> /*
> * In operations like 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
> * 'CREATE SUBSCRIPTION', if the specified publication does not exist or
> * if the replication origin is not updated before the worker exits,
> * the WAL start location may point to a position prior to the publication=
's
> * WAL record. This can cause persistent restarts and errors
> * in the apply worker.
> *

I think that is too much related to pub-sub model, and ideally,
pgoutput should not care about it. I have written a comment
considering somebody using pgoutput decoding module via APIs.

> > * Additionally, dropping a subscription's publication should not
> > * disrupt logical replication.
> *
> > * This ensures that a missing publication is skipped and loaded
> > * when its corresponding WAL record is encountered.
> */
>
>
>
> > ereport(WARNING,
> > errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> > errmsg("skipped loading publication: %s", pubname),
> > errhint("If the publication is missing, create and refresh it.
> > Otherwise, wait for the slot to reach the WAL record, then refresh"));
> >
> > > 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?
> >
> > Users need to refresh the publication in case the relation is not
> > already added to pg_subscription_rel and apply incremental changes.
> > How about an error hint like:
> > "If the publication is missing, create and refresh it. Otherwise, wait
> > for the slot to reach the WAL record for the created publication, then
> > refresh"
>

I have tried to split this information into errdetail and errhint in
the attached. See and let me know what you think of the same.

--=20
With Regards,
Amit Kapila.

--00000000000084af020630363ff5
Content-Type: application/octet-stream;
	name="v9-0001-Fix-ALTER-SUBSCRIPTION-.-SET-PUBLICATION-.-comman.patch"
Content-Disposition: attachment;
	filename="v9-0001-Fix-ALTER-SUBSCRIPTION-.-SET-PUBLICATION-.-comman.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m8763uig0>
X-Attachment-Id: f_m8763uig0

RnJvbSBkZTc2Nzk0MGZlNmYzM2JiOGUxMjk3NTQyOGI4MjdjZmQxODNkY2Q1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBWaWduZXNoIDx2aWduZXNoMjFAZ21haWwuY29tPgpEYXRlOiBN
b24sIDMgTWFyIDIwMjUgMTE6NTQ6MjcgKzA1MzAKU3ViamVjdDogW1BBVENIIHY5XSBGaXggQUxU
RVIgU1VCU0NSSVBUSU9OIC4uLiBTRVQgUFVCTElDQVRJT04gLi4uIGNvbW1hbmQuCgpUaGUgcHJv
YmxlbSBpcyB0aGF0IEFMVEVSIFNVQlNDUklQVElPTiAuLi4gU0VUIFBVQkxJQ0FUSU9OIC4uLiB3
aWxsIGxlYWQKdG8gcmVzdGFydGluZyBvZiBhcHBseSB3b3JrZXIgYW5kIGFmdGVyIHRoZSByZXN0
YXJ0LCB0aGUgYXBwbHkgd29ya2VyIHdpbGwKdXNlIHRoZSBleGlzdGluZyBzbG90IGFuZCByZXBs
aWNhdGlvbiBvcmlnaW4gY29ycmVzcG9uZGluZyB0byB0aGUKc3Vic2NyaXB0aW9uLiBOb3csIGl0
IGlzIHBvc3NpYmxlIHRoYXQgYmVmb3JlIHRoZSByZXN0YXJ0LCB0aGUgb3JpZ2luIGhhcwpub3Qg
YmVlbiB1cGRhdGVkLCBhbmQgdGhlIFdBTCBzdGFydCBsb2NhdGlvbiBwb2ludHMgdG8gYSBsb2Nh
dGlvbiBiZWZvcmUKd2hlcmUgUFVCTElDQVRJT04gcG9pbnRlZCB0byBieSBTRVQgUFVCTElDQVRJ
T04gZG9lc24ndCBleGlzdCwgYW5kIHRoYXQKY2FuIGxlYWQgdG8gYW4gZXJyb3IgbGlrZTogIkVS
Uk9SOiAgcHVibGljYXRpb24gInB1YjEiIGRvZXMgbm90IGV4aXN0Ii4KT25jZSB0aGlzIGVycm9y
IG9jY3VycywgYXBwbHkgd29ya2VyIHdpbGwgbmV2ZXIgYmUgYWJsZSB0byBwcm9jZWVkIGFuZAp3
aWxsIGFsd2F5cyByZXR1cm4gdGhlIHNhbWUgZXJyb3IuCgpXZSBkZWNpZGVkIHRvIHNraXAgbG9h
ZGluZyB0aGUgcHVibGljYXRpb24gaWYgdGhlIHB1YmxpY2F0aW9uIGRvZXMgbm90CmV4aXN0LiBU
aGUgcHVibGljYXRpb24gaXMgbG9hZGVkIGxhdGVyIGFuZCB1cGRhdGVzIHRoZSByZWxhdGlvbiBl
bnRyeSB3aGVuCnRoZSBwdWJsaWNhdGlvbiBnZXRzIGNyZWF0ZWQuCgpXZSBkZWNpZGVkIG5vdCB0
byBiYWNrcGF0Y2ggdGhpcyBhcyB0aGlzIGlzIGEgYmVoYXZpb3VyIGNoYW5nZSwgYW5kIHdlIGRv
bid0CnNlZSBmaWVsZCByZXBvcnRzLiBUaGlzIHByb2JsZW0gaGFzIGJlZW4gZm91bmQgYnkgaW50
ZXJtaXR0ZW50IGJ1aWxkZmFybQpmYWlsdXJlcy4KCkF1dGhvcjogdmlnbmVzaCBDIDx2aWduZXNo
MjFAZ21haWwuY29tPgpSZXZpZXdlZC1ieTogRGlsaXAgS3VtYXIgPGRpbGlwYmFsYXV0QGdtYWls
LmNvbT4KUmV2aWV3ZWQtYnk6IEFtaXQgS2FwaWxhIDxhbWl0LmthcGlsYTE2QGdtYWlsLmNvbT4K
UmV2aWV3ZWQtYnk6IE1hc2FoaWtvIFNhd2FkYSA8c2F3YWRhLm1zaGtAZ21haWwuY29tPgpEaXNj
dXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL2ZsYXQvQ0FMRGFObTAtbjhGR0Fvck0lMkJiVHhr
em4lMkJBT1V5eDUlM0RMX1htbnZPUDZUMjQlMkItTmNCS2clNDBtYWlsLmdtYWlsLmNvbQpEaXNj
dXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBQTRlSzErVC1FVFhlUk00REhXekd4QnBLYWZM
Q3BfXzViUEFfUVpmRlFwNy0wd2o0UUBtYWlsLmdtYWlsLmNvbQotLS0KIHNyYy9iYWNrZW5kL3Jl
cGxpY2F0aW9uL3Bnb3V0cHV0L3Bnb3V0cHV0LmMgfCAxNiArKysrKysrLQogc3JjL3Rlc3Qvc3Vi
c2NyaXB0aW9uL3QvMDI0X2FkZF9kcm9wX3B1Yi5wbCB8IDQ0ICsrKysrKysrKysrKysrKysrKysr
LQogMiBmaWxlcyBjaGFuZ2VkLCA1NyBpbnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygtKQoKZGlm
ZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL3Bnb3V0cHV0L3Bnb3V0cHV0LmMgYi9z
cmMvYmFja2VuZC9yZXBsaWNhdGlvbi9wZ291dHB1dC9wZ291dHB1dC5jCmluZGV4IGVkODA2YzU0
MzAwLi44MzU3YmY4YjRjMCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vcGdv
dXRwdXQvcGdvdXRwdXQuYworKysgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9wZ291dHB1dC9w
Z291dHB1dC5jCkBAIC0xNzY0LDYgKzE3NjQsMTEgQEAgcGdvdXRwdXRfc2h1dGRvd24oTG9naWNh
bERlY29kaW5nQ29udGV4dCAqY3R4KQogCiAvKgogICogTG9hZCBwdWJsaWNhdGlvbnMgZnJvbSB0
aGUgbGlzdCBvZiBwdWJsaWNhdGlvbiBuYW1lcy4KKyAqCisgKiBIZXJlLCB3ZSBza2lwIHRoZSBw
dWJsaWNhdGlvbnMgdGhhdCBkb24ndCBleGlzdCB5ZXQuIFRoaXMgd2lsbCBhbGxvdyB1cworICog
dG8gc2lsZW50bHkgY29udGludWUgdGhlIHJlcGxpY2F0aW9uIGluIHRoZSBhYnNlbmNlIG9mIGEg
bWlzc2luZyBwdWJsaWNhdGlvbi4KKyAqIFRoaXMgaXMgcmVxdWlyZWQgYmVjYXVzZSB3ZSBhbGxv
dyB0aGUgdXNlcnMgdG8gY3JlYXRlIHB1YmxpY2F0aW9ucyBhZnRlciB0aGV5CisgKiBoYXZlIHNw
ZWNpZmllZCB0aGUgcmVxdWlyZWQgcHVibGljYXRpb25zIGF0IHRoZSB0aW1lIG9mIHJlcGxpY2F0
aW9uIHN0YXJ0LgogICovCiBzdGF0aWMgTGlzdCAqCiBMb2FkUHVibGljYXRpb25zKExpc3QgKnB1
Ym5hbWVzKQpAQCAtMTc3NCw5ICsxNzc5LDE2IEBAIExvYWRQdWJsaWNhdGlvbnMoTGlzdCAqcHVi
bmFtZXMpCiAJZm9yZWFjaChsYywgcHVibmFtZXMpCiAJewogCQljaGFyCSAgICpwdWJuYW1lID0g
KGNoYXIgKikgbGZpcnN0KGxjKTsKLQkJUHVibGljYXRpb24gKnB1YiA9IEdldFB1YmxpY2F0aW9u
QnlOYW1lKHB1Ym5hbWUsIGZhbHNlKTsKKwkJUHVibGljYXRpb24gKnB1YiA9IEdldFB1YmxpY2F0
aW9uQnlOYW1lKHB1Ym5hbWUsIHRydWUpOwogCi0JCXJlc3VsdCA9IGxhcHBlbmQocmVzdWx0LCBw
dWIpOworCQlpZiAocHViKQorCQkJcmVzdWx0ID0gbGFwcGVuZChyZXN1bHQsIHB1Yik7CisJCWVs
c2UKKwkJCWVyZXBvcnQoV0FSTklORywKKwkJCQkJZXJyY29kZShFUlJDT0RFX09CSkVDVF9OT1Rf
SU5fUFJFUkVRVUlTSVRFX1NUQVRFKSwKKwkJCQkJZXJybXNnKCJza2lwcGVkIGxvYWRpbmcgcHVi
bGljYXRpb246ICVzIiwgcHVibmFtZSksCisJCQkJCWVycmRldGFpbCgiVGhlIHB1YmxpY2F0aW9u
IGRvZXMgbm90IGV4aXN0IGF0IHRoaXMgcG9pbnQgaW4gdGhlIFdBTC4iKSwKKwkJCQkJZXJyaGlu
dCgiQ3JlYXRlIHRoZSBwdWJsaWNhdGlvbiBpZiBpdCBkb2VzIG5vdCBleGlzdC4iKSk7CiAJfQog
CiAJcmV0dXJuIHJlc3VsdDsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3N1YnNjcmlwdGlvbi90LzAy
NF9hZGRfZHJvcF9wdWIucGwgYi9zcmMvdGVzdC9zdWJzY3JpcHRpb24vdC8wMjRfYWRkX2Ryb3Bf
cHViLnBsCmluZGV4IDQ0MjhhMzQxM2RiLi5iNTk0OTQxYzdjYiAxMDA2NDQKLS0tIGEvc3JjL3Rl
c3Qvc3Vic2NyaXB0aW9uL3QvMDI0X2FkZF9kcm9wX3B1Yi5wbAorKysgYi9zcmMvdGVzdC9zdWJz
Y3JpcHRpb24vdC8wMjRfYWRkX2Ryb3BfcHViLnBsCkBAIC0xLDcgKzEsOSBAQAogCiAjIENvcHly
aWdodCAoYykgMjAyMS0yMDI1LCBQb3N0Z3JlU1FMIEdsb2JhbCBEZXZlbG9wbWVudCBHcm91cAog
Ci0jIFRoaXMgdGVzdCBjaGVja3MgYmVoYXZpb3VyIG9mIEFMVEVSIFNVQlNDUklQVElPTiAuLi4g
QUREL0RST1AgUFVCTElDQVRJT04KKyMgVGhpcyB0ZXN0IGNoZWNrcyBiZWhhdmlvdXIgb2YgQUxU
RVIgU1VCU0NSSVBUSU9OIC4uLiBBREQvRFJPUCBQVUJMSUNBVElPTiBhbmQKKyMgZW5zdXJlcyB0
aGF0IGNyZWF0aW5nIGEgcHVibGljYXRpb24gYXNzb2NpYXRlZCB3aXRoIGEgc3Vic2NyaXB0aW9u
IGF0IGEgbGF0ZXIKKyMgcG9pbnQgb2YgdGltZSBkb2VzIG5vdCBicmVhayBsb2dpY2FsIHJlcGxp
Y2F0aW9uLgogdXNlIHN0cmljdDsKIHVzZSB3YXJuaW5ncyBGQVRBTCA9PiAnYWxsJzsKIHVzZSBQ
b3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyOwpAQCAtODAsNiArODIsNDYgQEAgJHJlc3VsdCA9ICRu
b2RlX3N1YnNjcmliZXItPnNhZmVfcHNxbCgncG9zdGdyZXMnLAogCSJTRUxFQ1QgY291bnQoKiks
IG1pbihhKSwgbWF4KGEpIEZST00gdGFiXzEiKTsKIGlzKCRyZXN1bHQsIHFxKDIwfDF8MTApLCAn
Y2hlY2sgaW5pdGlhbCBkYXRhIGlzIGNvcGllZCB0byBzdWJzY3JpYmVyJyk7CiAKKyMgRW5zdXJl
IHRoYXQgc2V0dGluZyBhIG1pc3NpbmcgcHVibGljYXRpb24gdG8gdGhlIHN1YnNjcmlwdGlvbiBk
b2VzIG5vdAorIyBkaXNydXB0IGV4aXN0aW5nIGxvZ2ljYWwgcmVwbGljYXRpb24uIEluc3RlYWQs
IGl0IHNob3VsZCBsb2cgYSB3YXJuaW5nCisjIHdoaWxlIGFsbG93aW5nIHJlcGxpY2F0aW9uIHRv
IGNvbnRpbnVlLiBBZGRpdGlvbmFsbHksIHZlcmlmeSB0aGF0IHJlcGxpY2F0aW9uCisjIHJlc3Vt
ZXMgYWZ0ZXIgdGhlIG1pc3NpbmcgcHVibGljYXRpb24gaXMgY3JlYXRlZCBmb3IgdGhlIHB1Ymxp
Y2F0aW9uIHRhYmxlLgorCisjIENyZWF0ZSB0YWJsZSBvbiBwdWJsaXNoZXIgYW5kIHN1YnNjcmli
ZXIKKyRub2RlX3B1Ymxpc2hlci0+c2FmZV9wc3FsKCdwb3N0Z3JlcycsICJDUkVBVEUgVEFCTEUg
dGFiXzMgKGEgaW50KSIpOworJG5vZGVfc3Vic2NyaWJlci0+c2FmZV9wc3FsKCdwb3N0Z3Jlcycs
ICJDUkVBVEUgVEFCTEUgdGFiXzMgKGEgaW50KSIpOworCisjIFNldCB0aGUgc3Vic2NyaXB0aW9u
IHdpdGggYSBtaXNzaW5nIHB1YmxpY2F0aW9uCiskbm9kZV9zdWJzY3JpYmVyLT5zYWZlX3BzcWwo
J3Bvc3RncmVzJywKKwkiQUxURVIgU1VCU0NSSVBUSU9OIHRhcF9zdWIgU0VUIFBVQkxJQ0FUSU9O
IHRhcF9wdWJfMyIpOworCitteSAkb2Zmc2V0ID0gLXMgJG5vZGVfcHVibGlzaGVyLT5sb2dmaWxl
OworCiskbm9kZV9wdWJsaXNoZXItPnNhZmVfcHNxbCgncG9zdGdyZXMnLCJJTlNFUlQgSU5UTyB0
YWJfMyB2YWx1ZXMoMSkiKTsKKworIyBWZXJpZnkgdGhhdCBhIHdhcm5pbmcgaXMgbG9nZ2VkLgor
JG5vZGVfcHVibGlzaGVyLT53YWl0X2Zvcl9sb2coCisJcXIvV0FSTklORzogKCBbQS1aMC05XSs6
KT8gc2tpcHBlZCBsb2FkaW5nIHB1YmxpY2F0aW9uOiB0YXBfcHViXzMvLCAkb2Zmc2V0KTsKKwor
JG5vZGVfcHVibGlzaGVyLT5zYWZlX3BzcWwoJ3Bvc3RncmVzJywKKwkiQ1JFQVRFIFBVQkxJQ0FU
SU9OIHRhcF9wdWJfMyBGT1IgVEFCTEUgdGFiXzMiKTsKKworJG5vZGVfc3Vic2NyaWJlci0+c2Fm
ZV9wc3FsKCdwb3N0Z3JlcycsCisJIkFMVEVSIFNVQlNDUklQVElPTiB0YXBfc3ViIFJFRlJFU0gg
IFBVQkxJQ0FUSU9OIik7CisKKyRub2RlX3N1YnNjcmliZXItPndhaXRfZm9yX3N1YnNjcmlwdGlv
bl9zeW5jKCRub2RlX3B1Ymxpc2hlciwgJ3RhcF9zdWInKTsKKworJG5vZGVfcHVibGlzaGVyLT5z
YWZlX3BzcWwoJ3Bvc3RncmVzJywgIklOU0VSVCBJTlRPIHRhYl8zIHZhbHVlcygyKSIpOworCisk
bm9kZV9wdWJsaXNoZXItPndhaXRfZm9yX2NhdGNodXAoJ3RhcF9zdWInKTsKKworIyBWZXJpZnkg
dGhhdCB0aGUgaW5zZXJ0IG9wZXJhdGlvbiBnZXRzIHJlcGxpY2F0ZWQgdG8gc3Vic2NyaWJlciBh
ZnRlcgorIyBwdWJsaWNhdGlvbiBpcyBjcmVhdGVkLgorJHJlc3VsdCA9ICRub2RlX3N1YnNjcmli
ZXItPnNhZmVfcHNxbCgncG9zdGdyZXMnLAorCSJTRUxFQ1QgKiBGUk9NIHRhYl8zIik7Citpcygk
cmVzdWx0LCBxcSgxCisyKSwgJ2NoZWNrIHRoYXQgdGhlIGluY3JlbWVudGFsIGRhdGEgaXMgcmVw
bGljYXRlZCBhZnRlciB0aGUgcHVibGljYXRpb24gaXMgY3JlYXRlZCcpOworCiAjIHNodXRkb3du
CiAkbm9kZV9zdWJzY3JpYmVyLT5zdG9wKCdmYXN0Jyk7CiAkbm9kZV9wdWJsaXNoZXItPnN0b3Ao
J2Zhc3QnKTsKLS0gCjIuMjguMC53aW5kb3dzLjEKCg==
--00000000000084af020630363ff5--