Feedback-ID: i0c21471d:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-591-gfe6c3a2700-fm-20220427.001-gfe6c3a27
Mime-Version: 1.0
Message-Id: <bc557ebe-92dc-4afa-b6bb-285a9eeaa614@www.fastmail.com>
Date: Fri, 27 May 2022 18:12:54 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: pgsql-hackers@lists.postgresql.org
Subject: Ignore heap rewrites for materialized views in logical replication
Content-Type: multipart/mixed;
 boundary=4a5def00fd874368b63de01f0def2860
Archived-At: 
 <https://www.postgresql.org/message-id/bc557ebe-92dc-4afa-b6bb-285a9eeaa614%40www.fastmail.com>
Precedence: bulk

--4a5def00fd874368b63de01f0def2860
Content-Type: multipart/alternative;
 boundary=7642febdec6f4191b1ae8271bc3a8573

--7642febdec6f4191b1ae8271bc3a8573
Content-Type: text/plain

Hi,

While investigating an internal report, I concluded that it is a bug. The
reproducible test case is simple (check 0002) and it consists of a FOR ALL
TABLES publication and a non-empty materialized view on publisher. After the
setup, if you refresh the MV, you got the following message on the subscriber:

ERROR:  logical replication target relation "public.pg_temp_NNNNN" does not exist

That's because the commit 1a499c2520 (that fixes the heap rewrite for tables)
forgot to consider that materialized views can also create transient heaps and
they should also be skipped. The affected version is only 10 because 11
contains a different solution (commit 325f2ec555) that provides a proper fix
for the heap rewrite handling in logical decoding.

0001 is a patch to skip MV too. I attached 0002 to demonstrate the issue but it
doesn't seem appropriate to be included. The test was written to detect the
error and bail out. After this fix, it takes a considerable amount of time to
finish the test because it waits for a message that never arrives. Since nobody
reports this bug in 5 years and considering that version 10 will be EOL in 6
months, I don't think an additional test is crucial here.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--7642febdec6f4191b1ae8271bc3a8573
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>Hi,<br></div><d=
iv><br></div><div>While investigating an internal report, I concluded th=
at it is a bug. The<br></div><div>reproducible test case is simple (chec=
k 0002) and it consists of a FOR ALL<br></div><div>TABLES publication an=
d a non-empty materialized view on publisher. After the<br></div><div>se=
tup, if you refresh the MV, you got the following message on the subscri=
ber:<br></div><div><br></div><div>ERROR:&nbsp; logical replication targe=
t relation "public.pg_temp_NNNNN" does not exist<br></div><div><br></div=
><div>That's because the commit 1a499c2520 (that fixes the heap rewrite =
for tables)<br></div><div>forgot to consider that materialized views can=
 also create transient heaps and<br></div><div>they should also be skipp=
ed. The affected version is only 10 because 11<br></div><div>contains a =
different solution (commit 325f2ec555) that provides a proper fix<br></d=
iv><div>for the heap rewrite handling in logical decoding.<br></div><div=
><br></div><div>0001 is a patch to skip MV too. I attached 0002 to demon=
strate the issue but it<br></div><div>doesn't seem appropriate to be inc=
luded. The test was written to detect the<br></div><div>error and bail o=
ut. After this fix, it takes a considerable amount of time to<br></div><=
div>finish the test because it waits for a message that never arrives. S=
ince nobody<br></div><div>reports this bug in 5 years and considering th=
at version 10 will be EOL in 6<br></div><div>months, I don't think an ad=
ditional test is crucial here.</div><div><br></div><div><br></div><div i=
d=3D"sig112409080"><div class=3D"signature">--<br></div><div class=3D"si=
gnature">Euler Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp;=
 <a href=3D"https://www.enterprisedb.com/">https://www.enterprisedb.com/=
</a><br></div></div><div><br></div></body></html>
--7642febdec6f4191b1ae8271bc3a8573--

--4a5def00fd874368b63de01f0def2860
Content-Disposition: 
 attachment;filename="v1-0001-Ignore-heap-rewrites-for-materialized-views-in-lo.patch"
