Re: PostgreSQL 9.0b1 - Error when checking table sizes

On 27 May 2010 23:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thombrown(at)gmail(dot)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