Thread: [BUGS] BUG #14822: Partition check constraint is ignored if parameter ispassed from SQL function
[BUGS] BUG #14822: Partition check constraint is ignored if parameter ispassed from SQL function
From
paziek@gmail.com
Date:
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