Sorry the animations stopped working once I posted to Google groups. I put them up here:
*First GIF* <https://www.dropbox.com/s/uui1kkxf8mylylg/demo1.gif?dl=0> *Second 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/f3054fd8-b901-42d7-bfbd-a600560b5187o%40googlegroups.com.
