*** /home/jjanes/pgsql/git/src/test/regress/expected/mv_dependencies.out 2016-03-08 18:08:45.275328461 -0800 --- /home/jjanes/pgsql/git/src/test/regress/results/mv_dependencies.out 2016-03-08 18:17:34.914707058 -0800 *************** *** 21,26 **** --- 21,28 ---- ERROR: unrecognized STATISTICS option "unknown_option" -- correct command CREATE STATISTICS s1 ON functional_dependencies (a, b, c) WITH (dependencies); + ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index" + DETAIL: Key (staname, stanamespace)=(s1, 2200) already exists. -- random data (no functional dependencies) INSERT INTO functional_dependencies SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i); *************** *** 29,36 **** FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! t | f | ! (1 row) TRUNCATE functional_dependencies; -- a => b, a => c, b => c --- 31,37 ---- FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! (0 rows) TRUNCATE functional_dependencies; -- a => b, a => c, b => c *************** *** 41,48 **** FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3, 2 => 3 ! (1 row) TRUNCATE functional_dependencies; -- a => b, a => c --- 42,48 ---- FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! (0 rows) TRUNCATE functional_dependencies; -- a => b, a => c *************** *** 53,60 **** FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3 ! (1 row) TRUNCATE functional_dependencies; -- check explain (expect bitmap index scan, not plain index scan) --- 53,59 ---- FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! (0 rows) TRUNCATE functional_dependencies; -- check explain (expect bitmap index scan, not plain index scan) *************** *** 66,83 **** FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3, 2 => 3 ! (1 row) EXPLAIN (COSTS off) SELECT * FROM functional_dependencies WHERE a = 10 AND b = 5; ! QUERY PLAN ! --------------------------------------------- ! Bitmap Heap Scan on functional_dependencies ! Recheck Cond: ((a = 10) AND (b = 5)) ! -> Bitmap Index Scan on fdeps_idx ! Index Cond: ((a = 10) AND (b = 5)) ! (4 rows) DROP TABLE functional_dependencies; -- varlena type (text) --- 65,79 ---- FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; deps_enabled | deps_built | pg_mv_stats_dependencies_show --------------+------------+------------------------------- ! (0 rows) EXPLAIN (COSTS off) SELECT * FROM functional_dependencies WHERE a = 10 AND b = 5; ! QUERY PLAN ! ------------------------------------------------------- ! Index Scan using fdeps_idx on functional_dependencies ! Index Cond: ((a = 10) AND (b = 5)) ! (2 rows) DROP TABLE functional_dependencies; -- varlena type (text) *************** *** 103,172 **** INSERT INTO functional_dependencies SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i); ANALYZE functional_dependencies; ! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps) ! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; ! deps_enabled | deps_built | pg_mv_stats_dependencies_show ! --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3, 2 => 3 ! (1 row) ! ! TRUNCATE functional_dependencies; ! -- a => b, a => c ! INSERT INTO functional_dependencies ! SELECT i/10, i/150, i/200 FROM generate_series(1,10000) s(i); ! ANALYZE functional_dependencies; ! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps) ! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; ! deps_enabled | deps_built | pg_mv_stats_dependencies_show ! --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3 ! (1 row) ! ! TRUNCATE functional_dependencies; ! -- check explain (expect bitmap index scan, not plain index scan) ! INSERT INTO functional_dependencies ! SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i); ! CREATE INDEX fdeps_idx ON functional_dependencies (a, b); ! ANALYZE functional_dependencies; ! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps) ! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; ! deps_enabled | deps_built | pg_mv_stats_dependencies_show ! --------------+------------+------------------------------- ! t | t | 1 => 2, 1 => 3, 2 => 3 ! (1 row) ! ! EXPLAIN (COSTS off) ! SELECT * FROM functional_dependencies WHERE a = '10' AND b = '5'; ! QUERY PLAN ! ------------------------------------------------------------ ! Bitmap Heap Scan on functional_dependencies ! Recheck Cond: ((a = '10'::text) AND (b = '5'::text)) ! -> Bitmap Index Scan on fdeps_idx ! Index Cond: ((a = '10'::text) AND (b = '5'::text)) ! (4 rows) ! ! DROP TABLE functional_dependencies; ! -- NULL values (mix of int and text columns) ! CREATE TABLE functional_dependencies ( ! a INT, ! b TEXT, ! c INT, ! d TEXT ! ); ! CREATE STATISTICS s3 ON functional_dependencies (a, b, c, d) WITH (dependencies); ! INSERT INTO functional_dependencies ! SELECT ! mod(i, 100), ! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END), ! mod(i, 400), ! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END) ! FROM generate_series(1,10000) s(i); ! ANALYZE functional_dependencies; ! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps) ! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass; ! deps_enabled | deps_built | pg_mv_stats_dependencies_show ! --------------+------------+---------------------------------------- ! t | t | 2 => 1, 3 => 1, 3 => 2, 4 => 1, 4 => 2 ! (1 row) ! ! DROP TABLE functional_dependencies; --- 99,108 ---- INSERT INTO functional_dependencies SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i); ANALYZE functional_dependencies; ! WARNING: terminating connection because of crash of another server process ! 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. ! HINT: In a moment you should be able to reconnect to the database and repeat your command. ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost ====================================================================== *** /home/jjanes/pgsql/git/src/test/regress/expected/mv_mcv.out 2016-03-08 18:08:45.299328161 -0800 --- /home/jjanes/pgsql/git/src/test/regress/results/mv_mcv.out 2016-03-08 18:17:34.643710446 -0800 *************** *** 80,207 **** EXPLAIN (COSTS off) SELECT * FROM mcv_list WHERE a = 10 AND b = 5; ! QUERY PLAN ! -------------------------------------------- ! Bitmap Heap Scan on mcv_list ! Recheck Cond: ((a = 10) AND (b = 5)) ! -> Bitmap Index Scan on mcv_idx ! Index Cond: ((a = 10) AND (b = 5)) ! (4 rows) ! ! DROP TABLE mcv_list; ! -- varlena type (text) ! CREATE TABLE mcv_list ( ! a TEXT, ! b TEXT, ! c TEXT ! ); ! CREATE STATISTICS s2 ON mcv_list (a, b, c) WITH (mcv); ! -- random data ! INSERT INTO mcv_list ! SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | f | ! (1 row) ! ! TRUNCATE mcv_list; ! -- a => b, a => c, b => c ! INSERT INTO mcv_list ! SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | t | nitems=1000 ! (1 row) ! ! TRUNCATE mcv_list; ! -- a => b, a => c ! INSERT INTO mcv_list ! SELECT i/10, i/150, i/200 FROM generate_series(1,10000) s(i); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | t | nitems=1000 ! (1 row) ! ! TRUNCATE mcv_list; ! -- check explain (expect bitmap index scan, not plain index scan) ! INSERT INTO mcv_list ! SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i); ! CREATE INDEX mcv_idx ON mcv_list (a, b); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | t | nitems=100 ! (1 row) ! ! EXPLAIN (COSTS off) ! SELECT * FROM mcv_list WHERE a = '10' AND b = '5'; ! QUERY PLAN ! ------------------------------------------------------------ ! Bitmap Heap Scan on mcv_list ! Recheck Cond: ((a = '10'::text) AND (b = '5'::text)) ! -> Bitmap Index Scan on mcv_idx ! Index Cond: ((a = '10'::text) AND (b = '5'::text)) ! (4 rows) ! ! TRUNCATE mcv_list; ! -- check explain (expect bitmap index scan, not plain index scan) with NULLs ! INSERT INTO mcv_list ! SELECT ! (CASE WHEN i/10000 = 0 THEN NULL ELSE i/10000 END), ! (CASE WHEN i/20000 = 0 THEN NULL ELSE i/20000 END), ! (CASE WHEN i/40000 = 0 THEN NULL ELSE i/40000 END) ! FROM generate_series(1,1000000) s(i); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | t | nitems=100 ! (1 row) ! ! EXPLAIN (COSTS off) ! SELECT * FROM mcv_list WHERE a IS NULL AND b IS NULL; ! QUERY PLAN ! --------------------------------------------------- ! Bitmap Heap Scan on mcv_list ! Recheck Cond: ((a IS NULL) AND (b IS NULL)) ! -> Bitmap Index Scan on mcv_idx ! Index Cond: ((a IS NULL) AND (b IS NULL)) ! (4 rows) ! ! DROP TABLE mcv_list; ! -- NULL values (mix of int and text columns) ! CREATE TABLE mcv_list ( ! a INT, ! b TEXT, ! c INT, ! d TEXT ! ); ! CREATE STATISTICS s3 ON mcv_list (a, b, c, d) WITH (mcv); ! INSERT INTO mcv_list ! SELECT ! mod(i, 100), ! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END), ! mod(i, 400), ! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END) ! FROM generate_series(1,10000) s(i); ! ANALYZE mcv_list; ! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv) ! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass; ! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info ! -------------+-----------+-------------------------- ! t | t | nitems=1200 ! (1 row) ! ! DROP TABLE mcv_list; --- 80,86 ---- EXPLAIN (COSTS off) SELECT * FROM mcv_list WHERE a = 10 AND b = 5; ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost ====================================================================== *** /home/jjanes/pgsql/git/src/test/regress/expected/mv_histogram.out 2016-03-08 18:08:45.373327236 -0800 --- /home/jjanes/pgsql/git/src/test/regress/results/mv_histogram.out 2016-03-08 18:17:34.920706983 -0800 *************** *** 30,35 **** --- 30,37 ---- ERROR: maximum number of buckets is 16384 -- correct command CREATE STATISTICS s1 ON mv_histogram (a, b, c) WITH (histogram); + ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index" + DETAIL: Key (staname, stanamespace)=(s1, 2200) already exists. -- random data (no functional dependencies) INSERT INTO mv_histogram SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i); *************** *** 38,45 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- a => b, a => c, b => c --- 40,46 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- a => b, a => c, b => c *************** *** 50,57 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- a => b, a => c --- 51,57 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- a => b, a => c *************** *** 62,69 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- check explain (expect bitmap index scan, not plain index scan) --- 62,68 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- check explain (expect bitmap index scan, not plain index scan) *************** *** 75,92 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) EXPLAIN (COSTS off) SELECT * FROM mv_histogram WHERE a = 10 AND b = 5; ! QUERY PLAN ! -------------------------------------------- ! Bitmap Heap Scan on mv_histogram ! Recheck Cond: ((a = 10) AND (b = 5)) ! -> Bitmap Index Scan on hist_idx ! Index Cond: ((a = 10) AND (b = 5)) ! (4 rows) DROP TABLE mv_histogram; -- varlena type (text) --- 74,88 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) EXPLAIN (COSTS off) SELECT * FROM mv_histogram WHERE a = 10 AND b = 5; ! QUERY PLAN ! ------------------------------------------- ! Index Scan using hist_idx on mv_histogram ! Index Cond: ((a = 10) AND (b = 5)) ! (2 rows) DROP TABLE mv_histogram; -- varlena type (text) *************** *** 96,101 **** --- 92,99 ---- c TEXT ); CREATE STATISTICS s2 ON mv_histogram (a, b, c) WITH (histogram); + ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index" + DETAIL: Key (staname, stanamespace)=(s2, 2200) already exists. -- random data (no functional dependencies) INSERT INTO mv_histogram SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i); *************** *** 104,111 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- a => b, a => c, b => c --- 102,108 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- a => b, a => c, b => c *************** *** 116,123 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- a => b, a => c --- 113,119 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- a => b, a => c *************** *** 128,207 **** FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! t | t ! (1 row) TRUNCATE mv_histogram; -- check explain (expect bitmap index scan, not plain index scan) INSERT INTO mv_histogram SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i); ! CREATE INDEX hist_idx ON mv_histogram (a, b); ! ANALYZE mv_histogram; ! SELECT hist_enabled, hist_built ! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; ! hist_enabled | hist_built ! --------------+------------ ! t | t ! (1 row) ! ! EXPLAIN (COSTS off) ! SELECT * FROM mv_histogram WHERE a = '10' AND b = '5'; ! QUERY PLAN ! ------------------------------------------------------------ ! Bitmap Heap Scan on mv_histogram ! Recheck Cond: ((a = '10'::text) AND (b = '5'::text)) ! -> Bitmap Index Scan on hist_idx ! Index Cond: ((a = '10'::text) AND (b = '5'::text)) ! (4 rows) ! ! TRUNCATE mv_histogram; ! -- check explain (expect bitmap index scan, not plain index scan) with NULLs ! INSERT INTO mv_histogram ! SELECT ! (CASE WHEN i/10000 = 0 THEN NULL ELSE i/10000 END), ! (CASE WHEN i/20000 = 0 THEN NULL ELSE i/20000 END), ! (CASE WHEN i/40000 = 0 THEN NULL ELSE i/40000 END) ! FROM generate_series(1,1000000) s(i); ! ANALYZE mv_histogram; ! SELECT hist_enabled, hist_built ! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; ! hist_enabled | hist_built ! --------------+------------ ! t | t ! (1 row) ! ! EXPLAIN (COSTS off) ! SELECT * FROM mv_histogram WHERE a IS NULL AND b IS NULL; ! QUERY PLAN ! --------------------------------------------------- ! Bitmap Heap Scan on mv_histogram ! Recheck Cond: ((a IS NULL) AND (b IS NULL)) ! -> Bitmap Index Scan on hist_idx ! Index Cond: ((a IS NULL) AND (b IS NULL)) ! (4 rows) ! ! DROP TABLE mv_histogram; ! -- NULL values (mix of int and text columns) ! CREATE TABLE mv_histogram ( ! a INT, ! b TEXT, ! c INT, ! d TEXT ! ); ! CREATE STATISTICS s3 ON mv_histogram (a, b, c, d) WITH (histogram); ! INSERT INTO mv_histogram ! SELECT ! mod(i, 100), ! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END), ! mod(i, 400), ! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END) ! FROM generate_series(1,10000) s(i); ! ANALYZE mv_histogram; ! SELECT hist_enabled, hist_built ! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; ! hist_enabled | hist_built ! --------------+------------ ! t | t ! (1 row) ! ! DROP TABLE mv_histogram; --- 124,139 ---- FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass; hist_enabled | hist_built --------------+------------ ! (0 rows) TRUNCATE mv_histogram; -- check explain (expect bitmap index scan, not plain index scan) INSERT INTO mv_histogram SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i); ! WARNING: terminating connection because of crash of another server process ! 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. ! HINT: In a moment you should be able to reconnect to the database and repeat your command. ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost ======================================================================