[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_relation_size, relation does not exist
Thanks, Frank. That works perfectly. Cheers, Whit On Tue, Jun 16, 2009 at 7:36 AM, Frank Heikens 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 > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_relation_size, relation does not exist
Frank Heikens 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
Re: [GENERAL] 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: Frank Heikens 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
Re: [GENERAL] 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: > 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 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
Re: [GENERAL] 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: 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
