stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
DROP ROLE IF EXISTS super_user;
CREATE ROLE super_user SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
-- create object
-CREATE TABLE pt0(id integer, day date);
+CREATE TABLE pt0(id integer, day date) WITH (autovacuum_enabled = 'false');
CREATE INDEX pt0_idx ON pt0(id);
-CREATE TABLE st0(id integer, name char(5));
+CREATE TABLE st0(id integer, name char(5)) WITH (autovacuum_enabled = 'false');
CREATE INDEX st0_idx ON st0(id);
-CREATE TABLE st1(val integer, str text);
+CREATE TABLE st1(val integer, str text) WITH (autovacuum_enabled = 'false');
CREATE SCHEMA s0;
-CREATE TABLE s0.st0(id integer, num integer);
+CREATE TABLE s0.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
CREATE INDEX st0_idx ON s0.st0(id);
-CREATE TABLE s0.st1() INHERITS(s0.st0);
+CREATE TABLE s0.st1() INHERITS(s0.st0) WITH (autovacuum_enabled = 'false');
CREATE INDEX st1_idx ON s0.st1(id);
-CREATE TABLE s0.st2(id integer, txt text);
+CREATE TABLE s0.st2(id integer, txt text) WITH (autovacuum_enabled = 'false');
CREATE INDEX st2_idx ON s0.st2(id);
CREATE VIEW sv0 AS
SELECT st0.id, st0.num, st2.txt
CREATE TYPE s0.sc0 AS (num integer, txt text);
CREATE SEQUENCE s0.ss0 START 1;
CREATE SCHEMA s1;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
CREATE SCHEMA s2;
GRANT USAGE ON SCHEMA s0 TO regular_user;
GRANT SELECT ON TABLE s0.st2 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
0
(1 row)
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
END;
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
lock_table_stats
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
super_user
(1 row)
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
/*
* No.4-1 DATA TYPE dbms_stats.anyarray
*/
-CREATE TABLE st3(id integer, name char(1000), num_arr char(5)[]);
+CREATE TABLE st3(id integer, name char(1000), num_arr char(5)[])
+ WITH (autovacuum_enabled = 'false');
INSERT INTO st3 SELECT i, i , ARRAY[i::char, 'a'] FROM generate_series(1,10) g(i);
ANALYZE st3;
SELECT staattnum, stavalues1 FROM pg_statistic
\copy (SELECT stavalues1::dbms_stats.anyarray FROM dbms_stats.column_stats_effective WHERE starelid = 'st3'::regclass ORDER BY staattnum) TO 'results/anyarray_test.cp' binary
SET client_min_messages TO WARNING;
-CREATE TABLE st4 (arr dbms_stats.anyarray, ord serial);
+CREATE TABLE st4 (arr dbms_stats.anyarray, ord serial)
+ WITH (autovacuum_enabled = 'false');
SET client_min_messages TO DEFAULT;
SELECT t.typname, n.nspname,
t.typlen, t.typbyval, t.typtype,
(1 row)
-- No.5-4-5
-CREATE TABLE s0.droptest(id integer);
+CREATE TABLE s0.droptest(id integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.droptest VALUES (1),(2),(3);
VACUUM ANALYZE;
SELECT * FROM s0.droptest
/*
* No.18-1 dbms_stats.clean_up_stats
*/
-CREATE TABLE clean_test(id integer, num integer);
+CREATE TABLE clean_test(id integer, num integer)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
ANALYZE clean_test;
-- No.18-1-1
(1 row)
-- No.18-1-6
-CREATE TABLE clean_test(id integer, num integer);
+CREATE TABLE clean_test(id integer, num integer)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
ANALYZE clean_test;
SELECT dbms_stats.lock_table_stats('clean_test');
/*
* No.21 anyarray stuff
*/
-CREATE TABLE st_ary (i int, f float, d timestamp without time zone);
+CREATE TABLE st_ary (i int, f float, d timestamp without time zone)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO st_ary
(SELECT a, random(), '2016-3-25 00:00:00'::date + (a || 'day')::interval
FROM generate_series(0, 9999) a);
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
stavalues3 dbms_stats.anyarray,
stavalues4 dbms_stats.anyarray
,stavalues5 dbms_stats.anyarray
-);
+) WITH (autovacuum_enabled = 'false');
CREATE VIEW work_v AS
SELECT nspname, relname, relpages, reltuples, relallvisible,
curpages, attname, nspname_of_typename, typname, atttypmod,
CREATE ROLE super_user SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
-- create object
-CREATE TABLE pt0(id integer, day date);
+CREATE TABLE pt0(id integer, day date) WITH (autovacuum_enabled = 'false');
CREATE INDEX pt0_idx ON pt0(id);
-CREATE TABLE st0(id integer, name char(5));
+CREATE TABLE st0(id integer, name char(5)) WITH (autovacuum_enabled = 'false');
CREATE INDEX st0_idx ON st0(id);
-CREATE TABLE st1(val integer, str text);
+CREATE TABLE st1(val integer, str text) WITH (autovacuum_enabled = 'false');
CREATE SCHEMA s0;
-CREATE TABLE s0.st0(id integer, num integer);
+CREATE TABLE s0.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
CREATE INDEX st0_idx ON s0.st0(id);
-CREATE TABLE s0.st1() INHERITS(s0.st0);
+CREATE TABLE s0.st1() INHERITS(s0.st0) WITH (autovacuum_enabled = 'false');
CREATE INDEX st1_idx ON s0.st1(id);
-CREATE TABLE s0.st2(id integer, txt text);
+CREATE TABLE s0.st2(id integer, txt text) WITH (autovacuum_enabled = 'false');
CREATE INDEX st2_idx ON s0.st2(id);
CREATE VIEW sv0 AS
SELECT st0.id, st0.num, st2.txt
CREATE SEQUENCE s0.ss0 START 1;
CREATE SCHEMA s1;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
CREATE SCHEMA s2;
GRANT USAGE ON SCHEMA s0 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
SELECT count(*) FROM dbms_stats.column_stats_locked;
-CREATE TABLE s1.st0(id integer, num integer);
+CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
VACUUM ANALYZE;
-- No.9-1-16
$$ LANGUAGE plpgsql;
SET pg_dbms_stats.use_locked_stats TO on;
-CREATE TABLE s0.st4 (a text);
+CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
VACUUM ANALYZE s0.st4;
-- should estimate that rows = 1, not 5000
/*
* No.15-2 Ditto for index stats
*/
-CREATE TABLE s0.st4 (a double precision);
+CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
CREATE INDEX on s0.st4 (floor(log(a)));
SELECT dbms_stats.lock_table_stats('s0.st4');
INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
-- No.16 error description. -- abnormal case.
RESET SESSION AUTHORIZATION;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
* No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
*/
SELECT CURRENT_USER;
-CREATE TABLE s0.st4 (a int, b text);
+CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
CREATE INDEX i_st4_a on s0.st4 (a);
CREATE VIEW s0.vst4 AS select * FROM s0.st4;
GRANT SELECT ON s0.vst4 TO regular_user;
/*
* No.4-1 DATA TYPE dbms_stats.anyarray
*/
-CREATE TABLE st3(id integer, name char(1000), num_arr char(5)[]);
+CREATE TABLE st3(id integer, name char(1000), num_arr char(5)[])
+ WITH (autovacuum_enabled = 'false');
INSERT INTO st3 SELECT i, i , ARRAY[i::char, 'a'] FROM generate_series(1,10) g(i);
ANALYZE st3;
SELECT staattnum, stavalues1 FROM pg_statistic
ORDER BY staattnum;
\copy (SELECT stavalues1::dbms_stats.anyarray FROM dbms_stats.column_stats_effective WHERE starelid = 'st3'::regclass ORDER BY staattnum) TO 'results/anyarray_test.cp' binary
SET client_min_messages TO WARNING;
-CREATE TABLE st4 (arr dbms_stats.anyarray, ord serial);
+CREATE TABLE st4 (arr dbms_stats.anyarray, ord serial)
+ WITH (autovacuum_enabled = 'false');
SET client_min_messages TO DEFAULT;
SELECT t.typname, n.nspname,
SELECT dbms_stats.unlock_database_stats();
-- No.5-4-5
-CREATE TABLE s0.droptest(id integer);
+CREATE TABLE s0.droptest(id integer) WITH (autovacuum_enabled = 'false');
INSERT INTO s0.droptest VALUES (1),(2),(3);
VACUUM ANALYZE;
SELECT * FROM s0.droptest
/*
* No.18-1 dbms_stats.clean_up_stats
*/
-CREATE TABLE clean_test(id integer, num integer);
+CREATE TABLE clean_test(id integer, num integer)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
ANALYZE clean_test;
-- No.18-1-1
SELECT dbms_stats.clean_up_stats() ORDER BY 1;
SELECT count(*) FROM dbms_stats.relation_stats_locked;
-- No.18-1-6
-CREATE TABLE clean_test(id integer, num integer);
+CREATE TABLE clean_test(id integer, num integer)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
ANALYZE clean_test;
SELECT dbms_stats.lock_table_stats('clean_test');
/*
* No.21 anyarray stuff
*/
-CREATE TABLE st_ary (i int, f float, d timestamp without time zone);
+CREATE TABLE st_ary (i int, f float, d timestamp without time zone)
+ WITH (autovacuum_enabled = 'false');
INSERT INTO st_ary
(SELECT a, random(), '2016-3-25 00:00:00'::date + (a || 'day')::interval
FROM generate_series(0, 9999) a);
OSDN Git Service
