pg_sift 4.1.3: a patch release of 4.1.2 that makes `REINDEX INDEX CONCURRENTLY` of a `sift_col` index with `track_changes` concurrent: it took an ACCESS EXCLUSIVE lock on the table at its end (every release since 4.0.0, errors and a stall, never a wrong answer). ## Highlights - **Fixed: `REINDEX INDEX CONCURRENTLY` of a tracked index stalled the writers of the table and deadlocked with them.** When the old index was dropped, the two triggers that the copy had made for itself went with it, and `DROP TRIGGER` takes ACCESS EXCLUSIVE on the table: the writers queued behind it, and a writer that held a lock on the old index while it waited for the new one deadlocked with the swap (`deadlock detected`; the transaction of the writer failed). In a test with 8 clients (an update, a delete and an insert-on-conflict in each transaction) and a loop of reindexes, 40 s: 4 reindexes done, 270 deadlocks, 1.4% of the transactions failed, 400 transactions a second, against 650 reindexes, no deadlock and 32,900 a second for a btree index. The copy now creates no triggers: the two of the old index move to it at the swap, and dropping the old index drops nothing of the table (ShareUpdateExclusiveLock only): 587 to 655 reindexes, no deadlock, no failed transaction, 25,500 to 26,200 transactions a second. A reindex cancelled in the middle leaves an invalid copy that owns no trigger. ## Who is affected and what to do - **Everyone who runs `REINDEX INDEX CONCURRENTLY` on a `sift_col` index with `track_changes`** (automatic for a table with a primary key) **on a table that is written meanwhile**: update. Until then: `ALTER INDEX i SET (track_changes = 'off')` before the reindex (0 deadlocks, 634 to 663 reindexes in the same test) and `ALTER INDEX i SET (track_changes = 'id')` after it (the key column of the table if it is not `id`; a pass over the heap, about 0.1 s for 600,000 rows). - Nothing changes for a plain `REINDEX`, `CREATE INDEX CONCURRENTLY` (tested under the same writers: no deadlock, no failed transaction) or a table that nobody writes. - No format change, no extension update: install the files and restart the server. ## Known issues - The log of the copy of a concurrent reindex is not complete (writers run beside the build), as before: the reads check the heap and answer as it does until `REINDEX INDEX` (without `CONCURRENTLY`). - `DROP INDEX CONCURRENTLY` of a tracked index still fails (`must be first action in transaction`: PostgreSQL drops the triggers of the index first), and so does `pg_repack --only-indexes`, which ends with it: `ALTER INDEX i SET (track_changes = 'off')` first. ## Tests `col_track_reindex_concurrently` (regress: the copy of the reindex has no triggers of its own, the old index's two move to it, a reindex cancelled in the middle leaves an invalid copy with no trigger) and `col_reindex_concurrent` (TAP: writers update, delete and upsert while a loop reindexes; no deadlock, no failed transaction; it fails on 4.1.2), and the whole suite of 4.1.2 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.2, 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.3-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.1.3-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.3-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.3/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.1.3 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.3 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.3-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.3/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.3/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.1.3 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.3 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.3-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.3/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.3/mariadb/CHANGELOG.md).