Invalid PROJ string in ST_Transform breaks subsequent queries

[kszafran]

If I call ST_Transform with a valid SRID, then with an invalid PROJ string, and then a valid SRID, the third query fails with some error. I have tested this on a few different setups and the exact error differs between them.

To reproduce, run these queries one after another:

test=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);
... (correct response)

test=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), '+wat');
ERROR:  could not parse proj string '+wat'
CONTEXT:  SQL function "st_transform" statement 1

test=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);
ERROR:  (message depends on the setup, see below) 

test=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);
... (correct response)

Here are the setups I've tested and the error they give:

1. Running a custom Docker image:

   PostgreSQL 12.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
   POSTGIS="3.1.5 0" [EXTENSION] PGSQL="120" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.0" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.14" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
   
   ERROR:  transform: Unknown error (code 4096) (4096)
   

2. Running in AWS Aurora:

   PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit
   POSTGIS="3.1.5 0" [EXTENSION] PGSQL="120" GEOS="3.8.2-CAPI-1.13.4" PROJ="6.2.1" LIBXML="2.9.9" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"
   
   ERROR:  transform: generic error of unknown origin (-61)
   

3. Running this Docker image: postgis/postgis:14-3.2-alpine

   PostgreSQL 14.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
   POSTGIS="3.2.1 0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="9.0.0" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" TOPOLOGY
   
   ERROR:  transform: Invalid PROJ string syntax (1025)
   

4. Running this Docker image: postgis/postgis:15beta2-3.3.0beta1-alpine

   PostgreSQL 15beta2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
   POSTGIS="3.3.0beta1 0" [EXTENSION] PGSQL="150" GEOS="3.10.2-CAPI-1.16.0" PROJ="9.0.0" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" TOPOLOGY
   
   ERROR:  transform: Invalid PROJ string syntax (1025)
   

If this is fixed, a backport to 3.1.x will be highly appreciated.

[robe]

I've pushed this back one micro, sadly too late for this release.

I confirm, I get the same error on:

POSTGIS="3.2.0 3.2.0" [EXTENSION] PGSQL="130" GEOS="3.10.1-CAPI-1.16.0" SFCGAL="1.4.0" PROJ="7.2.1" GDAL="GDAL 3.3.3, released 2021/10/25" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit

when doing this:

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), '+wat');
-- could not parse proj string '+wat'

then following with:

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);
-- get error ERROR:  transform: generic error of unknown origin (-61)

So I suspect it's our proj caching layer at fault here and it's probably caching something from the bad transform which is screwing it up.

[pramsey]

No problems here

pramsey=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), '+wat');
ERROR:  could not parse proj string '+wat'
CONTEXT:  SQL function "st_transform" statement 1
pramsey=# SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);
                    st_transform                    
----------------------------------------------------
 0101000020230F0000258F57505E567241549FA0B755BD0C41
(1 row)

pramsey=# select postgis_full_version();
                                                                                                                  postgis_full_version                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.3.0dev 3.3.0beta2-19-ga80b805b0" [EXTENSION] PGSQL="140" GEOS="3.11.0dev-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" (core procs from "3.3.0dev 3.2.0-867-g216aca648" need upgrade)
(1 row)

[pramsey]

Also "works for me" in 3.2.1dev.

[robe]

I still get the error on 3.2.2 released and 3.2.3dev . I don't get it running pramsey's example though.

Seems you have to do all 3 to trigger the issue. If you leave out the first query the problem doesn't happen. It's almost as if the system first caches the answer of 3875 on first run. The error pollutes that cache, and the 3rd tries to use the polluted cache. without that first run seems the system thinks it a fresh request so doesn't attempt to use cache.

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), '+wat');

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(1, 2), 4326), 3875);

[Paul Ramsey <pramsey@…>]

In 6424da43/git:

Reset errno in case of bad projtext parse, References #5181

[Paul Ramsey <pramsey@…>]

In 330a37b/git:

Reset errno in case of bad projtext parse, References #5181

[Paul Ramsey <pramsey@…>]

In caafaab3/git:

Reset errno in case of bad projtext parse, References #5181