pg_sift 4.2.2: a patch release of 4.2.1 that fixes a wrong answer of sift_table that is present since 4.0.0: a VACUUM of a table that waited for the table’s maintenance lock could make the old versions of updated rows, or rows that were deleted, visible again. It adds sift.table_find_stale_versions() to find the copies of updated rows; extension SQL 4.2.2 (one new function, no format changes): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database. Updating fixes the cause; it does not repair rows that already came back (see “If a sift_table may be affected”).

Highlights

  • Fixed: a VACUUM of a sift_table could make old versions of updated rows, or deleted rows, visible again (4.0.0 to 4.2.1; sift_table only). A VACUUM that waits for the table’s maintenance lock went on with the list of segments it had read before the wait. When the holder of the lock promoted or merged write buffers in the meantime, the VACUUM did not mark the dead rows of the new column groups as removed, and then dropped the records of their deletion: every snapshot saw the old version of an updated row next to the new one, and the rows that a DELETE had removed. The VACUUM can be a manual one, autovacuum (also the one that prevents transaction ID wraparound), a pass of the background worker (sift.table_auto_defrag) or a second VACUUM of the same table; the holder can be a writer that closes a write buffer (sift.table_inline_promote), the worker, sift.table_maintain() or another VACUUM. Every maintenance entry now takes the lock and reads the list of segments in one step. sift_col and sift (BM25) indexes and the heap are not affected.
  • Added: sift.table_find_stale_versions(tbl regclass, sample integer DEFAULT 10) returns one row: duplicate_keys, the number of primary key values that are on more than one row, and sample_keys, up to sample of them (text[], each key written as a row: (7), (3,k1)). It reads the table once (a sequential scan and a hash GROUP BY on the key, work_mem applies), takes only ACCESS SHARE, writes nothing and runs with the rights of the caller. It fails for a table without a primary key and returns 0 for a heap table; a partitioned table is read through all its partitions. It finds updated rows that came back; it cannot see deleted rows that came back.
  • Fixed: assert builds stop where a merge of a sift index finds none of the segments it replaces in the directory (the maintenance lock and the claims rule it out). A build without assertions is unchanged.

Who is affected and what to do

  • Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.2.1 to 4.2.2 adds the function; a database at 4.2 goes through 4.2.1 by itself; nothing to REINDEX, no format change).
  • Everyone who has a sift_table that is updated or deleted from, and VACUUMed or maintained while it is written: update, then run the detector on each such table (below). A table that nothing ever updated or deleted from has no old versions to bring back.
  • Reduce the exposure until the update is installed: sift.table_auto_defrag = off removes only the background worker’s VACUUMs. A manual VACUUM, autovacuum (the anti-wraparound one cannot be disabled) and two VACUUMs of one table at once can still trigger it, and no maintenance setting removes the cause (it was reproduced with sift.table_auto_defrag = off and sift.async_maintenance = off). autovacuum_enabled = false on the table with no manual VACUUM avoids all but the anti-wraparound one, at the cost of dead space and stale statistics. VACUUM only while the table is not written, and never two at once.
  • The error sift index "<name>" is corrupted ... expected page type/generation X, found Y was seen from VACUUM and sift.table_maintain() in the same race on 4.0.0 to 4.2.1: the call read a page of a segment that the lock holder had already released. The error aborts that VACUUM or sift.table_maintain() call and leaves the table and its indexes consistent (checked on a set of cases); run the call again after updating. It does not by itself mean that rows reappeared: sift.table_find_stale_versions() and the comparison with your source of truth are the check for that. The evidence is small and says what it says: three tables that raised the error in a test were examined seconds after it, and had the rows of a heap twin, no key on two rows, a clean check of the structure of its segments, and the same state after VACUUM, VACUUM (FREEZE) and sift.table_maintain(), with the B-tree indexes clean under amcheck (bt_index_check and bt_index_parents_check with heapallindexed); no REINDEX or rebuild was needed. A wrong answer is a different outcome of the same race, not a consequence of the error.

If a sift_table may be affected

