Re: ERROR: could not open relation with OID XXXX

> 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 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:

> On 8/25/24 15:42, 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
> > 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 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
>