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 1siEoG-000wpR-CI for pgsql-general@arkaria.postgresql.org; Sun, 25 Aug 2024 15:05: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 1siEoC-00FwDZ-RO for pgsql-general@arkaria.postgresql.org; Sun, 25 Aug 2024 15:05:37 +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 1siEoC-00FwDR-Db for pgsql-general@lists.postgresql.org; Sun, 25 Aug 2024 15:05:37 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1siEo9-001R77-UA for pgsql-general@lists.postgresql.org; Sun, 25 Aug 2024 15:05:36 +0000 Received: by mail-ej1-x62f.google.com with SMTP id a640c23a62f3a-a869332c2c2so513695066b.0 for ; Sun, 25 Aug 2024 08:05:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724598334; x=1725203134; darn=lists.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=fCTo03v0FhqIpOsrr2RjoSb0+OFVSlvdosTf17llUpg=; b=E8jcIM8FCioITQ6XG0K0EcOLm6AAYa05dfOvydYJsxCDPeNdD2TyVSJa2W69E6gEnn iObnhOo8b134b6VGsJVOVuposZT+oa++Yc6MLTnoIKlm3/hmlud5rZEhVW8pXzTFWwnQ QGuPHfXiKFr6qvx9tVt5871qupOLOGVqwthPbAuzHDcJ6DZodCwghgyOjduJOBgnZm1d wHbUOZk+2d3LUbzoQDGfnK4dhENnZoz+qtI5aiLJa6B0vP/W4LB2Qj8U/wVOU3GlgYi6 cqt2yyWRI+PydEQFFOMg1yNdFa5c36Wfgdx0FdQHFcM4a8c8On0imIPC1pw+OK18HSO3 PQcw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724598334; x=1725203134; 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=fCTo03v0FhqIpOsrr2RjoSb0+OFVSlvdosTf17llUpg=; b=Tl29ccuH+ZWp0p8e0tVJohixA2IIzbt0GLpDAIqmwDZ3LvhQ1cwMIw9IcsIPaVVEuB NrnxpoTO84WPlXZVJw2axzJLJtICzq/SBN2lJi+1/vfT8zxU3XAM+Gj7309WCdly2Gxk Z3ZAIkhqb4tws4u25t1erzTg0ebDNvuvTB2ODrnU0TZGKm3sbijHw0rikyYMJZmIFUi7 uket4/I0EMGHEbHNX15pDhzSQw6FELGXFtior3b2j74iGb+7byHnIEyBQocAv3LDKtAH h413mj21SuAJqS1+qTQE41Aju0+dJHcfeFnnMrDtZpMCkn3JcujmQdPEER63ZwujvxB/ 5Iyw== X-Gm-Message-State: AOJu0YzxkOVfotS49St11MRWbkb2jsaKqTBuR9zYJY3nKwBhEIbjM/yK DelOGD8OjbbmWFYiXQbErtAhYiB2B8xtdjcGkIuNH7I5+4smACQhb0/JeLu8Hgj70QbuR9dEP0S +BxBUjIuMmc1wwq6ZYo+DjF+3soM= X-Google-Smtp-Source: AGHT+IF51+qpkz9BsiA2ksUzJCM0zIhJY29XzIxLd4TUd7vRnbAbkF/d93VtiWoRzBtHDhKeCr7QnfiKoa9/hTDrz7c= X-Received: by 2002:a17:907:1c8e:b0:a75:7a8:d70c with SMTP id a640c23a62f3a-a86a2f14394mr933241966b.4.1724598333440; Sun, 25 Aug 2024 08:05:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Marcelo Zabani Date: Sun, 25 Aug 2024 12:05:22 -0300 Message-ID: Subject: Re: ERROR: could not open relation with OID XXXX To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000000b8d4c0620835788" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000b8d4c0620835788 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > What version (including minor number)? On my computer I can reproduce the error with v16.1. At my job I know it's v15 but I can't access it right now so don't know the minor version. In any case, at my job it's much bigger queries we run; I'll elaborate why below. > Just out of curiosity, *WHY* do you do this? It's never occurred to me to do that; maybe it's something useful that I've been overlooking. A project developed by me, codd - https://github.com/mzabani/codd - applies postgresql migrations and checks tables, columns names/order/types, indexes, etc. ("the full schema") to ensure databases in every environment match what developers have on their computers. This is why the queries are bigger, e.g. https://github.com/mzabani/codd/blob/master/src/Codd/Representations/Databa= se/Pg12.hs#L523-L540 One yet untested hypothesis is that codd is picking up temporary tables when we deploy that are soon destroyed by the application, and that maybe postgres is running into this error because it executes some of these functions _before_ filtering out temporary relations (WHERE relpersistence <> 't'). This might be possible depending on query plan, I believe. But again, an untested hypothesis. On Sun, Aug 25, 2024 at 11:31=E2=80=AFAM Ron Johnson wrote: > On Sun, Aug 25, 2024 at 9:42=E2=80=AFAM Marcelo Zabani wrote: > >> Hi all, >> >> I can reproduce the error in the subject from time to time when querying >> catalog tables while DDL is happening concurrently. Here's a bash script >> that reproduces it (not always, you might have to run it many times unti= l >> you see ERROR: could not open relation with OID XXXX): >> > [snip] > >> I've seen this happen in Production without pg_sleep in the mix, too. I >> added pg_sleep to the example above only because it makes the error easi= er >> to reproduce. >> > > What version (including minor number)? > > >> Is there something I can do to avoid this? Is my understanding of how th= e >> catalog tables work wrong? >> > > Just out of curiosity, *WHY* do you do this? It's never occurred to me > to do that; maybe it's something useful that I've been overlooking. > > -- > Death to America, and butter sauce. > Iraq lobster! > --0000000000000b8d4c0620835788 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> What version (including minor number)?

