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 ` or `ALTER INDEX SET (track_changes = '')` 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 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 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](https://github.com/brauliobo/pg_sift/releases/tag/v4.1.1) and [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v4.1.2/docs/UPGRADING.md#the-wrong-counts-that-400-to-410-left-in-an-index-and-412) 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 ` or `ALTER INDEX SET (track_changes = '')` once for each of them, one by one; the list of the indexes still to do is in [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v4.1.2/docs/UPGRADING.md#the-wrong-counts-that-400-to-410-left-in-an-index-and-412). 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](https://github.com/brauliobo/pg_sift/blob/v4.1.2/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](https://github.com/brauliobo/pg_sift/blob/v4.1.2/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.2/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:` 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//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](https://github.com/brauliobo/pg_sift/blob/v4.1.2/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.2/mariadb/CHANGELOG.md).