Opened 3 years ago

Last modified 15 months ago

#2985 reopened defect

server crash when analyzing small tables on FreeBSD Ports

Reported by: nkiraly Owned by: pramsey
Priority: critical Milestone: PostGIS Packaging
Component: postgis Version: 2.2.x
Keywords: analyze server crash freebsd Cc:

Description

If analyze or vacuum analyze runs on a table with 1 Geography type row, there is shared memory corruption reported and postmaster restarts all aspects of the server.

This ONLY happens when a table has a column with a PostGIS Geography type with just 1 row is analyzed by autovacuum process or manually.

Same failures on FreeBSD 10 amd64 with

PostgreSQL 9.3.4 + PostGIS 2.1.0 and PostgreSQL 9.3.5 + PostGIS 2.1.5

built from ports.

Success and failure scenarios attached as SQL statements.

Attachments (6)

pg93_postgis21_sig10_test.sql (4.8 KB) - added by nkiraly 3 years ago.
SQL statements to cause sig 10 and postmaster panic
trac_2985_tests.sql (502 bytes) - added by nkiraly 3 years ago.
tests to test regression of #2267
trac_2985_tests.txt (2.5 KB) - added by nkiraly 3 years ago.
results of #2267 regression test
build_with_cassert.txt (1.4 KB) - added by nkiraly 3 years ago.
cassert build terminal output
trac_2985_postgres_gdb.txt (7.0 KB) - added by nkiraly 3 years ago.
gdb trace during backend crash
trac_2985_postgres_postgis_gdb_trace.txt (34.5 KB) - added by nkiraly 3 years ago.
postgis with debug symbols trace

Download all attachments as: .zip

Change History (49)

Changed 3 years ago by nkiraly

SQL statements to cause sig 10 and postmaster panic

comment:1 Changed 3 years ago by nkiraly

I updated sample statements to include a scenario where if there are 2 rows with the same lat, this still happens.

comment:2 Changed 3 years ago by pramsey

Using your example, or constructing my own, I'm not able to reproduce, on 2.1.5 and PgSQL 9.3. I'm under OSX.

comment:3 Changed 3 years ago by robe

nkiraly,

When you say same issue on FreeBSD 10 amd64, I presume that means you tested on an additional platform. What other platform did you test on besides FreeBSD? I know we've had some reports of FreeBSD and amd64 issues with regress failures so wondering if it is platform specific or an underlying issue that only exhibits itself on certain platforms.

I tried too on my 9.3.3 postgis 2.1.3 windows 7 64-bit and 9.3.5 postgis 2.1.3 windows 2012 r2 64-bit and couldn't get it to crash with your above tests. Does it consistently crash or only sometimes?

{{{ Windows 7 64-bit PostgreSQL 9.3.3, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.1.3 r12547" GEOS="3.5.0dev-CAPI-1.9.0 r3989" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

Windows 2012 R2 64-bit PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER }}}

comment:4 Changed 3 years ago by robe

nkiraly,

This issue sounds very similar to the one noted here: #2267

Can you do me a favor and run the tests in that ticket to confirm we are looking at a different issue. Also can you let us know how you compiled postgres -- e.g. with cassert enabled. I had only tested on production postgres and cassert enabled sometimes shows issues you don't see in standard production compiles.

Thanks, Regina

comment:5 Changed 3 years ago by nkiraly

The place where I tested this and always see failures is a FreeBSD 10 virtual machine. I am getting these errors on QA servers also running FreeBSD 10 when QA databases are redeployed clean.

I ran tests from #2267 and I get postmaster terminate notification,

pg93_postgis21_trac2985=# analyze verbose foo;
INFO:  analyzing "public.foo"
INFO:  "foo": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
The connection to the server was lost. Attempting reset: WARNING:  terminating connection because of crash of another server process

and sig 10 in server logs.

Nov  5 11:17:34 bludgeon kernel: pid 37842 (postgres), uid 70: exited on signal 10 (core dumped)
Nov  5 11:17:34 bludgeon postgres[15724]: [27-1] WARNING:  terminating connection because of crash of another server process
Nov  5 11:17:34 bludgeon postgres[15724]: [27-2] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
Nov  5 11:17:34 bludgeon postgres[15724]: [27-3] HINT:  In a moment you should be able to reconnect to the database and repeat your command.

VM build is

[nicholas.kiraly@bludgeon ~]$ uname -a
FreeBSD bludgeon 10.0-RELEASE-p7 FreeBSD 10.0-RELEASE-p7 #0: Tue Jul  8 06:37:44 UTC 2014     root@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64

Changed 3 years ago by nkiraly

Attachment: trac_2985_tests.sql added

tests to test regression of #2267

Changed 3 years ago by nkiraly

