Thread: [GENERAL] Possible bug: could not open relation with OID [numbers]SQL State: XX000
[GENERAL] Possible bug: could not open relation with OID [numbers]SQL State: XX000
From
Adam Brusselback
Date:
Hey all, First off: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit I have something going on, and i'm not sure what is causing it. I recently upgraded our development environment to PG10, and the error in the subject appeared with one of my analytical functions. It creates some temporary tables, joins them together, and then spits out a result. If I run it for one "contract_id", it'll work just fine, then I run it for another similar "contract_id", it'll throw the error in the subject. I attached the function. Any help would be appreciated. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Attachment
Re: [GENERAL] Possible bug: could not open relation with OID[numbers] SQL State: XX000
From
Justin Pryzby
Date:
On Wed, Nov 01, 2017 at 04:11:07PM -0400, Adam Brusselback wrote: > I have something going on, and i'm not sure what is causing it. I > recently upgraded our development environment to PG10, and the error > in the subject appeared with one of my analytical functions. What relation is that ? I guess it's harder to know since it's within a function, but could you add NOTICE for all the relations you're outputting ? Something like ts=# SELECT 'alarms'::regclass::oid; oid | 19575 Also, if you have log_statement=all (and maybe log_destination=stderr,csvlog), can you send the log fragment for the line with error_severity='ERROR' ? https://www.postgresql.org/docs/current/static/runtime-config-logging.html#runtime-config-logging-csvlog Justin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible bug: could not open relation with OID[numbers] SQL State: XX000
From
Adam Brusselback
Date:
I believe it's one of the temp tables. The oid changes each time the function is run.
I'll put some logging in place to identify the exact temp table it is though.
Re: [GENERAL] Possible bug: could not open relation with OID[numbers] SQL State: XX000
From
Adam Brusselback
Date:
Alright I figured it out. The OID does not match any of the temp tables, so not sure what's up there. I have the function RETURN QUERY, and then I drop all my temp tables. If I don't drop the tmp_base table at the end of the function, it will work just fine. If I keep the drop at the end in there, it'll blow up every time. This seriously seems like a bug to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000
From
Tom Lane
Date:
Adam Brusselback <adambrusselback@gmail.com> writes: > The OID does not match any of the temp tables, so not sure what's up there. > I have the function RETURN QUERY, > and then I drop all my temp tables. I'll bet the OID corresponds to the toast table for one of those temp tables. RETURN QUERY will stash away all the values read by the query, but it doesn't make an attempt to inline out-of-line values; so you get a failure when the out-of-line column value is eventually demanded. I think we've seen one previous complaint of the same ilk. Probably somebody will get annoyed enough to fix it at some point, but the sticking point is how to cover this corner case without causing a performance drop for normal cases. In the meantime, maybe you could make the temp tables be ON COMMIT DROP instead of dropping them explicitly mid-transaction. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible bug: could not open relation with OID[numbers] SQL State: XX000
From
Adam Brusselback
Date:
Huh, so in the other cases where the function works fine, it's likely that the data all just fits within the regular table and doesn't have to be TOAST'ed?
So this is something that isn't changed in PG10, and I could have encountered in 9.6, and just by chance didn't?
This is a pattern I've used in quite a few (at least 50) functions, so it's surprising I've not seen this issue until now.
Thanks,
-Adam
Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000
From
Tom Lane
Date:
Adam Brusselback <adambrusselback@gmail.com> writes: > Huh, so in the other cases where the function works fine, it's likely that > the data all just fits within the regular table and doesn't have to be > TOAST'ed? If that's the correct theory, yes. Did you match up the OID yet? > So this is something that isn't changed in PG10, and I could have > encountered in 9.6, and just by chance didn't? You could have encountered it anytime since TOAST was invented, or at least since RETURN QUERY was invented (the latter is newer IIRC). The fact that the bug has been there so long and has only been reported a couple of times is the main reason why I'm loath to take a brute force duplicate-the-data approach to fixing it. Such a fix would penalize many more people than it would help. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible bug: could not open relation with OID[numbers] SQL State: XX000
From
Adam Brusselback
Date:
> If that's the correct theory, yes. Did you match up the OID yet? Yes, I did just now. The OID matches the TOAST table for the temp table: contract_actual_direct. This just really surprises me I haven't seen it before considering I know for a fact that some of my other functions are way more likely to have their data stored TOASTed, and use the same DROP TABLE pattern at the end of the function. Now I suppose i'll have to figure out what to do going forward. Dropping on commit is not an option, because some of these functions need to be able to be run multiple times within a transaction. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000
From
Tom Lane
Date:
I wrote: > You could have encountered it anytime since TOAST was invented, or at > least since RETURN QUERY was invented (the latter is newer IIRC). > The fact that the bug has been there so long and has only been reported > a couple of times is the main reason why I'm loath to take a brute > force duplicate-the-data approach to fixing it. Such a fix would > penalize many more people than it would help. Just thinking idly about what a not-so-brute-force fix might look like ... I wonder if we could postpone the actual drop of toast tables to end of transaction? I'm not sure how messy that would be, or if it would have negative consequences elsewhere. But it might be an idea. We already postpone removal of the underlying disk files till end of transaction, since we don't know if a DROP TABLE will get rolled back. The idea here would be to postpone deletion of the system catalog entries for the toast table as well. I'm not likely to work on this idea myself in the near future, but if anyone else is feeling motivated to attack the problem, have at it ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
I would like to refresh the topic and add another report about the issue that just happened to me. I'm sure it's the toast table that cannot be opened inside the function. I have added following RAISE NOTICE clauses to it and run analyze inside of the function:
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
analyze verbose temp_table;It's pointing to the toast table:
raise notice 'oid temp_table %', ( SELECT array_agg(relname::TEXT|| relfilenode::TEXT|| 'relpages:'||relpages::TEXT|| 'reltuples:' || reltuples::TEXT|| 'relallvisible:' ||relallvisible::TEXT||'reltoastrelid:'|| reltoastrelid::TEXT) FROM pg_class where relname= 'temp_table');
raise notice 'rel size %', (select pg_total_relation_size('temp_table'));
1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total rows psql:/tmp/gg:23: NOTICE: oid temp_table {temp_table106538relpages:1reltuples:1relallvisible:0reltoastrelid:106541} psql:/tmp/gg:23: NOTICE: rel size 32768 psql:/tmp/gg:23: ERROR: could not open relation with OID 106541Thank you for the advice about ON COMMIT DROP - it's working. When the table size is smaller, about 16k this issue simply disappears.
Sent from the PostgreSQL - general mailing list archive at Nabble.com.