pg_sift 4.1.2: a patch release of 4.1.1 that repairs on its own the wrong counts that 4.0.0 to 4.1.1 left in a sift_col index with track_changes (the files of 4.1.1 stopped new ones, but the update did not take the old ones away), fixes two more wrong answers (a transaction that took its snapshot before running REINDEX, CLUSTER or ALTER INDEX .. SET (track_changes = ..) on the same index, every release since 4.0.0; the rows of an aborted or still-open INSERT ... ON CONFLICT, 4.1.0 and 4.1.1), a backend crash on phrase queries of a BM25 index in a correlated subquery or lateral join (every release since 4.0.0), and an error after an aborted UPDATE and a VACUUM that truncates the heap (4.1.0 and 4.1.1; never a wrong answer). The library only: install the files and restart the server; the extension SQL stays at 4.1.

Highlights

  • Fixed: results that were already wrong are repaired without a manual step, in a cluster upgraded by replacing the library (not by pg_upgrade: see below). An index written by 4.0.0 to 4.1.1 whose log kept the old versions of rows (a statement whose command id moved ended row versions without a tombstone) went on counting them after the update to 4.1.1, after ALTER EXTENSION UPDATE, after sift.col_merge and after a VACUUM that skipped the indexes (PostgreSQL skips them when few pages hold a dead row); only REINDEX, ALTER INDEX .. SET (track_changes = 'off') and back, or a VACUUM that vacuumed the indexes took them away. The log of such an index is not taken for complete any more: its reads check the heap, so their answers are the heap’s at once, and the log is re-baselined (a tombstone for every heap address no version is live for) by the first of the next VACUUM of the table (also one that skips the indexes), the first read that checks the heap for it (the maintenance worker does it, sift.col_auto_merge on, so a table nobody writes or vacuums is repaired too), and the maintenance worker that a write of the table asks for. The cost is about 0.1 s per 600,000 rows and up to 14 MB of WAL on a churned table that was not vacuumed, with only a ShareUpdateExclusiveLock (writers go on); sift.col_tracking shows complete true and heap_free false until then. A standby cannot write the log: it checks the heap until the primary’s re-baseline is replayed. After pg_upgrade (or a dump restore with triggers disabled, or REINDEX CONCURRENTLY) the answers are right but the reads check the heap until REINDEX INDEX <index> or ALTER INDEX <index> SET (track_changes = '<value>') is run once for every tracked sift_col index, one by one: the restore recreates the triggers with a new xmin, so the witnesses of the log no longer match and no worker, VACUUM or write marks the log (true since 4.0.0, damaged index or not). An automatic repair for that case is planned for 4.2. Held by col_track_rebaseline and, with the real library of 4.1.0 writing the wrong counts, old_lib_refusal.
  • Fixed: a transaction that took its snapshot before it ran REINDEX, CLUSTER or ALTER INDEX .. SET (track_changes = ..) on the same index missed rows that another session ended afterwards (every release since 4.0.0). The command writes a baseline of tombstones, and the transaction that wrote it took them for deletes its older snapshot sees through, so a read after another session committed an UPDATE or DELETE left out the old versions it should have counted. Reproduced on 4.1.0 and 4.1.1 with an explicit REPEATABLE READ snapshot; any other session was right. Its reads now check the heap. Held by col_track_own_baseline (isolation test).
  • Fixed: the rows of an aborted or still-open INSERT ... ON CONFLICT were counted (4.1.0 and 4.1.1). A read of a sift_col index with track_changes that skips the heap counted them until the write buffer was promoted. The set of buffer records is stamped “every inserter committed before this xid” so that a read takes its rows for visible; the record of an upsert has no inserter, and the first of them was taken for the record before it, which left the set stamped when a later statement committed. The damage did not outlive the buffer: the promotion checks the rows against the heap. Held by col_heapfree_xmin.
  • Fixed: a backend crash (or memory corruption) when a phrase query of a sift (BM25) index runs in a correlated subquery or lateral join (every release since 4.0.0; confirmed on 4.0.3, 4.1.0 and 4.1.1). The rescan deleted the context that the phrase recheck of the next row worked in, and the context another part of the backend made next (the stem memo of a text search configuration, in a session that had not stemmed before) took its place, so the recheck reset it: a SIGSEGV and a restart, or writes into memory that was not the recheck’s, depending on which context comes next. Updating is recommended. Held by bm25_rescan_context and bm25_cold_stems.
  • Fixed: could not read blocks N..N in file ...: read only 0 of 8192 bytes after an aborted UPDATE and a VACUUM that truncates the heap, in the session that wrote (4.1.0 and 4.1.1). The lookup of the write buffer returned the addresses it had cached for the rows of the aborted transaction, past the new end of the heap. A fresh session never saw it; it is an error, never a wrong answer. Held by col_wbuf_lookup.