Attachment: trac_2985_tests.txt added

results of #2267 regression test

Changed 3 years ago by nkiraly

Attachment: build_with_cassert.txt added

cassert build terminal output

comment:6 Changed 3 years ago by nkiraly

No I had not built with cassert enabled. I rebuilt postgres and postgis with cassert, no change in output or behavior. Terminal output attached.

comment:7 Changed 3 years ago by pramsey

Just on the off-chance that some useful info is revealed, could you attach gdb to the process and get the stack trace from the crash?

  • connect to your database
  • find the backend process you're connected to (ps -ax) and then attached gdb to it (gdb -p)
  • resume the backend in gdb (type 'c')
  • go to your connection and crash the backend
  • return to gdb and get the backtrace (type 'bt")

Thanks!

comment:8 Changed 3 years ago by nkiraly

I built with debug symbols and crashed the backend with 1 row inserted scenario, here is the trace, gdb console log attached

(gdb) bt
#0  0x000000000060b1d2 in std_fetch_func (stats=0x807846852, rownum=0, isNull=0x7fffffffca57 "?") at analyze.c:1755
#1  0x0000000807c3fea7 in geometry_estimated_extent () from /usr/local/lib/postgresql/postgis-2.1.so
#2  0x000000000060777f in do_analyze_rel (onerel=0x802e2ee10, vacstmt=0x802c5dae0, acquirefunc=0x606590 <acquire_sample_rows>, relpages=1, inh=0 '\0', elevel=17) at analyze.c:527
#3  0x0000000000606515 in analyze_rel (relid=58705, vacstmt=0x802c5dae0, bstrategy=0x80781a038) at analyze.c:267
#4  0x0000000000690f32 in vacuum (vacstmt=0x802c5dae0, relid=0, do_toast=1 '\001', bstrategy=0x80781a038, for_wraparound=0 '\0', isTopLevel=1 '\001') at vacuum.c:254
#5  0x0000000000839cc3 in standard_ProcessUtility (parsetree=0x802c5dae0, queryString=0x802c5d038 "ANALYZE VERBOSE dat_location_tho;", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, dest=0x802c5de88,
    completionTag=0x7fffffffd480 "") at utility.c:639
#6  0x00000000008393eb in ProcessUtility (parsetree=0x802c5dae0, queryString=0x802c5d038 "ANALYZE VERBOSE dat_location_tho;", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, dest=0x802c5de88,
    completionTag=0x7fffffffd480 "") at utility.c:309
#7  0x0000000000838f78 in PortalRunUtility (portal=0x802dce038, utilityStmt=0x802c5dae0, isTopLevel=1 '\001', dest=0x802c5de88, completionTag=0x7fffffffd480 "") at pquery.c:1187
#8  0x0000000000837f68 in PortalRunMulti (portal=0x802dce038, isTopLevel=1 '\001', dest=0x802c5de88, altdest=0x802c5de88, completionTag=0x7fffffffd480 "") at pquery.c:1318
#9  0x00000000008375bd in PortalRun (portal=0x802dce038, count=9223372036854775807, isTopLevel=1 '\001', dest=0x802c5de88, altdest=0x802c5de88, completionTag=0x7fffffffd480 "") at pquery.c:816
#10 0x0000000000832e21 in exec_simple_query (query_string=0x802c5d038 "ANALYZE VERBOSE dat_location_tho;") at postgres.c:1048
#11 0x0000000000832150 in PostgresMain (argc=1, argv=0x802c0c210, dbname=0x802c0c058 "pg93_postgis21_sig10", username=0x802c0c030 "deployment") at postgres.c:4007
#12 0x00000000007b8842 in BackendRun (port=0x802c71200) at postmaster.c:4011
#13 0x00000000007b7e17 in BackendStartup (port=0x802c71200) at postmaster.c:3685
#14 0x00000000007b4090 in ServerLoop () at postmaster.c:1586
#15 0x00000000007b1984 in PostmasterMain (argc=3, argv=0x7fffffffdbe8) at postmaster.c:1253
#16 0x00000000006f912c in main (argc=3, argv=0x7fffffffdbe8) at main.c:226

Changed 3 years ago by nkiraly

Attachment: trac_2985_postgres_gdb.txt added

gdb trace during backend crash

comment:9 Changed 3 years ago by pramsey

Looks like the postgis part isn't compiled w/ debug symbols, can you rebuilt it too (it should pick up the flags you used in pgsql)

comment:10 Changed 3 years ago by robe

pramsey just a thought and a possibly stupid one.

since nkiraly is also crashing on #2267, could it be that whatever fix we did there might apply to this 2.1 (but of course in a different way since the code has changed significantly). It looks like whatever happened in #2267 was never ported to 2.1, but then again the 2.1 doesn't have geography_esimate anymore and just has gserialized_gist_nd.c (which I presume replaced the geography_estimate.c)

