Обсуждение: Indexes on expressions that include user-defined functions are notreindexed when the function definition is changed

If the index on expression is defined so, that the indexed value
depends on a user-defined function, it doesn't get reindexed when the
function's definition changes, therefore completely wrong values can
be displayed when the query is executed using that index.

MWE:

psql (11.3)
Type "help" for help.

postgres=# create database bugtest;
CREATE DATABASE
postgres=# \c bugtest
You are now connected to database "bugtest" as user "postgres".
bugtest=# create function f(x int) returns int as select($1 * 2) LANGUAGE SQL;
ERROR:  syntax error at or near "select"
LINE 1: create function f(x int) returns int as select($1 * 2) LANGU...
                                                ^
bugtest=# create function f(x int) returns int as $$select($1 * 2)$$
LANGUAGE SQL;
CREATE FUNCTION
bugtest=# create table tbl (id serial primary key, n int);
CREATE TABLE
bugtest=# create index idx on tbl(f(n));
CREATE INDEX
bugtest=# insert into tbl(n) (select * from generate_series(1,1000));
INSERT 0 1000
bugtest=# create or replace function f(x int) returns int as
$$select($1 * 3)$$ LANGUAGE SQL;
CREATE FUNCTION
bugtest=# select * from tbl where f(n) = 12;
 id | n
----+---
  6 | 6
(1 row)

bugtest=# select id,n,f(n) from tbl where f(n) = 12;
 id | n | f
----+---+----
  6 | 6 | 18
(1 row)

Dmytry Strikha <dm.strikha@gmail.com> writes:
> If the index on expression is defined so, that the indexed value
> depends on a user-defined function, it doesn't get reindexed when the
> function's definition changes, therefore completely wrong values can
> be displayed when the query is executed using that index.

If you declare the function immutable, as you must to use it in an
index, then redefining the function in a way that changes its results
means *you* broke the contract.  I don't think it's unreasonable of
us to insist that it's on your head to issue any necessary reindexing.
Certainly, neither of the plausible alternatives are acceptable:

* Reindex any time the function is changed at all.  This is putting
an undue burden on people who did follow the contract.

* Detecting whether the redefinition changed the results.
See halting problem.

            regards, tom lane

Could this get a special mention in the documentation for Indexes on
expressions? Most unfamiliar users will look there to figure out the
correct way to create the index and will be at least warned.

On Wed, May 29, 2019 at 7:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dmytry Strikha <dm.strikha@gmail.com> writes:
> > If the index on expression is defined so, that the indexed value
> > depends on a user-defined function, it doesn't get reindexed when the
> > function's definition changes, therefore completely wrong values can
> > be displayed when the query is executed using that index.
>
> If you declare the function immutable, as you must to use it in an
> index, then redefining the function in a way that changes its results
> means *you* broke the contract.  I don't think it's unreasonable of
> us to insist that it's on your head to issue any necessary reindexing.
> Certainly, neither of the plausible alternatives are acceptable:
>
> * Reindex any time the function is changed at all.  This is putting
> an undue burden on people who did follow the contract.
>
> * Detecting whether the redefinition changed the results.
> See halting problem.
>
>                         regards, tom lane

On 2019-May-29, Dmytry Strikha wrote:

> Could this get a special mention in the documentation for Indexes on
> expressions? Most unfamiliar users will look there to figure out the
> correct way to create the index and will be at least warned.

That sounds reasonable.  Care to submit a patch?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services