MIME-Version: 1.0
From: Ashutosh Sharma <ashu.coek88@gmail.com>
Date: Thu, 6 Jun 2024 17:59:00 +0530
Message-ID: <CAE9k0Pnznev=s0t=M61fiO4==cxsxt4nbTxe42zzEJscaekwVA@mail.gmail.com>
Subject: How about using dirty snapshots to locate dependent objects?
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000851ab3061a37d4c3"
Archived-At: <https://www.postgresql.org/message-id/CAE9k0Pnznev%3Ds0t%3DM61fiO4%3D%3Dcxsxt4nbTxe42zzEJscaekwVA%40mail.gmail.com>
Precedence: bulk

--000000000000851ab3061a37d4c3
Content-Type: multipart/alternative; boundary="000000000000851ab2061a37d4c1"

--000000000000851ab2061a37d4c1
Content-Type: text/plain; charset="UTF-8"

Hello everyone,

At present, we use MVCC snapshots to identify dependent objects. This
implies that if a new dependent object is inserted within a transaction
that is still ongoing, our search for dependent objects won't include this
recently added one. Consequently, if someone attempts to drop the
referenced object, it will be dropped, and when the ongoing transaction
completes, we will end up having an entry for a referenced object that has
already been dropped. This situation can lead to an inconsistent state.
Below is an example illustrating this scenario:

Session 1:
- create table t1(a int);
- insert into t1 select i from generate_series(1, 10000000) i;
- create extension btree_gist;
- create index i1 on t1 using gist( a );

Session 2: (While the index creation in session 1 is in progress, drop the
btree_gist extension)
- drop extension btree_gist;

Above command succeeds and so does the create index command running in
session 1, post this, if we try running anything on table t1, i1, it fails
with an error: "cache lookup failed for opclass ..."

Attached is the patch that I have tried, which seems to be working for me.
It's not polished and thoroughly tested, but just sharing here to clarify
what I am trying to suggest. Please have a look and let me know your
thoughts.

--
With Regards,
Ashutosh Sharma.

--000000000000851ab2061a37d4c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello everyone,<br><br>At present, we use MVCC snapshots t=
o identify dependent objects. This implies that if a new dependent object i=
s inserted within a transaction that is still ongoing, our search for depen=
dent objects won&#39;t include this recently added one. Consequently, if so=
meone attempts to drop the referenced object, it will be dropped, and when =
the ongoing transaction completes, we will end up having an entry for a ref=
erenced object that has already been dropped. This situation can lead to an=
 inconsistent state. Below is an example illustrating this scenario:<br><br=
>Session 1:<br>- create table t1(a int);<br>- insert into t1 select i from =
generate_series(1, 10000000) i;<br>- create extension btree_gist;<br>- crea=
te index i1 on t1 using gist( a );<br><br>Session 2: (While the index creat=
ion in session 1 is in progress, drop the btree_gist extension)<br>- drop e=
xtension btree_gist; <br><br>Above command succeeds and so does the create =
index command running in session 1, post this, if we try running anything o=
n table t1, i1, it fails with an error: &quot;cache lookup failed for opcla=
ss ...&quot;<br><br>Attached is the patch that I have tried, which seems to=
 be working for me. It&#39;s not polished and thoroughly tested, but just s=
haring here to clarify what I am trying to suggest. Please have a look and =
let me know your thoughts.<br><div><br></div><div>--</div><div>With Regards=
,</div><div>Ashutosh Sharma.</div></div>

--000000000000851ab2061a37d4c1--
--000000000000851ab3061a37d4c3
Content-Type: application/octet-stream; 
	name="use-dirty-snapshots-to-find-dependent-objects.patch"
Content-Disposition: attachment; 
	filename="use-dirty-snapshots-to-find-dependent-objects.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lx38f3yi0>
X-Attachment-Id: f_lx38f3yi0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvZGVwZW5kZW5jeS5jIGIvc3JjL2JhY2tl
bmQvY2F0YWxvZy9kZXBlbmRlbmN5LmMKaW5kZXggZDRiNWIyYWRlMS4uNzhhNzNjMmU5YSAxMDA2
NDQKLS0tIGEvc3JjL2JhY2tlbmQvY2F0YWxvZy9kZXBlbmRlbmN5LmMKKysrIGIvc3JjL2JhY2tl
bmQvY2F0YWxvZy9kZXBlbmRlbmN5LmMKQEAgLTQ0OSw2ICs0NDksNyBAQCBmaW5kRGVwZW5kZW50
T2JqZWN0cyhjb25zdCBPYmplY3RBZGRyZXNzICpvYmplY3QsCiAJaW50CQkJbWF4RGVwZW5kZW50
T2JqZWN0czsKIAlPYmplY3RBZGRyZXNzU3RhY2sgbXlzdGFjazsKIAlPYmplY3RBZGRyZXNzRXh0
cmEgZXh0cmE7CisJU25hcHNob3REYXRhIFNuYXBzaG90RGlydHk7CiAKIAkvKgogCSAqIElmIHRo
ZSB0YXJnZXQgb2JqZWN0IGlzIGFscmVhZHkgYmVpbmcgdmlzaXRlZCBpbiBhbiBvdXRlciByZWN1
cnNpb24KQEAgLTgyMyw4ICs4MjQsMTQgQEAgZmluZERlcGVuZGVudE9iamVjdHMoY29uc3QgT2Jq
ZWN0QWRkcmVzcyAqb2JqZWN0LAogCWVsc2UKIAkJbmtleXMgPSAyOwogCisJLyoKKwkgKiBXZSB1
c2UgYSAiZGlydHkgc25hcHNob3QiIHRvIHJlYWQgcm93cyBtb2RpZmllZCBieSB0cmFuc2FjdGlv
bnMgc3RpbGwKKwkgKiBpbi1wcm9ncmVzcywgd2hpY2ggd291bGRuJ3QgYmUgdmlzaWJsZSB3aXRo
IG5vcm1hbCBzbmFwc2hvdHMuCisJICovCisJSW5pdERpcnR5U25hcHNob3QoU25hcHNob3REaXJ0
eSk7CisKIAlzY2FuID0gc3lzdGFibGVfYmVnaW5zY2FuKCpkZXBSZWwsIERlcGVuZFJlZmVyZW5j
ZUluZGV4SWQsIHRydWUsCi0JCQkJCQkJICBOVUxMLCBua2V5cywga2V5KTsKKwkJCQkJCQkgICZT
bmFwc2hvdERpcnR5LCBua2V5cywga2V5KTsKIAogCXdoaWxlIChIZWFwVHVwbGVJc1ZhbGlkKHR1
cCA9IHN5c3RhYmxlX2dldG5leHQoc2NhbikpKQogCXsK
--000000000000851ab3061a37d4c3--