Content-Type: multipart/mixed; boundary="------------92y2WSq062DfO7YTH09wypdf"
Message-ID: <26277b42-a950-4c56-8280-3371f2d0bf7c@pritambaral.com>
Date: Mon, 9 Dec 2024 18:50:41 +0530
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-GB
To: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Pritam Baral <pritam@pritambaral.com>
Subject: Subscription sometimes loses txns after initial table sync
Archived-At: <https://www.postgresql.org/message-id/26277b42-a950-4c56-8280-3371f2d0bf7c%40pritambaral.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------92y2WSq062DfO7YTH09wypdf
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

This was discovered when testing the plan for a major version upgrade via
logical replication. Said plan requires that some tables be synced before
others. So I implemented it using ALTER PUBLICATION ... ADD TABLE ... followed
by ALTER SUBSCRIPTION ... REFRESH PUBLICATION. A test for correctness revealed
that sometimes, for some tables added this way, txns after the initial data copy
are lost by the subscription.

A reproducer script is attached. It has been tested with PG 17.2, 14.15, and
even 12.22 (on either side of the replication setup). The script runs at a
default scale of 100 tables with 10k inserts each. This scale is enough to
demonstrate a failure rate of 1% to 9% of tables on my modest laptop.

In attempts to analyse why this happens, it has been observed that the sender
sometimes does not pick up a published table, even when the receiver that
started the sender process has seen the table as available (as returned by
pg_get_publication_tables()) and has thus begun COPYing its data. When the COPY
finishes (and the tablesync worker is finished), the apply loop on the receiver
expects to receive (and apply) subsequent changes for such tables, but simply
isn't sent any. This was observed by dumping every CopyData message sent over
the wire.

The attached script (like the original migration plan) uses a single publication
and adds tables to it successively. Curiously, when the script was changed to
use a dedicated publication per table (and thus, ALTER SUBSCRIPTION ... ADD
PUBLICATION instead of ALTER SUBSCRIPTION ... REFRESH PUBLICATION), the no. of
tables with data loss jumped to 100%.

-- 
#!/usr/bin/env regards
Chhatoi Pritam Baral

--------------92y2WSq062DfO7YTH09wypdf
Content-Type: application/x-shellscript; name="sub-loss-repro.sh"
Content-Disposition: attachment; filename="sub-loss-repro.sh"
Content-Transfer-Encoding: base64

