[GENERAL] Partition check constraint is ignored if parameter is passed from SQLfunction

Поиск
Список
Период
Сортировка
От Lukasz Kaminski
Тема [GENERAL] Partition check constraint is ignored if parameter is passed from SQLfunction
Дата
Msg-id HE1PR0902MB204277D565EFD14967C7CCE1FA7B0@HE1PR0902MB2042.eurprd09.prod.outlook.com
обсуждение исходный текст
Список pgsql-general
Дерево обсуждения
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

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

Предыдущее
От: Abhinav Singh
Дата:
Сообщение: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum
Следующее
От: Thomas Güttler
Дата:
Сообщение: Re: [GENERAL] VM-Ware Backup of VM safe?: snapshots: quiesced andnon-quiesced