Thread: ERROR: could not open relation with OID XXXX
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
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.
On Sun, Aug 25, 2024 at 9:42 AM Marcelo Zabani <mzabani@gmail.com> 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):
[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 easier to reproduce.
What version (including minor number)?
Is there something I can do to avoid this? Is my understanding of how the 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!
> 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/Database/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 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Sun, Aug 25, 2024 at 9:42 AM Marcelo Zabani <mzabani@gmail.com> 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):[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 easier to reproduce.What version (including minor number)?Is there something I can do to avoid this? Is my understanding of how the 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!
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
> 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@vondra.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
On 8/25/24 17:36, Marcelo Zabani wrote: >> 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. > Yeah, I tried that with a superuser, I didn't realize it'd fail for regular users who don't have rights to modify pg_class directly. > 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? I have no idea what you mean - what would be filtered out, etc. But you can give it a try - you have a reproducer, so testing if it fails should not be that difficult. Other than that, I think you can do two things: 1) Make the application to simply retry in it sees this error. 2) Lock the objects using LOCK TABLE before running the query. regards -- Tomas Vondra
On 8/25/24 08:36, Marcelo Zabani wrote: > > 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. > As I understand it this issue came up in: https://github.com/mzabani/codd I would think that the queries in that case would be running as a superuser in order to do the migrations. -- Adrian Klaver adrian.klaver@aklaver.com
Thank you all for your comments.
> I would think that the queries in that case would be running as a
superuser in order to do the migrations.
superuser in order to do the migrations.
Users of codd can choose the role that applies their migrations. Codd even supports individual migrations running with ad-hoc users (so that a migration can use the _postgres_ user to create the application's database, for example) and users are free to add statements like `SET ROLE` inside their migrations, too. So it's sadly not possible AFAICT to force superuser onto them.
But I think I have plenty of things to try to avoid this problem, from retrying like Tomas suggested to materialized CTEs that filter out temporary relations before functions like pg_get_indexdef are called.
I will give these things a shot.
Regards.
On Sun, Aug 25, 2024 at 1:13 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/25/24 08:36, Marcelo Zabani wrote:
> > 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.
>
As I understand it this issue came up in:
https://github.com/mzabani/codd
I would think that the queries in that case would be running as a
superuser in order to do the migrations.
--
Adrian Klaver
adrian.klaver@aklaver.com