PostgreSQL 9.0b1 - Error when checking table sizes

On tor, 2010-05-27 at 22:41 +0100, Thom Brown wrote:

This probably isn't a legitimate bug, but as a precaution....

I'm running the following command against PostgreSQL 9.0 beta 1:

psql -U postgres -d test -c "select tablename,
pg_size_pretty(pg_table_size(tablename::regclass)) from pg_tables
order by tablename;"

And getting the following message:

ERROR: relation "sql_sizing" does not exist

This can be reduced to

select 'sql_sizing'::regclass;
ERROR: relation "sql_sizing" does not exist

You need a schema qualification (it's in information_schema).

Thom Brown <thombrown@gmail.com> writes:

This probably isn't a legitimate bug, but as a precaution....
I'm running the following command against PostgreSQL 9.0 beta 1:

psql -U postgres -d test -c "select tablename,
pg_size_pretty(pg_table_size(tablename::regclass)) from pg_tables
order by tablename;"

And getting the following message:

ERROR: relation "sql_sizing" does not exist

The "tablename::regclass" bit is guaranteed to fail for any relation
that's not in your current search_path, because you're just handing
an unqualified name to the regclass converter.

If you're absolutely intent on using the pg_tables view here, you
could do this instead:
(quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass

Frankly though this seems like quite the hard way. Why not just

select relname, pg_size_pretty(pg_table_size(oid)) from pg_class
where relkind = 'r'
order by relname;

regards, tom lane

On 27 May 2010 23:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thombrown@gmail.com> writes:

This probably isn't a legitimate bug, but as a precaution....
I'm running the following command against PostgreSQL 9.0 beta 1:

psql -U postgres -d test -c "select tablename,
pg_size_pretty(pg_table_size(tablename::regclass)) from pg_tables
order by tablename;"

And getting the following message:

ERROR:  relation "sql_sizing" does not exist

The "tablename::regclass" bit is guaranteed to fail for any relation
that's not in your current search_path, because you're just handing
an unqualified name to the regclass converter.

If you're absolutely intent on using the pg_tables view here, you
could do this instead:
       (quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass

Frankly though this seems like quite the hard way.  Why not just

select relname, pg_size_pretty(pg_table_size(oid)) from pg_class
where relkind = 'r'
order by relname;

                       regards, tom lane

Of course, you're both right. I've changed it to:

psql -U postgres -d test -c "select tablename,
pg_size_pretty(pg_table_size(tablename::regclass)) from pg_tables
where schemaname = 'public' order by tablename;"

And this, for some reason, works... which is how I did it the other
day (hence why I've only just got the error today).

Apologies

Thom

* Thom Brown (thombrown@gmail.com) wrote:

psql -U postgres -d test -c "select tablename,
pg_size_pretty(pg_table_size(tablename::regclass)) from pg_tables
where schemaname = 'public' order by tablename;"

And this, for some reason, works... which is how I did it the other
day (hence why I've only just got the error today).

public is in your search_path by default- that's why it works. Still,
it's not really a *good* approach. Use the one that Tom showed.

Thanks,

Stephen