The following bug has been logged on the website:
Bug reference: 14822
Logged by: Łukasz Kamiński
Email address: paziek@gmail.com
PostgreSQL version: 9.6.5
Operating system: Windows 10 64 Bit
Description:
When query is executed in function that has language set to "sql" then
parameters passed to it are not considered as either externally supplied
parameters or constants as far as partitioning goes. It will scan all
partitions, despite parameters clearly limiting it to just one.
Changing function to plpgsql or doing plain SQL query does not share this
issue.
Below is full code that can be used to reproduce this issue.
CREATE SERVER broken_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'broken_server', dbname 'postgres', port '5432');
CREATE USER MAPPING FOR postgres SERVER broken_server
OPTIONS (user 'foreign_username', password 'foreign_password');
CREATE TABLE table1 (id serial PRIMARY KEY, date_col date, some_col int, col1 int, col2 text);
CREATE TABLE part$_table1_201707 ()
INHERITS (table1);
ALTER TABLE part$_table1_201707 ADD CONSTRAINT
part$_table1_201707_date_chk CHECK (date_col BETWEEN '2017-07-01'::date AND
'2017-07-31'::date);
CREATE FOREIGN TABLE part$_table1_201603 ()
INHERITS (table1) SERVER broken_server
OPTIONS (schema_name 'public', table_name 'part$_table1_201603');
ALTER TABLE part$_table1_201603 ADD CONSTRAINT
part$_table1_201603_date_chk CHECK (date_col BETWEEN '2016-03-01'::date AND
'2016-03-31'::date);
CREATE OR REPLACE FUNCTION function_plpgsql(param1 date, param2 int[])RETURNS TABLE(col1 int, col2 text)LANGUAGE
plpgsqlSECURITYDEFINER
AS $function$
BEGIN -- RETURN QUERY WITH ct AS (SELECT unnest(param2) AS arr) SELECT t.col1, t.col2 FROM table1 AS t WHERE
date_col= param1 AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[]); --reasons --
END;
$function$;
CREATE OR REPLACE FUNCTION function_sql(param1 date, param2 int[])RETURNS TABLE(col1 int, col2 text)LANGUAGE
SQLSECURITYDEFINER
AS $function$ -- WITH ct AS (SELECT unnest(param2) AS arr) SELECT t.col1, t.col2 FROM table1 AS t WHERE date_col =
param1 AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[]) --
$function$;
CREATE OR REPLACE FUNCTION function_sql_hardcoded(param1 date, param2
int[])RETURNS TABLE(col1 int, col2 text)LANGUAGE SQLSECURITY DEFINER
AS $function$ -- WITH ct AS (SELECT unnest(param2) AS arr) SELECT t.col1, t.col2 FROM table1 AS t WHERE date_col =
'2017-07-30'::date AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[]) --
$function$;
EXPLAIN ANALYZE
SELECT * FROM function_sql('2017-07-30'::date, array[1,2]);
-- ERROR: could not connect to server "broken_server"
EXPLAIN ANALYZE
SELECT * FROM function_plpgsql('2017-07-30'::date, array[1,2]);
--Executes sucessfully, as expected
EXPLAIN ANALYZE
SELECT * FROM function_sql_hardcoded('2017-07-30'::date, array[1,2]);
--Executes sucessfully, but useless
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs