MIME-Version: 1.0
References: <CACgY3QYe3mqMkODOzwx=EN4iGoddObUotLL8hGnCg==3vmxd_g@mail.gmail.com>
 <73bc7752-442c-4c67-a263-46017f1948cc@vondra.me>
In-Reply-To: <73bc7752-442c-4c67-a263-46017f1948cc@vondra.me>
From: Marcelo Zabani <mzabani@gmail.com>
Date: Sun, 25 Aug 2024 12:36:18 -0300
Message-ID: <CACgY3QYdsYyZQR0hDYFLrk42OJcNOkbW4nfmrauN7byad=6f0A@mail.gmail.com>
Subject: Re: ERROR: could not open relation with OID XXXX
To: Tomas Vondra <tomas@vondra.me>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000aca237062083c51c"
Archived-At: <https://www.postgresql.org/message-id/CACgY3QYdsYyZQR0hDYFLrk42OJcNOkbW4nfmrauN7byad%3D6f0A%40mail.gmail.com>
Precedence: bulk

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

> we do some special stuff for catalogs

That is good to know, thanks!

> I believe you could actually lock the pg_class rows for update. Just add
FOR UPDATE at the end of the query.

Thanks, but I tried that and got "ERROR:  permission denied for table
pg_class", even if I try it only for tables the user owns.

At least considering the use-case of avoiding this error due to temporary
tables/indexes (which are a part of normal application execution), I was
thinking of using materialized CTEs that filters those out, and only after
that using other functions that for example take OIDs and return
definitions. Other kinds of DDL that create non-temporary tables can be
"blamed" on developers in my case.
Do you think using those materialized CTEs could help? And do you think
this can be considered a bug that I should report or is it just too
edge-casey to consider?

Regards.

On Sun, Aug 25, 2024 at 12:06=E2=80=AFPM Tomas Vondra <tomas@vondra.me> wro=
te:

> On 8/25/24 15:42, Marcelo Zabani wrote:
> > Hi all,
> >
> > I can reproduce the error in the subject from time to time when queryin=
g
> > catalog tables while DDL is happening concurrently. Here's a bash scrip=
t
> > 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=3D'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=3Dpg_class.oid WHERE
> > relname=3D'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 think you're assuming the whole query runs with a single snapshot, and
> AFAIK that's not quite accurate - we do some special stuff for catalogs,
> for example. There's also the additional complexity of maintaining a
> cache on catalogs, invalidating it, etc.
>
> I don't have a great simple "this happens because X" explanation, but a
> lot of this relies on proper locking - in particular, that we lock all
> the objects before execution, which also invalidates all the caches etc.
>
> But that can't happen here, because we only realize we need to access
> the OID very late in the execution, when we get to pg_get_indexdef.
>
> > 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.
> >
>
> It's a race condition, essentially. The sleep just makes it easier to
> hit, but it can happen without it.
>
> > Is there something I can do to avoid this? Is my understanding of how
> > the catalog tables work wrong?
> >
>
> I believe you could actually lock the pg_class rows for update. Just add
> FOR UPDATE at the end of the query.
>
>
> regards
>
> --
> Tomas Vondra
>

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

<div dir=3D"ltr"><div>&gt; we do some special stuff for catalogs</div><div>=
<br></div><div>That is good to know, thanks!</div><div><br></div><div>&gt; =
I believe you could actually lock the pg_class rows for update. Just add FO=
R UPDATE at the end of the query.</div><div><br></div><div>Thanks, but I tr=
ied that and got &quot;ERROR: =C2=A0permission denied for table pg_class&qu=
ot;, even if I try it only for tables the user owns.<br></div><div><br></di=
v><div>At least considering the use-case of avoiding this error due to temp=
orary tables/indexes (which are a part of normal application execution), I =
was thinking of using materialized CTEs that filters those out, and only af=
ter that using other functions that for example take OIDs and return defini=
tions. Other kinds of DDL that create non-temporary tables can be &quot;bla=
med&quot; on developers in my case.</div><div>Do you think using those mate=
rialized CTEs could help? And do you think this can be considered a bug tha=
t I should report or is it just too edge-casey to consider?</div><div><br><=
/div><div>Regards.<br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Sun, Aug 25, 2024 at 12:06=E2=80=AFPM Toma=
s Vondra &lt;<a href=3D"mailto:tomas@vondra.me">tomas@vondra.me</a>&gt; wro=
te:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 8/25/24 1=
5:42, Marcelo Zabani wrote:<br>
&gt; Hi all,<br>
&gt; <br>
&gt; I can reproduce the error in the subject from time to time when queryi=
ng<br>
&gt; catalog tables while DDL is happening concurrently. Here&#39;s a bash =
script<br>
&gt; that reproduces it (not always, you might have to run it many times<br=
>
&gt; until you see ERROR: =C2=A0could not open relation with OID XXXX):<br>
&gt; <br>
&gt; #!/usr/bin/env bash<br>
&gt; psql -c &quot;create table test(x serial primary key); select oid, rel=
name<br>
&gt; from pg_class where relname=3D&#39;test&#39;&quot;<br>
&gt; # The next two queries will run concurrently<br>
&gt; psql -c &quot;select oid, relname, pg_sleep(3), pg_get_indexdef(oid) f=
rom<br>
&gt; pg_class join pg_index on indexrelid=3Dpg_class.oid WHERE<br>
&gt; relname=3D&#39;test_pkey&#39;;&quot; 2&gt;&amp;1 1&gt;/tmp/pgbug.log &=
amp;<br>
&gt; sleep 1<br>
&gt; psql -c &quot;drop table test&quot;<br>
&gt; cat /tmp/pgbug.log<br>
&gt; wait<br>
&gt; <br>
&gt; I am confused as to how this is possible. I assume if the row with the=
<br>
&gt; test_pkey index exists in the pg_index catalog table, that the snapsho=
t<br>
&gt; of the catalog tables contains the test table itself and is generally<=
br>
&gt; consistent, so querying the catalog should not run into such errors.<b=
r>
&gt; <br>
<br>
I think you&#39;re assuming the whole query runs with a single snapshot, an=
d<br>
AFAIK that&#39;s not quite accurate - we do some special stuff for catalogs=
,<br>
for example. There&#39;s also the additional complexity of maintaining a<br=
>
cache on catalogs, invalidating it, etc.<br>
<br>
I don&#39;t have a great simple &quot;this happens because X&quot; explanat=
ion, but a<br>
lot of this relies on proper locking - in particular, that we lock all<br>
the objects before execution, which also invalidates all the caches etc.<br=
>
<br>
But that can&#39;t happen here, because we only realize we need to access<b=
r>
the OID very late in the execution, when we get to pg_get_indexdef.<br>
<br>
&gt; I&#39;ve seen this happen in Production without pg_sleep in the mix, t=
oo. I<br>
&gt; added pg_sleep to the example above only because it makes the error<br=
>
&gt; easier to reproduce.<br>
&gt; <br>
<br>
It&#39;s a race condition, essentially. The sleep just makes it easier to<b=
r>
hit, but it can happen without it.<br>
<br>
&gt; Is there something I can do to avoid this? Is my understanding of how<=
br>
&gt; the catalog tables work wrong?<br>
&gt; <br>
<br>
I believe you could actually lock the pg_class rows for update. Just add<br=
>
FOR UPDATE at the end of the query.<br>
<br>
<br>
regards<br>
<br>
-- <br>
Tomas Vondra<br>
</blockquote></div>

--000000000000aca237062083c51c--