Re: Postgres segfaults on raster query
Thanks for the tip! I found the root of the problem. When I ran pg_config it returned config info for pg 15. That's great, but this db is running on pg 13! Not long ago, apt upgrade added pg 15, which I did not want. I thought I removed, purged it. Apparently not. I just physically moved the /usr/lib/postgresql/15. Now pg_config returns pg 13 info. Obviously, there was no postgis installed on pg 15. pg_config is returning the correct info...now. Restarted the db and all is well. On 1/18/24 21:06, Regina Obe wrote: This seems… odd to me? In what context is putting postgis into the preload a requirement? P On Jan 18, 2024, at 8:20 PM, Scott wrote: Bam! That was it. Thanks Regina, you rock! On 1/18/24 20:16, Regina Obe wrote: ALTER SYSTEM SET shared_preload_libraries = 'postgis-3'; The only time I've seen it be a requirement is with mobilitydb, where they are relying on the postgis-3 library to be already loaded and are dynamically linking to it. I recall I used to see reference to the postgis-3 when I'd do ldd /usr/lib/postgresql/16/lib/libMobilityDB-1.1.so When it's not preloaded, mobilitydb crashes in a similar fashion which is why I thought about this. Though I'm not seeing reference to that anymore though (or maybe I only saw is on windows), but it still relies on postgis-3 to load. So when Scott said it works if he runs postgis_full_version() before hand, got me thinking, it must be relying on postgis already loaded and postgis_full_version() loads postgis lib first. But for postgis_raster that should not be required, since postgis_raster has the liblwgeom baked into it so it never links directly to postgis-3 or at least shouldn't be. @Scott, Can you run an ldd check on your postgis_raster-3? If you don't know where it is pg_config | grep PKGLIBDIR Should tell you the folder Mine looks like below -- verify you don't have reference to postgis-3.so ldd /usr/lib/postgresql/16/lib/postgis_raster-3.so linux-vdso.so.1 (0x7ffd7efda000) libgdal.so.34 => /lib/x86_64-linux-gnu/libgdal.so.34 (0x7f73f3b59000) libgeos_c.so.1 => /usr/local/lib/libgeos_c.so.1 (0x7f73f3b0c000) libproj.so.25 => /lib/x86_64-linux-gnu/libproj.so.25 (0x7f73f370b000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f73f362c000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f73f344a000) libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x7f73f342b000) libcurl-gnutls.so.4 => /lib/x86_64-linux-gnu/libcurl-gnutls.so.4 (0x7f73f336e000) libodbc.so.2 => /lib/x86_64-linux-gnu/libodbc.so.2 (0x7f73f32fd000) libodbcinst.so.2 => /lib/x86_64-linux-gnu/libodbcinst.so.2 (0x7f73f32e5000) libxml2.so.2 => /lib/x86_64-linux-gnu/libxml2.so.2 (0x7f73f3133000) libcrypto.so.3 => /lib/x86_64-linux-gnu/libcrypto.so.3 (0x7f73f2cac000) liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x7f73f2c7c000) libdeflate.so.0 => /lib/x86_64-linux-gnu/libdeflate.so.0 (0x7f73f2c66000) liblz4.so.1 => /lib/x86_64-linux-gnu/liblz4.so.1 (0x7f73f2c4) libblosc.so.1 => /lib/x86_64-linux-gnu/libblosc.so.1 (0x7f73f2c3) libarmadillo.so.12 => /lib/libarmadillo.so.12 (0x7f73f2c1e000) libqhull_r.so.8.0 => /lib/x86_64-linux-gnu/libqhull_r.so.8.0 (0x7f73f2ba8000) libxerces-c-3.2.so => /lib/x86_64-linux-gnu/libxerces-c-3.2.so (0x7f73f280d000) libjpeg.so.62 => /lib/x86_64-linux-gnu/libjpeg.so.62 (0x7f73f2778000) libtiff.so.6 => /lib/x86_64-linux-gnu/libtiff.so.6 (0x7f73f26eb000) libgeotiff.so.5 => /lib/x86_64-linux-gnu/libgeotiff.so.5 (0x7f73f26b5000) libheif.so.1 => /lib/x86_64-linux-gnu/libheif.so.1 (0x7f73f25dc000) libpng16.so.16 => /lib/x86_64-linux-gnu/libpng16.so.16 (0x7f73f25a6000) libzstd.so.1 => /lib/x86_64-linux-gnu/libzstd.so.1 (0x7f73f24e5000) libaec.so.0 => /lib/x86_64-linux-gnu/libaec.so.0 (0x7f73f24d9000) libpoppler.so.126 => /lib/x86_64-linux-gnu/libpoppler.so.126 (0x7f73f2144000) libgif.so.7 => /lib/x86_64-linux-gnu/libgif.so.7 (0x7f73f2139000) libnetcdf.so.19 => /lib/x86_64-linux-gnu/libnetcdf.so.19 (0x7f73f1f3d000) libcfitsio.so.10 => /lib/x86_64-linux-gnu/libcfitsio.so.10 (0x7f73f1c21000) libhdf5_serial.so.103 => /lib/x86_64-linux-gnu/libhdf5_serial.so.103 (0x7f73f1868000) libwebp.so.7 => /lib/x86_64-linux-gnu/libwebp.so.7 (0x7f73f17e9000) libsqlite3.so.0 => /lib/x86_64-linux-gnu/libsqlite3.so.0 (0x7f73f1679000) libpq.so.5 => /lib/x86_64-linux-gnu/libpq.so.5 (0x7f73f1622000) libopenjp2.so.7 => /lib/x86_64-linux-gnu/libopenjp2.so.7 (0x7f73f15be000) libkmlbase.so.1 => /lib/x86_64-linux-gnu/libkmlbase.so.1 (0x7f73f15a1000) libkmldom.so.1
RE: Postgres segfaults on raster query
> This seems… odd to me? In what context is putting postgis into the preload a > requirement? > > P > > > On Jan 18, 2024, at 8:20 PM, Scott wrote: > > > > Bam! > > > > That was it. Thanks Regina, you rock! > > > > On 1/18/24 20:16, Regina Obe wrote: > >> ALTER SYSTEM SET shared_preload_libraries = 'postgis-3'; The only time I've seen it be a requirement is with mobilitydb, where they are relying on the postgis-3 library to be already loaded and are dynamically linking to it. I recall I used to see reference to the postgis-3 when I'd do ldd /usr/lib/postgresql/16/lib/libMobilityDB-1.1.so When it's not preloaded, mobilitydb crashes in a similar fashion which is why I thought about this. Though I'm not seeing reference to that anymore though (or maybe I only saw is on windows), but it still relies on postgis-3 to load. So when Scott said it works if he runs postgis_full_version() before hand, got me thinking, it must be relying on postgis already loaded and postgis_full_version() loads postgis lib first. But for postgis_raster that should not be required, since postgis_raster has the liblwgeom baked into it so it never links directly to postgis-3 or at least shouldn't be. @Scott, Can you run an ldd check on your postgis_raster-3? If you don't know where it is pg_config | grep PKGLIBDIR Should tell you the folder Mine looks like below -- verify you don't have reference to postgis-3.so ldd /usr/lib/postgresql/16/lib/postgis_raster-3.so linux-vdso.so.1 (0x7ffd7efda000) libgdal.so.34 => /lib/x86_64-linux-gnu/libgdal.so.34 (0x7f73f3b59000) libgeos_c.so.1 => /usr/local/lib/libgeos_c.so.1 (0x7f73f3b0c000) libproj.so.25 => /lib/x86_64-linux-gnu/libproj.so.25 (0x7f73f370b000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f73f362c000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f73f344a000) libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x7f73f342b000) libcurl-gnutls.so.4 => /lib/x86_64-linux-gnu/libcurl-gnutls.so.4 (0x7f73f336e000) libodbc.so.2 => /lib/x86_64-linux-gnu/libodbc.so.2 (0x7f73f32fd000) libodbcinst.so.2 => /lib/x86_64-linux-gnu/libodbcinst.so.2 (0x7f73f32e5000) libxml2.so.2 => /lib/x86_64-linux-gnu/libxml2.so.2 (0x7f73f3133000) libcrypto.so.3 => /lib/x86_64-linux-gnu/libcrypto.so.3 (0x7f73f2cac000) liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x7f73f2c7c000) libdeflate.so.0 => /lib/x86_64-linux-gnu/libdeflate.so.0 (0x7f73f2c66000) liblz4.so.1 => /lib/x86_64-linux-gnu/liblz4.so.1 (0x7f73f2c4) libblosc.so.1 => /lib/x86_64-linux-gnu/libblosc.so.1 (0x7f73f2c3) libarmadillo.so.12 => /lib/libarmadillo.so.12 (0x7f73f2c1e000) libqhull_r.so.8.0 => /lib/x86_64-linux-gnu/libqhull_r.so.8.0 (0x7f73f2ba8000) libxerces-c-3.2.so => /lib/x86_64-linux-gnu/libxerces-c-3.2.so (0x7f73f280d000) libjpeg.so.62 => /lib/x86_64-linux-gnu/libjpeg.so.62 (0x7f73f2778000) libtiff.so.6 => /lib/x86_64-linux-gnu/libtiff.so.6 (0x7f73f26eb000) libgeotiff.so.5 => /lib/x86_64-linux-gnu/libgeotiff.so.5 (0x7f73f26b5000) libheif.so.1 => /lib/x86_64-linux-gnu/libheif.so.1 (0x7f73f25dc000) libpng16.so.16 => /lib/x86_64-linux-gnu/libpng16.so.16 (0x7f73f25a6000) libzstd.so.1 => /lib/x86_64-linux-gnu/libzstd.so.1 (0x7f73f24e5000) libaec.so.0 => /lib/x86_64-linux-gnu/libaec.so.0 (0x7f73f24d9000) libpoppler.so.126 => /lib/x86_64-linux-gnu/libpoppler.so.126 (0x7f73f2144000) libgif.so.7 => /lib/x86_64-linux-gnu/libgif.so.7 (0x7f73f2139000) libnetcdf.so.19 => /lib/x86_64-linux-gnu/libnetcdf.so.19 (0x7f73f1f3d000) libcfitsio.so.10 => /lib/x86_64-linux-gnu/libcfitsio.so.10 (0x7f73f1c21000) libhdf5_serial.so.103 => /lib/x86_64-linux-gnu/libhdf5_serial.so.103 (0x7f73f1868000) libwebp.so.7 => /lib/x86_64-linux-gnu/libwebp.so.7 (0x7f73f17e9000) libsqlite3.so.0 => /lib/x86_64-linux-gnu/libsqlite3.so.0 (0x7f73f1679000) libpq.so.5 => /lib/x86_64-linux-gnu/libpq.so.5 (0x7f73f1622000) libopenjp2.so.7 => /lib/x86_64-linux-gnu/libopenjp2.so.7 (0x7f73f15be000) libkmlbase.so.1 => /lib/x86_64-linux-gnu/libkmlbase.so.1 (0x7f73f15a1000) libkmldom.so.1 => /lib/x86_64-linux-gnu/libkmldom.so.1 (0x7f73f14ff000) libkmlengine.so.1 => /lib/x86_64-linux-gnu/libkmlengine.so.1 (0x7f73f14c5000) libfyba.so.0 => /lib/x86_64-linux-gnu/libfyba.so.0 (0x7f73f146d000) libpcre2-8.so.0 => /lib/x86_64-linux-gnu/libpcre2-8.so.0 (0x7f73f13d2000) libspatialite.so.8 => /lib/x86_64-linux-gnu/libspatialite.so.8 (0x7f73f0c2c000) libmariadb.so.3 => /lib/x86_64-linux-gnu/libmariadb.so.3 (0x7f73f0bd6000) libfreexl.so.1 =>
Re: Postgres segfaults on raster query
This seems… odd to me? In what context is putting postgis into the preload a requirement? P > On Jan 18, 2024, at 8:20 PM, Scott wrote: > > Bam! > > That was it. Thanks Regina, you rock! > > On 1/18/24 20:16, Regina Obe wrote: >> ALTER SYSTEM SET shared_preload_libraries = 'postgis-3';
Re: Postgres segfaults on raster query
Bam! That was it. Thanks Regina, you rock! On 1/18/24 20:16, Regina Obe wrote: ALTER SYSTEM SET shared_preload_libraries = 'postgis-3';
RE: Postgres segfaults on raster query
> Here's what's happening: > > psql -d mydb > > select rid from rastertable where rid = 1; > > Psql connection drops, postgres segfaults and restarts. > > BUT, if I do a query such as this FIRST: > > select postgis_full_version(); > > Then do other raster queries, it works fine. I suspect I broke something but > I'm > not sure where to start. > > Thanks for any help. > Scott What other extensions do you have loaded. Also what does your output return for SELECT postgis_full_version(); Only thing I can think of is calling postgis_full_version() is causing the raster and postgis extensions to preload, I'd check to see if you have anything in SHOW shared_preload_libraries; You might actually want to try doing something like: ALTER SYSTEM SET shared_preload_libraries = 'postgis-3'; And then restart your service to see if it has the same effect as the SELECT postgis_full_version();
Re: Postgres segfaults on raster query
You aren’t even hitting the raster subsystem… your target list is rid and your filter is rid… so…? I’d guess you have a seriously borked install, somehow. P > On Jan 18, 2024, at 5:02 PM, Scott wrote: > > Here's what's happening: > > psql -d mydb > > select rid from rastertable where rid = 1; > > Psql connection drops, postgres segfaults and restarts. > > BUT, if I do a query such as this FIRST: > > select postgis_full_version(); > > Then do other raster queries, it works fine. I suspect I broke something but > I'm not sure where to start. > > Thanks for any help. > Scott > >
