*First Animation GIF*
<https://www.dropbox.com/s/uui1kkxf8mylylg/demo1.gif?dl=0>
*Second Animation GIF*
<https://www.dropbox.com/s/vq9j2t8ltibnee8/demo2.gif?dl=0>
On Sunday, July 26, 2020 at 9:55:41 PM UTC+3, Aryk Grosz wrote:
>
> I'm deploying my app to production and I have a very strange bug.
>
> The values that are coming back from using the <-> postgis distance
> operator are missing 1 or 2 characters of precision in production and
> *only* when retrieved through Ruby code using Sequel. I am running slightly
> different versions of postgres on development and production, but the issue
> that even within the production environment, it loses precision and causes
> cursor pagination to not perform correctly. For example. 10.123456782 will
> become 10.12345678 when it comes into Ruby and then when it looks for
> something greater than that, it returns the same result again. since
> 10.123456782 > 10.12345678.
>
> Production System:
> * Aurora Postgres on Amazon - PostgreSQL 11.7 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 4.9.3, 64-bit
> * POSTGIS="2.5.2" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2
> 4d2925d6" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.1,
> released 2018/06/22" LIBXML="2.9.3" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0"
> RASTER
>
> Development System:
> * PostgreSQL 12.3 on x86_64-apple-darwin18.7.0, compiled by Apple clang
> version 11.0.0 (clang-1100.0.33.17), 64-bit
> * POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120"
> GEOS="3.8.1-CAPI-1.13.3" PROJ="7.1.0" LIBXML="2.9.10" LIBJSON="0.14"
> LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"
>
>
> From my development environment I connected to both my production DB and
> to my local DB so that I would be using the same code and gems. I put the
> same data in both databases, and ran queries calculating distance using the
> <-> operator.
>
> Here is what I ran:
>
> User.select(:id, Sequel.lit("location_point <->
> 'SRID=4326;POINT(14.43280472 50.0715455)' as test")).order(:id).all
>
> I've created an animation so you can see what happens when I switched
> tabs. The longer results are from my development environment. In
> production, they are getting truncated.
>
>
> [image: demo1.gif]
>
>
>
> I then decided to go into my DB gui and run some raw sql functions:
>
>
> *SELECT* "id", location_point <-> 'SRID=4326;POINT(14.43280472
> 50.0715455)' *as* test,
>
> st_distance(location_point, 'SRID=4326;POINT(14.43280472 50.0715455)',
> *false*) *as* test1,
>
> st_distance(location_point, 'SRID=4326;POINT(14.43280472 50.0715455)',
> *true*) *as* test2 *FROM* "users" *ORDER* *BY* "id"
>
>
> Here are the results. Again, I'm switching back-and-forth so you can see
> the difference:
>
>
> [image: demo2.gif]
>
>
>
> I'm not surprised that the <-> is giving different results, but I wasn't
> expecting it to be taken out of the database with less precision than the
> database calculates it at. Again, this is causing issues with my cursor
> pagination, so looks like I'm going to switch to using
> st_distance(location_point,
> 'SRID=4326;POINT(14.43280472 50.0715455)', *false*) which is close in
> value and supposed to be faster then with "true".
>
>
> Any experts out there have seen this issue before and give me some advice?
> Is the solution simply to use st_distance whenever I'm doing cursor
> pagination?
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/b46fb90a-880c-46f0-af7e-bd700310f35co%40googlegroups.com.
