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";
 count
-------
 24490
(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';
 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');
 pg_relation_size
------------------
                0
(1 row)

 

However this does work for both cases.

 

 

ALTER TABLE "Attempts" RENAME TO "attempts"


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



select pg_relation_size('attempts');
 pg_relation_size
------------------
          2080768
(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! del.icio.us 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 #

pg_relation_size(text)
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 |