pg_sift 4.0.4: a patch release of 4.0.3 that fixes wrong answers of sift_col indexes with track_changes in the default configuration (rows counted twice after an UPDATE that called a function that runs SQL, after the first statement of a new session, or with a BM25 index maintained by the same statement; rows missed by a transaction that took its snapshot before a REINDEX or CLUSTER; the rows of an aborted upsert), repairs on its own the wrong counts that 4.0.0 to 4.0.3 left in an index, and fixes a memory write of BM25 phrase queries in correlated subqueries and an error after an aborted UPDATE and a VACUUM that truncates the heap. Every release since 4.0.0 has them. The library only: install the files and restart the server; the extension SQL stays at 4.0.3.

Highlights

Wrong answers in the default configuration (a table with a primary key has track_changes on), 4.0.0 to 4.0.3:

  • Fixed: rows counted twice after an UPDATE, MERGE, INSERT ... ON CONFLICT DO UPDATE or a cursor UPDATE ... WHERE CURRENT OF that called a function that runs SQL (a plpgsql function with PERFORM or EXECUTE, a trigger that updates the table). The function advances the command id inside the statement, and the entries of the versions the statement ended were looked for, and their tombstones written, with that command id instead of the statement’s. The same happened to a bulk UPDATE of a table that also has a sift (BM25) index, which seals its segments in a subtransaction of the statement. One command id now serves the whole statement. Held by col_stmt_cid and col_stmt_cid_bm25.
  • Fixed: rows counted twice when the first statement of a new session was a write. An UPDATE, INSERT ... ON CONFLICT DO UPDATE, DELETE or COPY that was the first statement of a backend that needed a table the backends share (the dictionaries of a text key, the statistics of the index, the choices of the planner) attached it in a subtransaction, whose commit advanced the command id under the statement; a row hit by two statements of the same transaction was counted twice after the commit. Reproduced on 4.0.0, 4.0.2 and 4.0.3 with a text key. Held by col_dsm_write_statements.
  • Fixed: a transaction that took its snapshot before it ran REINDEX or CLUSTER on the same index missed rows that another session ended afterwards. The command writes a baseline of tombstones, and the transaction that wrote it took them for deletes its older snapshot sees through, so its reads left out the old versions it should have counted (reproduced on 4.0.3 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 by a read that skips the heap, until the write buffer was promoted. The set of buffer records is stamped “every inserter committed before this xid”; the record of an upsert has no inserter, and the first of them was taken for the record before it. The damage did not outlive the buffer. Held by col_heapfree_xmin.
  • Fixed: results that were already wrong are repaired without a manual step. An index written by 4.0.0 to 4.0.3 whose log kept the old versions of rows went on counting them after the update, after a 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. It holds 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. This covers a cluster upgraded by replacing the library; after pg_upgrade (or a dump restore with triggers disabled) 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 each tracked sift_col index. Held by col_track_rebaseline.

Not a wrong answer:

  • Fixed: a BM25 phrase query in a correlated subquery or lateral join could write into memory that was not its own (4.0.0 to 4.0.3): the rescan deleted the context that the phrase recheck of the next row worked in, and the context the backend made next took its place, so the recheck reset it (a backend crash or memory corruption, depending on which context comes next). The recheck context now ends with the scan node. Held by bm25_rescan_context.
  • 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.0.0 to 4.0.3): the lookup of the write buffer returned addresses 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 on 4.0.0 to 4.0.3 with a sift_col index that tracks changes (track_changes is automatic for a table with a primary key): update; the wrong answers above are in the default configuration. Until you update: ALTER INDEX <index> SET (track_changes = 'off') on the sift_col indexes of those tables (no rewrite, no REINDEX; every row is then checked against the heap, which also answers right from an index that already counted old versions), and ALTER INDEX <index> SET (track_changes = 'id') after the update (the key column of the table if it is not id). The price of 'off': on a table that has churned and was not vacuumed, the statements after an update check more rows in the heap; nothing after a VACUUM; writes get cheaper.
  • 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.0.3, so no ALTER EXTENSION pg_sift UPDATE and no REINDEX. After the restart the indexes that earlier releases left wrong re-check themselves as above (not after pg_upgrade: run REINDEX INDEX <index> or ALTER INDEX <index> SET (track_changes = '<value>') once for each tracked index).
  • Format: the log header of a sift_col index with track_changes gets one more flag, additive. A 4.0.3 library reads it as an incomplete log and checks the heap, which is right: going back to 4.0.3 needs nothing first (docs/UPGRADING.md, “An older release”).
  • Everyone with a BM25 index that runs phrase queries in a correlated subquery or a lateral join: update.
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.0.3.

Tests

col_stmt_cid, col_stmt_cid_bm25, col_dsm_write_statements, col_track_own_baseline, col_heapfree_xmin, col_track_rebaseline, bm25_rescan_context and col_wbuf_lookup; the isolation suite (46 specs), the whole regression suite on PostgreSQL 18 and the upgrade checks (pg_upgrade, upgrade_check, the library of 4.0.3 reading what this one writes).

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.0.3, 4.0.2, 4.0.1 and 4.0.0 with the install and a restart: extension SQL 4.0.3 (no change since 4.0.3); 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.0.4-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.0.4-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.0.4-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: the AUR package follows the newest release; for 4.0.4 build from the tag (make USE_PGXS=1).

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.0.4 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.4 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.0.4-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.0.4 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.4 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.0.4-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.