/*
  When only 1 row in table with Geography type
  sig 10 and postmaster terminates and restarts postgres entirely

  syslog  
Oct 31 12:52:50 src@db10 postgres[8243]: [88-2] STATEMENT:  ANALYZE dat_location_tho;
Oct 31 12:53:08 src@db10 postgres[96530]: [88-1] LOG:  server process (PID 8468) was terminated by signal 10: Bus error
Oct 31 12:53:08 src@db10 postgres[96530]: [88-2] DETAIL:  Failed process was running: ANALYZE dat_location_tho;
Oct 31 12:53:08 src@db10 postgres[96530]: [89-1] LOG:  terminating any other active server processes
Oct 31 12:53:08 src@db10 postgres[96530]: [90-1] LOG:  all server processes terminated; reinitializing
Oct 31 12:53:08 src@db10 postgres[8656]: [91-1] LOG:  database system was interrupted; last known up at 2014-10-31 16:52:06 GMT
Oct 31 12:53:08 src@db10 postgres[8656]: [92-1] LOG:  database system was not properly shut down; automatic recovery in progress
Oct 31 12:53:08 src@db10 postgres[8656]: [93-1] LOG:  redo starts at 3/FF395D20
Oct 31 12:53:08 src@db10 postgres[8656]: [94-1] LOG:  record with zero length at 3/FF3A4F48
Oct 31 12:53:08 src@db10 postgres[8656]: [95-1] LOG:  redo done at 3/FF3A4F18
Oct 31 12:53:08 src@db10 postgres[8656]: [96-1] LOG:  last completed transaction was at log time 2014-10-31 16:53:08.391697+00
Oct 31 12:53:08 src@db10 postgres[96530]: [91-1] LOG:  database system is ready to accept connections
Oct 31 12:53:08 src@db10 postgres[8660]: [91-1] LOG:  autovacuum launcher started

  $ uname
FreeBSD db10.dev 10.0-RELEASE-p6 FreeBSD 10.0-RELEASE-p6 #0: Wed Oct  1 22:40:07 EDT 2014     root@db10.dev:/usr/obj/usr/src/sys/GENERIC  amd64

  $ pkg info | grep -i post
postgis-2.1.0_6                Geographic objects support for PostgreSQL databases
postgresql93-client-9.3.4      PostgreSQL database (client)
postgresql93-contrib-9.3.4_1   The contrib utilities from the PostgreSQL distribution
postgresql93-server-9.3.4      The most advanced open-source database available anywhere

*/

\c postgres

DROP DATABASE pg93_postgis21_sig10;
CREATE DATABASE pg93_postgis21_sig10;

\c pg93_postgis21_sig10

CREATE EXTENSION postgis;

CREATE TABLE dat_location_tho (
  name bigserial PRIMARY KEY,
  rounds int NOT NULL DEFAULT width_bucket(random(), 0, 10, 100000),
  location geography(Point,4326)
);


/* SCENARIO 1: ANALYZE empty table: okay */  
ANALYZE dat_location_tho;


/* SCENARIO 2: insert a single row */
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-79.935308 40.458163)', 4326) );

/* ANALYZE 1 row in table: CRASH */  
ANALYZE dat_location_tho;

/* reconnect, repeat, will crash again */


/* SCENARIO 3: insert a second row */
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-83.214038 42.115114)', 4326) );

/* ANALYZE 2 rows in table: okay */  
ANALYZE dat_location_tho;


/* SCENARIO 4: delete first row */
DELETE FROM dat_location_tho WHERE location = ST_GeomFromText('POINT(-79.935308 40.458163)', 4326);

/* ANALYZE remaining 1 row in table: CRASH */  
ANALYZE dat_location_tho;


/* SCENARIO 5: insert 3rd and 4th rows */
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-118.168945 40.713956)', 4326) );
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-119.707031 42.358544)', 4326) );

/* ANALYZE 3 rows in table: okay */  
ANALYZE dat_location_tho;


/* SCENARIO 6: delete 2nd and 3rd rows */
DELETE FROM dat_location_tho WHERE location = ST_GeomFromText('POINT(-83.214038 42.115114)', 4326);
DELETE FROM dat_location_tho WHERE location = ST_GeomFromText('POINT(-118.168945 40.713956)', 4326);

/* ANALYZE 1 rows in table: CRASH */  
ANALYZE dat_location_tho;



/* SCENARIO 7: delete all existing rows, add 4th and 5th rows, one with null location */
DELETE FROM dat_location_tho;
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-120.058594 46.195042)', 4326) );
INSERT INTO dat_location_tho ( location ) VALUES ( NULL );

/* ANALYZE 2 rows, one location NULL in table: CRASH */  
ANALYZE dat_location_tho;


/* SCENARIO 8: add 6th row */
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-119.794922 47.931066)', 4326) );

/* ANALYZE 3 rows, one location NULL in table: okay */  
ANALYZE dat_location_tho;



/* SCENARIO 9: 2 rows with same lon value will crash analyze as well
 This suggests if every row has different lon values but the same lat values
 it crashes, so maybe 1 row counts and same lat values is caused by the same thing?
 This only happens when these are the only 2 rows.
*/
DELETE FROM dat_location_tho;

/* insert 7th row */  
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-84.214038 40.115114)', 4326) );

/* insert 8th row with same lat */  
INSERT INTO dat_location_tho ( location ) VALUES ( ST_GeomFromText('POINT(-83.214038 40.115114)', 4326) );

/* ANALYZE 2 rows, one location NULL in table: CRASH */  
ANALYZE dat_location_tho;


