Geospatial functionality unavailable on SQL-only pods

[kernfeld-cockroach]

Describe the problem

Although I'm able to successfully use geospatial functionality when running CRDB by itself, it doesn't appear to work from a SQL pod.

To Reproduce

Terminal 1:

1. cockroach start-single-node --listen-addr=localhost --insecure

Terminal 2:

1. cockroach sql --insecure -e 'select crdb_internal.create_tenant(123);'
2. cockroach mt start-sql --insecure --kv-addrs localhost:26257 --sql-addr 0.0.0.0:36257 --http-addr localhost:8081 --tenant-id 123

Terminal 3:

1. cockroach sql --insecure --url postgresql://localhost:36257 -e "SELECT ST_AsText( ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );"

output:

ERROR: st_astext(): st_polygon(): could not parse geometry: geos: no locations to init GEOS
SQLSTATE: 22023
HINT: Ensure you have the spatial libraries installed as per the instructions in https://www.cockroachlabs.com/docs/v21.1/install-cockroachdb-mac
Failed running "sql"

Expected behavior

Query in terminal 3 returns:

                st_astext
------------------------------------------
  POLYGON ((75 29, 77 29, 77 29, 75 29))
(1 row)

Time: 1ms

Additional data / screenshots

I confirmed that geospatial functionality does work on the normal CRDB server by running (note port 26257 rather than 36257):

cockroach sql --insecure --url postgresql://localhost:26257 -e "SELECT ST_AsText( ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );"

Environment:

• CockroachDB version: 20.2.3
• Server OS: MacOS 10.15.7, although this bug appears to also exist in our official Docker images
• Client app: cockroach sql

Additional context

This blocks our internal JIRA ticket CC-3225.

CC @andy-kimball

[andy-kimball]

CC @otan, @sumeerbhola, @awoods187

[jordanlewis]

Following the given steps, this doesn't repro for me. I wonder what could be different.

[petermattis]

Is the geos lib at a good location relative to where cockroach mt start-sql is run in step 2? The error message seems to indicate that it is not.

[otan]

mt doesn't start up the GEOS library, cause i did not realise that was an entry point.

any place i could add tests to catch this?

[otan]

ah nah, i think this is a case of starting cockroach from the "wrong directory".

if the lib directory is in the right place, then the second terminal should initialise GEOS correctly. If we're executing this from $GOPATH/src/github.com/cockroachdb/cockroach and using ./cockroach from the same location, that works as expected as ./lib is populated with the correct libraries. This is probably why @jordanlewis could not reproduce this.

fix incoming!

[kernfeld-cockroach]

Well, it sounds like @otan has already figured it out... but here is how I reproduced this in Docker:

Terminal 1:

1. (If you haven't already created roachnet) docker network create -d bridge roachnet
2. docker run --name=mamaroach --hostname=mamaroach --net=roachnet -p 26257:26257 --rm cockroachdb/cockroach:v20.2.3 start-single-node --insecure

Terminal 2:

1. cockroach sql --insecure -e 'select crdb_internal.create_tenant(123);'
2. docker run --name=babyroach --hostname=babyroach --net=roachnet -p 36257:36257 --rm cockroachdb/cockroach:v20.2.3 mt start-sql --insecure --kv-addrs mamaroach:26257 --sql-addr 0.0.0.0:36257 --tenant-id 123

Terminal 3: cockroach sql --insecure --url postgresql://localhost:36257 -e "SELECT ST_AsText( ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );"

[otan]

@kernfeld-cockroach You can work around this by making sure the lib directory is in same directory as where you are running cockroach from / where the cockroach binary location is. However, when https://github.com/cockroachdb/cockroach/pull/59259 merges you can specify where the GEOS libraries are using --geos-libs (which is the default)

EDIT: looks like the docker contains put the libraries in a static directory, so yeah you'd need to wait for a customer docker container or https://github.com/cockroachdb/cockroach/pull/59259 to go through!

[kernfeld-cockroach]

@otan thanks for jumping on this so quickly!! How long do you think it would take for these fixes to make their way into an official Docker container? Alternatively, do you think there is a workaround where we could still use the current official Docker images, e.g. by overriding ENTRYPOINT with a shell command to cd to the right directory?

[otan]

@kernfeld-cockroach i think you can get around this by hacking our docker image to copy the contents of /usr/local/lib/cockroach/ to /cockroach/lib.

to make it to a Docker container needs a release cycle. We either delay v20.2.4 (https://github.com/cockroachdb/cockroach/issues/58871), or wait til v20.2.5 which looking at the release calendar isn't scheduled (curiously v20.2.6 is scheduled for mid-Feb)

[andy-kimball]

Once you've merged this fix into the 20.2 branch, we'll get a nightly Docker build at https://console.cloud.google.com/gcr/images/cockroach-cloud-images/US/cockroach. We can then upgrade Staging to use a nightly build. Assuming that works well, I'm comfortable upgrading Production as well. Free-tier is in beta, so I think it's fine to use a patched version of the latest 20.2 branch rather than official dot release certified build. We might also consider asking for an "extraordinary release" if we're worried.