Обсуждение: Expression indexes ignore typmod of expression

A Salesforce colleague asked me why, for something like

regression=# create table foo1 (f1 char(15), f2 char(15));
CREATE TABLE
regression=# create index on foo1((case when f1>'z' then f1 else f2 end));
CREATE INDEX
regression=# \d foo1_f2_idx
             Index "public.foo1_f2_idx"
 Column |  Type  |            Definition
--------+--------+-----------------------------------
 f2     | bpchar | (                                +
        |        | CASE                             +
        |        |     WHEN f1 > 'z'::bpchar THEN f1+
        |        |     ELSE f2                      +
        |        | END)
btree, for table "public.foo1"

the index column ends up as "bpchar" and not "char(15)".  The CASE
expression does get resolved as char(15), but it turns out that
index.c just ignores that.  I think this is just a hangover from
before we paid much attention to expression typmods at all, and
propose the attached patch.

Comments?

            regards, tom lane

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 47f0647..c932c83 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
*************** ConstructTupleDescriptor(Relation heapRe
*** 389,395 ****
              to->attalign = typeTup->typalign;
              to->attstattarget = -1;
              to->attcacheoff = -1;
!             to->atttypmod = -1;
              to->attislocal = true;
              to->attcollation = collationObjectId[i];

--- 389,395 ----
              to->attalign = typeTup->typalign;
              to->attstattarget = -1;
              to->attcacheoff = -1;
!             to->atttypmod = exprTypmod(indexkey);
              to->attislocal = true;
              to->attcollation = collationObjectId[i];

Hi,

On 2014-04-25 17:05:26 -0400, Tom Lane wrote:
> A Salesforce colleague asked me why, for something like
> ....
> the index column ends up as "bpchar" and not "char(15)".  The CASE
> expression does get resolved as char(15), but it turns out that
> index.c just ignores that.

I've seen that before but never looked what's the origin. +1 for fixing
it.

>  I think this is just a hangover from
> before we paid much attention to expression typmods at all, and
> propose the attached patch.
> 
> Comments?

Any chance it could cause problems with stored trees being different
from newly generated ones due to it? I.e. is it something that can be
done without a catversion bump?

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-25 17:05:26 -0400, Tom Lane wrote:
>> I think this is just a hangover from
>> before we paid much attention to expression typmods at all, and
>> propose the attached patch.

> Any chance it could cause problems with stored trees being different
> from newly generated ones due to it? I.e. is it something that can be
> done without a catversion bump?

Not sure.  I wasn't proposing this as a back-patch, just 9.4 only.
        regards, tom lane

On 2014-04-25 17:19:00 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2014-04-25 17:05:26 -0400, Tom Lane wrote:
> >> I think this is just a hangover from
> >> before we paid much attention to expression typmods at all, and
> >> propose the attached patch.
> 
> > Any chance it could cause problems with stored trees being different
> > from newly generated ones due to it? I.e. is it something that can be
> > done without a catversion bump?
> 
> Not sure.  I wasn't proposing this as a back-patch, just 9.4 only.

Then a clear +1 for me. It's rather confusing to see bpchar, a type very
rarely used explicitly in explain output.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services