ST_Transform fails in parallel mode

[pramsey]

From an example query:

ebc_13_17=# create table db_2011 as 
  select st_transform(g.geom,3005) as geom, g.dbuid, a.dbpop2011, a.dbtdwell2011, a.dburdwell2011 
  from db_attr_2011 a join db_gis_2011 g 
  using (dbuid) where g.pruid = '59' ;

ERROR:  cannot start commands during a parallel operation
CONTEXT:  SQL statement "SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1"

Looks like the underlying proj lookup using SPI is not happy w/ the parallel executor.

[robe]

I wonder does the behavior change if we mark ST_Transform as Stable instead of IMMUTABLE?

[pramsey]

Well, I think it makes sense to make that change in principle, since ST_Transform is not immutable, but it is stable.

However, for the problem at hand, it turns out we can probably dodge it with just the change in r16248. I still have to test to confirm, but from reading the pgsql code and docs, it should be sufficient to dodge the error case.

[pramsey]

This is more specific than I thought. Using our Carto pgsql branch, which has a backport of the pg11 parallel "create table as" support in it, and changing to SPI_execute, causes a crash condition. Using generic pg10, there is no parallel mode, and no crash condition. Running the ST_Transform query without the "create table as" part causes no problems even when a parallel plan is used. So the problem is (currently) specific to the carto branch, but might also be something that is an issue in pg11 since that's where we pulled the "create table as" support from.

Next test: on pg11 is this use case (parallel "create table as") problematic.

[pramsey]

OK, so the problem has nothing to do w/ ST_Transform, and the fix in r16248 is still worth applying "on the merits", since, per the pgsql documentation, marking the SPI routine as read_only has the effect of rendering functions "truly stable", so we can also mark st_transform as stable and feel good about it.

I can crash our carto branch of pgsql just be forcing it into a parallel "create table as", so the problem has nothing to do w/ postgis and is contained in our own hackery.

[pramsey]

Not a problem in community or even in Carto anymore.