Search code examples
ruby-on-railspostgresqldockerpostgissidekiq

Intermittent error in PostGIS query via Rails (string interpolation failure?)

I have an intermittent error come up after some deploys of my Rails app.

This code is running in Sidekiq (5 processes each with 10 threads), which is running in a Docker container. I can have tens of thousands of these jobs queued up at any point.

path = Path.find(path_id)
nearby_nodes = Node.where("ST_DWITHIN(geog, ST_GeographyFromText(?), 25)", path.geog.to_s)

The error is:

ActiveRecord::StatementInvalid: PG::InternalError: ERROR: parse error - invalid geometry
HINT: "01" <-- parse error at position 2 within geometry
PG::InternalError: ERROR: parse error - invalid geometry
HINT: "01" <-- parse error at position 2 within geometry

I can get these jobs to run successfully if I quiet all the Sidekiq processes, stop the workers, wait a moment, then start the workers back up.

I added a number of delays to my deploy process (guessing that slowing things down might help, if restarting workers solves the problem), but that did not help.

I can usually get one successful deploy per day. After that first deploy, it's more likely to fall into this failure state & if it gets into this state every deploy thereafter will cause this same issue.

Path.first.geog returns: #<RGeo::Geographic::SphericalPointImpl:0x3ffd8b2a6688 "POINT (-72.633932 42.206081)">

Path.first.geog.class returns: RGeo::Geographic::SphericalPointImpl

I've tried a number of different formats of this query, which might shed some light on how/why this is failing (though I'm still stumped as to why it's only intermittent):

  1. Node.where("ST_DWITHIN(geog, ST_GeographyFromText(?), 25)", path.geog) fails, generating this query:
Node Load (1.0ms)  SELECT "nodes".* FROM "nodes" WHERE (ST_DWITHIN(geog, ST_GeographyFromText('0020000001000010e6c05228925785f8d340451a60dcb9a9da'), 25)) LIMIT $1  [["LIMIT", 11]]

and this error:

ActiveRecord::StatementInvalid (PG::InternalError: ERROR:  parse error - invalid geometry)
HINT:  "00" <-- parse error at position 2 within geometry
  1. Node.where("ST_DWITHIN(geog, ST_GeographyFromText('#{path.geog}'), 25)") succeeds, generating this query:
Node Load (5.1ms)  SELECT "nodes".* FROM "nodes" WHERE (ST_DWITHIN(geog, ST_GeographyFromText('POINT (-72.633932 42.206081)'), 25)) LIMIT $1  [["LIMIT", 11]]
  1. Node.where("ST_DWITHIN(geog, ST_GeographyFromText(?), 25)", path.geog.to_s) also succeeds, generating the same query:
Node Load (2.3ms)  SELECT "nodes".* FROM "nodes" WHERE (ST_DWITHIN(geog, ST_GeographyFromText('POINT (-72.633932 42.206081)'), 25)) LIMIT $1  [["LIMIT", 11]]
  1. Doing the to_s conversion in a preceding line as some kind of superstitious test also works:
geog_string  = path.geog.to_s
nearby_nodes = Node.where("ST_DWITHIN(geog, ST_GeographyFromText(?), 25)", geog_string)

Queries 2-4 generally work, but behave like query number 1 some of the time and only after a deploy. I could not make 2-4 behave like the first query in a Rails console. The only time queries 2-4 behave like the first query is in a Sidekiq job after a deploy. It's as if the string conversion isn't working sometimes.

Here's a list of potentially relevant gems/versions:

  • activerecord-postgis-adapter (6.0.0)
  • pg (1.2.3)
  • rails (6.0.2.2)
  • rgeo (2.1.1)
  • rgeo-activerecord (6.2.1)
  • sidekiq (6.0.6)
  • Ruby 2.6.6
  • PostgreSQL 11.6
  • PostGIS 2.5.2
  • Docker 19.03.8, build afacb8b7f0
Solution

  • There is no need to convert the geography to a string, and then to read it back as a geography.

    You can try directly

    Node.where("ST_DWITHIN(geog, ?, 25)", path.geog)

    That being said, you may indeed have some invalid geometries