The update does not repair the rows that already came back.

  1. Detect updated rows that came back. SELECT * FROM sift.table_find_stale_versions('t') (the table name is an example). A count above 0 means primary key values on more than one row. On a table damaged in a test (100,000 rows in its heap twin, 100,795 in the table) it found 795 keys, and 0 on the heap twin. The function runs with sift.enable_agg, enable_indexscan, enable_bitmapscan and enable_indexonlyscan off for its own duration: with them on, the aggregate engine answers count(*) and GROUP BY as if the key were unique, and the primary key index no longer holds the old versions, so a plain GROUP BY pk HAVING count(*) > 1 finds nothing. A query of your own needs the same four as SET LOCAL.
  2. Repair updated rows. The copy that was visible before the VACUUM is the one with the highest ctid (tuple identifiers are never reused and promotion and merge keep them), so one transaction rebuilds the table with that copy per key:
   BEGIN;
   LOCK TABLE t IN ACCESS EXCLUSIVE MODE;
   SET LOCAL sift.enable_agg = off;
   SET LOCAL enable_indexscan = off;
   SET LOCAL enable_bitmapscan = off;
   SET LOCAL enable_indexonlyscan = off;
   CREATE TABLE t_new (LIKE t INCLUDING ALL) USING sift_table;
   INSERT INTO t_new SELECT DISTINCT ON (pk) * FROM t ORDER BY pk, ctid DESC;
   ALTER TABLE t RENAME TO t_old;
   ALTER TABLE t_new RENAME TO t;
   COMMIT;

It was checked against a heap twin on three data sets with several updates per row: no row differed. It holds only if the table was not rewritten since the copies appeared (VACUUM FULL and CLUSTER assign new ctids), and xmin cannot order the versions. If the table has a column that orders them (updated_at, a version number), order by it instead; otherwise choose the rows from SELECT *, ctid ... ORDER BY pk, ctid with the four settings. Foreign keys, views and grants move by hand, and the indexes and constraints of the copy are named after it. 3. Deleted rows that came back have no duplicate. SQL on the table alone cannot find them and the copy above does not remove them. Delete the keys you know were deleted again, or restore from a backup taken before the first VACUUM that could have been affected. 4. Going back: install the 4.2.1 files and restart; ALTER EXTENSION pg_sift UPDATE TO '4.2.1' runs the empty script back to 4.2 and then the 4.2.1 script. The function stays in the database and works with the 4.2.1 library; the cause of the wrong answer is back with that library.

  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.2.1.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.2.1 with the install and a restart; extension SQL 4.2.2 (pg_sift--4.2.1--4.2.2.sql, and an empty pg_sift--4.2.2--4.2.sql back); no column format minor and no BM25 metapage minor changes.

Packages

x86-64 and arm64 packages for PostgreSQL 17, 18 and 19 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1.

  • Debian 12 and 13, Ubuntu 22.04 and 24.04 (PGDG postgresql-N, N = 17, 18 or 19): sudo apt install ./postgresql-N-pg-sift_4.2.2-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.2.2-1.el9.x86_64.rpm (.aarch64.rpm on arm64)
  • Amazon Linux 2023 (the distribution’s postgresqlN-server, N = 17 or 18): sudo dnf install ./pg_sift_N-4.2.2-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.2.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.2.2 release: no change to the SQL interface or the shared format.

  • No change: the changes of this release are in the PostgreSQL column index, which is not in this extension. No speed-up or slow-down is claimed.
  • Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
  • Storage format 2: tables of 3.4.0 to 3.7.0 open as they are, nothing to rebuild.

Download sift-sqlite-4.2.2-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it and .load ./sift from the sqlite3 shell (sha256sum --ignore-missing -c SHA256SUMS). Documentation: sqlite/README.md. Every change: sqlite/CHANGELOG.md.

MariaDB extension

pg_sift for MariaDB 4.2.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.2.2 release: no change to the SQL files.

  • No change: the changes of this release are in the PostgreSQL column index, which is not in this extension. No speed-up or slow-down is claimed.
  • One library per MariaDB series, because it calls the server’s own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock mariadb:<series> image against that server’s headers and loaded into a server of the same image before release. BUILD-INFO in the tarball lists the server version and the glibc each library needs.
  • Index format 2: an index of 3.4.0 to 3.7.0 opens as it is, nothing to rebuild.

Download sift-mariadb-4.2.2-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it, copy lib/<series>/libsift_udf.so of your server’s series (SELECT VERSION()) into its plugin_dir, then SOURCE sql/functions.sql once per server and sql/sift.sql once per database (sha256sum --ignore-missing -c SHA256SUMS). Documentation: mariadb/README.md. Every change: mariadb/CHANGELOG.md.