Indexes on expressions that include user-defined functions are notreindexed when the function definition is changed

Поиск
Список
Период
Сортировка
От Dmytry Strikha
Тема Indexes on expressions that include user-defined functions are notreindexed when the function definition is changed
Дата
Msg-id CAJqEY-ER3P46yewawoRVXbW_p20EnRf8pVyvgmA2-dJQ9qKHKg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Indexes on expressions that include user-defined functions are not reindexed when the function definition is changed  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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)



В списке pgsql-bugs по дате отправления:

Предыдущее
От: Francisco Olarte
Дата:
Сообщение: Re: BUG #15823: application server could not be contacted
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Indexes on expressions that include user-defined functions are not reindexed when the function definition is changed