pg_sift 4.3.1: a patch release of 4.3.0 that fixes a wrong answer of `sift_col` indexes that track their changes, present since 4.0.0: a read made while a `DELETE`, `UPDATE` of the key column or `MERGE` was running (from an `AFTER ROW` trigger, an `AFTER STATEMENT` trigger that fires before the index's own, or a function the statement calls) counted the rows that statement had just ended. It also fixes every `DROP` failing on a server with a `FOR ALL TABLES` publication (4.2.0 to 4.3.0), a warning of the parallel `VACUUM` of a `sift` (BM25) index and a rare second load of a shared lookup table; extension SQL 4.3.1 (one statement, no new object, no format change): install the files, restart the server (a standby too), then `ALTER EXTENSION pg_sift UPDATE` in each database. ## Highlights - **Fixed: a read inside a statement that deletes rows counted the rows it had deleted (4.0.0 to 4.3.0; `sift_col` with `track_changes`, which is the default).** An index with `track_changes` hears the versions a statement ends (the rows of a `DELETE`, the old versions of an `UPDATE` of the key column or of a `MERGE` or `INSERT .. ON CONFLICT DO UPDATE`) from its own statement triggers, which fire after the statement has run. A read of the same transaction that runs before them took the log for the whole truth and counted those versions: an `AFTER ROW` trigger, an `AFTER STATEMENT` trigger of the table whose name sorts before the index's own (`sift_track_*`), a function the statement calls (in a `WHERE` clause or a `MERGE` condition) and a `BEFORE` trigger of the same statement. The wrong answers were counts, sums and other aggregates, filtered counts and top-N reads that the index answers without the heap (a count of 50,000 rows against 47,500 in a statement trigger that had deleted 2,500). The next statement, an `AFTER` trigger with a later name and the commit were right, and no stored byte is wrong: nothing to rebuild. The index now reads the heap for a table while a statement that ends row versions of it runs. Held by the regression test `col_track_own_statement_reads`. - **Fixed: every `DROP` failed on a server with a publication that publishes deletes and covers the extension's tables (4.2.0 to 4.3.0).** With `CREATE PUBLICATION .. FOR ALL TABLES`, any `DROP` of a relation (`DROP INDEX`, `DROP TABLE`, a plain table too) failed with `cannot delete from table "table_option_values" because it does not have a replica identity and publishes deletes`: the event trigger that removes the options of a dropped table deletes from `sift.table_option_values`, which had no replica identity. `ALTER EXTENSION pg_sift UPDATE` makes the unique key index of that table its replica identity (the one statement of the 4.3 to 4.3.1 script; a fresh install has it); the other tables of the extension have a primary key. A server without such a publication was never affected. Held by the TAP test `publication_all_tables` (the update from 4.2.0 and from 4.3.0, a fresh install, a subscriber). - **Fixed: a `VACUUM` that vacuums the b-trees of a table with parallel workers no longer warns for a `sift` (BM25) index (4.0.0 to 4.3.0).** The merge of the segments by workers needs a transaction id before a parallel mode of its own, which the leader of that `VACUUM` cannot take: the step logged `maintenance failed: cannot assign transaction IDs during a parallel operation`, counted a failure in `sift.workers` and left the segments to a retry after a delay. One process merges them in the same `VACUUM` now. The answers were right in both. Held by the TAP test `vacuum_parallel_merge`. - **Fixed: sessions that run one query at once could load the rows of a joined table more than once (the shared lookup).** A session that missed the shared rows and asked for the load's claim just after the loader had kept the rows and dropped its claim found no claim to wait for, and loaded the table again (the answers were right; the load was wasted work). It looks for the rows again once it holds the claim. Held by the TAP test `lookup_shared_claim` (PostgreSQL 18 and later built with injection points). ## 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.3 to 4.3.1 runs an empty script: no object changes; nothing to `REINDEX`, no format change). A database at 4.2.1 or 4.2.2 goes through 4.2 and 4.3 by itself. - **Affected by the failing `DROP`:** a database that has the extension at 4.2.0 to 4.3.0 on a server with a publication that publishes deletes and covers the extension's tables. Until the update, `ALTER TABLE sift.table_option_values REPLICA IDENTITY USING INDEX table_option_values_key` (as a superuser or the owner) does what the update does. - **Affected by the wrong answer:** a table with a `sift_col` index that tracks its changes (every index made with the default `track_changes`, since 4.0.0) whose `AFTER ROW` triggers, `AFTER STATEMENT` triggers or functions called from a `DELETE`, `UPDATE` or `MERGE` read the same table through an aggregate or a top-N. A table nothing reads from inside its own writing statements gives the same answers as before. Plans, speeds and answers outside this case are those of 4.3.0. - **If you cannot update yet:** `ALTER INDEX SET (track_changes = 'off')` on the indexes of the tables that are read that way makes those reads check the heap (the setting that keeps the index as it was before 4.0; the heap-free reads that `track_changes` gives are lost until `ALTER INDEX SET (track_changes = '')` after the update). A trigger whose name sorts after the index's `sift_track_*` triggers and fires `AFTER STATEMENT` is not affected, but an `AFTER ROW` trigger or a function the statement calls cannot be ordered after them. - **Going back:** install the 4.3.0 files and restart; `ALTER EXTENSION pg_sift UPDATE TO '4.3'` runs the empty script back to 4.3. The cause of the wrong answer is back with that library. ## 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.3.0 with the install and a restart; extension SQL 4.3.1 (`pg_sift--4.3--4.3.1.sql`, and an empty `pg_sift--4.3.1--4.3.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.3.1-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.3.1-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.3.1-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.3.1/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.3.1 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.1 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.3.1-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.3.1/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.1/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.3.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.1 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.3.1-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.3.1/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.1/mariadb/CHANGELOG.md).