one of the things I noticed -- which may mean nothing is that in http://trac.osgeo.org/postgis/changeset/11287 -- you added #include "liblwgeom_internal.h", and while I see that include in gserialized_gist_2d.c, I don't see it in gserialized_gist_nd.c. Is there a reason for that?

comment:11 Changed 3 years ago by pramsey

The *_gist_*d.c files are about the index binding, which is entirely separate from analysis, so there's no clue there.

comment:12 Changed 3 years ago by robe

Keywords: freebsd added

tagged this as a freebsd issue. I verfied my 9.2 which used to crash with older 2.0 (pre #2267) test doesn't seem to crash with 2.1.4 install with this or the #2267 test.

Changed 3 years ago by nkiraly

postgis with debug symbols trace

comment:13 Changed 21 months ago by dbaston

Replicated on FreeBSD 10.2, Postgres 9.3.11 installed from packages, PostGIS 2.1.7 from packages.

Last edited 21 months ago by dbaston (previous) (diff)

comment:14 Changed 21 months ago by dbaston

Also 2.2.1 compiled from downloaded source.

comment:15 Changed 21 months ago by robe

Milestone: PostGIS PostgreSQLPostGIS 2.2.2

Why is this marked as PostgreSQL. Switching to PostGIS

comment:16 Changed 21 months ago by pramsey

Milestone: PostGIS 2.2.2PostGIS 2.2.3

comment:17 Changed 20 months ago by pramsey

Still "works for me" on:

PostgreSQL 9.3.11 on x86_64-apple-darwin15.3.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit POSTGIS="2.2.3dev r14829" GEOS="3.6.0dev-CAPI-1.10.0 r4125" PROJ="Rel. 4.9.2, 08 September 2015" LIBXML="2.9.2" LIBJSON="0.12" (core procs from "2.2.1dev r14503" need upgrade)

comment:18 Changed 19 months ago by pramsey

Resolution: worksforme
Status: newclosed

comment:19 Changed 15 months ago by ofca

There's still something here.

FreeBSD 10.3-RELEASE-p2 #0 r299977 amd64
postgis22-2.2.2 installed from ports
postgresql95-server-9.5.4

Server crashes when vacuuming (or autovacuuming) a table that contains one (and only one) row.

There's nothing complicated in regards to table itself, and it's fully repeatable.

  Column  |   Type   | Modifiers
----------+----------+-----------
 entityid | integer  | not null
 geom     | geometry |
Last edited 15 months ago by ofca (previous) (diff)

comment:20 Changed 15 months ago by robe

Resolution: worksforme
Status: closedreopened

comment:21 Changed 15 months ago by robe

ofca,

Can you provide a script to build table, add row, and vacuum that triggers the crash,

comment:22 Changed 15 months ago by ofca

After further testing I found out that the crash is not dependant on amount of rows, but more likely on amount of data.

You need at least three rows with geom of size from example to prevent crashes, and I managed to find some larger multiline geom, and vacuum processed without any problems even tough table contained one row.

template1=# create database testdb;
CREATE DATABASE
template1=# create extension postgis;
CREATE EXTENSION
template1=# create table test (a integer not null, g geometry);
CREATE TABLE
template1=# insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
INSERT 0 1
template1=# vacuum FULL ANALYZE VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 1 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 1 live rows and 0 dead rows; 1 rows in sample, 1 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: Succeeded.

comment:23 Changed 15 months ago by robe

I can't replicate the crash under windows any variant I tried e.g.

PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 64-bit
POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.7.8" LIBJSON="0.12" RASTER

But on a FreeBSD running

PostgreSQL 9.3.14
                                                                         postgis_full_version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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
(1 row)

I do get a crash

So does seem like it might be a FreeBSD, PostGIS specific issue.

comment:24 Changed 15 months ago by ofca

Some more details for c wizards ;)

Program received signal SIGBUS, Bus error.
0x00000000005b5132 in std_fetch_func (stats=0x803271422, rownum=0, isNull=0x7fffffffd9a7 "") at analyze.c:1761
1761    analyze.c: No such file or directory.
(gdb) bt
#0  0x00000000005b5132 in std_fetch_func (stats=0x803271422, rownum=0, isNull=0x7fffffffd9a7 "") at analyze.c:1761
#1  0x0000000807034ee3 in ?? () from /usr/local/lib/postgresql/postgis-2.2.so
#2  0x00000000005b1974 in do_analyze_rel (onerel=0x80332b768, vacstmt=0x80320ba38, acquirefunc=0x5b09f0 <acquire_sample_rows>, relpages=1, inh=0 '\000',
    in_outer_xact=0 '\000', elevel=17) at analyze.c:528
