Expression index can get an empty generated column name
If you CREATE INDEX on an expression without naming the index, Postgres
generates a name for the index column from that expression. For some
expressions the generated name comes out empty, so the index column is
left with an empty attname.
Example (a whole-row reference):
CREATE TABLE t (a int, b int);
CREATE INDEX ON t ((t IS NOT NULL));
SELECT attname, length(attname)
FROM pg_attribute
WHERE attrelid = 't__idx'::regclass AND attnum = 1;
attname | length
---------+--------
| 0
(1 row)
The index is named "t__idx" -- the doubled underscore is where the
empty column name landed.
Cause: ChooseIndexExpressionName() builds the name from the Vars,
Consts, and function names in the expression. A whole-row Var is
skipped and a punctuation-only Const is stripped away, so such
expressions contribute no text and the result is empty. Before commit
181b6185c7, expression columns were always named "expr" (giving
"t_expr_idx" here).
Fix (attached): if the walk finds nothing usable, fall back to "expr".
Ordinary expressions are unaffected. Includes a regression test; make
check is green. Against the master.
Patch is attached.
Thanks,
Dhruv Chauhan
Attachments:
v1-0001-Avoid-generating-an-empty-name-for-an-expression-.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Avoid-generating-an-empty-name-for-an-expression-.patchDownload+42-1
Chauhan Dhruv <chauhandhruv351@gmail.com> writes:
If you CREATE INDEX on an expression without naming the index, Postgres
generates a name for the index column from that expression. For some
expressions the generated name comes out empty, so the index column is
left with an empty attname.
Example (a whole-row reference):
CREATE TABLE t (a int, b int);
CREATE INDEX ON t ((t IS NOT NULL));
Hmm, yeah, that's not great, although I'd argue that the real issue in
this particular example is that we should treat the whole-row Var as
being named "t" rather than being ignored. Still, installing a
fallback of "expr" isn't a bad idea.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
Hmm, yeah, that's not great, although I'd argue that the real issue in
this particular example is that we should treat the whole-row Var as
being named "t" rather than being ignored. Still, installing a
fallback of "expr" isn't a bad idea.
good idea! A whole-row Var now contributes the relation's name, and
"expr" remains as a fallback for expressions that still yield no text
(e.g. a constant that sanitizes away to nothing).
CREATE TABLE t (a int, b int);
CREATE INDEX ON t ((t IS NOT NULL)); -- index t_t_idx, column "t"
CREATE INDEX ON t ((','::text)); -- index t_expr_idx, column
"expr"
CREATE INDEX ON t ((a + b)); -- index t_a_b_idx, column "a_b"
Regression test updated. make check is green.
Patch is attached.
Thanks,
Dhruv