Thread: BUG #12784: pg_relation_size has problems with case in index name

The following bug has been logged on the website:

Bug reference:      12784
Logged by:          Stefan Radomski
Email address:      gibheer@zero-knowledge.org
PostgreSQL version: 9.4.1
Operating system:   FreeBSD 10.1p5
Description:

I found a problem with indexes which have an uppercase letter in the name.
You can reproduce it with the following statements:

  create table unfindable("Test" text);
  create index on unfindable("Test");
  \d unfindable
  select pg_relation_size('unfindable_Test_idx');

It does work for tables with upper case characters in the name though.

Gibheer wrote
> The following bug has been logged on the website:
>
> Bug reference:      12784
> Logged by:          Stefan Radomski
> Email address:

> gibheer@

> PostgreSQL version: 9.4.1
> Operating system:   FreeBSD 10.1p5
> Description:
>
> I found a problem with indexes which have an uppercase letter in the name.
> You can reproduce it with the following statements:
>
>   create table unfindable("Test" text);
>   create index on unfindable("Test");
>   \d unfindable
>   select pg_relation_size('unfindable_Test_idx');
>
> It does work for tables with upper case characters in the name though.

To be specific: your complaint is that the system generated index name
(since you didn't provide a name of your own) does not respect the
capitalization of the table name upon which it is being created.

My guess is that the name of the table is normalized in some form in order
to make the index name less complicated - say if the table name has special
characters or embedded spaces.

Your complaint is likely better categorized as a feature request or just an
implementation detail you can live with or avoid by providing your own index
name.  I haven't dug into this at all to make a better determination.

David J.



--
View this message in context:
http://postgresql.nabble.com/BUG-12784-pg-relation-size-has-problems-with-case-in-index-name-tp5838476p5838477.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

gibheer@zero-knowledge.org writes:
> I found a problem with indexes which have an uppercase letter in the name.
> You can reproduce it with the following statements:

>   create table unfindable("Test" text);
>   create index on unfindable("Test");
>   \d unfindable
>   select pg_relation_size('unfindable_Test_idx');

This isn't a bug.  You need to double-quote the index name, just the same
as you would do at the SQL level.

regression=# create table unfindable("Test" text);
CREATE TABLE
regression=# create index on unfindable("Test");
CREATE INDEX
regression=# \d unfindable
 Table "public.unfindable"
 Column | Type | Modifiers
--------+------+-----------
 Test   | text |
Indexes:
    "unfindable_Test_idx" btree ("Test")

regression=# select pg_relation_size('unfindable_Test_idx');
ERROR:  relation "unfindable_test_idx" does not exist
LINE 1: select pg_relation_size('unfindable_Test_idx');
                                ^
regression=# select pg_relation_size('"unfindable_Test_idx"');
 pg_relation_size
------------------
             8192
(1 row)


            regards, tom lane