On my computer I can reproduce the error with v16.1. At my = job I know it's v15 but I can't access it right now so don't kn= ow the minor version. In any case, at my job it's much bigger queries w= e run; I'll elaborate why below.

> Just out= of curiosity, WHY=C2=A0do you do this?=C2=A0 It's never occurre= d to me to do that; maybe it's something useful that I've been over= looking.

A project developed by me, codd - https://github.com/mzabani/codd - = applies postgresql migrations and checks tables, columns names/order/types,= indexes, etc. ("the full schema") to ensure databases in every e= nvironment match what developers have on their computers. This is why the q= ueries are bigger, e.g. https://github.com= /mzabani/codd/blob/master/src/Codd/Representations/Database/Pg12.hs#L523-L5= 40

One yet untested hypothesis is that codd is= picking up temporary tables when we deploy that are soon destroyed by the = application, and that maybe postgres is running into this error because it = executes some of these functions _before_ filtering out temporary relations= (WHERE relpersistence <> 't'). This might be possible depend= ing on query plan, I believe. But again, an untested hypothesis.
<= /div>
O= n Sun, Aug 25, 2024 at 11:31=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Sun, Aug 25, 2024 at 9:42=E2=80=AFAM Marcelo Zabani <mzabani@gmail.com> wrot= e:
Hi all,

I can = reproduce the error in the subject from time to time when querying catalog = tables while DDL is happening concurrently. Here's a bash script that r= eproduces it (not always, you might have to run it many times until you see= ERROR: =C2=A0could not open relation with OID XXXX):
[snip]=C2=A0
I've seen this happen in Production w= ithout pg_sleep in the mix, too. I added pg_sleep to the example above only= because it makes the error easier to reproduce.

What version (including minor number)?
=C2=A0
Is there something I can do to avoid this? Is my understanding of how the = catalog tables work wrong?

Just out of curiosity, WHY=C2=A0do you do this?=C2=A0 It's nev= er occurred to me to do that; maybe it's something useful that I've= been overlooking.

--
Death to America, and butter sauce.
Iraq lobster!
--0000000000000b8d4c0620835788--