31 观察数据库运行

select lab.prove(
  'pg_stat_statements is preloaded, compute_query_id is auto, and it tracks top-level statements',
  current_setting('shared_preload_libraries') like '%pg_stat_statements%'
  and current_setting('compute_query_id') = 'auto'
  and current_setting('pg_stat_statements.track') = 'top');

select count(*) from projects where id = 1;
select   count(*)
  from projects   where id = 2;

select lab.prove(
  'whitespace and constants do not change the queryid',
  (select calls from lab.stmts where query = 'select count(*) from projects where id = $1') = 2);

create table scratch (id int);
select count(*) from scratch where id = 1;
drop table scratch;
create table scratch (id int);
select count(*) from scratch where id = 1;

select lab.prove(
  'PostgreSQL 18 hashes tables by name: a dropped and recreated table keeps its queryid',
  (select calls from lab.stmts where query = 'select count(*) from scratch where id = $1') = 2);

select count(*) from users where id in (1, 2);
select count(*) from users where id in (1, 2, 3, 4, 5);
select count(*) from users where id in (7, 8, 9, 10, 11, 12, 13, 14, 15, 16);
select count(*) from users where id in ($1, $2) \bind 1 2 \g
select count(*) from users where id in ($1, $2, $3) \bind 1 2 3 \g

select lab.prove(
  'PostgreSQL 18 collapses IN lists of any length into one entry, shown as in ($1 /*, ... */)',
  (select calls from lab.stmts
   where query = 'select count(*) from users where id in ($1 /*, ... */)') = 5);

select lab.prove(
  'the report ran over 20 times less often than the lookup but took over 5 times more total time',
  (select calls from w where name = 'lookup') > 20 * (select calls from w where name = 'report')
  and (select total_exec_time from w where name = 'report')
      > 5 * (select total_exec_time from w where name = 'lookup'));

select lab.prove(
  'the report touches the entire events table every call (blocks per call within 5% of its pages)',
  (select (shared_blks_hit + shared_blks_read)::numeric / calls from w where name = 'report')
    between 0.95 * (select relpages from pg_class where relname = 'events')
        and 1.05 * (select relpages from pg_class where relname = 'events'));

select lab.prove(
  'the primary key lookup touches 3 pages: root and leaf of users_pkey, plus one heap page',
  (select (shared_blks_hit + shared_blks_read)::numeric / calls from w where name = 'lookup')
    between 3 and 3.5
  and (select level from bt_metap('users_pkey')) = 1);

select lab.prove(
  'the project list touches about 257 pages for 20 rows (between 200 and 320)',
  (select (shared_blks_hit + shared_blks_read) / calls from w where name = 'projects')
    between 200 and 320);

select lab.prove(
  'each insert writes about five WAL records: heap tuple, primary key entry, and three foreign key row locks',
  (select wal_records::numeric / calls from w where name = 'insert') between 4.9 and 5.6);

-- one insert, read straight from the WAL with pg_walinspect
select lab.prove(
  'one insert transaction: heap INSERT, B-tree INSERT_LEAF, three heap LOCK records, then COMMIT',
  (select array_agg(resource_manager || '/' || record_type order by start_lsn)
   from pg_get_wal_records_info(:'wal_start', :'wal_end')
   where xid = :'insert_xid'::xid)
  = array['Heap/INSERT', 'Btree/INSERT_LEAF', 'Heap/LOCK', 'Heap/LOCK', 'Heap/LOCK',
          'Transaction/COMMIT']);

-- a: begin; update accounts set name = name where id = 7; select pg_sleep(60)
-- b: begin; select count(*) from users where account_id = 7;  (then idle)
-- c: update accounts set plan = plan where id = 7;            (blocks on a)
select lab.prove(
  'a session sleeping inside its transaction is active, waiting on Timeout/PgSleep, and has a backend_xid',
  (select state = 'active' and wait_event_type = 'Timeout' and wait_event = 'PgSleep'
          and backend_xid is not null
   from pg_stat_activity where pid = :pid_a));

select lab.prove(
  'a session waiting for a row waits on Lock/transactionid, and pg_blocking_pids names the holder',
  (select wait_event_type = 'Lock' and wait_event = 'transactionid'
          and pg_blocking_pids(pid) = array[:pid_a]
   from pg_stat_activity where pid = :pid_c));

