Thread: parallel query vs extensions
I think there are a lot of extensions which create functions which could benefit from being declared parallel safe. But how does one go about doing that? create extension xml2; select xml_valid(filler),count(*) from pgbench_accounts group by 1;Time: 3205.830 ms alter function xml_valid (text) parallel safe; select xml_valid(filler),count(*) from pgbench_accounts group by 1; Time: 1803.936 ms (Note that I have no particular interest in the xml2 extension, it just provides a convenient demonstration of the general principle) Should every relevant contrib extension get a version bump with a transition file which is nothing but a list of "alter function blah blah blah parallel safe" ? And what of non-contrib extensions? Is there some clever alternative to having a bunch of pseudo versions, like "1.0", "1.0_96", "1.1", "1.1_9.6", "1.2", "1.2_96", etc.? Cheers, Jeff
On 15 April 2016 at 12:45, Jeff Janes <jeff.janes@gmail.com> wrote:
I think there are a lot of extensions which create functions which
could benefit from being declared parallel safe. But how does one go
about doing that?
create extension xml2;
select xml_valid(filler),count(*) from pgbench_accounts group by 1;
Time: 3205.830 ms
alter function xml_valid (text) parallel safe;
select xml_valid(filler),count(*) from pgbench_accounts group by 1;
Time: 1803.936 ms
(Note that I have no particular interest in the xml2 extension, it
just provides a convenient demonstration of the general principle)
Should every relevant contrib extension get a version bump with a
transition file which is nothing but a list of "alter function blah
blah blah parallel safe" ?
And what of non-contrib extensions? Is there some clever alternative
to having a bunch of pseudo versions, like "1.0", "1.0_96", "1.1",
"1.1_9.6", "1.2", "1.2_96", etc.?
What I've done in the past for similar problems is preprocess the extension--x.y.sql files in the Makefile to conditionally remove unsupported syntax, functions, etc.
It's rather less than perfect because if the user pg_upgrades they won't get the now-supported options. They'll have the old-version extension on the new version and would have to drop & re-create to get the new version contents.
You could create variant pseudo-extensions to make this clearer - myext95--1.0.sql, myext96--1.0.sql, etc - but there's still no way to ALTER EXTENSION to upgrade. pseudo-versions like you suggest are probably going to work, but the extension machinery doesn't understand them and you can only specify one of them as the default in the control file.
On Fri, Apr 15, 2016 at 12:45 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > Should every relevant contrib extension get a version bump with a > transition file which is nothing but a list of "alter function blah > blah blah parallel safe" ? Yes, I think that's what we would need to do. It's a lot of work, albeit mostly mechanical. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Apr 18, 2016 at 09:56:28AM -0400, Robert Haas wrote: > On Fri, Apr 15, 2016 at 12:45 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > > Should every relevant contrib extension get a version bump with a > > transition file which is nothing but a list of "alter function blah > > blah blah parallel safe" ? > > Yes, I think that's what we would need to do. It's a lot of work, > albeit mostly mechanical. This is in the open items list, but I think it is too late to include such a change in 9.6. This is an opportunity for further optimization, not a defect.