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 ` or `ALTER INDEX SET (track_changes = '')` 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 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 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 ` or `ALTER INDEX SET (track_changes = '')` 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](https://github.com/brauliobo/pg_sift/blob/v4.0.4/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](https://github.com/brauliobo/pg_sift/blob/v4.0.4/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](https://github.com/brauliobo/pg_sift/blob/v4.0.4/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.4/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:` 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//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.0.4/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.4/mariadb/CHANGELOG.md).