wiki Disk Usage, table size: ERROR: could not open relation with OID 0
Hi
I got the message
ERROR: could not open relation with OID 0
when running the "General Table Size Information" from
https://wiki.postgresql.org/wiki/Disk_Usage
This patch gives some system tables
@@ -12,5 +12,6 @@
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
+ AND reltoastrelid!=0
) a
) a;
But I guess it was supposed to give size of all tables.
I'm running version 9.1.9 so it should be working according to the wiki.
The original statement:
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS
table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a) a;
Any help much appreciated.
./best regards
On Wed, Mar 14, 2018 at 04:17:54PM +0100, Hans Schou wrote:
I got the message
ERROR: could not open relation with OID 0
when running the "General Table Size Information" from
https://wiki.postgresql.org/wiki/Disk_Usage
I cannot see this failure on latest HEAD on a database running the
regression database. This is an elog() message by the way, which is
something to report internal errors, so users should never be able to
face it.
But I guess it was supposed to give size of all tables.
I'm running version 9.1.9 so it should be working according to the
wiki.
You should update and upgrade. 9.1 has fallen out of community support
1 year and a half ago, and 9.1.9 is utterly outdated.
--
Michael
Michael Paquier <michael@paquier.xyz> writes:
On Wed, Mar 14, 2018 at 04:17:54PM +0100, Hans Schou wrote:
I got the message
ERROR: could not open relation with OID 0
when running the "General Table Size Information" from
https://wiki.postgresql.org/wiki/Disk_Usage
I'm running version 9.1.9 so it should be working according to the
wiki.
You should update and upgrade. 9.1 has fallen out of community support
1 year and a half ago, and 9.1.9 is utterly outdated.
The query does fail on < 9.2, because on rows with no reltoastrelid
it will call pg_total_relation_size(0), and we didn't make those
functions forgiving of bogus OIDs until 9.2.
Given that pre-9.2 is well out of support I didn't feel like complicating
the query to handle that; what I did do was change the labeling to say
"works with >= 9.2" instead of "works with >= 9.0". But hey, it's a wiki;
if you feel more ambitious, edit away.
regards, tom lane
On Thu, Mar 15, 2018 at 12:32 AM, Michael Paquier <michael@paquier.xyz>
wrote:
I'm running version 9.1.9 so it should be working according to the
wiki.You should update and upgrade. 9.1 has fallen out of community support
I will recommend that to the database owner. Thanks
On Thu, Mar 15, 2018 at 12:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The query does fail on < 9.2, because on rows with no reltoastrelid
Thats, fine. I will live with that until upgrade.
But hey, it's a wiki;
if you feel more ambitious, edit away.
I tried but it said:
"The site you are trying to log in to (the postgresql wiki) requires a
cool-off period between account creation and logging in. Please try again
later, or contact the postgresql.org webmasters if you have an urgent need
to log in."