Thread: Functions Immutable but not parallel safe?
There's 11 functions which are marked immutable, but are marked as parallel unsafe. postgres=# select proname from pg_proc where provolatile = 'i' and proparallel = 'u'; proname -----------------------------_pg_expandarray_pg_keysequal_pg_truetypid_pg_truetypmod_pg_char_max_length_pg_char_octet_length_pg_numeric_precision_pg_numeric_precision_radix_pg_numeric_scale_pg_datetime_precision_pg_interval_type (11 rows) I'm finding hard to imagine a reason why these might be unsafe, but failed. I do notice they're all only used in information_schema. Could it just perhaps be that these just missed the verification process the other functions went through to determine their parallel safety? -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Nov 24, 2016 at 5:29 AM, David Rowley <david.rowley@2ndquadrant.com> wrote: > There's 11 functions which are marked immutable, but are marked as > parallel unsafe. > > postgres=# select proname from pg_proc where provolatile = 'i' and > proparallel = 'u'; > proname > ----------------------------- > _pg_expandarray > _pg_keysequal > _pg_truetypid > _pg_truetypmod > _pg_char_max_length > _pg_char_octet_length > _pg_numeric_precision > _pg_numeric_precision_radix > _pg_numeric_scale > _pg_datetime_precision > _pg_interval_type > (11 rows) > > I'm finding hard to imagine a reason why these might be unsafe, but > failed. I do notice they're all only used in information_schema. > > Could it just perhaps be that these just missed the verification > process the other functions went through to determine their parallel > safety? Yes, I think that's it. I went through pg_proc.h, but never looked at information_schema.sql. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 11/24/16 18:13, Robert Haas wrote: >> I'm finding hard to imagine a reason why these might be unsafe, but >> failed. I do notice they're all only used in information_schema. >> >> Could it just perhaps be that these just missed the verification >> process the other functions went through to determine their parallel >> safety? > Yes, I think that's it. I went through pg_proc.h, but never looked at > information_schema.sql. This hasn't been fixed yet. It's easy to to, but taking a step back, - Is there any reason an immutable function (that is not lying about it) should be anything but parallel safe? - If so, could CREATE FUNCTION default it that way? - Maybe add a check to opr_sanity to make sure the default set of functions is configured the way we want? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Apr 5, 2017 at 8:57 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 11/24/16 18:13, Robert Haas wrote: >>> I'm finding hard to imagine a reason why these might be unsafe, but >>> failed. I do notice they're all only used in information_schema. >>> >>> Could it just perhaps be that these just missed the verification >>> process the other functions went through to determine their parallel >>> safety? >> Yes, I think that's it. I went through pg_proc.h, but never looked at >> information_schema.sql. > > This hasn't been fixed yet. It's easy to to, but taking a step back, > > - Is there any reason an immutable function (that is not lying about it) > should be anything but parallel safe? It certainly isn't very likely. It's not outright impossible. For example, imagine a function that does a calculation which is deterministic given the inputs but which creates and uses temporary tables in the course of performing the calculation. Because the function performs writes, it's parallel-unsafe. > - If so, could CREATE FUNCTION default it that way? This could be done but I'm not sure whether it's wise to make the default value for one parameter depend on another parameter. > - Maybe add a check to opr_sanity to make sure the default set of > functions is configured the way we want? That seems like a good idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 5, 2017 at 8:57 AM, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> - Maybe add a check to opr_sanity to make sure the default set of
>> functions is configured the way we want?
> That seems like a good idea.
+1 for that. We could adopt the strategy already used in opr_sanity of
searching for functions having an unexpected combination of these
attributes. If there are any legitimate exceptions, they could be
embedded in the expected output.
I concur that changing the behavior of CREATE FUNCTION seems a bit too
cute.
regards, tom lane
On 4/5/17 09:58, Robert Haas wrote: >> - Maybe add a check to opr_sanity to make sure the default set of >> functions is configured the way we want? > That seems like a good idea. patch -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Apr 5, 2017 at 12:18 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 4/5/17 09:58, Robert Haas wrote: >>> - Maybe add a check to opr_sanity to make sure the default set of >>> functions is configured the way we want? >> That seems like a good idea. > > patch LGTM -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 4/5/17 09:58, Robert Haas wrote:
>>> - Maybe add a check to opr_sanity to make sure the default set of
>>> functions is configured the way we want?
>> That seems like a good idea.
> patch
Looks sane to me, although I wonder if opr_sanity ought to be looking
for any other combinations.
regards, tom lane
On 4/5/17 12:26, Robert Haas wrote: > On Wed, Apr 5, 2017 at 12:18 PM, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: >> On 4/5/17 09:58, Robert Haas wrote: >>>> - Maybe add a check to opr_sanity to make sure the default set of >>>> functions is configured the way we want? >>> That seems like a good idea. >> >> patch > > LGTM committed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services