Thread: 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 tablesI 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
@@ -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;
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
Re: wiki Disk Usage, table size: ERROR: could not open relation withOID 0
From
Michael Paquier
Date:
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
Attachment
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."