search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
9.04k stars 1.08k forks source link

postgis not working for spatial query #9478

Open becks9999 opened 3 years ago

becks9999 commented 3 years ago 
FROM yugabytedb/yugabyte:2.7.1.1-b1

RUN rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN yum update -y
RUN yum install -y postgresql11-server postgis31_11

RUN cp -v "$(/usr/pgsql-11/bin/pg_config --pkglibdir)"/*postgis*.so "$(/home/yugabyte/postgres/bin/pg_config --pkglibdir)"
RUN cp -v "$(/usr/pgsql-11/bin/pg_config --sharedir)"/extension/*postgis*.sql "$(/home/yugabyte/postgres/bin/pg_config --sharedir)"/extension
RUN cp -v "$(/usr/pgsql-11/bin/pg_config --sharedir)"/extension/*postgis*.control "$(/home/yugabyte/postgres/bin/pg_config --sharedir)"/extension

RUN /home/yugabyte/bin/post_install.sh -e

Below are the sql

CREATE EXTENSION postgis;
CREATE TABLE roads (road_id serial, road_name text, roads_geom geometry)
INSERT INTO roads (road_id, road_name, roads_geom) VALUES (1,‘test’,‘POINT(0 1)’);
SELECT * from roads where ST_Equals(roads_geom, ST_GeomFromText(‘POINT(0 1)’,64));

I got an error

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed.

Error logs from tserver

2021-07-27 13:38:26.039 UTC [46] LOG: server process (PID 445) was terminated by signal 11: Segmentation fault 2021-07-27 13:38:26.039 UTC [46] DETAIL: Failed process was running: select * from roads where ST_Equals(roads_geom, ST_GeomFromText('POINT(0 0)',64)); 2021-07-27 13:38:26.039 UTC [46] LOG: terminating any other active server processes 2021-07-27 13:38:26.040 UTC [455] FATAL: the database system is in recovery mode 2021-07-27 13:38:26.045 UTC [46] LOG: all server processes terminated; reinitializing 2021-07-27 13:38:26.065 UTC [456] LOG: database system was interrupted; last known up at 2021-07-27 13:36:13 UTC 2021-07-27 13:38:26.097 UTC [456] LOG: database system was not properly shut down; automatic recovery in progress 2021-07-27 13:38:26.101 UTC [456] LOG: invalid record length at 0/10002C8: wanted 24, got 0 2021-07-27 13:38:26.101 UTC [456] LOG: redo is not required 2021-07-27 13:38:26.134 UTC [46] LOG: database system is ready to accept connections

Is there any wrong with my sql statment? Thanks

ddorian commented 3 years ago

@becks9999 are there any errors on .postgresql files ? Is there a core dump being created ?

becks9999 commented 3 years ago

@ddorian sorry that the errors on .postgresql files Where do i see the core dump? is it same directory as .postgresql files

ddorian commented 3 years ago

@becks9999 the core dump location depends on your operating system

becks9999 commented 3 years ago

I using the yugabyte docker images yugabytedb/yugabyte:2.7.1.1-b1

becks9999 commented 3 years ago

Anyone have a working postgis dockerfile? Or my dockerfile is correct just sql statment is wrong?

ddorian commented 3 years ago

@becks9999

Can you try with the latest version released today 2.7.2.0 ? Start from scratch and retry. I believe it may be connected to https://github.com/yugabyte/yugabyte-db/issues/9208.

becks9999 commented 3 years ago

cool. I do the same as my first post and is working fine. Will try with other queries. Thanks @ddorian