Friday, November 18, 2016

postgresql: pg_relation_size glitches?

How did pg_relation_size locate something does not exist in the db?

VCDB=# select current_database(), version();
 current_database |                                                                     version

------------------+---------------------------------------------------------------------------------------------------------------
-----------------------------------
 VCDB             | PostgreSQL 9.4.9 (VMware Postgres 9.4.9.1-4536683 release) on x86_64-unknown-linux-gnu, compiled by x86_64-vmk
-linux-gnu-gcc (GCC) 4.8.4, 64-bit
(1 row)

VCDB=# select pg_relation_size(table_name) as Size  FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
ERROR:  relation "vci_version" does not exist

VCDB=# \d vci_version;
Did not find any relation named "vci_version".
VCDB=#

VCDB=#  select table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog');
           table_name
--------------------------------
 cis_kv_providers
 cis_kv_keyvalue
 vpx_alarm_expression
 vpx_alarm_expr_comp
 vpx_alarm
...

SELECT tablename,  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
FROM pg_tables WHERE schemaname = 'vc';

           tablename            | pg_size_pretty
--------------------------------+----------------
 cis_kv_providers               | 8192 bytes
 cis_kv_keyvalue                | 8192 bytes
 vpx_alarm_expression           | 112 kB
 vpx_alarm_expr_comp            | 48 kB
 vpx_alarm                      | 104 kB
 vpx_alarm_runtime              | 8192 bytes


No comments:

Post a Comment