Postgres - pg_relation_size is broken

28. January 2012 12:24


I started using postgres and inside my first week I amnaged to find an issue with pg_relation_size. The problem being is that SQL is mean to be non case sensitive. So I guess this is a bug since if you have an table name with an upper case character in it things seem to break, Badly.


to put this in context I create a table called "Attempts". It is for analyzing password attempts from a honeypot. I wrote a quick syslog parser and fired the output into sql so I could run some quries. So I have the following in postgres.



=# \d
                 List of relations
 Schema |          Name          |   Type   | Owner
 public | Attempts               | table    | root
 public | Attempts_AttemptID_seq | sequence | root
(2 rows)


So that is really simple. The following works without a problem. This will also work with a lower case "attempts"



=# select count(*) from "Attempts";
(1 row)


The following however does not.



select pg_relation_size('Attempts');

ERROR:  relation "attempts" does not exist
LINE 1: select pg_relation_size('Attempts');


Neither does



select pg_relation_size('attempts');
ERROR:  relation "attempts" does not exist
LINE 1: select pg_relation_size('attempts');


Or this.



select pg_relation_size("attempts");
ERROR:  column "attempts" does not exist
LINE 1: select pg_relation_size("attempts");

select pg_relation_size("Attempts");
ERROR:  column "Attempts" does not exist
LINE 1: select pg_relation_size("Attempts");


Or this.


select tablename from pg_tables where tablename = 'Attempts';
(1 row)

select tablename,  pg_relation_size(tablename) from pg_tables where tablename = 'attempts';
ERROR:  function pg_relation_size(name) does not exist
LINE 1: select tablename,  pg_relation_size(tablename) from pg_table...



This does ...



create table tmp (id int);

select pg_relation_size('tmp');
(1 row)


However this does work for both cases.



ALTER TABLE "Attempts" RENAME TO "attempts"

select pg_relation_size('Attempts');
(1 row)

select pg_relation_size('attempts');
(1 row)



So I guess that is broken then! This becomes a real pain now. Since I am using some orm software and the schema is defined by the classes in the code and not the other way around. I guess there is an extrac tolower somewhere.

E-mail Kick it! DZone it! Permalink

Comments (2) -

1/28/2012 8:24:08 PM #

The following solution works

select pg_relation_size('"Attempts"');

james United Kingdom |

5/24/2012 10:13:38 AM #

Disk space used by the table or index with the specified name. The table name may be qualified with a schema name
should be:
select pg_relation_size(public.Attempts);

Jack People's Republic of China |