BUG #14607: session variables are non-deterministic

Hi

2017-03-31 14:35 GMT+02:00 <jankaraffa@gmail.com>:

The following bug has been logged on the website:

Bug reference: 14607
Logged by: Jan Karaffa
Email address: jankaraffa@gmail.com
PostgreSQL version: 9.4.11
Operating system: Red Hat 4.4.7-17, 64bit
Description:

let's have myPartTable as partitioned table with check constraint on
part_key::text column

SET myvar.partition_key='ABC';
select count(*) from myPartTable
where part_key = current_setting('myvar.partition_key')::text;

Execution plan shows that all inherited (partitioned) tables are scanned.
It
behaves like current_setting function is non-deterministic and therefore
sql
optimalizator is not considered output as constant. For me it looks like
bug
as session variable can not change during parsing/execution.

If current_setting function would behave deterministic it would enable us
to
pass session parameters into the view and perform limitation inside of view
where it makes sense. Current "workaround" is to move logic from view to
function, which is not as easy to use in hibernate as the standard views.

session variable is volatile - so it is not bug.

There is a workaround - you can wrap a access to this variable to STABLE
function

CREATE OR REPLACE FUNCTION varval(text)
RETURNS text AS $$
SELECT current_setting($1)
$$ LANGUAGE SQL STABLE;

select count(*) from myPartTable
where part_key = varval('myvar.partition_key')::text;

should to work for you

Regards

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

2017-03-31 14:35 GMT+02:00 <jankaraffa@gmail.com>:

It behaves like current_setting function is non-deterministic and therefore
sql optimalizator is not considered output as constant. For me it looks like
bug as session variable can not change during parsing/execution.

session variable is volatile - so it is not bug.

There is a workaround - you can wrap a access to this variable to STABLE
function

current_setting() already is stable according to the pg_proc catalog.
This is not a bug because the OP is wrong: GUC values *can* change between
planning and execution. It's very easy for that to happen when dealing
with cached plans.

(Really, they could change even during execution of a query, but we choose
to avert our eyes from that fact rather than mark the function volatile
and thereby make it even less optimizable.)

You could force it by using a wrapper function marked IMMUTABLE, but that
really seems like carrying around a loaded gun aimed at your foot.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs