INSERT INTO heaptest (a, b)

(SELECT gs, repeat('x', gs)

FROM generate_series(1,50) gs);

-- pg_stat_io test:

-- verify_heapam always uses a BAS_BULKREAD BufferAccessStrategy, whereas a

-- sequential scan does so only if the table is large enough when compared to

-- shared buffers (see initscan()). CREATE DATABASE ... also unconditionally

-- uses a BAS_BULKREAD strategy, but we have chosen to use a tablespace and

-- verify_heapam to provide coverage instead of adding another expensive

-- operation to the main regression test suite.

--

-- Create an alternative tablespace and move the heaptest table to it, causing

-- it to be rewritten and all the blocks to reliably evicted from shared

-- buffers -- guaranteeing actual reads when we next select from it.

SET allow_in_place_tablespaces = true;

CREATE TABLESPACE regress_test_stats_tblspc LOCATION '';

SELECT sum(reads) AS stats_bulkreads_before

FROM pg_stat_io WHERE io_context = 'bulkread' \gset

ALTER TABLE heaptest SET TABLESPACE regress_test_stats_tblspc;

-- Check that valid options are not rejected nor corruption reported

-- for a non-empty table

SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'none');

-------+--------+--------+-----

(0 rows)

-- verify_heapam should have read in the page written out by

-- ALTER TABLE ... SET TABLESPACE ...

-- causing an additional bulkread, which should be reflected in pg_stat_io.

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(reads) AS stats_bulkreads_after

FROM pg_stat_io WHERE io_context = 'bulkread' \gset

SELECT :stats_bulkreads_after > :stats_bulkreads_before;

?column?

----------

t

(1 row)

CREATE ROLE regress_heaptest_role;

-- verify permissions are checked (error due to function not callable)

SET ROLE regress_heaptest_role;

DETAIL: This operation is not supported for foreign tables.

-- cleanup

DROP TABLE heaptest;

DROP TABLESPACE regress_test_stats_tblspc;

DROP TABLE test_partition;

DROP TABLE test_partitioned;

DROP OWNED BY regress_heaptest_role; -- permissions

SELECT*FROM verify_heapam(relation :='heaptest', skip :='ALL-FROZEN');

SELECT*FROM verify_heapam(relation :='heaptest', skip :='ALL-VISIBLE');

INSERT INTO heaptest (a, b)

(SELECT gs, repeat('x', gs)

FROM generate_series(1,50) gs);

SET allow_in_place_tablespaces= true;

CREATETABLESPACEregress_test_stats_tblspc LOCATION'';

SELECTsum(reads)AS stats_bulkreads_before

FROM pg_stat_ioWHERE io_context='bulkread' \gset

ALTERTABLE heaptestSET TABLESPACE regress_test_stats_tblspc;

SELECT*FROM verify_heapam(relation :='heaptest', skip :='none');

SELECT*FROM verify_heapam(relation :='heaptest', skip :='all-frozen');

SELECT*FROM verify_heapam(relation :='heaptest', skip :='all-visible');

SELECT*FROM verify_heapam(relation :='heaptest', startblock :=0, endblock :=0);

SELECT pg_stat_force_next_flush();

SELECTsum(reads)AS stats_bulkreads_after

FROM pg_stat_ioWHERE io_context='bulkread' \gset

SELECT :stats_bulkreads_after> :stats_bulkreads_before;

CREATE ROLE regress_heaptest_role;

DROPTABLE heaptest;

DROPTABLESPACE regress_test_stats_tblspc;

DROPTABLE test_partition;

DROPTABLE test_partitioned;

DROP OWNED BY regress_heaptest_role;-- permissions

(1 row)

-- Test that the following operations are tracked in pg_stat_io:

-- - reads of target blocks into shared buffers

-- - writes of shared buffers to permanent storage

-- - extends of relations using shared buffers

-- - fsyncs done to ensure the durability of data dirtying shared buffers

-- There is no test for blocks evicted from shared buffers, because we cannot

-- be sure of the state of shared buffers at the point the test is run.

-- Create a regular table and insert some data to generate IOCONTEXT_NORMAL

-- extends.

SELECT sum(extends) AS io_sum_shared_before_extends

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'relation' \gset

CREATE TABLE test_io_shared(a int);

INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(extends) AS io_sum_shared_after_extends

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'relation' \gset

SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends;

?column?

----------

t

(1 row)

-- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes

-- and fsyncs.

SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs

FROM pg_stat_io

WHERE io_context = 'normal' AND io_object = 'relation' \gset io_sum_shared_before_

-- See comment above for rationale for two explicit CHECKPOINTs.

CHECKPOINT;

CHECKPOINT;

SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs

FROM pg_stat_io

WHERE io_context = 'normal' AND io_object = 'relation' \gset io_sum_shared_after_

SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes;

?column?

----------

t

(1 row)

SELECT current_setting('fsync') = 'off'

OR :io_sum_shared_after_fsyncs > :io_sum_shared_before_fsyncs;

?column?

----------

t

(1 row)

-- Change the tablespace so that the table is rewritten directly, then SELECT

-- from it to cause it to be read back into shared buffers.

SELECT sum(reads) AS io_sum_shared_before_reads

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'relation' \gset

ALTER TABLE test_io_shared SET TABLESPACE regress_tblspace;

-- SELECT from the table so that the data is read into shared buffers and

-- io_context 'normal', io_object 'relation' reads are counted.

SELECT COUNT(*) FROM test_io_shared;

count

-------

100

(1 row)

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(reads) AS io_sum_shared_after_reads

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'relation' \gset

SELECT :io_sum_shared_after_reads > :io_sum_shared_before_reads;

?column?

----------

t

(1 row)

DROP TABLE test_io_shared;

-- Test that the follow IOCONTEXT_LOCAL IOOps are tracked in pg_stat_io:

-- - eviction of local buffers in order to reuse them

-- - reads of temporary table blocks into local buffers

-- - writes of local buffers to permanent storage

-- - extends of temporary tables

-- Set temp_buffers to its minimum so that we can trigger writes with fewer

-- inserted tuples. Do so in a new session in case temporary tables have been

-- accessed by previous tests in this session.

\c

SET temp_buffers TO 100;

CREATE TEMPORARY TABLE test_io_local(a int, b TEXT);

SELECT sum(extends) AS extends, sum(evictions) AS evictions, sum(writes) AS writes

FROM pg_stat_io

WHERE io_context = 'normal' AND io_object = 'temp relation' \gset io_sum_local_before_

-- Insert tuples into the temporary table, generating extends in the stats.

-- Insert enough values that we need to reuse and write out dirty local

-- buffers, generating evictions and writes.

INSERT INTO test_io_local SELECT generate_series(1, 5000) as id, repeat('a', 200);

-- Ensure the table is large enough to exceed our temp_buffers setting.

SELECT pg_relation_size('test_io_local') / current_setting('block_size')::int8 > 100;

?column?

----------

t

(1 row)

SELECT sum(reads) AS io_sum_local_before_reads

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'temp relation' \gset

-- Read in evicted buffers, generating reads.

SELECT COUNT(*) FROM test_io_local;

count

-------

5000

(1 row)

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(evictions) AS evictions,

sum(reads) AS reads,

sum(writes) AS writes,

sum(extends) AS extends

FROM pg_stat_io

WHERE io_context = 'normal' AND io_object = 'temp relation' \gset io_sum_local_after_

SELECT :io_sum_local_after_evictions > :io_sum_local_before_evictions,

:io_sum_local_after_reads > :io_sum_local_before_reads,

:io_sum_local_after_writes > :io_sum_local_before_writes,

:io_sum_local_after_extends > :io_sum_local_before_extends;

?column? | ?column? | ?column? | ?column?

----------+----------+----------+----------

t | t | t | t

(1 row)

-- Change the tablespaces so that the temporary table is rewritten to other

-- local buffers, exercising a different codepath than standard local buffer

-- writes.

ALTER TABLE test_io_local SET TABLESPACE regress_tblspace;

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(writes) AS io_sum_local_new_tblspc_writes

FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'temp relation' \gset

SELECT :io_sum_local_new_tblspc_writes > :io_sum_local_after_writes;

?column?

----------

t

(1 row)

RESET temp_buffers;

-- Test that reuse of strategy buffers and reads of blocks into these reused

-- buffers while VACUUMing are tracked in pg_stat_io.

-- Set wal_skip_threshold smaller than the expected size of

-- test_io_vac_strategy so that, even if wal_level is minimal, VACUUM FULL will

-- fsync the newly rewritten test_io_vac_strategy instead of writing it to WAL.

-- Writing it to WAL will result in the newly written relation pages being in

-- shared buffers -- preventing us from testing BAS_VACUUM BufferAccessStrategy

-- reads.

SET wal_skip_threshold = '1 kB';

SELECT sum(reuses) AS reuses, sum(reads) AS reads

FROM pg_stat_io WHERE io_context = 'vacuum' \gset io_sum_vac_strategy_before_

CREATE TABLE test_io_vac_strategy(a int, b int) WITH (autovacuum_enabled = 'false');

INSERT INTO test_io_vac_strategy SELECT i, i from generate_series(1, 8000)i;

-- Ensure that the next VACUUM will need to perform IO by rewriting the table

-- first with VACUUM (FULL).

VACUUM (FULL) test_io_vac_strategy;

VACUUM (PARALLEL 0) test_io_vac_strategy;

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(reuses) AS reuses, sum(reads) AS reads

FROM pg_stat_io WHERE io_context = 'vacuum' \gset io_sum_vac_strategy_after_

SELECT :io_sum_vac_strategy_after_reads > :io_sum_vac_strategy_before_reads,

:io_sum_vac_strategy_after_reuses > :io_sum_vac_strategy_before_reuses;

?column? | ?column?

----------+----------

t | t

(1 row)

RESET wal_skip_threshold;

-- Test that extends done by a CTAS, which uses a BAS_BULKWRITE

-- BufferAccessStrategy, are tracked in pg_stat_io.

SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_before

FROM pg_stat_io WHERE io_context = 'bulkwrite' \gset

CREATE TABLE test_io_bulkwrite_strategy AS SELECT i FROM generate_series(1,100)i;

SELECT pg_stat_force_next_flush();

pg_stat_force_next_flush

--------------------------

(1 row)

SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_after

FROM pg_stat_io WHERE io_context = 'bulkwrite' \gset

SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_extends_before;

?column?

----------

t

(1 row)

-- Test IO stats reset

SELECT pg_stat_have_stats('io', 0, 0);

pg_stat_have_stats

--------------------

t

(1 row)

SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) AS io_stats_pre_reset

FROM pg_stat_io \gset

SELECT pg_stat_reset_shared('io');

pg_stat_reset_shared

----------------------

(1 row)

SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) AS io_stats_post_reset

FROM pg_stat_io \gset

SELECT :io_stats_post_reset < :io_stats_pre_reset;

?column?

----------

t

(1 row)

-- End of Stats Test