Re: creating index names automatically?

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> On Dec 20, 2009, at 13:58 , Tom Lane wrote:
>> * Append "_index" not "_key" if it's not a constraint-related index.

> "_idx" instead of "_index" keeps things a bit shorter (and a couple of
> keystrokes further from NAMEDATALEN). There's precedent for
> abbreviations with automatic naming in Postgres, e.g., "_fkey".

No objection here.

BTW, I'm having second thoughts about the last part of my proposal:

>> I'm also a bit tempted to propose that we start using FigureColname
>> for the actual attribute names of expression indexes, instead of the
>> not terribly helpful "pg_expression_n" convention.

The trouble with changing the index attnames for expressions is that it
increases the risk of collisions with attnames for regular index
columns. You can hit that case today:

regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create index fooi on foo(f1, lower(f2));
CREATE INDEX
regression=# \d fooi
Index "public.fooi"
Column | Type | Definition
-----------------+---------+------------
f1 | integer | f1
pg_expression_2 | text | lower(f2)
btree, for table "public.foo"

regression=# alter table foo rename f1 to pg_expression_2;
ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnam_index"
DETAIL: Key (attrelid, attname)=(64621, pg_expression_2) already exists.

but it's not exactly probable that someone would name a column
pg_expression_N. The risk goes up quite a lot if we might use simple
names like "abs" or "lower" for expression columns.

We could work around this by being willing to rename index columns on
the fly, but that creates a big risk of failing to dump and reload
comments on index columns, because the columns might not get the same
names in a newer PG version. (I seem to remember having objected to the
whole concept of comments on index columns on the grounds that it would
lock us into the current index column naming scheme, and that's exactly
what it's doing here.)

So I think we're stuck with the current column naming rule, but we do
have wiggle room on the name of the index itself.

regards, tom lane