On Thu, Jun 6, 2024 at 6:57 PM Bertrand Drouvot < bertranddrouvot...@gmail.com> wrote:
> On Thu, Jun 06, 2024 at 05:59:00PM +0530, Ashutosh Sharma wrote: > > 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. > > Thanks for the patch proposal! > > The patch does not fix the other way around: > > - session 1: BEGIN; DROP extension btree_gist; > - session 2: create index i1 on t1 using gist( a ); > - session 1: commits while session 2 is creating the index > > and does not address all the possible orphaned dependencies cases. > > There is an ongoing thread (see [1]) to fix the orphaned dependencies > issue. > > v9 attached in [1] fixes the case you describe here. > > [1]: > https://www.postgresql.org/message-id/flat/ZiYjn0eVc7pxVY45%40ip-10-97-1-34.eu-west-3.compute.internal I see. Thanks for sharing this. I can take a look at this and help in whatever way I can. With Regards, Ashutosh Sharma.
