SET enable_indexscan TO on;
-- for the moment, we don't want index-only scans here
SET enable_indexonlyscan TO off;
--- wait to let any prior tests finish dumping out stats;
--- else our messages might get lost due to contention
-SELECT pg_sleep_for('2 seconds');
- pg_sleep_for
---------------
-
-(1 row)
-
-- save counters
-CREATE TEMP TABLE prevstats AS
+CREATE TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
- (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
+ pg_stat_get_snapshot_timestamp() as snap_ts
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
create function wait_for_stats() returns void as $$
declare
start_time timestamptz := clock_timestamp();
- updated bool;
+ updated1 bool;
+ updated2 bool;
+ updated3 bool;
+ updated4 bool;
begin
-- we don't want to wait forever; loop will exit after 30 seconds
for i in 1 .. 300 loop
+ -- With parallel query, the seqscan and indexscan on tenk2 might be done
+ -- in parallel worker processes, which will send their stats counters
+ -- asynchronously to what our own session does. So we must check for
+ -- those counts to be registered separately from the update counts.
+
+ -- check to see if seqscan has been sensed
+ SELECT (st.seq_scan >= pr.seq_scan + 1) INTO updated1
+ FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+
-- check to see if indexscan has been sensed
- SELECT (st.idx_scan >= pr.idx_scan + 1) INTO updated
+ SELECT (st.idx_scan >= pr.idx_scan + 1) INTO updated2
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
- exit when updated;
+ -- check to see if all updates have been sensed
+ SELECT (n_tup_ins > 0) INTO updated3
+ FROM pg_stat_user_tables WHERE relname='trunc_stats_test4';
+
+ -- We must also check explicitly that pg_stat_get_snapshot_timestamp has
+ -- advanced, because that comes from the global stats file which might
+ -- be older than the per-DB stats file we got the other values from.
+ SELECT (pr.snap_ts < pg_stat_get_snapshot_timestamp()) INTO updated4
+ FROM prevstats AS pr;
+
+ exit when updated1 and updated2 and updated3 and updated4;
-- wait a little
- perform pg_sleep(0.1);
+ perform pg_sleep_for('100 milliseconds');
-- reset stats snapshot so we can test again
perform pg_stat_clear_snapshot();
extract(epoch from clock_timestamp() - start_time);
end
$$ language plpgsql;
+-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
+CREATE TABLE trunc_stats_test(id serial);
+CREATE TABLE trunc_stats_test1(id serial, stuff text);
+CREATE TABLE trunc_stats_test2(id serial);
+CREATE TABLE trunc_stats_test3(id serial, stuff text);
+CREATE TABLE trunc_stats_test4(id serial);
+-- check that n_live_tup is reset to 0 after truncate
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+INSERT INTO trunc_stats_test DEFAULT VALUES;
+TRUNCATE trunc_stats_test;
+-- test involving a truncate in a transaction; 4 ins but only 1 live
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
+DELETE FROM trunc_stats_test1 WHERE id = 3;
+BEGIN;
+UPDATE trunc_stats_test1 SET id = id + 100;
+TRUNCATE trunc_stats_test1;
+INSERT INTO trunc_stats_test1 DEFAULT VALUES;
+COMMIT;
+-- use a savepoint: 1 insert, 1 live
+BEGIN;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+TRUNCATE trunc_stats_test2;
+INSERT INTO trunc_stats_test2 DEFAULT VALUES;
+RELEASE SAVEPOINT p1;
+COMMIT;
+-- rollback a savepoint: this should count 4 inserts and have 2
+-- live tuples after commit (and 2 dead ones due to aborted subxact)
+BEGIN;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+SAVEPOINT p1;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+TRUNCATE trunc_stats_test3;
+INSERT INTO trunc_stats_test3 DEFAULT VALUES;
+ROLLBACK TO SAVEPOINT p1;
+COMMIT;
+-- rollback a truncate: this should count 2 inserts and produce 2 dead tuples
+BEGIN;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+TRUNCATE trunc_stats_test4;
+INSERT INTO trunc_stats_test4 DEFAULT VALUES;
+ROLLBACK;
-- do a seqscan
SELECT count(*) FROM tenk2;
count
(1 row)
-- do an indexscan
+-- make sure it is not a bitmap scan, which might skip fetching heap tuples
+SET enable_bitmapscan TO off;
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
count
-------
1
(1 row)
--- force the rate-limiting logic in pgstat_report_tabstat() to time out
--- and send a message
-SELECT pg_sleep(1.0);
- pg_sleep
-----------
-
-(1 row)
-
+RESET enable_bitmapscan;
+-- We can't just call wait_for_stats() at this point, because we only
+-- transmit stats when the session goes idle, and we probably didn't
+-- transmit the last couple of counts yet thanks to the rate-limiting logic
+-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
+-- timeout to elapse, let's just start a new session. The old one will
+-- then send its stats before dying.
+\c -
-- wait for stats collector to update
SELECT wait_for_stats();
wait_for_stats
(1 row)
-- check effects
+SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
+ FROM pg_stat_user_tables
+ WHERE relname like 'trunc_stats_test%' order by relname;
+ relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
+-------------------+-----------+-----------+-----------+------------+------------
+ trunc_stats_test | 3 | 0 | 0 | 0 | 0
+ trunc_stats_test1 | 4 | 2 | 1 | 1 | 0
+ trunc_stats_test2 | 1 | 0 | 0 | 1 | 0
+ trunc_stats_test3 | 4 | 0 | 0 | 2 | 2
+ trunc_stats_test4 | 2 | 0 | 0 | 0 | 2
+(5 rows)
+
SELECT st.seq_scan >= pr.seq_scan + 1,
st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
st.idx_scan >= pr.idx_scan + 1,
t | t
(1 row)
+SELECT pr.snap_ts < pg_stat_get_snapshot_timestamp() as snapshot_newer
+FROM prevstats AS pr;
+ snapshot_newer
+----------------
+ t
+(1 row)
+
+DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
+DROP TABLE prevstats;
-- End of Stats Test
projects / postgresql / blobdiff