From: | dmitry-ryabov(at)mail(dot)ru |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #9817: Broken index detection in case of functions with variadic array parameters |
Date: | 2014-04-01 06:44:09 |
Message-ID: | 20140401064409.342.76410@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 9817
Logged by: Dmitry Ryabov
Email address: dmitry-ryabov(at)mail(dot)ru
PostgreSQL version: 9.3.4
Operating system: Any
Description:
-- HOW REPRODUCE:
CREATE TABLE test (
id INTEGER NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY(id)
);
-- just test function
CREATE FUNCTION test_value_func (
text,
variadic text []
)
RETURNS bigint AS
$body$
select sum(position(unnest in $1)) from unnest($2)
$body$
LANGUAGE 'sql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER;
truncate test;
insert into test select generate_series(1, 1000000), md5(random()::text);
CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, 'a', 'b')));
-- index scan used
select * from test where test_value_func(value, 'a', 'b') > 60;
DROP INDEX test_idx;
-- but after restore from backup index created like this
CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])));
-- BUG! index scan isn't used
select * from test where test_value_func(value, 'a', 'b') > 60;
-- index scan used
select * from test where test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])>60
DROP INDEX test_idx;
CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, 'a', 'b')));
-- index scan used
select * from test where test_value_func(value, 'a', 'b') > 60;
-- BUG! index scan isn't used
select * from test where test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])>60
-- affected version: 9.3.4
-- not affected version: 9.1.13 - index scan used in both cases
DROP TABLE test;
DROP FUNCTION test_value_func(text, variadic text []);
From | Date | Subject | |
---|---|---|---|
Next Message | Samokhin, Dmitry [MNPP Saturn] | 2014-04-01 12:16:11 | Re: BUG #9756: Inconsistent database after OS restart |
Previous Message | Tom Lane | 2014-04-01 02:54:00 | Re: BUG #9518: temporary login failure - "missing pg_hba entry" |