select lab.prove(
  'an open read committed transaction that has not written shows idle in transaction, ClientRead, no xid, no xmin',
  (select state = 'idle in transaction' and wait_event = 'ClientRead'
          and backend_xid is null and backend_xmin is null
   from pg_stat_activity where pid = :pid_b));

select lab.prove(
  'a repeatable read transaction keeps its xmin while idle',
  (select state = 'idle in transaction' and backend_xmin is not null
   from pg_stat_activity where pid = :pid_d));

begin;
select count(*) from pg_stat_activity where application_name = 'ws_late' \g /dev/null
select dblink_connect('ws_late', format('dbname=%s application_name=ws_late', current_database())) \g /dev/null
select pg_sleep(0.3) \g /dev/null

select lab.prove(
  'inside one transaction pg_stat_activity repeats its first snapshot: a session that connected since is invisible',
  (select count(*) from pg_stat_activity where application_name = 'ws_late') = 0);

select pg_stat_clear_snapshot() \g /dev/null

select lab.prove(
  'after pg_stat_clear_snapshot() the next read sees it',
  (select count(*) from pg_stat_activity where application_name = 'ws_late') = 1);
commit;

-- ws_cpu spins in a PL/pgSQL loop, ws_sleep runs pg_sleep, ws_hold updates
-- account 9 and idles in its transaction, ws_wait updates the same row;
-- 20 samples, 100 ms apart, with \watch
select lab.prove(
  'a session running a CPU loop samples as active with no wait event (at least 16 of 20)',
  (select count(*) from wait_samples
   where application_name = 'ws_cpu' and state = 'active' and wait_event is null) >= 16);

select lab.prove(
  'a session waiting for a row samples as Lock:transactionid every time',
  (select count(*) filter (where (wait_event_type, wait_event) = ('Lock', 'transactionid')) = count(*)
          and count(*) >= 18
   from wait_samples where application_name = 'ws_wait'));

select lab.prove(
  'a session idle inside its transaction samples as idle in transaction, Client:ClientRead',
  (select bool_and(state = 'idle in transaction' and (wait_event_type, wait_event) = ('Client', 'ClientRead'))
          and count(*) >= 18
   from wait_samples where application_name = 'ws_hold'));

select lab.prove(
  'every report read the whole events table: about 2 million rows read per report',
  (select seq_tup_read::numeric from pg_stat_user_tables where relname = 'events')
  / (select calls from w where name = 'report') between 1900000 and 2200000);

select lab.prove(
  'events counts more sequential scans than report runs, because each parallel worker''s share counts',
  (select seq_scan from pg_stat_user_tables where relname = 'events')
    >= 1.5 * (select calls from w where name = 'report'));

select lab.prove(
  'events_pkey shows idx_scan = 0 after hundreds of inserts: uniqueness checks do not count as scans',
  (select idx_scan from pg_stat_user_indexes where indexrelname = 'events_pkey') = 0
  and (select n_tup_ins from pg_stat_user_tables where relname = 'events') > 100);

select lab.prove(
  'a database cloned from a template starts with empty statistics: n_live_tup is 0 for 100,000 users',
  (select n_live_tup from pg_stat_user_tables where relname = 'users') = 0
  and (select reltuples from pg_class where relname = 'users') = 100000);

select lab.prove(
  'the events table is larger than a quarter of shared_buffers, so its scans use a bulkread ring',
  pg_relation_size('events') > pg_size_bytes(current_setting('shared_buffers')) / 4);

select lab.prove(
  'the report finds under 60% of its pages in shared buffers, however often it runs',
  (select 100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read)
   from w where name = 'report') < 60);

select lab.prove(
  'pg_stat_io (PostgreSQL 18) reports bytes: read_bytes, write_bytes, extend_bytes',
  (select count(*) from information_schema.columns
   where table_name = 'pg_stat_io'
     and column_name in ('read_bytes', 'write_bytes', 'extend_bytes')) = 3);

select lab.prove(
  'pg_stat_io covers WAL as an object in PostgreSQL 18',
  exists (select 1 from pg_stat_io where object = 'wal'));

delete from events where id % 4 = 0;

select lab.prove(
  'after deleting a quarter of events, pgstattuple_approx reports 20 to 30% dead tuples',
  (select dead_tuple_percent between 20 and 30 from pgstattuple_approx('events')));

select lab.prove(
  'with nothing vacuumed since, no page is all-visible, so it scans 100% of the table',
  (select scanned_percent > 99 from pgstattuple_approx('events')));