IMMUTABLE and PARALLEL SAFE function markings
I have defined a function "is_not_distinct" for the purpose of creating a
custom operator "===".
CREATE FUNCTION is_not_distinct(a anyelement, b anyelement)
returns boolean
language sql as $$
select a is not distinct from b;
$$
IMMUTABLE;
CREATE OPERATOR === (
LEFTARG = anyelement,
RIGHTARG = anyelement,
PROCEDURE = is_not_distinct,
NEGATOR = !==
);
I have observed that the resulting queries were executed without using
parallelisation.
I have learned by asking on Freenode that the reason my queries are not
using parallelisation is because I have not configured PARALLEL SAFE.
I find it counter-intuitive that a function with IMMUTABLE marking would
require an explicit PARALLEL SAFE. It would seem logical that in all cases
where a function is appropriately market as IMMUTABLE it would also
be PARALLEL SAFE.
I was wondering what is the reason IMMUTABLE functions are not by
default PARALLEL SAFE and if the default behaviour could be changed to make
IMMUTABLE functions PARALLEL SAFE?
Thank you,
Gajus
ᐧ
On 26/11/2018 22:23, Gajus Kuizinas wrote:
I was wondering what is the reason IMMUTABLE functions are not by
default PARALLEL SAFE and if the default behaviour could be changed to
make IMMUTABLE functions PARALLEL SAFE?
I think I have to concur with this. When is an immutable function not
parallel safe?
Sure it could be mislabeled as immutable but it could just as easily be
mislabeled as parallel safe. And we already treat fake immutable
functions as user errors, for example in indexes.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hi,
On 2018-11-27 00:33:10 +0100, Vik Fearing wrote:
On 26/11/2018 22:23, Gajus Kuizinas wrote:
I was wondering what is the reason IMMUTABLE functions are not by
default�PARALLEL SAFE and if the default behaviour could be changed to
make IMMUTABLE functions�PARALLEL SAFE?I think I have to concur with this. When is an immutable function not
parallel safe?Sure it could be mislabeled as immutable but it could just as easily be
mislabeled as parallel safe. And we already treat fake immutable
functions as user errors, for example in indexes.
I think it'd introduce more problems than it'd solve. Either you ignore
the proparallel setting - resulting in broken catalog querying - or you
have to have a decent amount of special behaviour that an explicit ALTER
FUNCTION ... IMMUTABLE | STABLE | VOLATILE and SET PARALLEL { UNSAFE
| RESTRICTED | SAFE } would also need to change the respective other
category.
Greetings,
Andres Freund
On 27/11/2018 00:39, Andres Freund wrote:
Hi,
On 2018-11-27 00:33:10 +0100, Vik Fearing wrote:
On 26/11/2018 22:23, Gajus Kuizinas wrote:
I was wondering what is the reason IMMUTABLE functions are not by
default PARALLEL SAFE and if the default behaviour could be changed to
make IMMUTABLE functions PARALLEL SAFE?I think I have to concur with this. When is an immutable function not
parallel safe?Sure it could be mislabeled as immutable but it could just as easily be
mislabeled as parallel safe. And we already treat fake immutable
functions as user errors, for example in indexes.I think it'd introduce more problems than it'd solve. Either you ignore
the proparallel setting - resulting in broken catalog querying - or you
have to have a decent amount of special behaviour that an explicit ALTER
FUNCTION ... IMMUTABLE | STABLE | VOLATILE and SET PARALLEL { UNSAFE
| RESTRICTED | SAFE } would also need to change the respective other
category.
Surely a simple rule could be made that provolatile='i' trumps
proparallel. No need to make them agree.
The default catalogs should agree (and I would expect the sanity checks
to look for that) but here we're talking about user functions.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 2018-11-27 00:47:47 +0100, Vik Fearing wrote:
On 27/11/2018 00:39, Andres Freund wrote:
Hi,
On 2018-11-27 00:33:10 +0100, Vik Fearing wrote:
On 26/11/2018 22:23, Gajus Kuizinas wrote:
I was wondering what is the reason IMMUTABLE functions are not by
default�PARALLEL SAFE and if the default behaviour could be changed to
make IMMUTABLE functions�PARALLEL SAFE?I think I have to concur with this. When is an immutable function not
parallel safe?Sure it could be mislabeled as immutable but it could just as easily be
mislabeled as parallel safe. And we already treat fake immutable
functions as user errors, for example in indexes.I think it'd introduce more problems than it'd solve. Either you ignore
the proparallel setting - resulting in broken catalog querying - or you
have to have a decent amount of special behaviour that an explicit ALTER
FUNCTION ... IMMUTABLE | STABLE | VOLATILE and SET PARALLEL { UNSAFE
| RESTRICTED | SAFE } would also need to change the respective other
category.Surely a simple rule could be made that provolatile='i' trumps
proparallel. No need to make them agree.The default catalogs should agree (and I would expect the sanity checks
to look for that) but here we're talking about user functions.
I think it'd be entirely unacceptable that
SELECT * FROM pg_proc WHERE proparallel = 's'
wouldn't actually return all the functions that are parallel safe.
Greetings,
Andres Freund
Greetings,
* Andres Freund (andres@anarazel.de) wrote:
On 2018-11-27 00:47:47 +0100, Vik Fearing wrote:
On 27/11/2018 00:39, Andres Freund wrote:
On 2018-11-27 00:33:10 +0100, Vik Fearing wrote:
On 26/11/2018 22:23, Gajus Kuizinas wrote:
I was wondering what is the reason IMMUTABLE functions are not by
default PARALLEL SAFE and if the default behaviour could be changed to
make IMMUTABLE functions PARALLEL SAFE?I think I have to concur with this. When is an immutable function not
parallel safe?Sure it could be mislabeled as immutable but it could just as easily be
mislabeled as parallel safe. And we already treat fake immutable
functions as user errors, for example in indexes.I think it'd introduce more problems than it'd solve. Either you ignore
the proparallel setting - resulting in broken catalog querying - or you
have to have a decent amount of special behaviour that an explicit ALTER
FUNCTION ... IMMUTABLE | STABLE | VOLATILE and SET PARALLEL { UNSAFE
| RESTRICTED | SAFE } would also need to change the respective other
category.Surely a simple rule could be made that provolatile='i' trumps
proparallel. No need to make them agree.The default catalogs should agree (and I would expect the sanity checks
to look for that) but here we're talking about user functions.I think it'd be entirely unacceptable that
SELECT * FROM pg_proc WHERE proparallel = 's'
wouldn't actually return all the functions that are parallel safe.
Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.
Thanks!
Stephen
On 27/11/2018 01:05, Stephen Frost wrote:
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.
Does that mean we also shouldn't assume that functions marked as
immutable are index safe?
User error is user error.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hi,
On 2018-11-26 19:05:02 -0500, Stephen Frost wrote:
Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.
That doesn't help if a user writes a query to review the not parallel
safe functions in their installation.
Greetings,
Andres Freund
Greetings,
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:05, Stephen Frost wrote:
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.Does that mean we also shouldn't assume that functions marked as
immutable are index safe?
We've got an index safe flag?
That's news to me.
Thanks!
Stephen
On 27/11/2018 01:10, Stephen Frost wrote:
Greetings,
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:05, Stephen Frost wrote:
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.Does that mean we also shouldn't assume that functions marked as
immutable are index safe?We've got an index safe flag?
Yes. It's called provolatile='i'.
That's news to me.
Thanks!
You're welcome.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Greetings,
* Andres Freund (andres@anarazel.de) wrote:
On 2018-11-26 19:05:02 -0500, Stephen Frost wrote:
Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.That doesn't help if a user writes a query to review the not parallel
safe functions in their installation.
I'm really not sure what you're getting at here..?
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.
My suggestion for a regression test was to make PG developers really
think about if their new immutable functions should also be marked as
parallel safe, in the event that they forget to mark it as such. If
it's really immutable and not parallel safe, then they need to adjust
the expected regression test output (and we can all see it...).
Thanks!
Stephen
Greetings,
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:10, Stephen Frost wrote:
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:05, Stephen Frost wrote:
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.Does that mean we also shouldn't assume that functions marked as
immutable are index safe?We've got an index safe flag?
Yes. It's called provolatile='i'.
... and we complain if someone tries to use a provolatile <> 'i'
function directly in an index, so not sure what you're getting at here?
Thanks!
Stephen
On 2018-11-26 19:13:17 -0500, Stephen Frost wrote:
Greetings,
* Andres Freund (andres@anarazel.de) wrote:
On 2018-11-26 19:05:02 -0500, Stephen Frost wrote:
Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.That doesn't help if a user writes a query to review the not parallel
safe functions in their installation.I'm really not sure what you're getting at here..?
You replied to me saying it'd be a bad idea to infer parallel safety
from immutability:
Surely a simple rule could be made that provolatile='i' trumps
proparallel. No need to make them agree.
[...]I think it'd be entirely unacceptable that
SELECT * FROM pg_proc WHERE proparallel = 's'
wouldn't actually return all the functions that are parallel safe.Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.
I'm saying that that doesn't address my concern.
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.My suggestion for a regression test was to make PG developers really
think about if their new immutable functions should also be marked as
parallel safe, in the event that they forget to mark it as such. If
it's really immutable and not parallel safe, then they need to adjust
the expected regression test output (and we can all see it...).
See http://archives.postgresql.org/message-id/20181126234521.rh3grz7aavx2ubjv%40alap3.anarazel.de
Greetings,
Andres Freund
Greetings,
* Andres Freund (andres@anarazel.de) wrote:
On 2018-11-26 19:13:17 -0500, Stephen Frost wrote:
* Andres Freund (andres@anarazel.de) wrote:
On 2018-11-26 19:05:02 -0500, Stephen Frost wrote:
Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.That doesn't help if a user writes a query to review the not parallel
safe functions in their installation.I'm really not sure what you're getting at here..?
You replied to me saying it'd be a bad idea to infer parallel safety
from immutability:
I don't think we should programatically assume it, but given that it's
very likely the case, we should have a check (as you suggested in the
other thread) that makes PG developers at least think about it.
Surely a simple rule could be made that provolatile='i' trumps
proparallel. No need to make them agree.
[...]I think it'd be entirely unacceptable that
SELECT * FROM pg_proc WHERE proparallel = 's'
wouldn't actually return all the functions that are parallel safe.Agreed, but I could see us having a regression test which complains if
it finds any which are marked as immutable but aren't parallel safe.I'm saying that that doesn't address my concern.
Then I'm unclear as to what the concern here is..?
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.My suggestion for a regression test was to make PG developers really
think about if their new immutable functions should also be marked as
parallel safe, in the event that they forget to mark it as such. If
it's really immutable and not parallel safe, then they need to adjust
the expected regression test output (and we can all see it...).See http://archives.postgresql.org/message-id/20181126234521.rh3grz7aavx2ubjv%40alap3.anarazel.de
Right, so you're also suggestion we have a regression test for it..
I'm starting to wonder if maybe we're in violent agreement here..?
Thanks!
Stephen
On 27/11/2018 01:14, Stephen Frost wrote:
Greetings,
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:10, Stephen Frost wrote:
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:05, Stephen Frost wrote:
That said, I do *not* think we should make any assumptions here- users
incorrectly mark things all the time but we shouldn't encourage that and
we shouldn't assume that functions marked as immutable are parallel
safe.Does that mean we also shouldn't assume that functions marked as
immutable are index safe?We've got an index safe flag?
Yes. It's called provolatile='i'.
... and we complain if someone tries to use a provolatile <> 'i'
function directly in an index, so not sure what you're getting at here?
I'm getting at we should do the same for parallel safety checks.
If it's immutable, it's safe.
If it's not immutable, check if it's safe.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 27/11/2018 01:13, Stephen Frost wrote:
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.
Yes we should! Unless you can produce a case where an immutable
function is not parallel safe.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hi,
On 2018-11-27 01:27:41 +0100, Vik Fearing wrote:
On 27/11/2018 01:13, Stephen Frost wrote:
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.Yes we should! Unless you can produce a case where an immutable
function is not parallel safe.
Well, then write a patch that documents that behaviour, automatically
sets proparallel accordingly, and defines an escape hatch for when the
user actually meant unsafe, not just "unsafe maybe". Just saying "we
should" doesn't go far actually convincing anybody.
Greetings,
Andres Freund
FWIW, I'm inclined to think that pg_config should be marked as stable
not immutable. Aside from the minor-version-upgrade issue, what if
you install new binaries that are the same version but built with
different configure flags?
The pg_config outputs are roughly as stable as initdb-inserted
catalog data, and we've never judged that functions that return
catalog data can be marked immutable.
There seems no reason it can't be parallel safe, though, since
worker processes should get the same answers as their parent.
regards, tom lane
Greetings,
* Vik Fearing (vik.fearing@2ndquadrant.com) wrote:
On 27/11/2018 01:13, Stephen Frost wrote:
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.Yes we should! Unless you can produce a case where an immutable
function is not parallel safe.
What's the advantage of running a function that isn't marked as parallel
safe in a parallel worker because it's marked as immutable?
Seems like the only thing we're doing is making assumptions about what
the user meant when they've clearly told us something different and that
just strikes me as both a bad idea and an unnecessary complication of
the code.
Thanks!
Stephen
Vik Fearing <vik.fearing@2ndquadrant.com> writes:
On 27/11/2018 01:13, Stephen Frost wrote:
Parallel safe functions should be marked as such. Immutable functions
should be marked as such. We should not assume that one implies the
other, nor should we operate as if they do.
Yes we should! Unless you can produce a case where an immutable
function is not parallel safe.
As far as that goes, I agree with the idea of adding an oprsanity test
that shows any built-in functions that are immutable but not parallel
safe, on the grounds Stephen mentioned: it's probably a mistake, and
if it isn't, we can add that function to the expected output.
I'm way less inclined to buy into the idea that it MUST be wrong, though.
Immutability is a promise about result stability and lack of side effects,
but it is not a promise about implementation details. There could be an
implementation reason not to run something in a parallel worker. Off the
top of my head, a possible example is "it's written in plfoo which hasn't
yet been made to work correctly in parallel workers".
regards, tom lane
