Обсуждение: pg_relation_size, relation does not exist
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
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
Thanks, Frank. That works perfectly. Cheers, Whit 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 > >
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 :-(
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
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 > >
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: > 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