MIME-Version: 1.0
From: Marcelo Zabani <mzabani@gmail.com>
Date: Sun, 25 Aug 2024 10:42:33 -0300
Message-ID: <CACgY3QYe3mqMkODOzwx=EN4iGoddObUotLL8hGnCg==3vmxd_g@mail.gmail.com>
Subject: ERROR: could not open relation with OID XXXX
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e8408d0620822eaf"
Archived-At: <https://www.postgresql.org/message-id/CACgY3QYe3mqMkODOzwx%3DEN4iGoddObUotLL8hGnCg%3D%3D3vmxd_g%40mail.gmail.com>
Precedence: bulk

--000000000000e8408d0620822eaf
Content-Type: text/plain; charset="UTF-8"

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 until
you see ERROR:  could not open relation with OID XXXX):

#!/usr/bin/env bash
psql -c "create table test(x serial primary key); select oid, relname from
pg_class where relname='test'"
# The next two queries will run concurrently
psql -c "select oid, relname, pg_sleep(3), pg_get_indexdef(oid) from
pg_class join pg_index on indexrelid=pg_class.oid WHERE
relname='test_pkey';" 2>&1 1>/tmp/pgbug.log &
sleep 1
psql -c "drop table test"
cat /tmp/pgbug.log
wait

I am confused as to how this is possible. I assume if the row with the
test_pkey index exists in the pg_index catalog table, that the snapshot of
the catalog tables contains the test table itself and is generally
consistent, so querying the catalog should not run into such errors.

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 easier
to reproduce.

Is there something I can do to avoid this? Is my understanding of how the
catalog tables work wrong?

Thanks,
Marcelo.

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

<div dir=3D"ltr"><div>Hi all,</div><div><br></div><div>I can reproduce the =
error in the subject from time to time when querying catalog tables while D=
DL is happening concurrently. Here&#39;s a bash script that reproduces it (=
not always, you might have to run it many times until you see ERROR: =C2=A0=
could not open relation with OID XXXX):<br></div><div><br></div><div>#!/usr=
/bin/env bash<br>psql -c &quot;create table test(x serial primary key); sel=
ect oid, relname from pg_class where relname=3D&#39;test&#39;&quot;<br># Th=
e next two queries will run concurrently<br>psql -c &quot;select oid, relna=
me, pg_sleep(3), pg_get_indexdef(oid) from pg_class join pg_index on indexr=
elid=3Dpg_class.oid WHERE relname=3D&#39;test_pkey&#39;;&quot; 2&gt;&amp;1 =
1&gt;/tmp/pgbug.log &amp;<br>sleep 1<br>psql -c &quot;drop table test&quot;=
<br>cat /tmp/pgbug.log<br>wait</div><div><br></div><div>I am confused as to=
 how this is possible. I assume if the row with the test_pkey index exists =
in the pg_index catalog table, that the snapshot of the catalog tables cont=
ains the test table itself and is generally consistent, so querying the cat=
alog should not run into such errors.</div><div><br></div><div>I&#39;ve see=
n this happen in Production without pg_sleep in the mix, too. I added pg_sl=
eep to the example above only because it makes the error easier to reproduc=
e.</div><div><br></div><div>Is there something I can do to avoid this? Is m=
y understanding of how the catalog tables work wrong?<br></div><div><br></d=
iv><div>Thanks,</div><div>Marcelo.<br></div></div>

--000000000000e8408d0620822eaf--