pg_relation_size, relation does not exist

Started by Whit Armstrongalmost 17 years ago7 messagesgeneral

armstrong.whit@gmail.com

almost 17 years ago

Does anyone know why I get an unknown relation error when I query for
relation size?

kls=# select tablename, pg_size_pretty(pg_relation_size(tablename))
from pg_tables where schemaname = 'econ' order by tablename;
ERROR: relation "series_info" does not exist
kls=#

Is there a better way to do this query to avoid this error?

Thanks,
Whit

Frank Heikens

frankheikens@mac.com

almost 17 years ago

In reply to: Whit Armstrong (#1)

Re: pg_relation_size, relation does not exist

The search_path isn't correct. You're looking for the size of tables
in schema 'econ' but you don't mention this schema inside the function
pg_relation_size(). Try to use the schemanames as well, saves you a
lot of problems with assumptions.

This one should work:

SELECT
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
FROM
pg_tables
WHERE
schemaname = 'econ';

Kind regard,
Frank

Op 16 jun 2009, om 13:17 heeft Whit Armstrong het volgende geschreven:

Show quoted text

Does anyone know why I get an unknown relation error when I query for
relation size?

kls=# select tablename, pg_size_pretty(pg_relation_size(tablename))
from pg_tables where schemaname = 'econ' order by tablename;
ERROR: relation "series_info" does not exist
kls=#

Is there a better way to do this query to avoid this error?

Thanks,
Whit

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Whit Armstrong

armstrong.whit@gmail.com

almost 17 years ago

In reply to: Frank Heikens (#2)

Re: pg_relation_size, relation does not exist

Thanks, Frank.

That works perfectly.

Cheers,
Whit

Show quoted text

On Tue, Jun 16, 2009 at 7:36 AM, Frank Heikens<frankheikens@mac.com> wrote:

The search_path isn't correct. You're looking for the size of tables in
schema 'econ' but you don't mention this schema inside the function
pg_relation_size(). Try to use the schemanames as well, saves you a lot of
problems with assumptions.

This one should work:

SELECT
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
FROM
pg_tables
WHERE
schemaname = 'econ';

Kind regard,
Frank

Op 16 jun 2009, om 13:17 heeft Whit Armstrong het volgende geschreven:

Does anyone know why I get an unknown relation error when I query for
relation size?

kls=# select tablename, pg_size_pretty(pg_relation_size(tablename))
from pg_tables where schemaname = 'econ' order by tablename;
ERROR: relation "series_info" does not exist
kls=#

Is there a better way to do this query to avoid this error?

Thanks,
Whit

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane

tgl@sss.pgh.pa.us

almost 17 years ago

In reply to: Frank Heikens (#2)

Re: pg_relation_size, relation does not exist

Frank Heikens <frankheikens@mac.com> writes:

pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))

At some point you're going to wish you'd used quote_ident() here.

regards, tom lane

PS: Personally I prefer to rely on pg_relation_size(oid), but to use
that you need to be looking directly at pg_class, not at pg_tables
which doesn't expose the oid column :-(

Frank Heikens

frankheikens@mac.com

almost 17 years ago

In reply to: Tom Lane (#4)

Re: pg_relation_size, relation does not exist

Agreed.

Personally I wouldn't use pg_tables at all because of the missing oid.
Would be nice to have in this view, but it can't be changed because
it's a system-view. pg_class would do the job.

Regards,
Frank

Op 16 jun 2009, om 16:12 heeft Tom Lane het volgende geschreven:

Show quoted text

Frank Heikens <frankheikens@mac.com> writes:

pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))

At some point you're going to wish you'd used quote_ident() here.

regards, tom lane

PS: Personally I prefer to rely on pg_relation_size(oid), but to use
that you need to be looking directly at pg_class, not at pg_tables
which doesn't expose the oid column :-(

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Magnus Hagander

magnus@hagander.net

almost 17 years ago

In reply to: Frank Heikens (#5)

Re: pg_relation_size, relation does not exist

Actually, is there any particular reason why we can't *add* that column
to the view in a future version? We certainly shouldn't go modify it,
but adding to it should be pretty safe, no?

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Frank Heikens wrote:

Show quoted text

Agreed.

Personally I wouldn't use pg_tables at all because of the missing oid.
Would be nice to have in this view, but it can't be changed because it's
a system-view. pg_class would do the job.

Regards,
Frank

Op 16 jun 2009, om 16:12 heeft Tom Lane het volgende geschreven:

Frank Heikens <frankheikens@mac.com> writes:

pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))

At some point you're going to wish you'd used quote_ident() here.

regards, tom lane

PS: Personally I prefer to rely on pg_relation_size(oid), but to use
that you need to be looking directly at pg_class, not at pg_tables
which doesn't expose the oid column :-(

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Frank Heikens

frankheikens@mac.com

almost 17 years ago

In reply to: Magnus Hagander (#6)

Re: pg_relation_size, relation does not exist

The same problem, missing oid's, occurs with several other system
views as well. If you have to do some serious work, it's always
pg_class you need.

oid's in these views would be nice, but only if all the system views
have the oid's of the underlaying objects. In case of pg_tables you
need the oid's of the schema and the table.

http://www.postgresql.org/docs/8.3/static/views-overview.html

Regards,
Frank

Op 16 jun 2009, om 16:52 heeft Magnus Hagander het volgende geschreven:

Show quoted text

Actually, is there any particular reason why we can't *add* that
column
to the view in a future version? We certainly shouldn't go modify it,
but adding to it should be pretty safe, no?

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Frank Heikens wrote:

Agreed.

Personally I wouldn't use pg_tables at all because of the missing
oid.
Would be nice to have in this view, but it can't be changed because
it's
a system-view. pg_class would do the job.

Regards,
Frank

Op 16 jun 2009, om 16:12 heeft Tom Lane het volgende geschreven:

Frank Heikens <frankheikens@mac.com> writes:

pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))

At some point you're going to wish you'd used quote_ident() here.

regards, tom lane

PS: Personally I prefer to rely on pg_relation_size(oid), but to use
that you need to be looking directly at pg_class, not at pg_tables
which doesn't expose the oid column :-(

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general