|
Hello,
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. Any idea why that is and if this is intended?
SELECT col1,
col2
FROM table1
WHERE date_col
= '2017-07-30'
AND some_col
= ANY(array[1,2])
This is query plan that I would expect to get:
Append (cost=0.00..26.05 rows=2 width=36)
-> Seq Scan on table1 (cost=0.00..0.00 rows=1 width=36)
Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = '2017-07-30'::date))
-> Seq Scan on "part$_table1_201707" (cost=0.00..26.05 rows=1 width=36)
Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = '2017-07-30'::date))
What I am likely getting is this tho:
Append (cost=0.01..156.55 rows=3 width=36)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=4)
-> Seq Scan on table1 (cost=0.00..0.00 rows=1 width=36)
Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = $0))
-> Seq Scan on "part$_table1_201707" (cost=0.00..26.05 rows=1 width=36)
Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = $0))
-> Foreign Scan on "part$_table1_201603" (cost=100.00..130.49 rows=1 width=36)
Something like this happens if I do this plain SQL query:
SELECT col1, col2
FROM table1
WHERE date_col = (SELECT '2017-07-30'::date) --problem here
AND some_col = ANY(array[1,2])
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 plpgsql
SECURITY DEFINER
AS $function$
BEGIN
--
RETURN QUERY
SELECT t.col1,
t.col2
FROM table1
AS t
WHERE date_col
= param1
AND some_col
= ANY(param2);
--
END;
$function$;
CREATE OR
REPLACE FUNCTION
function_sql(param1
date, param2
int[])
RETURNS TABLE(col1
int, col2
text)
LANGUAGE SQL
SECURITY DEFINER
AS $function$
--
SELECT t.col1,
t.col2
FROM table1
AS t
WHERE date_col
= param1
AND some_col
= ANY(param2)
--
$function$;
CREATE OR
REPLACE FUNCTION
function_sql_hardcoded(param1
date, param2
int[])
RETURNS TABLE(col1
int, col2
text)
LANGUAGE SQL
SECURITY DEFINER
AS $function$
--
SELECT t.col1,
t.col2
FROM table1
AS t
WHERE date_col
= '2017-07-30'::date
AND some_col
= ANY(param2)
--
$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
|