IyEvdXNyL2Jpbi9lbnYgYmFzaAoKZXhwb3J0IFBHVVNFUj0ke1BHVVNFUjotcG9zdGdyZXN9
CmV4cG9ydCBQR0RBVEFCQVNFPSR7UEdEQVRBQkFTRTotcG9zdGdyZXN9CmV4cG9ydCBTUkNf
UE9SVD0ke1NSQ19QT1JUOi01NDMyfQpleHBvcnQgRFNUX1BPUlQ9JHtEU1RfUE9SVDotNTQz
M30KCk5UQUJMRVM9JHtOVEFCTEVTOi0xMDB9Ck5ST1dTPSR7TlJPV1M6LTEwMDAwfQoKUFNR
TD0iJCh3aGljaCBwc3FsKSIKcHNxbCgpIHsKICAgICR7UFNRTH0gLVggLXYgT05fRVJST1Jf
U1RPUD0xICIkQCIKfQoKZm9yIHBvcnQgaW4geyR7U1JDX1BPUlR9LCR7RFNUX1BPUlR9fTsg
ZG8KICAgIGZvciB0bnVtIGluICQoc2VxIDAgJHtOVEFCTEVTfSk7IGRvCiAgICAgICAgZWNo
byAiQ1JFQVRFIFRBQkxFIHQke3RudW19IChpZCBJTlRFR0VSIFBSSU1BUlkgS0VZKTsiOwog
ICAgZG9uZSB8IHBzcWwgLXAgJHBvcnQ7CmRvbmUKCnBzcWwgLXAgJHtTUkNfUE9SVH0gLWMg
IkNSRUFURSBQVUJMSUNBVElPTiBwdWIgRk9SIFRBQkxFIHQwOyIKcHNxbCAtcCAke0RTVF9Q
T1JUfSA8PEVPRgpDUkVBVEUgU1VCU0NSSVBUSU9OIHN1YgogIENPTk5FQ1RJT04gJ3BvcnQ9
JHtTUkNfUE9SVH0gdXNlcj0ke1BHVVNFUn0gZGJuYW1lPSR7UEdEQVRBQkFTRX0nCiAgUFVC
TElDQVRJT04gcHViOwpFT0YKCmZvciB0bnVtIGluICQoc2VxIDAgJHtOVEFCTEVTfSk7IGRv
CiAgICAoZm9yIGlkIGluICQoc2VxIDEgJHtOUk9XU30pOyBkbyBlY2hvICJJTlNFUlQgSU5U
TyB0JHt0bnVtfSBWQUxVRVMgKCR7aWR9KTsiOyBkb25lIHwgcHNxbCAtcCAke1NSQ19QT1JU
fSAtcSkgJgpkb25lCgpmb3IgdG51bSBpbiAkKHNlcSAxICR7TlRBQkxFU30pOyBkbwogICAg
ZWNobyAiQWRkaW5nIHRhYmxlIHQke3RudW19IjsKICAgIHBzcWwgLXAgJHtTUkNfUE9SVH0g
LXEgLWMgIkFMVEVSIFBVQkxJQ0FUSU9OIHB1YiBBREQgVEFCTEUgdCR7dG51bX07IgogICAg
cHNxbCAtcCAke0RTVF9QT1JUfSAtcSAtYyAiQUxURVIgU1VCU0NSSVBUSU9OIHN1YiBSRUZS
RVNIIFBVQkxJQ0FUSU9OOyIKICAgIHdoaWxlIHBzcWwgLXEgMj4vZGV2L251bGwgPDxFT0Y7
IGRvCiAgICAgICAgU0VMRUNUIDEvMAogICAgICAgICAgRlJPTSBwZ19zdWJzY3JpcHRpb25f
cmVsIEFTIHNyCiAgICAgICAgIElOTkVSIEpPSU4gcGdfc3Vic2NyaXB0aW9uIEFTIHMgT04g
KHMub2lkID0gc3JzdWJpZCkKICAgICAgICAgV0hFUkUgc3VibmFtZSA9ICdzdWInCiAgICAg
ICAgICAgQU5EIHNycmVsaWQgPSAndCR7dG51bX0nOjpyZWdjbGFzcwpFT0YKICAgICAgICBz
bGVlcCAxOwogICAgZG9uZQpkb25lCgplY2hvICJXYWl0aW5nIGZvciBkYXRhIGdlbmVyYXRv
cnMgLi4uIgp3YWl0CgplY2hvICJXYWl0aW5nIGZvciByZXBsaWNhdGlvbiBsYWcgLi4uIgpT
UkNfTFNOPSQocHNxbCAtcCAke1NSQ19QT1JUfSAtcSAtdCAtYyAiU0VMRUNUIHBnX2N1cnJl
bnRfd2FsX2xzbigpIikKd2hpbGUgcHNxbCAtcSAyPi9kZXYvbnVsbCA8PEVPRjsgZG8KICAg
IFNFTEVDVCAxLzAKICAgICAgRlJPTSBwZ19zdGF0X3N1YnNjcmlwdGlvbgogICAgIFdIRVJF
IHN1Ym5hbWUgPSAnc3ViJwogICAgICAgQU5EIGxlYWRlcl9waWQgSVMgTlVMTCBBTkQgcmVs
aWQgSVMgTlVMTAogICAgICAgQU5EIHBnX3dhbF9sc25fZGlmZignJHtTUkNfTFNOfScsIGxh
dGVzdF9lbmRfbHNuKSA8PSAwCkVPRgogICAgc2xlZXAgMTsKZG9uZQoKIyBQRyAxNCBzZWVt
cyB0byBuZWVkIHNvbWUgZXh0cmEgdGltZSwgZm9yIHNvbWUgcmVhc29uCnNsZWVwIDEwCgpl
Y2hvICJWZXJpZnlpbmcgZGF0YSIKZm9yIHRudW0gaW4gJChzZXEgMCAke05UQUJMRVN9KTsg
ZG8KICAgIGlmICEgZGlmZiAtcSAmPi9kZXYvbnVsbCBcCiAgICAgICAgPChwc3FsIC1wICR7
U1JDX1BPUlR9IC1jICJDT1BZIChTRUxFQ1QgKiBGUk9NIHQke3RudW19IE9SREVSIEJZIGlk
KSBUTyBTVERPVVQiKSBcCiAgICAgICAgPChwc3FsIC1wICR7RFNUX1BPUlR9IC1jICJDT1BZ
IChTRUxFQ1QgKiBGUk9NIHQke3RudW19IE9SREVSIEJZIGlkKSBUTyBTVERPVVQiKTsgdGhl
bgogICAgICAgIGVjaG8gIlRhYmxlIHQke3RudW19IGhhcyBtaXNtYXRjaCI7CiAgICBmaQpk
b25lCg==

--------------92y2WSq062DfO7YTH09wypdf--