Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFDzn-000CgU-ED for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Jun 2024 14:21:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sFDzl-000gOT-WD for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Jun 2024 14:21:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFDzl-000gOL-LY for pgsql-hackers@lists.postgresql.org; Thu, 06 Jun 2024 14:21:38 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sFDzj-000J7o-PZ for pgsql-hackers@postgresql.org; Thu, 06 Jun 2024 14:21:37 +0000 Received: by mail-pj1-x1035.google.com with SMTP id 98e67ed59e1d1-2bfdae7997aso856266a91.2 for ; Thu, 06 Jun 2024 07:21:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717683694; x=1718288494; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=jFKuhLvsF2ZS6QWkFDRxfrRm9SUzMcB8MMPZZO3lrgc=; b=F9s8PnfGIa9roT6JwvXelk0ITP6N2Iry+pg2uikuDOnEOmMxOJaDFpXESiNZUnYJ3Z +YgBrK91SFaXYglWJeATnFNeIPCqwC0TteswqMmGGhhJ8wTY7+4CdlS93Zi7Aj11qisk WBi8ewApeTd7y2EUOysU29V1RNZSfcxf7lLsUT72z2HH0SJWE1h20rmfFiHD8ZfmZFjV XVkUmRjnFp7KEbeXf1qdwlJd+ng4WQlZTASfnQ05BfBKX6KFRfydn637D4GbUzQCQ1hV +Az3FBNEt0Snzzar4eoB9GoaPQfZ2EdXfni7QJKKKQgsQP0lhMIZfqn6+zx+9GLY+373 AKIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717683694; x=1718288494; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=jFKuhLvsF2ZS6QWkFDRxfrRm9SUzMcB8MMPZZO3lrgc=; b=uxFoFnmeNgpgOo28ZXD3MRRBeUs085TGEqNzgQeVn5t4jkz5vQ2lULvwMCjxDGQf4Q +KeNyg5EpnJjeh9HSaSLDHHvm03sVfxnAEuZunUXxZqC8U02TO8XSDzeheK+kM2nclkj EpNb56qppGThYlZD+TSfWT5PMxAwobeJiWJq3bC+/FewM7iuk0dg/368Eyu++sXT4/GH 6dYcm7cfkp8/h/vYc9+7BTH5oJY44X8+V1WPBiIIWlRoAmMKGq4/pPLdjgeXwNy3q+tP hPYNQ3fQN2W8djmuqc+4Rp7W0p0REmy/smcnCgMazwwn3CLwxK6CnFoB5MjcTBGUvyF7 0ALg== X-Gm-Message-State: AOJu0YxOFfstP1xSRjU9J8nhS1Y75ZnO/fT+o2KH61ExuZtKuW4AwFoR RgZIKfXvm6PfP28xApGSqS1Mik+hfpuX0LQms1UtcAAyNmCO6xDzcG7IvaIupzjwrT6wN6pvzJJ YM7ZIknQz2r8nOoh5NKL0+d/csLc= X-Google-Smtp-Source: AGHT+IFolZHGTpytjrBx1MRZttK+R4GXpRwXfMe9P01N2ng7zX4CYtQBcTiOQfi9HDCHO6U5Xaxb0LTEbGNuVHqvk6U= X-Received: by 2002:a17:90b:46d3:b0:2c2:852a:e663 with SMTP id 98e67ed59e1d1-2c2852aecfamr4371192a91.43.1717683693739; Thu, 06 Jun 2024 07:21:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ashutosh Sharma Date: Thu, 6 Jun 2024 19:51:22 +0530 Message-ID: Subject: Re: How about using dirty snapshots to locate dependent objects? To: Bertrand Drouvot Cc: pgsql-hackers Content-Type: multipart/alternative; boundary="00000000000066e544061a396609" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000066e544061a396609 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jun 6, 2024 at 6:57=E2=80=AFPM Bertrand Drouvot < bertranddrouvot.pg@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 clari= fy > > 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. --00000000000066e544061a396609 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, Jun 6, 2024 at 6:57=E2=80=AFP= M Bertrand Drouvot <bert= randdrouvot.pg@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<= br> > implies that if a new dependent object is inserted within a transactio= n
> that is still ongoing, our search for dependent objects won't incl= ude this
> recently added one. Consequently, if someone attempts to drop the
> referenced object, it will be dropped, and when the ongoing transactio= n
> 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 f= ails
> 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 se= e. Thanks for sharing this. I can take a look at this and help in whatever = way I can.

With Regards,
Ashutosh Sharma= .
--00000000000066e544061a396609--