#3  0x00000000005b0960 in analyze_rel (relid=623595, vacstmt=0x80320ba38, in_outer_xact=0 '\000', bstrategy=0x8030eb030) at analyze.c:266
#4  0x000000000063892b in vacuum (vacstmt=0x80320ba38, relid=0, do_toast=1 '\001', bstrategy=0x8030eb030, for_wraparound=0 '\000', isTopLevel=1 '\001')
    at vacuum.c:259
#5  0x00000000007ad798 in standard_ProcessUtility (parsetree=0x80320ba38, queryString=0x80320b030 "vacuum FULL ANALYZE VERBOSE ;", params=0x0,
    isTopLevel=1 '\001', dest=0x80320bd78, completionTag=0x7fffffffe470 "") at utility.c:1003

comment:25 Changed 15 months ago by ofca

OK, after trying million different things, this is what I came up with.

Good analyze run:

 DEBUG: StartTransactionCommand
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: ProcessUtility
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: building index "pg_toast_909285_index" on table "pg_toast_909285"
 INFO: vacuuming "public.test"
 INFO: "test": found 1 removable, 1 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.
 DEBUG: drop auto-cascades to type pg_temp_623595
 DEBUG: drop auto-cascades to type pg_temp_623595[]
 DEBUG: drop auto-cascades to toast table pg_toast.pg_toast_909285
 DEBUG: drop auto-cascades to index pg_toast.pg_toast_909285_index
 DEBUG: drop auto-cascades to type pg_toast.pg_toast_909285
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 13757591/1/12, nestlvl: 1, children:
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 INFO: analyzing "public.test"
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1770] gserialized_analyze_nd called
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1777] attribute stat target: 100
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1783] minrows: 30000
 INFO: "test": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1282] compute_gserialized_stats called
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1283] # sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1284] estimate of total_rows: 1
 DEBUG: [gserialized_estimate.c:nd_box_from_gbox:511] GBOX((20.900011,52.199986),(20.915567,52.208111))
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1395] stats->attr->attstattarget: 100
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1396] target # of histogram cells: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1406] sample_extent: {"min":[20.9,52.2],"max":[20.9156,52.2081]}
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 0, feature 0: bin 0 to bin 49
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 0: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 0: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 0: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 0: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 1, feature 0: bin 0 to bin 49
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 1: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 1: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 1: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 1: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1498] special case: no axes have variability
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1506] histo_cells_new: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1546] histo_cells: 0
 DEBUG: [gserialized_estimate.c:nd_box_overlap:599] nd_box: {"min":[20.9,52.2],"max":[20.9156,52.2081]}
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1604] feature 0: ibox (0, 0, 0, 0) (-1, -1, 0, 0)
 DEBUG: [gserialized_estimate.c:nd_stats_value_index:357] bad index at (0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1644] ratio (1) num_cells (1)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1645] at (0, 0, 0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1655] histogram_features: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1656] sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1657] table_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1694] out: slot 0: kind 0 (STATISTIC_KIND_ND)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1695] out: slot 0: op 0 (InvalidOid)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1696] out: slot 0: numnumbers 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1697] out: null fraction: 0.000000=0/1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1698] out: average width: 944 bytes
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1699] out: distinct values: all (no check done)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1700] out: {"ndims":2,"size":[0,0],"extent":{"min":[20.8999,52.1999],"max":[20.9156,52.2082]},"table_features":1,"sample_features":1,"not_null_features":1,"histogram_features":1,"histogram_cells":0,"cells_covered":1}
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1282] compute_gserialized_stats called
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1283] # sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1284] estimate of total_rows: 1
 DEBUG: [gserialized_estimate.c:nd_box_from_gbox:511] GBOX((20.900011,52.199986),(20.915567,52.208111))
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1395] stats->attr->attstattarget: 100
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1396] target # of histogram cells: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1406] sample_extent: {"min":[20.9,52.2],"max":[20.9156,52.2081]}
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 0, feature 0: bin 0 to bin 49
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 0: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 0: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 0: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 0: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 1, feature 0: bin 0 to bin 49
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 1: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 1: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 1: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 1: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1498] special case: no axes have variability
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1506] histo_cells_new: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1546] histo_cells: 0
 DEBUG: [gserialized_estimate.c:nd_box_overlap:599] nd_box: {"min":[20.9,52.2],"max":[20.9156,52.2081]}
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1604] feature 0: ibox (0, 0, 0, 0) (-1, -1, 0, 0)
 DEBUG: [gserialized_estimate.c:nd_stats_value_index:357] bad index at (0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1644] ratio (1) num_cells (1)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1645] at (0, 0, 0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1655] histogram_features: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1656] sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1657] table_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1694] out: slot 0: kind 102 (STATISTIC_KIND_ND)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1695] out: slot 0: op 0 (InvalidOid)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1696] out: slot 0: numnumbers 19
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1697] out: null fraction: 0.000000=0/1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1698] out: average width: 944 bytes
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1699] out: distinct values: all (no check done)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1700] out: {"ndims":2,"size":[0,0],"extent":{"min":[20.8999,52.1999],"max":[20.9156,52.2082]},"table_features":1,"sample_features":1,"not_null_features":1,"histogram_features":1,"histogram_cells":0,"cells_covered":1}
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 13757592/1/0 (used), nestlvl: 1, children:
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: CommitTransactionCommand
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

