Обсуждение: PostgreSQL 9.0b1 - Error when checking table sizes
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 It doesn't matter which database name I use. It doesn't have a problem getting database sizes though. This is under Gentoo Linux x64 with 2.6.32 kernel. I'm certain I've successfully listed table sizes in the database a couple days ago. Thanks Thom
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: =A0relation "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: > =A0 =A0 =A0 =A0(quote_ident(schemaname) || '.' || quote_ident(tablename))= ::regclass > > Frankly though this seems like quite the hard way. =A0Why not just > > select relname, pg_size_pretty(pg_table_size(oid)) from pg_class > where relkind =3D 'r' > order by relname; > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, 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 =3D '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 =3D 'public' order by tablename;" >=20 > 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