Обсуждение: [GENERAL] Possible bug: could not open relation with OID [numbers]SQL State: XX000

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

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

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

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

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

> 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

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:

   analyze verbose temp_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'));

It's pointing to the toast 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 106541

Thank 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.