Content-Type: text/x-patch;
 name="v1-0001-Ignore-heap-rewrites-for-materialized-views-in-lo.patch"
Content-Transfer-Encoding: BASE64

RnJvbSA2NTJlZmU0NTY2NWQ5MWYyZjRhZTg2NWRiYTA3OGZjYWZmZGMwYTE3IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBFdWxlciBUYXZlaXJhIDxldWxlci50YXZlaXJhQGVu
dGVycHJpc2VkYi5jb20+CkRhdGU6IEZyaSwgMjcgTWF5IDIwMjIgMTE6MzU6MjcgLTAzMDAK
U3ViamVjdDogW1BBVENIIHYxIDEvMl0gSWdub3JlIGhlYXAgcmV3cml0ZXMgZm9yIG1hdGVy
aWFsaXplZCB2aWV3cyBpbiBsb2dpY2FsCiByZXBsaWNhdGlvbgoKSWYgeW91IGhhdmUgYSBw
dWJsaWNhdGlvbiB0aGF0IHNwZWNpZmllcyBGT1IgQUxMIFRBQkxFUyBjbGF1c2UsIGEgUkVG
UkVTSApNQVRFUklBTElaRUQgVklFVyBjYW4gYnJlYWsgeW91ciBzZXR1cCB3aXRoIHRoZSBm
b2xsb3dpbmcgbWVzc2FnZQoKRVJST1I6ICBsb2dpY2FsIHJlcGxpY2F0aW9uIHRhcmdldCBy
ZWxhdGlvbiAicHVibGljLnBnX3RlbXBfTk5OIiBkb2VzIG5vdCBleGlzdAoKQ29tbWl0IDFh
NDk5YzI1MjAgaW50cm9kdWNlcyBhIGhldXJpc3RpYyB0byBwcmV2ZW50IHRoYXQgdHJhbnNp
ZW50IGhlYXBzIChkdWUKdG8gdGFibGUgcmV3cml0ZXMpIGFyZSBpbmNsdWRlZCBmb3IgYSBG
T1IgQUxMIFRBQkxFUyBwdWJsaWNhdGlvbi4gSG93ZXZlciwgaXQKZm9yZ290IHRvIGV4Y2x1
ZGUgbWF0ZXJpYWxpemVkIHZpZXdzIChoZWFwIHJld3JpdGVzIG9jY3VycyB3aGVuIHlvdSBl
eGVjdXRlClJFRlJFU0ggTUFURVJJQUxJWkVEIFZJRVcpLiBTaW5jZSBtYXRlcmlhbGl6ZWQg
dmlld3MgYXJlIG5vdCBzdXBwb3J0ZWQgaW4KbG9naWNhbCBkZWNvZGluZywgaXQgaXMgYXBw
cm9wcmlhdGUgdG8gZmlsdGVyIHRoZW0gdG9vLgoKQXMgZXhwbGFpbmVkIGluIHRoZSBjb21t
aXQgbWVzc2FnZSwgYSBtb3JlIHJvYnVzdCBzb2x1dGlvbiB3YXMgaW5jbHVkZWQgaW4KdmVy
c2lvbiAxMSAoY29tbWl0IDMyNWYyZWM1NTUpLCBoZW5jZSBvbmx5IHZlcnNpb24gMTAgaXMg
YWZmZWN0ZWQuCi0tLQogc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vcGdvdXRwdXQvcGdvdXRw
dXQuYyB8IDQgKysrLQogMSBmaWxlIGNoYW5nZWQsIDMgaW5zZXJ0aW9ucygrKSwgMSBkZWxl
dGlvbigtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL3Bnb3V0cHV0
L3Bnb3V0cHV0LmMgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9wZ291dHB1dC9wZ291dHB1
dC5jCmluZGV4IGY5NmZkZTNkZjAuLmUwM2ZmNGExMWUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNr
ZW5kL3JlcGxpY2F0aW9uL3Bnb3V0cHV0L3Bnb3V0cHV0LmMKKysrIGIvc3JjL2JhY2tlbmQv
cmVwbGljYXRpb24vcGdvdXRwdXQvcGdvdXRwdXQuYwpAQCAtNTQwLDcgKzU0MCw5IEBAIGdl
dF9yZWxfc3luY19lbnRyeShQR091dHB1dERhdGEgKmRhdGEsIE9pZCByZWxpZCkKIAkJCQlp
ZiAoc3NjYW5mKHJlbG5hbWUsICJwZ190ZW1wXyV1JW4iLCAmdSwgJm4pID09IDEgJiYKIAkJ
CQkJcmVsbmFtZVtuXSA9PSAnXDAnKQogCQkJCXsKLQkJCQkJaWYgKGdldF9yZWxfcmVsa2lu
ZCh1KSA9PSBSRUxLSU5EX1JFTEFUSU9OKQorCQkJCQljaGFyCXJlbGtpbmQgPSBnZXRfcmVs
X3JlbGtpbmQodSk7CisKKwkJCQkJaWYgKHJlbGtpbmQgPT0gUkVMS0lORF9SRUxBVElPTiB8
fCByZWxraW5kID09IFJFTEtJTkRfTUFUVklFVykKIAkJCQkJCWJyZWFrOwogCQkJCX0KIAkJ
CX0KLS0gCjIuMzAuMgoK

