Revision history for pg_integrity_checksums (formerly pg_checksums) 2.7.0 2026-11-15 - Fix: the logical database checksum summed the per-relation checksums without the identity of the relations, so moving rows between two tables (or indexes) of the same structure, or swapping their contents, did not change it. Each relation's checksum is now combined with a hash of its qualified name (schema.relname); the name, unlike the OID, is the same after a dump and restore. Values of pg_database_logical_checksum change for every database, and renaming a relation or moving it to another schema now changes them. Table and index logical checksums are not affected. - Fix: in a SERIALIZABLE transaction the checksum functions took no SIREAD locks and did not check for conflicts with the transactions whose tuples they read, so a write skew through a checksum was not detected. Table, index and database scans now lock each table they read, like a sequential scan; tuple and cell checksums lock the page. New TAP test t/011_serializable.pl (PostgreSQL 16 and later). - Fix: physical checksums used pg_class.relfilenode, which is 0 for mapped catalogs (pg_class, pg_type, pg_proc, pg_attribute, shared catalogs), so a rewrite of such a catalog (VACUUM FULL) did not change them. The relation's actual file number is used now. Values do not change for other relations. - Fix: pg_page_checksum_status took a block read from disk with pd_upper = 0 for a new page. Only an all-zero block is new; a corrupt header with pd_upper = 0 is now compared like any other page and reported as "mismatch". - The install and upgrade scripts use CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION, as the PostgreSQL documentation recommends for extension scripts: an existing function of the same signature is not silently taken over. - Documentation: renaming a table or a column does not change its logical checksum (the old text said it did); renaming a type does. - Code cleanup: 64-bit table and index checksums limit the number of workers by the number of blocks like the 32-bit ones; removed unreachable NULL-snapshot checks; pg_checksum_bigint no longer initialises its result with an enum constant. - Fix: the LP_DEAD masking of physical index checksums treated the content of every index page between the page header and pd_lower as line pointers. On B-tree and hash metapages, B-tree and GiST deleted pages, hash bitmap pages and on all pages of GIN, SP-GiST and BRIN indexes that content is something else (metadata, posting lists, bitmaps), and bits in it that looked like an LP_DEAD flag were cleared, so changes to them did not change the checksum. LP_DEAD and "has garbage" hints are now masked only on B-tree, hash and GiST pages that hold index tuples, the only ones index scans set them on. Values change only for indexes that had such a page. - Documentation: tuple and table physical checksums include xmin, xmax and the command id even without the tuple header, so row locks (SELECT ... FOR UPDATE/FOR SHARE, the FOR KEY SHARE lock of foreign key checks) and rolled-back UPDATEs/DELETEs change them although no data changed. The regression tests check it. - New TAP test t/010_index_metapage.pl. - Security: pg_tuple_physical_checksum, pg_tuple_logical_checksum and pg_cell_checksum (and their algorithm overloads) did not check tuple visibility. For the TID of a deleted row, an old version of an updated row or a row inserted by an uncommitted transaction they returned a checksum of data the caller cannot SELECT, and a checksum of a short value is easily reversed by trying candidates. They now return NULL for a tuple that is not visible to the query's snapshot. Checksums of visible tuples do not change. - pg_tuple_physical_checksum returns NULL instead of 0 for a TID without a tuple, like the logical tuple and cell functions. - Fix: pg_page_checksum_status reads the block from disk without a buffer lock, so a read overlapping a concurrent write could see a torn page and report a false "mismatch". A block whose checksum does not match is now read again, up to five times, as pg_basebackup does; "mismatch" is reported only when every read disagrees. - The page checksum TAP test also checks that a corrupted page is reported as "mismatch". - Fix: logical index checksums with pg_integrity_checksums.workers > 0 evaluated index expressions and partial-index predicates in parallel mode even when they called functions that are not PARALLEL SAFE (the default for user-defined functions), which fails or misbehaves (for example "cannot be set during a parallel operation"). Such indexes are now computed without workers; a parallel database checksum computes them in the leader after the parallel part. Values do not change. - Fix: a relation dropped while a database checksum was running made the checksum fail with "could not open relation with OID", and in strict mode (the default) aborted it, so a routine DROP TABLE in a busy database made scheduled checks fail at random. Each relation is now locked and looked up before it is read; one that no longer exists is left out with a NOTICE, in strict mode too, since it is no longer part of the database. - Security: logical index checksums (pg_index_logical_checksum and pg_database_logical_checksum with include_indexes, the default) evaluated index expressions and partial-index predicates with the privileges of the caller. A role that could create a table and an index could therefore run arbitrary SQL as the superuser computing a database checksum, or as any role computing an index checksum (for example through the CLI). The index definition is now evaluated as the owner of the table in a security-restricted operation, with a GUC nest level that undoes SET commands, and on PostgreSQL 17 and later with a restricted search_path, as REINDEX, ANALYZE and amcheck do. Checksum values do not change. - Fix: logical checksums depended on quote_all_identifiers, which pg_dump sets and any session can change. Type tags were built with quote_identifier() and format_type_be(), which quote every identifier when the setting is on, so a session with it on computed different values for columns of types such as jsonb, inet or user-defined types, and a session that changed it mid-way kept or lost the difference depending on what the type cache already held. Tags are now always built as with the default (off), so stored checksums stay valid. - Fix: parallel table and index checksums used the snapshot restored in a worker without making it active. PostgreSQL builds with assertions (--enable-cassert) aborted the worker; release builds were not affected. The worker now makes the snapshot active, as the database scan already did. - Fix: pg_page_checksum and pg_page_checksum_status read the page from disk into a buffer that was not I/O-aligned, which PostgreSQL 16 and later require (an assertion failure in assert-enabled builds, and a problem with debug_io_direct). - Performance: logical checksums are 2-5x faster and every checksum value is unchanged. Measured with bench/ on PostgreSQL 19devel, data in shared buffers, no parallel workers: logical table checksums of a 1M-row table 354 -> 118 ms, of a 60-column table 538 -> 108 ms, logical index checksums up to 2.2x, pg_database_logical_checksum 2018 -> 801 ms. With 4 workers the gains are the same. - The type cache keeps the FmgrInfo of send and output functions, so a value no longer costs a pg_proc lookup. Cache entries stay in place across invalidations (a generation counter marks them out of date), and the cache is also invalidated by pg_proc changes. - The common built-in types (bool, "char", int2/4/8, oid, date, time, timetz, timestamp(tz), money, float4/8, interval, uuid, text, varchar, bpchar, name, bytea) are encoded without calling the send function and without its 1 kB buffer. The bytes hashed are exactly what the send function returns; text types still go through textsend() when it would convert to a different client encoding. - Rows are deformed once instead of with heap_getattr() per column (quadratic in the number of columns), the type tag and column position are hashed once per column instead of once per value, and the FNV-1a primitives are inlined. - A serial pg_database_logical_checksum reads each table once for the table and all of its indexes instead of once per relation. If the combined pass fails, the relations are computed one by one as before, so warnings, strict-mode errors and the skipped count do not change. Parallel database scans keep the per-relation split, which balances better across workers. - Heap scans skip per-tuple visibility checks on all-visible pages (as a sequential scan does), and physical index and TOAST pages are hashed after the buffer lock is released, from the masked copy. - Add bench/, a benchmark that loads a data set and reports the median time and the value of each checksum, for comparing two builds. - Add regression tests checksum_fastpath (the direct encodings against the real send functions, including another client encoding) and checksum_dbgroup (the grouped database scan and its fallback). 2.6.0 2026-11-14 - Fix: physical index checksums no longer change when nothing changed. Index pages were hashed as raw buffer bytes, including the page LSN, the in-buffer pd_checksum (stale until the page is evicted, because PostgreSQL sets the checksum on the copy it writes out) and the hints index scans set under a share lock (LP_DEAD line pointers, B-tree BTP_HAS_GARBAGE, hash LH_PAGE_HAS_DEAD_TUPLES, GiST F_HAS_GARBAGE). A plain SELECT or a server restart therefore changed the result. Pages are now hashed from a copy with that state masked out. - Fix: physical TOAST checksums (pg_table_physical_checksum on a TOAST relation, database physical checksums with include_toast) mask the same page state and the infomask hint bits of TOAST tuples. - Value change: physical checksums of indexes and TOAST relations differ from 2.5.0. Re-capture physical baselines after upgrading. Logical checksums and physical heap checksums are unchanged. - Fix: logical table, tuple, cell and index checksums no longer canonicalize values under a buffer content lock. Detoasting, type send functions and index expressions ran with interrupts held off, so a slow expression or a large toasted value could not be cancelled (statement_timeout and pg_cancel_backend waited for it to finish), and reading other buffers and the catalogs under the lock risked an undetectable LWLock deadlock. Visibility is still checked under the lock; hashing now runs with the buffer only pinned. - Add regression tests checksum_physical_hints and checksum_buffer_locks and TAP test 009_physical_restart. - The pure utility functions are IMMUTABLE and PARALLEL SAFE: pg_data_checksum and pg_text_checksum (both overloads) and pg_checksum_bigint. The algorithm overloads and pg_checksum_bigint used to be VOLATILE, and none was PARALLEL SAFE, so they could not be used in index expressions or generated columns. - Build: drop the PostgreSQL 19 version gates. Arrays are decomposed with deconstruct_array() instead of the array_iter API, and the pg_class scan of database checksums uses systable_beginscan(); both have the same signature on every supported release, so the extension also builds against PostgreSQL 19 development snapshots from before the API changes. Checksum values are unchanged. - Docs: the fnv64 NULL sentinel is 0x00000000FFFFFFFF (the 32-bit sentinel zero-extended), not UINT64_MAX as documented. The value is unchanged. - Add 2.5.0 -> 2.6.0 upgrade script and the checksum_upgrade regression test, which installs 2.0.0, updates to the default version and checks that every function matches a fresh install (attributes, privileges and comments). 2.5.0 2026-11-12 - Behavior change: pg_integrity_checksums.strict now defaults to on. A database checksum (pg_database_physical_checksum and pg_database_logical_checksum, with or without an algorithm argument) fails when a relation cannot be checksummed, for example an invalid index, an unsupported relation kind or a scan error. Previously such a relation was skipped with a WARNING, so a corrupt table did not show up as a changed result. - To keep the old behavior set pg_integrity_checksums.strict = off, per session, per role or database (ALTER ROLE/DATABASE ... SET) or in postgresql.conf. - No checksum values change. - With strict mode off, the summary WARNING now says how many relations were skipped (invalid indexes and unsupported index access methods are counted too; they used to be skipped silently), including in parallel scans. - Errors raised in strict mode now name the relation (a CONTEXT line: "while computing the database checksum of relation ..."), and the invalid-index error says that the index is invalid. - Add 2.4.0 -> 2.5.0 upgrade script (no SQL object changes), a regression test for the strict default across all database-level functions and parallel scans, and a TAP test for corrupt pages and unlogged tables on a standby. 2.4.0 2026-11-12 - Fix: logical checksums no longer depend on dropped columns. Table columns, composite fields and pg_cell_checksum() now hash the position among the live columns instead of the physical attribute number, so a table with dropped columns has the same logical checksum as an identical table without them (for example after pg_dump/restore). Tables without dropped columns are unchanged. - Fix: logical checksums no longer depend on the session's search_path. User-defined types are identified by their unqualified name instead of format_type_be(), which qualified the name when the schema was not on the search_path. Types in pg_catalog and types visible in the default search_path are unchanged. - Fix: physical tuple checksums that include the tuple header (include_header = true) ignore the xmin/xmax hint bits, which readers set lazily; the value used to change after the first read of a tuple. Checksums without the header are unchanged. - Revoke EXECUTE from PUBLIC on the generic database-level overloads (pg_database_*_checksum(boolean, boolean, text)); they already required a superuser. - Add 2.3.0 -> 2.4.0 upgrade script and a regression test for the portability fixes. Baselines are affected only if they were taken (1) on tables with dropped columns or composite types with dropped attributes, (2) on user-defined types from a session whose search_path did not include the type's schema, or (3) with include_header = true. See "Upgrading to 2.4" in docs/installation.md. 2.3.0 2026-11-10 - Add pg_page_checksum_status(regclass, integer) to verify a page against its stored pd_checksum, including data_checksums-disabled reporting. - Clarify that pg_page_checksum() calculates a 16-bit page checksum and does not itself verify the stored value. 2.2.0 2026-11-09 - Add generic algorithm overloads for data, text, cell, tuple, table, index and database logical checksums, plus physical tuple, table, index and database checksums. - Add FNV-1a 64-bit digests and the pg_checksum_bigint conversion helper. - Add parallel execution for 64-bit checksums; the 64-bit database path also reuses the existing parallel worker pool. - Add upgrade script from 2.1.0 to 2.2.0. 2.1.0 2026-10-31 - Make logical checksums independent of the server's native endianness. Array dimensions/lower bounds, composite field counts and multirange counts are now hashed in a canonical little-endian byte order instead of hashing native int/uint32 bytes. - Logical checksum values on little-endian systems are unchanged; 2.x baselines remain valid. Big-endian systems now produce the same logical values as little-endian systems. - Physical checksums remain storage- and architecture-dependent by design. - Add a manually triggered FreeBSD powerpc64 big-endian test workflow. - No SQL object changes; the 2.0.2 -> 2.1.0 upgrade script is a no-op. 2.0.2 2026-10-29 - Build against PostgreSQL 19 stable: adapt to the changed array_iter_setup, array_iter_next and table_beginscan APIs. - Add PostgreSQL 19 to CI, ASAN/UBSAN and coverage workflows, cross-version logical baselines, and property/fuzz tests. - No SQL object changes; the 2.0.1 -> 2.0.2 upgrade script is a no-op. 2.0.1 2026-10-29 - Fix logical checksums of system catalogs containing anyarray values (notably pg_statistic): the relation was skipped with "no binary output function available for type aclitem" when system catalogs were included. - Fix logical index checksums for Hash indexes. Hash index descriptors store an int4 hash code, but FormIndexDatum returns the original key; logical checksums now use the original input type, so a Hash index and a B-tree index on the same column have the same logical content and match across databases. - Add extensive type, system-catalog, DDL, privilege, TOAST, error and cross-database tests. - Hash-index logical checksums change because the previous values used the index descriptor's int4 type for the original key value. Recompute index and database logical baselines that include Hash indexes. - Database logical checksums with include_system=true change because system catalogs such as pg_statistic are now covered instead of skipped. - No SQL object changes; the 2.0.0 -> 2.0.1 upgrade script is a no-op. 2.0.0 2026-10-28 - BREAKING: rename the extension from pg_checksums to pg_integrity_checksums. The new name avoids confusion with the pg_checksums utility shipped with PostgreSQL core and with the unrelated credativ pg_checksums_ext tool. - BREAKING: rename the GUC prefix from pg_checksums.* to pg_integrity_checksums.*. - SQL function names and checksum values are unchanged. Existing checksum baselines remain valid after reinstalling the extension. - There is no ALTER EXTENSION rename path. Migrate with: DROP EXTENSION pg_checksums; CREATE EXTENSION pg_integrity_checksums; then update pg_checksums.workers/strict settings to the new prefix. - The 1.3.x extension remains available from the previous PGXN release and git tag for users who need the old name. 1.3.2 2026-10-28 - Performance-only release: all checksum values and SQL APIs are unchanged. - Cache base type, element type and type I/O metadata once per type instead of performing syscache lookups for every cell. - Iterate array elements with array_iter (no intermediate Datum/bool arrays) and keep small composite arguments on the stack. - Avoid duplicate page and tuple validation during physical table scans. - Reuse one BufferAccessStrategy per scan worker, scan 64 blocks per atomic work item, and do not start more workers than there are chunks. 1.3.1 2026-10-24 - Parallelize pg_index_logical_checksum(): the heap scan and index definition evaluation are split across pg_checksums.workers. Serial and parallel results are identical. Partitioned indexes aggregate their leaf indexes as before. - Remove unused parallel-scan state fields and the obsolete logical-table found_invalid flag. 1.3.0 2026-10-23 - BREAKING: replace the legacy logical checksum format. Logical checksums are now canonical, 32-bit values that exclude relation OIDs, heap TIDs and type OIDs. Enum labels, numeric scale, arrays, composites, domains and ranges are encoded canonically, so identical table content can be compared across databases (same major version and compatible schema/type names). - BREAKING: pg_table_logical_checksum() no longer requires a primary key and no longer mixes the relation OID into the result. Duplicate rows are preserved by a sum-plus-count aggregate. Partitioned tables accumulate all leaf rows into the same accumulator. - BREAKING: pg_tuple_logical_checksum() loses the include_header argument. The tuple header is never part of a logical checksum. - BREAKING: pg_index_logical_checksum() is now derived from the visible table rows and the index definition instead of B-tree pages. It works for every index access method, supports expression, partial, INCLUDE and partitioned indexes, and contains no heap TIDs or dead entries. A physical index checksum remains the tool for detecting missing/corrupt index entries. - BREAKING: pg_database_logical_checksum(include_system, include_indexes) replaces the old (include_system, include_toast) signature. It includes tables without primary keys and optionally logical index checksums; TOAST content is covered through the parent table. - Remove pg_table_content_checksum(); pg_table_logical_checksum() now provides the primary-key-free content checksum. - Rework table and database logical aggregates for the canonical format, including parallel workers. pg_data_checksum() and pg_text_checksum() remain low-level utilities. - Physical checksums are unchanged. All logical baselines must be recomputed. 1.2.0 2026-10-18 - Add partitioned index support. pg_index_physical_checksum() and pg_index_logical_checksum() now aggregate the checksums of all leaf indexes instead of returning NULL for the partitioned parent. Each leaf contributes combine(leaf checksum, leaf OID) to an order-independent aggregate, and the parent OID is folded in at the end; logical aggregation does the same for B-tree leaves. - Add pg_table_content_checksum(regclass). It does not require a primary key and does not include relation OIDs, so it can be used for tables without a PK and for content comparison across databases. Duplicate rows are preserved by a sum-plus-count aggregate, and partitioned tables aggregate all leaf rows into the same accumulator. - Add the pg_checksums.strict GUC. When enabled, database checksum functions fail if a relation cannot be checksummed (missing primary key, unsupported index access method, or a scan error) instead of silently skipping it. The default remains false. Strict errors release the per-relation internal subtransaction before propagating, so an exception handler can catch them safely. - Fix parallel scans to destroy the parallel context and leave parallel mode when a worker or the leader fails. Previously an error caught by a PL/pgSQL exception block could leave the backend in parallel mode, making later RESET or nested parallel operations fail. - Existing checksum values are unchanged for non-partitioned relations; partitioned indexes change from NULL to an aggregated value, and the new content checksum has its own algorithm. 1.1.1 2026-10-18 - Security: enforce SELECT privilege (and reject relations protected by row-level security) in pg_page_checksum(), pg_cell_checksum(), pg_tuple_*_checksum(), pg_table_*_checksum() and pg_index_*_checksum(). Previously any user could compute checksums of any relation, including tables they could not read. - Security: the database-level functions are no longer SECURITY DEFINER. With SECURITY DEFINER, superuser() saw the owner and the superuser check could be bypassed by any user. EXECUTE is now revoked from PUBLIC, and the extension can no longer be installed by a non-superuser. - Fix a server crash (signal 11) when pg_index_logical_checksum() was called on an SP-GiST index. Logical index checksums are now B-tree only; Hash, GiST, GIN, SP-GiST and BRIN return NULL instead of returning a meaningless value. Physical index checksums remain available for all supported access methods. - pg_page_checksum() now validates the block number. A negative block number used to be interpreted as P_NEW and silently extended the relation; an out-of-range block is now an error. The page is copied before pg_checksum_page() is called, so the shared pd_checksum field is never transiently zeroed in the buffer pool. - Validate relkinds, line pointers, tuple headers and page headers before following them. A corrupted page or a bogus TID now produces a clean error instead of reading out of bounds. pg_cell_checksum() and pg_tuple_logical_checksum() no longer read past PageGetMaxOffsetNumber. - Fix PG_RETURN_NULL() inside PG_TRY and mark locals modified between setjmp and longjmp as volatile in the tuple checksum functions. - Database scans now run each relation in an internal subtransaction. Errors release locks and buffer pins, are reported as a warning and no longer produce a silently partial checksum. Cancellation and statement timeouts are propagated instead of being swallowed. PostgreSQL 12-16 prohibit internal subtransactions in parallel mode; there a relation error aborts a parallel database scan, while serial scans (workers = 0) and PostgreSQL 17+ still skip the relation with a warning. - Enter parallel mode before creating a parallel context and leave it after destroying it. This fixes assertion failures in --enable-cassert builds and prevents the leader from starting nested parallel workers during a database scan. Workers are also clamped by max_worker_processes, and the block-chunk end calculation no longer overflows. - Physical checksums of TOAST relations now hash their raw pages instead of misinterpreting toast tuples as heap tuples. include_toast works independently of include_system, and logical database checksums skip TOAST relations (their content is covered by detoasting the parent table). Shared catalogs are excluded from per-database checksums even when include_system is true. - Physical table checksums no longer include relpages/reltuples. Those are planner statistics, not physical data: ANALYZE could change a physical checksum without any data change, and the implicit float4 to uint32 conversion was undefined for reltuples = -1. Physical table and database checksums computed with 1.1.0 must be refreshed. - Tuple and table physical checksums no longer include the page LSN, pd_checksum and pd_flags. Hint-bit-only changes made by a concurrent scan could change those fields, so serial and parallel scans of the same table could disagree. They now include the relation's relfilenode and reltablespace, so VACUUM FULL, CLUSTER, REINDEX and SET TABLESPACE still change physical checksums. Page-level checksums still hash the raw page bytes. - Skip unsupported index access methods in physical database scans instead of silently folding a zero checksum into the aggregate. - pg_index_physical_checksum() and pg_index_logical_checksum() return NULL for partitioned indexes until leaf-index aggregation is implemented. - Logical checksums are unchanged for the same data. Physical checksums have intentionally changed: planner statistics and transient page metadata were removed, and relfilenode/reltablespace were added. Any physical baseline computed with 1.1.0 must be refreshed. Database checksums can also change when include_system/include_toast is used (shared catalogs and unsupported indexes are now handled correctly) or when a database contains non-B-tree indexes. - TAP tests are opt-in again (enable_tap_tests=yes), matching the README, and run_tests.sh no longer stops a server it did not start and no longer requires sudo unconditionally. 1.1.0 2026-10-17 - Speed up the logical checksum paths considerably. The per-cell pg_type system cache lookup is gone, the primary key is looked up once per relation instead of once per tuple, and short-header varlenas are hashed in place instead of being detoasted into a fresh copy for every cell. Logical table checksums are up to ~180x faster on large tables; physical and utility functions are unchanged. - Fix a relcache reference leak in the parallel database scan: parallel workers cannot read the session's temporary tables, and the error that this raised left the relation open. - Database checksums no longer include temporary relations. They are session-local, and including them made the parallel result depend on how the work happened to be distributed. Checksums of persistent objects are unchanged, and the parallel and serial results now agree. - Initialise the relation and buffer variables used by the exception handler of pg_tuple_physical_checksum. - ALTER EXTENSION pg_checksums UPDATE TO '1.1.0' is a no-op: the SQL objects are unchanged. 1.0.0 2026-10-12 - First public release on PGXN. - Checksum functions at six levels: page, cell, tuple, table, index and database, in physical and logical variants (cell level is logical only). - FNV-1a 32-bit implementation, with order-independent aggregation so that table, index and database checksums do not depend on scan order. - Parallel scans for table, index and database checksums, controlled by the pg_checksums.workers GUC. - Partitioned tables are aggregated over all leaf partitions. - Supports PostgreSQL 12, 13, 14, 15, 16, 17 and 18.