Thread: PSQLException: ERROR: could not open relation with OID xxxx
Hi,
We're using PostgreSQL v8.2.3. Ours is a web-based application, language is
Java and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, etc.).
We're running a complex report as explained below. Sometimes, the report is
so huge and involves complex calculations that it may take more than 2 hours
to get a report.
1. Database connection is opened in Java with AutoCommit set to 'true'
2. Temporary table is created using "CREATE TEMP TABLE FOO(col1, col2, ...)"
3. INSERTs/UPDATEs are performed heavily to this temporary table FOO. Of
course, this portion of logic is the one handling complex calculation, etc.
Once everything is completed, finally all rows are deleted from this table.
Hence, there will be zero rows at any point of time after completing this
portion of logic. In fact, this INS/UPD are spanned across multiple
PostgreSQL functions.
4. Temporary table is dropped using "DROP TABLE pg_temp_xx.FOO". Since
DISCARD is available only from 8.3+, I couldn't use it here. So only choice
left out for us here is to drop the temporary table explicitly. We're
creating temporary table with the same as an existing permanent table which
is available in "public" schema, that is FOO. Hence, we always qualify with
temporary schema name while dropping the table. We're using the following
query to find out the temporary schema name created in that session, so that
this can be used in DROP TABLE:
select nspname from pg_namespace where oid = pg_my_temp_schema();
5. After building the report, database connection is closed.
Now, in order to monitor the growth (table size) of temporary tables created
while report is running, we've a "separate Java standalone program" which
automatically executes the following query every 5 minutes and keeps writing
output to a separate log file, which will helps us to analyze the size
occupied by each temporary table in detail at later point of time:
SELECT schemaname, tablename, pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size FROM
pg_tables where tablename ilike '%foo%') AS TABLES ORDER BY total_size DESC
Sometimes, the following PSQLException is thrown by the above query. As I
said, this error is thrown only sometime and not always.
"org.postgresql.util.PSQLException: ERROR: could not open relation with OID
1034158"
My questions are:
1) Though I'm dropping temporary tables explicitly just before closing
database connection within my application, why this error is
raised/reported?
2) In above query, am using the function which accepts
"schemaname.tablename" as its parameter "pg_relation_size(text)" and not the
function which accepts oid as its parameter "pg_relation_size(oid)". The
error "could not open relation with OID" should only be raised when
"pg_relation_size(oid)" is used, but why this error is raised here. I even
tried to execute the following queries from "psql", by passing both table
name and oid that do not exist in my database and am getting the proper
error message:
select pg_total_relation_size('pg_temp_25.nonexistence');
ERROR: relation "pg_temp_25.nonexistence" does not exist
select pg_total_relation_size(12345);
ERROR: could not open relation with OID 12345
Regards,
Gnanam
"Gnanakumar" <gnanam@zoniac.com> wrote: > We're using PostgreSQL v8.2.3. That version is over four years old, and there have been 17 minor releases for 8.2 containing nothing except fixes for bugs and security issues since then. It's asking a lot to expect other people to remember every single bug fixed over the last four years and interpret how it might affect your problem. Please read this page: http://www.postgresql.org/support/versioning If you want to see whether any of the bugs fixed might be related to your error messages, please read the release notes for 8.2.4 to 8.2.20 to see which might affect your situation: http://www.postgresql.org/docs/8.2/static/release.html Note that there is no need for a conversion to upgrade to a new minor release, although you might need to rebuild some indexes. (See the release notes.) Also, consider that the 8.2 release is so old that it is scheduled to go out of support entirely late this year: http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy You should probably begin planning very soon for an upgrade to version 9.0 or 9.1 sometime this year. > Temporary table is dropped using "DROP TABLE pg_temp_xx.FOO". > Since DISCARD is available only from 8.3+, I couldn't use it here. > So only choice left out for us here is to drop the temporary table > explicitly. We're creating temporary table with the same as an > existing permanent table which is available in "public" schema, > that is FOO. Why in the world would you want to do that? Have you considered naming the temporary table differently, like maybe with a _tt suffix to denote "temporary table"? -Kevin
First, thanks for your advice. > If you want to see whether any of the bugs fixed might be related to > your error messages, please read the release notes for 8.2.4 to > 8.2.20 to see which might affect your situation: > http://www.postgresql.org/docs/8.2/static/release.html I read all the release notes from 8.2.4 to 8.2.20, but I don't find anything mentioned/indicated similar to my error message. > Why in the world would you want to do that? Have you considered > naming the temporary table differently, like maybe with a _tt suffix > to denote "temporary table"? Well, let me explain the long story on why we've named temporary table same as that of an existing permanent table, in short here. We came to know the advantage and feature of TEMPORARY tables only at later point of time after completely developing all PostgreSQL functions (approx. 35 functions) for running this particular complex report. Also, the existing permanent table is used for other reports as well in our application, but is not used to the extent by which this complex report uses. Instead of touching/refactoring all the 35 functions just to rename the table name, we decided to create the temporary table name same as that of existing permanent table for running this complex report alone, so that those tables are automatically dropped at the end of session and doesn't occupy any disk space also (including its indexes and toast tables, if any). Hope this makes clear.
On Sun, Mar 13, 2011 at 11:59 PM, Gnanakumar <gnanam@zoniac.com> wrote: [...] Although it's unrelated to your query about an exception being raised, a word of advice on temporary tables. (This may not be relevant to your situation, but it's no small source of pain in one of the systems I'm responsible for.) Significant use of temporary tables will cause commensurately significant bloat in your system catalogs. We have a system which, in the course of its "normal" operations, creates hundreds of temporary tables for processing, aggregation, analysis, and reporting. Consequently, the database runs with significantly bloated system catalogs, particularly pg_attribute, which, even with autovacuum running, and tuned rather aggressively, as well as frequent scheduled VACUUMs, reaches a steady-state of around 90% dead tuples. This has adverse effects on query planning performance, among other things. (psql's tab-completion taking forever to complete is the one that keeps biting me, because it works everywhere else, so I constantly forget not to do it in this db.) The only remedy is to bring the application down, stop postgres and restart it in single-user mode, and VACUUM FULL and REINDEX the affected system catalogs. I'm working with the development team to re-write the application to use normal tables, which are TRUNCATEd after use, but until that can be deployed, we're stuck with periodic downtime. rls -- :wq
> Although it's unrelated to your query about an exception being raised, > a word of advice on temporary tables. (This may not be relevant to > your situation, but it's no small source of pain in one of the systems > I'm responsible for.) Significant use of temporary tables will cause > commensurately significant bloat in your system catalogs. We have a > system which, in the course of its "normal" operations, creates > hundreds of temporary tables for processing, aggregation, analysis, > and reporting. Thank you very much for sharing your real-time experience. Do you think that this "significantly bloated system catalogs" is caused because of the old version 8.2.3 we're running? Will this be resolved in latest PostgreSQL v9.0? BTW, what version of PostgreSQL are you handling? Doc says: " Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Any indexes created on a temporary table are automatically temporary as well." If temporary tables and its indexes are automatically dropped at the end of the session, why would it cause bloat in system catalogs? I hope you can make me understand by sharing your real-time experience. Again, thanks for your advice.
"Gnanakumar" <gnanam@zoniac.com> wrote: > Instead of touching/refactoring all the 35 functions just to > rename the table name, we decided to create the temporary table > name same as that of existing permanent table for running this > complex report alone If it were me I would be running some global search and replace on the scripts which create those functions. At a bare minimum you should be running these reports under a login without rights to drop permanent production tables. (We never let applications connect with a login which can do DDL except on temporary tables.) -Kevin
[getting back to the original question]
"Gnanakumar" <gnanam@zoniac.com> wrote:
> in order to monitor the growth (table size) of temporary tables
> created while report is running, we've a "separate Java standalone
> program" which automatically executes the following query every 5
> minutes and keeps writing output to a separate log file, which
> will helps us to analyze the size occupied by each temporary table
> in detail at later point of time:
>
> SELECT schemaname, tablename, pg_size_pretty(size) AS size_pretty,
> pg_size_pretty(total_size) AS total_size_pretty FROM (SELECT *,
> pg_relation_size(schemaname||'.'||tablename) AS size,
> pg_total_relation_size(schemaname||'.'||tablename) AS total_size
> FROM pg_tables where tablename ilike '%foo%') AS TABLES ORDER BY
> total_size DESC
>
> Sometimes, the following PSQLException is thrown by the above
> query. As I said, this error is thrown only sometime and not
> always.
> "org.postgresql.util.PSQLException: ERROR: could not open relation
> with OID 1034158"
> Though I'm dropping temporary tables explicitly just before
> closing database connection within my application, why this error
> is raised/reported?
Clearly it's trying to use an OID it calculated for one of these
tables after the table has been dropped, and I suspect that the lock
is released between gathering the data and sorting it. I don't have
any 8.2 databases around to try this on, but perhaps you would avoid
it with a slight rearrangement of your monitoring query:
SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname
||'.'||tablename)) AS size_pretty,
pg_size_pretty(pg_total_relation_size(schemaname
||'.'||tablename)) AS total_size_pretty
FROM pg_tables
where tablename ilike '%foo%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
;
If that doesn't do it I might try adding zero to numbers and
concatenating empty strings to try to prevent late use of the OID.
(Essentially as a form of optimization barrier.)
You could also try a more strict isolation level, like REPEATABLE
READ, but system table access often uses a special snapshot, so that
might not matter.
-Kevin
(Apologies for thread-jacking; I saw something similar in the OP's description of his setup that I thought warranted a word of advice.) On Mon, Mar 14, 2011 at 1:50 AM, Gnanakumar <gnanam@zoniac.com> wrote: > Do you think that this "significantly bloated system catalogs" is caused > because of the old version 8.2.3 we're running? Will this be resolved in > latest PostgreSQL v9.0? BTW, what version of PostgreSQL are you handling? This isn't a version-specific issue; it's a consequence of MVCC. (See below for more details.) We're using 8.3 for this application, but we'd encounter the problem whatever version we were using. I'm working on getting it migrated to 8.4, but it's a multi-tbyte database, and we don't currently have space available on the SAN for an upgrade. 9.0 has a new VACUUM FULL which behaves more like CLUSTER. That may help to shorten the downtime windows we need to take, but it won't eliminate them. The only thing that will is changing the application to make less pathological use of temporary tables. > If temporary tables and its indexes are automatically dropped at the end of > the session, why would it cause bloat in system catalogs? The key to understanding the problem is to realize that system catalogs are pretty much regular tables in PostgreSQL, with some additional special rules (no triggers, can't be CLUSTERed or ALTERed, &c). Anything you'd do in a regular table that would create a dead tuple — UPDATEs, DELETEs, &c — will have the same effect on a catalog. A CREATE TABLE is an INSERT into pg_class for the table, plus one for each of any indexes you may create, and so on. It's also an INSERT into pg_attribute for each column the table has, including the "housekeeping" columns postgres uses, mostly to manage MVCC. DROPping a table is a DELETE from those catalogs, creating the corresponding number of dead tuples. Implicitly dropped temp tables still cause dead tuples in the catalogs; it's just postgres dropping them for you at commit time, rather than you issuing the DROP command explicitly. If you're just using a few temp tables, you shouldn't be experiencing the kinds of problems we are, particularly if you have autovacuum enabled. You'll have dead tuples, but they should quickly reach a steady-state, where the space consumed by them is re-used by a later creation of those temp tables. Our situation is probably somewhat unique, because of the sheer number of temp tables the application uses. I hope that helps you understand better. rls -- :wq
> The key to understanding the problem is to realize that system > catalogs are pretty much regular tables in PostgreSQL, with some > additional special rules (no triggers, can't be CLUSTERed or ALTERed, > &c). Anything you'd do in a regular table that would create a dead > tuple UPDATEs, DELETEs, &c will have the same effect on a catalog. Thanks for the detailed explanation on this topic.
> Clearly it's trying to use an OID it calculated for one of these > tables after the table has been dropped, and I suspect that the lock > is released between gathering the data and sorting it. I don't have > any 8.2 databases around to try this on, but perhaps you would avoid > it with a slight rearrangement of your monitoring query: Thanks for both making this clear and providing an rearranged/modified version of my original query. I'll try out this option also. > If that doesn't do it I might try adding zero to numbers and > concatenating empty strings to try to prevent late use of the OID. > (Essentially as a form of optimization barrier.) I couldn't understand this approach clearly. Can you help explain me with some example?
"Gnanakumar" <gnanam@zoniac.com> wrote:
>> If that doesn't do it I might try adding zero to numbers and
>> concatenating empty strings to try to prevent late use of the
>> OID. (Essentially as a form of optimization barrier.)
>
> I couldn't understand this approach clearly. Can you help explain
> me with some example?
For a string it would be changing something like this:
schemaname,
to something like this:
schemaname || '' AS schemaname,
for a number it might be changing:
pg_total_relation_size(schemaname||'.'||tablename)
to:
pg_total_relation_size(schemaname||'.'||tablename) + 0
Again, I would try without these first. If the problem persists I
would sprinkle these throughout the query. If the problem goes
away, I would probably selectively remove them until the problem
returned, so I would know where it matters.
-Kevin