--4a5def00fd874368b63de01f0def2860
Content-Disposition: 
 attachment;filename="v1-0002-test-heap-rewrite-for-materialized-view-in-logica.patch"
Content-Type: text/x-patch;
 name="v1-0002-test-heap-rewrite-for-materialized-view-in-logica.patch"
Content-Transfer-Encoding: BASE64

RnJvbSA3YmEyOGRlYTY4MDAzNTlhZTYzMWMxYTEzMmY4ZjZjNmQ0MTg1NDhiIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBFdWxlciBUYXZlaXJhIDxldWxlci50YXZlaXJhQGVu
dGVycHJpc2VkYi5jb20+CkRhdGU6IEZyaSwgMjcgTWF5IDIwMjIgMTQ6NDM6MTAgLTAzMDAK
U3ViamVjdDogW1BBVENIIHYxIDIvMl0gdGVzdDogaGVhcCByZXdyaXRlIGZvciBtYXRlcmlh
bGl6ZWQgdmlldyBpbiBsb2dpY2FsCiByZXBsaWNhdGlvbgoKLS0tCiBzcmMvdGVzdC9zdWJz
Y3JpcHRpb24vdC8wMDZfcmV3cml0ZS5wbCB8IDI2ICsrKysrKysrKysrKysrKysrKysrKysr
KystCiAxIGZpbGUgY2hhbmdlZCwgMjUgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigtKQoK
ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3N1YnNjcmlwdGlvbi90LzAwNl9yZXdyaXRlLnBsIGIv
c3JjL3Rlc3Qvc3Vic2NyaXB0aW9uL3QvMDA2X3Jld3JpdGUucGwKaW5kZXggNWUzMjExYWVm
YS4uZTVlMGZkYWJlNyAxMDA2NDQKLS0tIGEvc3JjL3Rlc3Qvc3Vic2NyaXB0aW9uL3QvMDA2
X3Jld3JpdGUucGwKKysrIGIvc3JjL3Rlc3Qvc3Vic2NyaXB0aW9uL3QvMDA2X3Jld3JpdGUu
cGwKQEAgLTMsNyArMyw4IEBAIHVzZSBzdHJpY3Q7CiB1c2Ugd2FybmluZ3M7CiB1c2UgUG9z
dGdyZXNOb2RlOwogdXNlIFRlc3RMaWI7Ci11c2UgVGVzdDo6TW9yZSB0ZXN0cyA9PiAyOwor
dXNlIFRpbWU6OkhpUmVzIHF3KHVzbGVlcCk7Cit1c2UgVGVzdDo6TW9yZSB0ZXN0cyA9PiAz
OwogCiBzdWIgd2FpdF9mb3JfY2F1Z2h0X3VwCiB7CkBAIC0yNiw2ICsyNywxMyBAQCBteSAk
ZGRsID0gIkNSRUFURSBUQUJMRSB0ZXN0MSAoYSBpbnQsIGIgdGV4dCk7IjsKICRub2RlX3B1
Ymxpc2hlci0+c2FmZV9wc3FsKCdwb3N0Z3JlcycsICRkZGwpOwogJG5vZGVfc3Vic2NyaWJl
ci0+c2FmZV9wc3FsKCdwb3N0Z3JlcycsICRkZGwpOwogCiskZGRsID0gIkNSRUFURSBUQUJM
RSB0ZXN0MiAoYSBpbnQsIGIgdGV4dCk7IjsKKyRub2RlX3B1Ymxpc2hlci0+c2FmZV9wc3Fs
KCdwb3N0Z3JlcycsICRkZGwpOworJG5vZGVfc3Vic2NyaWJlci0+c2FmZV9wc3FsKCdwb3N0
Z3JlcycsICRkZGwpOworCiskbm9kZV9wdWJsaXNoZXItPnNhZmVfcHNxbCgncG9zdGdyZXMn
LCBxe0lOU0VSVCBJTlRPIHRlc3QyIChhLCBiKSBWQUxVRVMgKDEwLCAndGVuJyksICgyMCwg
J3R3ZW50eScpO30pOworJG5vZGVfcHVibGlzaGVyLT5zYWZlX3BzcWwoJ3Bvc3RncmVzJywg
J0NSRUFURSBNQVRFUklBTElaRUQgVklFVyB0ZXN0MyBBUyBTRUxFQ1QgYSwgYiBGUk9NIHRl
c3QyOycpOworCiBteSAkcHVibGlzaGVyX2Nvbm5zdHIgPSAkbm9kZV9wdWJsaXNoZXItPmNv
bm5zdHIgLiAnIGRibmFtZT1wb3N0Z3Jlcyc7CiBteSAkYXBwbmFtZSAgICAgICAgICAgPSAn
ZW5jb2RpbmdfdGVzdCc7CiAKQEAgLTY5LDUgKzc3LDIxIEBAIGlzKCRub2RlX3N1YnNjcmli
ZXItPnNhZmVfcHNxbCgncG9zdGdyZXMnLCBxe1NFTEVDVCBhLCBiLCBjIEZST00gdGVzdDF9
KSwKIDN8dGhyZWV8MzMpLAogICAgJ2RhdGEgcmVwbGljYXRlZCB0byBzdWJzY3JpYmVyJyk7
CiAKKyRub2RlX3B1Ymxpc2hlci0+c2FmZV9wc3FsKCdwb3N0Z3JlcycsICdSRUZSRVNIIE1B
VEVSSUFMSVpFRCBWSUVXIHRlc3QzOycpOworCitteSAkbWF4X2F0dGVtcHRzID0gMiAqICRU
ZXN0TGliOjp0aW1lb3V0X2RlZmF1bHQ7CitteSAkYXR0ZW1wdHMgICAgID0gMDsKK215ICRl
cnJtc2cgPSBxci9sb2dpY2FsIHJlcGxpY2F0aW9uIHRhcmdldCByZWxhdGlvbi4qZG9lcyBu
b3QgZXhpc3QvOworeworCXdoaWxlICgkYXR0ZW1wdHMgPCAkbWF4X2F0dGVtcHRzKQorCXsK
KwkJbGFzdCBpZiAoc2x1cnBfZmlsZSgkbm9kZV9zdWJzY3JpYmVyLT5sb2dmaWxlKSA9fiAk
ZXJybXNnKTsKKworCQl1c2xlZXAoMTAwXzAwMCk7CisJCSRhdHRlbXB0cysrOworCX0KK30K
K2lzKCRhdHRlbXB0cywgJG1heF9hdHRlbXB0cywgJ2hlYXAgcmV3cml0ZSBmb3IgbWF0ZXJp
YWxpemVkIHZpZXcgb24gc3Vic2NyaWJlcicpOworCiAkbm9kZV9zdWJzY3JpYmVyLT5zdG9w
OwogJG5vZGVfcHVibGlzaGVyLT5zdG9wOwotLSAKMi4zMC4yCgo=

--4a5def00fd874368b63de01f0def2860--