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
|
Список | 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 по дате отправления: