[postgis-tickets] [PostGIS] #2985: server crash when analyzing small tables

[PostGIS]

#2985: server crash when analyzing small tables
-----------------------+------------------------------------------
  Reporter:  nkiraly   |      Owner:  pramsey
      Type:  defect    |     Status:  closed
  Priority:  critical  |  Milestone:  PostGIS 2.2.3
 Component:  postgis   |    Version:  2.2.x
Resolution:  fixed     |   Keywords:  analyze server crash freebsd
-----------------------+------------------------------------------

Comment (by robe):

 The /usr/ports/databases/postgis22 didn't seem to work for me (I don't
 seem to have that folder).  However that gave me a clue to do this:


 {{{
 pkg install postgis22
 }}}

 Which gave me this message:


 {{{
 Proceed with this action? [y/N]: y
 Fetching postgis22-2.2.2.txz: 100%    2 MiB   1.7MB/s    00:01
 Checking integrity... done (1 conflicting)
   - postgis22-2.2.2 conflicts with postgis-2.1.7_2 on
 /usr/local/bin/pgsql2shp
 Checking integrity... done (0 conflicting)
 Conflicts with the existing packages have been found.
 One more solver iteration is needed to resolve them.
 The following 2 package(s) will be affected (of 0 checked):

 Installed packages to be REMOVED:
         postgis-2.1.7_2

 New packages to be INSTALLED:
         postgis22: 2.2.2

 Number of packages to be removed: 1
 Number of packages to be installed: 1

 The process will require 55 MiB more space.

 Proceed with this action? [y/N]: y
 [1/2] Deinstalling postgis-2.1.7_2...
 [1/2] Deleting files for postgis-2.1.7_2:   0%
 pkg: /usr/local/bin/pgsql2shp different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2:   1%
 pkg: /usr/local/bin/shp2pgsql different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2:   2%
 pkg: /usr/local/include/liblwgeom.h different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2:   4%
 pkg: /usr/local/lib/liblwgeom.a different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2:   5%
 pkg: /usr/local/lib/liblwgeom.la different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2:   6%
 pkg: /usr/local/lib/liblwgeom.so different from original checksum, not
 removing
 [1/2] Deleting files for postgis-2.1.7_2: 100%
 [2/2] Installing postgis22-2.2.2...

   ======================= GEOS Support Notice ========================
   In order to use the GEOS support, you may need to specially compile
   your version of PostgreSQL to link the C++ runtime library.
   To do this, invoke the PostgreSQL Makefile script this way:

   on csh shell:

   setenv LDFLAGS -lstdc++
   make

   on sh or bash shell:

   export LDFLAGS=-lstdc++
   make

   The initial LDFLAGS variable is passed through to the Makefile and
   adds the C++ library to the linking stage.

   ====================================================================

 [2/2] Extracting postgis22-2.2.2: 100%
 }}}


 I didn't even bother recreating my postgis222 database, but went to check
 on it


 {{{
 psql -U pgsql -d postgis222

 postgis222=# SELECT postgis_full_version() || ' ' || version();
 ?column?
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
 September 2015" GDAL="GDAL 2.1.0, released 2016/04/25" LIBXML="2.9.4"
 LIBJSON="0.12" RASTER PostgreSQL 9.3.14 on amd64-portbld-freebsd10.1,
 compiled by FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final
 208032) 20140512, 64-bit
 (1 row)

 }}}

 Run this:


 {{{
 create table test (a integer not null, g geometry);
  insert into test values (1,
 '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
 vacuum FULL ANALYZE VERBOSE test;
 }}}
 -- voila CRASHED.  So it seems the culprit is whatever is being pushed by
 ports
 -- perhaps they changed the code so it would compile and that change is
 causing this problem
 -- Like I said without patching the code, I couldn't even get it to
 compile and install under FreeBSD

 {{{
 ERROR:  relation "test" already exists
 postgis222=#  insert into test values (1,
 '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
 cuum FUINSERT 0 1
 postgis222=# vacuum FULL ANALYZE VERBOSE test;
 INFO:  vacuuming "public.test"
 INFO:  "test": found 0 removable, 2 nonremovable row versions in 1 pages
 DETAIL:  0 dead row versions cannot be removed yet.
 CPU 0.00s/0.00u sec elapsed 0.00 sec.
 INFO:  analyzing "public.test"
 INFO:  "test": scanned 1 of 1 pages, containing 2 live rows and 0 dead
 rows; 2 rows in sample, 2 estimated total rows
 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.
 !>
 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/2985#comment:36>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.