Content-Type: multipart/mixed; boundary="------------bLuz8awR8h0XcGGgQRHM5JcV"
Message-ID: <1a2d5aba-df29-ab8d-65ac-6ffd54d1ac62@gmail.com>
Date: Fri, 21 Jul 2023 12:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: BUG #18014: Releasing catcache entries makes
 schema_to_xmlschema() fail when parallel workers are used
Content-Language: en-US
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
References: <b2dcd258-82f1-dfe3-498e-9f652a0c0c4b@gmail.com>
 <20230720.162802.905550494964943231.horikyota.ntt@gmail.com>
 <20230720.164459.2183367718473165651.horikyota.ntt@gmail.com>
 <20230720.172924.535889842187000361.horikyota.ntt@gmail.com>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <20230720.172924.535889842187000361.horikyota.ntt@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/1a2d5aba-df29-ab8d-65ac-6ffd54d1ac62%40gmail.com>
Precedence: bulk

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

Hello Horiguchi-san,

20.07.2023 11:29, Kyotaro Horiguchi wrote:
> At Thu, 20 Jul 2023 16:44:59 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in
>> Stupid. What we should do here would be ensuring the funtion doesn't
>> invoke parallel workers, maybe.
> So, for testing, I didn't see the error with applying the attached patch.
> There are other SPI calls but I didn't see them at all.
>

Thank you for looking into it!

I think that we need to determine the level where the problem that should
be fixed is:
1) test xmlmap fails sporadically due to the catalog changes caused by
  parallel tests activity
2) schema_to_xmlschemaX() can fail when parallel workers are used
3) has_table_privilegeX() can fail sporadically when executed within a
  parallel worker
4) SearchSysCacheX(RELOID, ...) can switch to a newer catalog snapshot,
  when repeated in a parallel worker

The patch proposed fixes the issue on level 2, but that error still can be
seen on level 3. For example, you can replace
SELECT schema_to_xmlschema('testxmlschema_$c', true, false, '');
in the above script with
SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = 1 AND relkind IN ('r', 'm', 'v') AND 
pg_catalog.has_table_privilege (oid, 'SELECT');

Probably, similar errors can also occur with
"SELECT * FROM information_schema.tables" or with other
information_schema views, that call has_table_privilege().

But if it's okey to have sporadic errors on lower levels, then may be just
fix the issue on level 1. Please look at the patch attached, that makes
schema_get_xml_visible_tables(Oid nspid) isolated from changes in other
namespaces.

Best regards,
Alexander
--------------bLuz8awR8h0XcGGgQRHM5JcV
Content-Type: text/x-patch; charset=UTF-8;
 name="isolate-schema_get_xml_visible_tables.patch"
Content-Disposition: attachment;
 filename="isolate-schema_get_xml_visible_tables.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC94bWwuYyBiL3NyYy9iYWNrZW5k
L3V0aWxzL2FkdC94bWwuYwppbmRleCA4NjZkMGQ2NDlhLi41YjYyOTFjNmM5IDEwMDY0NAot
LS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQveG1sLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRp
bHMvYWR0L3htbC5jCkBAIC0yNzIzLDEyICsyNzIzLDEzIEBAIHNjaGVtYV9nZXRfeG1sX3Zp
c2libGVfdGFibGVzKE9pZCBuc3BpZCkKIAlTdHJpbmdJbmZvRGF0YSBxdWVyeTsKIAogCWlu
aXRTdHJpbmdJbmZvKCZxdWVyeSk7Ci0JYXBwZW5kU3RyaW5nSW5mbygmcXVlcnksICJTRUxF
Q1Qgb2lkIEZST00gcGdfY2F0YWxvZy5wZ19jbGFzcyIKKwlhcHBlbmRTdHJpbmdJbmZvKCZx
dWVyeSwgIlNFTEVDVCBvaWQgRlJPTSAiCisJCQkJCSAiIChTRUxFQ1Qgb2lkLCByZWxuYW1l
IEZST00gcGdfY2F0YWxvZy5wZ19jbGFzcyIKIAkJCQkJICIgV0hFUkUgcmVsbmFtZXNwYWNl
ID0gJXUgQU5EIHJlbGtpbmQgSU4gKCIKIAkJCQkJIENwcEFzU3RyaW5nMihSRUxLSU5EX1JF
TEFUSU9OKSAiLCIKIAkJCQkJIENwcEFzU3RyaW5nMihSRUxLSU5EX01BVFZJRVcpICIsIgot
CQkJCQkgQ3BwQXNTdHJpbmcyKFJFTEtJTkRfVklFVykgIikiCi0JCQkJCSAiIEFORCBwZ19j
YXRhbG9nLmhhc190YWJsZV9wcml2aWxlZ2UgKG9pZCwgJ1NFTEVDVCcpIgorCQkJCQkgQ3Bw
QXNTdHJpbmcyKFJFTEtJTkRfVklFVykgIikgT0ZGU0VUIDApIGMiCisJCQkJCSAiIFdIRVJF
IHBnX2NhdGFsb2cuaGFzX3RhYmxlX3ByaXZpbGVnZSAob2lkLCAnU0VMRUNUJykiCiAJCQkJ
CSAiIE9SREVSIEJZIHJlbG5hbWU7IiwgbnNwaWQpOwogCiAJcmV0dXJuIHF1ZXJ5X3RvX29p
ZF9saXN0KHF1ZXJ5LmRhdGEpOwo=

--------------bLuz8awR8h0XcGGgQRHM5JcV--