contrib/pg_walinspect/sql/pg_walinspect.sql

   1 CREATE EXTENSION pg_walinspect;
   2
   3 -- Mask DETAIL messages as these could refer to current LSN positions.
   4 \set VERBOSITY terse
   5
   6 -- Make sure checkpoints don't interfere with the test.
   7 SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
   8
   9 CREATE TABLE sample_tbl(col1 int, col2 int);
  10
  11 -- Save some LSNs for comparisons.
  12 SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
  13 INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
  14 SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
  15 INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
  16
  17 -- ===================================================================
  18 -- Tests for input validation
  19 -- ===================================================================
  20
  21 -- Invalid input LSN.
  22 SELECT * FROM pg_get_wal_record_info('0/0');
  23
  24 -- Invalid start LSN.
  25 SELECT * FROM pg_get_wal_records_info('0/0', :'wal_lsn1');
  26 SELECT * FROM pg_get_wal_stats('0/0', :'wal_lsn1');
  27 SELECT * FROM pg_get_wal_block_info('0/0', :'wal_lsn1');
  28
  29 -- Start LSN > End LSN.
  30 SELECT * FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1');
  31 SELECT * FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');
  32 SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1');
  33
  34 -- LSNs with the highest value possible.
  35 SELECT * FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
  36 -- Success with end LSNs.
  37 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  38 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  39 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  40 -- Failures with start LSNs.
  41 SELECT * FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
  42 SELECT * FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
  43 SELECT * FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
  44
  45 -- ===================================================================
  46 -- Tests for all function executions
  47 -- ===================================================================
  48
  49 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
  50 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
  51 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
  52 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
  53
  54 -- ===================================================================
  55 -- Test for filtering out WAL records of a particular table
  56 -- ===================================================================
  57
  58 SELECT oid AS sample_tbl_oid FROM pg_class WHERE relname = 'sample_tbl' \gset
  59
  60 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2')
  61                         WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap';
  62
  63 -- ===================================================================
  64 -- Test for filtering out WAL records based on resource_manager and
  65 -- record_type
  66 -- ===================================================================
  67
  68 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2')
  69                         WHERE resource_manager = 'Heap' AND record_type = 'INSERT';
  70
  71 -- ===================================================================
  72 -- Tests to get block information from WAL record
  73 -- ===================================================================
  74
  75 -- Update table to generate some block data.
  76 SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
  77 UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 1;
  78 SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
  79 -- Check if we get block data from WAL record.
  80 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn3', :'wal_lsn4')
  81   WHERE relfilenode = :'sample_tbl_oid' AND block_data IS NOT NULL;
  82
  83 -- Force a checkpoint so that the next update will log a full-page image.
  84 SELECT pg_current_wal_lsn() AS wal_lsn5 \gset
  85 CHECKPOINT;
  86
  87 -- Verify that an XLOG_CHECKPOINT_REDO record begins at precisely the redo LSN
  88 -- of the checkpoint we just performed.
  89 SELECT redo_lsn FROM pg_control_checkpoint() \gset
  90 SELECT start_lsn = :'redo_lsn'::pg_lsn AS same_lsn, resource_manager,
  91     record_type FROM pg_get_wal_record_info(:'redo_lsn');
  92
  93 -- This update should produce a full-page image because of the checkpoint.
  94 UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 2;
  95 SELECT pg_current_wal_lsn() AS wal_lsn6 \gset
  96 -- Check if we get FPI from WAL record.
  97 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn5', :'wal_lsn6')
  98   WHERE relfilenode = :'sample_tbl_oid' AND block_fpi_data IS NOT NULL;
  99
 100 -- ===================================================================
 101 -- Tests for permissions
 102 -- ===================================================================
 103 CREATE ROLE regress_pg_walinspect;
 104
 105 SELECT has_function_privilege('regress_pg_walinspect',
 106   'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- no
 107 SELECT has_function_privilege('regress_pg_walinspect',
 108   'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
 109 SELECT has_function_privilege('regress_pg_walinspect',
 110   'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
 111 SELECT has_function_privilege('regress_pg_walinspect',
 112   'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
 113
 114 -- Functions accessible by users with role pg_read_server_files.
 115 GRANT pg_read_server_files TO regress_pg_walinspect;
 116
 117 SELECT has_function_privilege('regress_pg_walinspect',
 118   'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
 119 SELECT has_function_privilege('regress_pg_walinspect',
 120   'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
 121 SELECT has_function_privilege('regress_pg_walinspect',
 122   'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
 123 SELECT has_function_privilege('regress_pg_walinspect',
 124   'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
 125
 126 REVOKE pg_read_server_files FROM regress_pg_walinspect;
 127
 128 -- Superuser can grant execute to other users.
 129 GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
 130   TO regress_pg_walinspect;
 131 GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
 132   TO regress_pg_walinspect;
 133 GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
 134   TO regress_pg_walinspect;
 135 GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
 136   TO regress_pg_walinspect;
 137
 138 SELECT has_function_privilege('regress_pg_walinspect',
 139   'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
 140 SELECT has_function_privilege('regress_pg_walinspect',
 141   'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
 142 SELECT has_function_privilege('regress_pg_walinspect',
 143   'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
 144 SELECT has_function_privilege('regress_pg_walinspect',
 145   'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
 146
 147 REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
 148   FROM regress_pg_walinspect;
 149 REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
 150   FROM regress_pg_walinspect;
 151 REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
 152   FROM regress_pg_walinspect;
 153 REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
 154   FROM regress_pg_walinspect;
 155
 156 -- ===================================================================
 157 -- Clean up
 158 -- ===================================================================
 159
 160 DROP ROLE regress_pg_walinspect;
 161
 162 SELECT pg_drop_replication_slot('regress_pg_walinspect_slot');
 163
 164 DROP TABLE sample_tbl;
 165 DROP EXTENSION pg_walinspect;