
CREATE DATABASE pg93_postgis21_sig10;



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;