Fail analyze run:

 DEBUG: StartTransactionCommand
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: ProcessUtility
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 DEBUG: building index "pg_toast_901093_index" on table "pg_toast_901093"
 INFO: vacuuming "public.test"
 INFO: "test": found 0 removable, 1 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.
 DEBUG: drop auto-cascades to type pg_temp_623595
 DEBUG: drop auto-cascades to type pg_temp_623595[]
 DEBUG: drop auto-cascades to toast table pg_toast.pg_toast_901093
 DEBUG: drop auto-cascades to index pg_toast.pg_toast_901093_index
 DEBUG: drop auto-cascades to type pg_toast.pg_toast_901093
 DEBUG: CommitTransaction
 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 13757589/1/12, nestlvl: 1, children:
 DEBUG: StartTransaction
 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 INFO: analyzing "public.test"
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1770] gserialized_analyze_nd called
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1777] attribute stat target: 100
 DEBUG: [gserialized_estimate.c:gserialized_analyze_nd:1783] minrows: 30000
 INFO: "test": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1282] compute_gserialized_stats called
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1283] # sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1284] estimate of total_rows: 1
 DEBUG: [g_serialized.c:gserialized_get_type:60] entered
 DEBUG: [g_serialized.c:gserialized_get_type:60] entered
 DEBUG: [g_serialized.c:lwgeom_from_gserialized:1265] Got type 2 (LineString), srid=4326
 DEBUG: [g_serialized.c:lwgeom_from_gserialized_buffer:1219] Got type 2 (LineString), hasz=0 hasm=0 geodetic=0 hasbox=0
 DEBUG: [lwgeom.c:lwgeom_count_vertices:1148] lwgeom_count_vertices got type LineString
 DEBUG: [lwgeom.c:lwgeom_is_empty:1301] lwgeom_is_empty: got type LineString
 DEBUG: [lwgeom.c:lwgeom_count_vertices:1183] counted 1 vertices
 DEBUG: [lwgeom.c:lwgeom_set_srid:1543] entered with srid=4326
 DEBUG: [g_box.c:lwgeom_calculate_gbox_cartesian:651] lwgeom_calculate_gbox got type (2) - LineString
 DEBUG: [g_box.c:ptarray_calculate_gbox_cartesian:525] ptarray_calculate_gbox Z: 0 M: 0
 DEBUG: [lwgeom_api.c:getPoint4d_p:245] getPoint4d_p called.
 DEBUG: [lwgeom_api.c:getPoint4d_p:251] ptr 0x8032ae058, zmflag 0
 DEBUG: [gserialized_estimate.c:nd_box_from_gbox:511] GBOX((20.951166,52.398453),(20.951168,52.398457))
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1395] stats->attr->attstattarget: 100
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1396] target # of histogram cells: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1406] sample_extent: {"min":[20.9512,52.3985],"max":[20.9512,52.3985]}
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 0, feature 0: bin 0 to bin 50
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 0: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 0: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 0: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 0: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:745] dimension 1, feature 0: bin 0 to bin 50
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:763] dimension 1: range = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:764] dimension 1: average = 1
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:765] dimension 1: stddev = 0
 DEBUG: [gserialized_estimate.c:nd_box_array_distribution:766] dimension 1: stddev_ratio = 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1498] special case: no axes have variability
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1503] histo_size[d]: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1506] histo_cells_new: 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1546] histo_cells: 0
 DEBUG: [gserialized_estimate.c:nd_box_overlap:599] nd_box: {"min":[20.9512,52.3985],"max":[20.9512,52.3985]}
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1604] feature 0: ibox (0, 0, 0, 0) (-1, -1, 0, 0)
 DEBUG: [gserialized_estimate.c:nd_stats_value_index:357] bad index at (0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1644] ratio (1) num_cells (1)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1645] at (0, 0, 0, 0)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1655] histogram_features: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1656] sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1657] table_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1694] out: slot 0: kind 0 (STATISTIC_KIND_ND)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1695] out: slot 0: op 0 (InvalidOid)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1696] out: slot 0: numnumbers 0
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1697] out: null fraction: 0.000000=0/1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1698] out: average width: 32 bytes
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1699] out: distinct values: all (no check done)
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1700] out: {"ndims":2,"size":[0,0],"extent":{"min":[20.9512,52.3985],"max":[20.9512,52.3985]},"table_features":1,"sample_features":1,"not_null_features":1,"histogram_features":1,"histogram_cells":0,"cells_covered":1}
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1282] compute_gserialized_stats called
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1283] # sample_rows: 1
 DEBUG: [gserialized_estimate.c:compute_gserialized_stats_mode:1284] estimate of total_rows: 1

 DEBUG: forked new backend, pid=37855 socket=12
 kernel: pid 37854 (postgres), uid 70: exited on signal 10 (core dumped)

comment:26 Changed 15 months ago by ofca

I've setup a fresh FreeBSD 10.3 stable with psql 9.5.4, postgis 2.2.2, and three test databases, two crashing, one with one row not crashing -- if someone would like remote access to take a stab at this, msg on #postgis

comment:27 Changed 15 months ago by ofca

Summary: server crash when analyzing table with 1 rowserver crash when analyzing small tables
Version: 2.1.x2.2.x

comment:28 Changed 15 months ago by robe

All right this is pretty annoying. I have a FreeBSD vm that I installed 9.5 from ports.

Compiled PostGIS 2.3 (bah for some reason didn't give me raster though I installed GDAL) anyrate ran the:

create table test (a integer not null, g geometry);
insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');

postgis=# insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
INSERT 0 1
postgis=# vacuum FULL ANALYZE VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 1 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 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
VACUUM

The darn thing won't crash for me. My

 SELECT version() || ' ' || postgis_full_version();

-- returns
                                                                                                                                ?column?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 on amd64-portbld-freebsd10.1, compiled by FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512, 64-bit POSTGIS="2.3.0dev r15129" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" LIBXML="2.9.4" LIBJSON="0.12"
(1 row)

ofca, did you ever try this on 2.3.0 trunk (and on 9.5, I recall you said you tried on 9.5), wondering if maybe the issue doesn't exist in 2.3, or it has something to do with the fact I didn't get raster enabled, so I ran from scripts. I doubt that is it since the regular postgis-2.3.so shouldn't have any gdal in it anyway.

On production server I have, it has 2.2.2 and PostgreSQL 9.3 installed.

comment:29 Changed 15 months ago by robe

-- I figured out my issue here

Fixed by r15130

ofca, how did you ever get PostGIS 2.2.2 installed? Did you have to hack it.

I just tried compiling from PostGIS 2.2.2 tarball and get this:

wget http://download.osgeo.org/postgis/source/postgis-2.2.2.tar.gz
tar -xvzf postgis-2.2.2.tar.gz
cd postgis-2.2.2
./configure
make
rtpg_geometry.c:105:42: error: too many arguments to function call, expected 2, have 3
        gser = gserialized_from_lwgeom(geom, 0, &gser_size);
               ~~~~~~~~~~~~~~~~~~~~~~~          ^~~~~~~~~~
/usr/local/include/liblwgeom.h:1957:1: note: 'gserialized_from_lwgeom' declared here
extern GSERIALIZED* gserialized_from_lwgeom(LWGEOM *geom, size_t *size);
^
rtpg_geometry.c:184:42: error: too many arguments to function call, expected 2, have 3
        gser = gserialized_from_lwgeom(geom, 0, &gser_size);
               ~~~~~~~~~~~~~~~~~~~~~~~          ^~~~~~~~~~
/usr/local/include/liblwgeom.h:1957:1: note: 'gserialized_from_lwgeom' declared here
extern GSERIALIZED* gserialized_from_lwgeom(LWGEOM *geom, size_t *size);
^
rtpg_geometry.c:326:81: error: too many arguments to function call, expected 2, have 3
                gser = gserialized_from_lwgeom(lwpoly_as_lwgeom(geomval2[call_cntr].geom), 0, &gser_size);
                       ~~~~~~~~~~~~~~~~~~~~~~~                                                ^~~~~~~~~~
/usr/local/include/liblwgeom.h:1957:1: note: 'gserialized_from_lwgeom' declared here
extern GSERIALIZED* gserialized_from_lwgeom(LWGEOM *geom, size_t *size);
^
rtpg_geometry.c:628:59: error: too many arguments to function call, expected 2, have 3
                gser = gserialized_from_lwgeom(pix2[call_cntr].geom, 0, &gser_size);
                       ~~~~~~~~~~~~~~~~~~~~~~~                          ^~~~~~~~~~
/usr/local/include/liblwgeom.h:1957:1: note: 'gserialized_from_lwgeom' declared here
extern GSERIALIZED* gserialized_from_lwgeom(LWGEOM *geom, size_t *size);
^
4 errors generated.
gmake[3]: *** [<builtin>: rtpg_geometry.o] Error 1
gmake[3]: Leaving directory '/usr/home/robe/sources/postgis-2.2.2/raster/rt_pg'
gmake[2]: *** [Makefile:28: pglib] Error 2
gmake[2]: Leaving directory '/usr/home/robe/sources/postgis-2.2.2/raster'
gmake[1]: *** [GNUmakefile:16: all] Error 1
gmake[1]: Leaving directory '/usr/home/robe/sources/postgis-2.2.2'
*** Error code 2

Stop.
Last edited 15 months ago by robe (previous) (diff)

comment:30 Changed 15 months ago by robe

Is it possible that we have fixed this issue?

I just tried with PostGIS 2.2 branch:

                                                                                                                                 ?column?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.2.3dev r15130" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" LIBXML="2.9.4" LIBJSON="0.12" PostgreSQL 9.5.4 on amd64-portbld-freebsd10.1, compiled by FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512, 64-bit
(1 row)

Using ofca's simple:

create table test (a integer not null, g geometry);
 insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
vacuum FULL ANALYZE VERBOSE test;

As well as the more extensive pg93_postgis21_sig10_test.sql​ provided by nkiraly

and I can't get my FreeBSD to crash with either.

Last edited 15 months ago by robe (previous) (diff)

comment:31 Changed 15 months ago by ofca

I installed 2.2.2 from ports. As to the trunk version, maybe it would be worthwhile to ask rhurlin@… to create some postgis-devel port?

I'll try to build 2.2.3dev and test on it, and I can give you access to mentioned VM with premade crash databases, so you can take a look at it yourself if time permits.

comment:32 Changed 15 months ago by robe

I downgraded to 9.3 and installed PostGIS 2.1.7 from ports and it crashes. I also tried by 9.5 compiling my own PostGIS 2.2.2 (after I applied Greg's patch) and it did not crash.

If I compiled PostGIS 2.2 from svn branch, it doesn't crash (even on the 9.3 I got from ports).

So I'm not sure if its something about the way FreeBSD ports is compiling PostGIS or patched it so it would work or perhaps Greg's liblwgeom patched fixed things and they were compiling against an incompatible liblwgeom.h header.

You said you got 2.2.2 from ports -- what's the pkg install for that.

If I do:

pkg install postgis

I just get the 2.1.7 which only works with 9.3. I thought I had done this before, but perhaps not. Not sure where my production got their 2.2.2 since I didn't install it.

comment:33 Changed 15 months ago by robe

Resolution: fixed
Status: reopenedclosed

Now that I got all my compile issues squared aware, I tried this exercise a couple of times on the same PostgresQL 9.3 freebsd server.

create table test (a integer not null, g geometry);
 insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
vacuum FULL ANALYZE VERBOSE test;

Conclusion - PostGIS 2.1.7 consistently crashes, it's impressive how faithful it is at crashing.

SELECT postgis_full_version() || ' ' || version();
                                                                                                                                ?column?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.7 r13414" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" LIBXML="2.9.4" LIBJSON="UNKNOWN" 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)


postgis21=# create table test (a integer not null, g geometry);
nsert iERROR:  relation "test" already exists
postgis21=#  insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
ALYZE VERBOSE test;INSERT 0 1
postgis21=# 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.

Went and compiled my own PostGIS 2.3.0rc1 (appyling Greg's patch to the tar ball to do so and patch from #3639) I can do CREATE EXTENSION now.

SELECT postgis_full_version() || ' ' || version();
                                                                                                                                                       ?column?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.3.0rc1 r15121" 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)

postgis23rc1=#  insert into test values (1, '0102000020E6100000010000006D1092A47FF33440AD4ECD9B00334A40');
INSERT 0 1
postgis23rc1=# vacuum FULL ANALYZE VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 1 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 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
VACUUM

-- compiled by own PostGIS 2.2.2, again applying Greg's patch from #3604 (cause I couldn't compile without it) -- no crash

postgis222=# create extension postgis version "2.2.2";
CREATE EXTENSION
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)

postgis222=# vacuum FULL ANALYZE VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 1 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 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
VACUUM
postgis222=#

-- YEH NO CRASH

So given that I can't get even the 2.2.2 to crash (which others were very successful with and I was successful with on another freebsd server (and only difference is the liblwgeo.h patch from #3604 or something else that FreeBSD package people are hacking that I'm not doing when I compile, to the best of my knowledge, if it was an issue in PostGIS code, the issue is resolved and probably caused by a rogue liblwgeom.h

People if you are still having issues feel free to reopen this.

comment:34 Changed 15 months ago by ofca

Well, I can reliably replicate this on fresh FreeBSD while building both postgresql and postgis from ports -- even with all postgis port options disabled.

Did you try building via cd /usr/ports/databases/postgis22 && make install

And who is rhurlin@… ? - is he the part of the team? I'll e-mail him (or her -- learning my leason with you ;p) about this; maybe he can shine some light here.

comment:35 Changed 15 months ago by robe

No I wasn't using the ports source files. What I did was this more or less (my instructions as I was writing down are a bit of a mess because I was installing and uninstalling PostgreSQL 9.3/9.5 to compare from ports

sudo bash
freebsd-update fetch
freebsd-update install
#to get PostGIS and PostgreSQL for binary ports
pkg install postgresql93-server postgresql93-contrib
sysrc postgresql_enable=yes
service postgresql initdb
service postgresql start
pkg install postgis (this gave me PostGIS 2.1.7 which consistently crashed)

## to install PostGIS from source
pkg install subversion
pkg install autoconf automake libtool git gmake wget libxslt
pkg install gdal geos #this got already installed if I did postgis install first from ports

To compile PostGIS 2.2.2

wget http://download.osgeo.org/postgis/source/postgis-2.2.2.tar.gz
tar -xvzf postgis-2.2.2.tar.gz 
cd postgis-2.2.2

#apply patch from r15130 #apply patch from r15135

./configure
make
make install

This gave me a perfectly nice PostGIS 2.2.2 in my PostgreSQL 9.3 which did not crash like the ports ones Which I installed this way

psql -U pgsql -d postgres
CREATE DATABASE postgis222;
\connect postgis222
CREATE EXTENSION postgis VERSION "2.2.2";
Last edited 15 months ago by robe (previous) (diff)

comment:36 Changed 15 months ago 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.
!>

comment:37 Changed 15 months ago by ofca

try:

rm -rf /usr/ports
portsnap fetch
portsnap extract

This should give you a current ports tree and you can look at what's going on, what kind of patches are used and what happens during build process.

Last edited 15 months ago by robe (previous) (diff)

comment:38 Changed 15 months ago by robe

I think I'm missing something. I get this:

Looking up portsnap.FreeBSD.org mirrors... 7 mirrors found.
Fetching snapshot tag from your-org.portsnap.freebsd.org... done.
Fetching snapshot metadata... done.
Fetching snapshot generated at Thu Sep 22 20:08:34 EDT 2016:
fetch: http://your-org.portsnap.freebsd.org/s/0831842c5695b8c81d35df948c917c1e482e189fdb4463687703fc567e5ece22.tgz: Not Found

Any rate I'm not going to have much time to debug this, but I will add this regress test so that it crashes for anyone packaging who has this issue when they go to do the make check.

comment:39 Changed 15 months ago by robe

In 15138:

Test to trap FreeBSD crash on small tables.
References #2985 for PostGIS 2.3.0

comment:40 Changed 15 months ago by robe

In 15141:

Test to trap FreeBSD crash on small tables.
References #2985 for PostGIS 2.2.3

comment:41 in reply to:  38 Changed 15 months ago by ofca

Replying to robe:

I think I'm missing something. I get this:

Looking up portsnap.FreeBSD.org mirrors... 7 mirrors found.
Fetching snapshot tag from your-org.portsnap.freebsd.org... done.
Fetching snapshot metadata... done.
Fetching snapshot generated at Thu Sep 22 20:08:34 EDT 2016:
fetch: http://your-org.portsnap.freebsd.org/s/0831842c5695b8c81d35df948c917c1e482e189fdb4463687703fc567e5ece22.tgz: Not Found

There's something wrong with your /etc/portsnap.conf seems there's SERVERNAME=your-org.portsnap.FreeBSD.org instead of normal SERVERNAME=portsnap.FreeBSD.org

Any rate I'm not going to have much time to debug this, but I will add this regress test so that it crashes for anyone packaging who has this issue when they go to do the make check.

Should your time permit, I'll be happy to help with FreeBSD-specific questions you may have. I also informed the port maintainer about this ticket, so lets see how it goes now. Thank you :)

comment:42 Changed 15 months ago by robe

Summary: server crash when analyzing small tablesserver crash when analyzing small tables on FreeBSD Ports

Okay this seems to still be an issue per Rainer and FreeBSD ports is working on a fix.

Will close this out once they have resolved.

Last edited 15 months ago by robe (previous) (diff)

comment:43 Changed 15 months ago by robe

Milestone: PostGIS 2.2.3PostGIS Packaging
Resolution: fixed
Status: closedreopened
Note: See TracTickets for help on using tickets.