Who is affected and what to do

  • Everyone who ran 4.0.0 to 4.1.1 with a sift_col index that tracks changes (track_changes is automatic for a table with a primary key): update; after the restart of a cluster whose library was replaced (the usual package update) the indexes re-check themselves as above, and no REINDEX or ALTER INDEX is needed. Until the update: ALTER INDEX <index> SET (track_changes = 'off') on the sift_col indexes of those tables (about 35 ms per 600,000 rows, no rewrite, no REINDEX), then ALTER INDEX <index> SET (track_changes = 'id') after the update (about 0.1 s per 600,000 rows). The wrong counts of 4.1.1 had three routes (the first use of a shared table inside a write statement of a new session, a function or trigger that runs SQL inside an UPDATE, a sift (BM25) index maintained by the same bulk update); the notes of 4.1.1 and docs/UPGRADING.md have them.
  • Everyone with a sift_col index with track_changes whose transactions run REINDEX, CLUSTER or ALTER INDEX .. SET (track_changes = ..) after taking a snapshot (a REPEATABLE READ or SERIALIZABLE transaction, a script that opens a transaction first), and everyone with a BM25 index that runs phrase queries in a correlated subquery or lateral join: update; the first is a wrong answer since 4.0.0, the second a crash since 4.0.0.
  • A cluster moved with pg_upgrade, restored from a dump with triggers disabled, or an index made with REINDEX CONCURRENTLY: the answers are right, but the reads of every tracked sift_col index check the heap (sift.col_tracking shows heap_free false) until you run REINDEX INDEX <index> or ALTER INDEX <index> SET (track_changes = '<value>') once for each of them, one by one; the list of the indexes still to do is in docs/UPGRADING.md. The automatic repair of this release does not cover this case; it is planned for 4.2.
  • Everyone on 4.1.0 or 4.1.1 who uses INSERT ... ON CONFLICT or sessions that abort writes and then see a VACUUM: update; the first is a wrong count until the buffer is promoted, the second an error that a new session does not show.
  • 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. There is nothing to update in the database: the extension SQL stays at 4.1, so no ALTER EXTENSION pg_sift UPDATE and no REINDEX; no column format changes. An older library reads a log marked by this release as incomplete and checks the heap, which is right.
  • Going back to 4.1.1 or 4.1.0: nothing to do first; the marked log reads as incomplete there, and the heap is checked.
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.1.1.

Tests

col_track_rebaseline (the log of an index written before this release is re-checked and re-baselined, with the real library of 4.1.0 writing the wrong counts, in regress and TAP), col_heapfree_xmin (the rows of an aborted upsert are not counted), col_track_own_baseline (the snapshot of the transaction that ran the command), bm25_rescan_context and bm25_cold_stems (the phrase recheck in a rescan, in a new session), col_wbuf_lookup (the lookup leaves out the addresses past the end of the heap), old_lib_refusal (the library of 4.1.0 reads the marked log), and the whole suite of 4.1.1 on PostgreSQL 17, 18 and 19.

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.1.1, 4.1.0, 4.0.3, 4.0.2, 4.0.1 and 4.0.0 with the install and a restart: extension SQL 4.1 (no change of an object since 4.1.0); column format minors 3 to 42 are read and written as in 4.0.0, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.0.

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.1.2-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.1.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.1.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.1.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.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.1.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.1.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.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.1.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.