From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | jankaraffa(at)gmail(dot)com |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14607: session variables are non-deterministic |
Date: | 2017-03-31 13:03:30 |
Message-ID: | CAFj8pRBuXmD645FMU0LiELvBj9YCkttoPPzkif5HzVP0C7-Ctw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi
2017-03-31 14:35 GMT+02:00 <jankaraffa(at)gmail(dot)com>:
> The following bug has been logged on the website:
>
> Bug reference: 14607
> Logged by: Jan Karaffa
> Email address: jankaraffa(at)gmail(dot)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
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim Gündüz | 2017-03-31 17:41:19 | Re: BUG #14548: Install plv8 issue |
Previous Message | jankaraffa | 2017-03-31 12:35:12 | BUG #14607: session variables are non-deterministic |