Re: How about using dirty snapshots to locate dependent objects?

On Thu, Jun 6, 2024 at 6:57 PM Bertrand Drouvot <
bertranddrouvot(dot)pg(at)gmail(dot)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.