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
Attachments:
fix-typmod-of-expression-indexes.patchtext/x-diff; charset=us-ascii; name=fix-typmod-of-expression-indexes.patchDownload+2-2
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers