postgis crashes postgresql backend

[vvk]

We're using postgis with postgresql and we're getting crashes after upgrade from 9.3 to 9.4 due to postgis.

Postgres log:

2016-05-04 09:10:53 YEKT::@:[2870]: LOG:  server process (PID 10844) was terminated by signal 11: Segmentation fault
2016-05-04 09:10:53 YEKT::@:[2870]: DETAIL:  Failed process was running: autovacuum: VACUUM ANALYZE public.device_summary
2016-05-04 09:10:53 YEKT::@:[2870]: LOG:  terminating any other active server processes

Versions:

 PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit POSTGIS="2.2.2 r14797" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" (core procs from "2.2.1 r14555" need upgrade) TOPOLOGY (topology procs from "2.2.1 r14555" need upgrade) RASTER (raster procs from "2.2.1 r14555" need upgrade)

backtrace:

#0  nd_box_array_distribution (distribution=0x7ffc0cf482b0, ndims=<optimized out>, extent=0x7ffc0cf48270, num_boxes=29229, nd_boxes=0x7f1475045260) at gserialized_estimate.c:750
#1  compute_gserialized_stats_mode (stats=stats@entry=0x7f1474dc36f0, fetchfunc=fetchfunc@entry=0x7f147306b970 <std_fetch_func>, sample_rows=sample_rows@entry=30000, total_rows=total_rows@entry=2500090, 
    mode=mode@entry=2) at gserialized_estimate.c:1471
#2  0x00007f125d336b79 in compute_gserialized_stats (stats=0x7f1474dc36f0, fetchfunc=0x7f147306b970 <std_fetch_func>, sample_rows=30000, total_rows=2500090) at gserialized_estimate.c:1731
#3  0x00007f147306e6f1 in do_analyze_rel (onerel=onerel@entry=0x7f1472e87100, acquirefunc=<optimized out>, relpages=93536, inh=inh@entry=0 '\000', in_outer_xact=in_outer_xact@entry=0 '\000', 
    elevel=elevel@entry=13, vacstmt=0x7ffc0cf489f0, vacstmt=0x7ffc0cf489f0) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/commands/analyze.c:531
#4  0x00007f147306f5bd in analyze_rel (relid=relid@entry=19103, vacstmt=vacstmt@entry=0x7ffc0cf489f0, in_outer_xact=<optimized out>, bstrategy=<optimized out>)
    at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/commands/analyze.c:269
#5  0x00007f14730c2e80 in vacuum (vacstmt=vacstmt@entry=0x7ffc0cf489f0, relid=<optimized out>, do_toast=do_toast@entry=0 '\000', bstrategy=<optimized out>, bstrategy@entry=0x7f1474e05af0, 
    for_wraparound=0 '\000', isTopLevel=isTopLevel@entry=1 '\001') at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/commands/vacuum.c:266
#6  0x00007f147315d5e8 in autovacuum_do_vac_analyze (bstrategy=0x7f1474e05af0, tab=0x7f1474e05c00) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/autovacuum.c:2856
#7  do_autovacuum () at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/autovacuum.c:2368
#8  0x00007f147315db13 in AutoVacWorkerMain (argv=0x0, argc=0) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/autovacuum.c:1702
#9  0x00007f147315dbe9 in StartAutoVacWorker () at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/autovacuum.c:1487
#10 0x00007f147316ad8a in StartAutovacuumWorker () at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/postmaster.c:5373
#11 sigusr1_handler (postgres_signal_arg=<optimized out>) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/postmaster.c:5027
#12 <signal handler called>
#13 0x00007f14712a9d83 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:81
#14 0x00007f1472f908ec in ServerLoop () at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/postmaster.c:1643
#15 0x00007f147316bedb in PostmasterMain (argc=5, argv=<optimized out>) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/postmaster/postmaster.c:1287
#16 0x00007f1472f920ad in main (argc=5, argv=0x7f1474cda1a0) at /build/postgresql-9.4-gA1vrE/postgresql-9.4-9.4.7/build/../src/backend/main/main.c:233

[pramsey]

Trace isn't informative (except to note the crash happens during a table analyze). If you copy the table into a new table and then run 'analyze newtable' does that also crash?

[vvk]

Replying to pramsey:

If you copy the table into a new table and then run 'analyze newtable' does that also crash?

It is not. Also, crash with original table does not happen with every ANALYZE.

[pramsey]

This is not going to be debuggable then, if the only table that causes the problem (intermittently at that) is on your machine. Presumably if I took a copy of your table I wouldn't be able replicate since copies aren't showing the problem at all?

[strk]

Did you rebuild PostGIS against 9.4 ?

[vvk]

Presumably if I took a copy of your table I wouldn't be able replicate since copies aren't showing the problem at all?

Original table also not crashing at every VACUUM ANALYZE, it only happens several times per week.

Did you rebuild PostGIS against 9.4 ?

Yes, I've rebuilt with 9.4 in order to add debug symbols.

[robe]

I'm wondering if it only happens if the table is dirty when vacuum analyze is run.

What if you copy, make an update to the table, and then vacuum analyze does it crash?

I'm also thinking it might not hurt to reindex the table. I've seen issues — usually just with queries moving to a crawl because of corrupt index. It almost always happens to me with gin or gist indexes that undergo massive updates.

In those cases doing a

reindex table public.device_summary;

does the trick, though sometimes I have been forced to drop the offending index and recreate it.

[pramsey]

This seems very localized, to the point it's not actionable for us. If a rebuild or a copy makes it go away for you, move on w/ your life.

[vvk]

Seems like REINDEX fixed this issue.