-- Robustness of logical checksums that evaluate index definitions and of -- database scans on a database that changes while it is being scanned. -- 1. Index expressions that are not PARALLEL SAFE. A user-defined function -- is PARALLEL UNSAFE unless declared otherwise; running it in parallel mode -- fails (here: SET is not allowed during a parallel operation). With -- workers configured, such an index must be computed serially and give the -- same value. CREATE FUNCTION rob_unsafe(int) RETURNS int LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN PERFORM set_config('rob.x', '1', true); RETURN $1; END $$; CREATE TABLE rob_big AS SELECT g AS a FROM generate_series(1, 100000) g; CREATE INDEX rob_big_expr ON rob_big (rob_unsafe(a)); CREATE INDEX rob_big_pred ON rob_big (a) WHERE rob_unsafe(a) > 0; CREATE INDEX rob_big_plain ON rob_big (a); SET pg_integrity_checksums.workers = 0; SELECT pg_index_logical_checksum('rob_big_expr') AS expr32 \gset SELECT pg_index_logical_checksum('rob_big_pred', 'fnv64') AS pred64 \gset SELECT pg_database_logical_checksum() AS db32 \gset SELECT pg_database_logical_checksum(false, true, 'fnv64') AS db64 \gset SET pg_integrity_checksums.workers = 2; SELECT pg_index_logical_checksum('rob_big_expr') = :expr32 AS expr_serial_fallback, pg_index_logical_checksum('rob_big_pred', 'fnv64') = :'pred64' AS pred_serial_fallback; expr_serial_fallback | pred_serial_fallback ----------------------+---------------------- t | t (1 row) -- The database scan computes the unsafe indexes outside the parallel scan. SELECT pg_database_logical_checksum() = :db32 AS db_parallel_same, pg_database_logical_checksum(false, true, 'fnv64') = :'db64' AS db64_parallel_same; db_parallel_same | db64_parallel_same ------------------+-------------------- t | t (1 row) RESET pg_integrity_checksums.workers; DROP TABLE rob_big; DROP FUNCTION rob_unsafe(int); -- 2. A relation dropped while a database checksum runs. The relations are -- enumerated with the statement snapshot; one dropped before the scan -- reaches it is left out with a NOTICE instead of failing the checksum, even -- in strict mode. To drop it at the right moment, an index expression of a -- table that is scanned first drops a table that is scanned later (tables -- are scanned in OID order without workers). CREATE FUNCTION rob_dropper(int) RETURNS int LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN $1; END $$; CREATE TABLE rob_trap (a int); INSERT INTO rob_trap VALUES (1); CREATE INDEX rob_trap_idx ON rob_trap (rob_dropper(a)); CREATE TABLE rob_victim (a int); INSERT INTO rob_victim VALUES (1); CREATE INDEX rob_victim_idx ON rob_victim (a); CREATE OR REPLACE FUNCTION rob_dropper(int) RETURNS int LANGUAGE plpgsql VOLATILE AS $$ BEGIN -- Schema-qualified: on PostgreSQL 17+ index expressions run with a -- restricted search_path (pg_catalog, pg_temp). EXECUTE 'DROP TABLE IF EXISTS public.rob_victim'; RETURN $1; END $$; SHOW pg_integrity_checksums.strict; pg_integrity_checksums.strict ------------------------------- on (1 row) SET pg_integrity_checksums.workers = 0; -- The NOTICE names the dropped relation by OID, which varies between runs. SET client_min_messages = warning; SELECT pg_database_logical_checksum() IS NOT NULL AS checksum_with_drop; checksum_with_drop -------------------- t (1 row) RESET client_min_messages; SELECT to_regclass('rob_victim') IS NULL AS victim_dropped; victim_dropped ---------------- t (1 row) -- Afterwards the checksum is that of the database without the table. SELECT pg_database_logical_checksum() IS NOT NULL AS checksum_after_drop; NOTICE: table "rob_victim" does not exist, skipping checksum_after_drop --------------------- t (1 row) RESET pg_integrity_checksums.workers; DROP TABLE rob_trap; DROP FUNCTION rob_dropper(int); -- Mapped catalogs have pg_class.relfilenode = 0; their file number lives in -- the relation map. Physical checksums must still change when such a -- catalog is rewritten. The first VACUUM FULL removes dead tuples, so the -- second one keeps every tuple in place and only changes the file. VACUUM FULL pg_type; -- (\gset rather than a temp table: creating one would add rows to pg_type.) SELECT pg_table_physical_checksum('pg_type', false) AS mapped_before \gset VACUUM FULL pg_type; SELECT :mapped_before <> pg_table_physical_checksum('pg_type', false) AS mapped_rewrite_changes_physical; mapped_rewrite_changes_physical --------------------------------- t (1 row)