Re: parallel query vs extensions

On 15 April 2016 at 12:45, Jeff Janes <jeff(dot)janes(at)gmail(dot)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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services