pg_sift 4.0.1: fixes for five problems of `track_changes`, the log that lets a read of a `sift_col` index skip the heap. Two silent wrong answers of grouped reads are closed (a table attached as a partition or inheritance child after its index was built; a primary key dropped and added again), a dump of a table with a primary key and a default `sift_col` index restores and upgrades, and `REINDEX CONCURRENTLY` and `pg_repack` no longer break the table's writes or the log. A patch release of 4.0.0: the library changes, nothing on disk and no SQL. ## Known issue - **Wrong results: `pg_squeeze` of a table with a `sift_col` index can leave rows out of the index (since 3.6.0; fixed in 4.0.2).** While pg_squeeze runs, the index of its copy can miss the rows that were inserted or updated meanwhile: in tests (a real pg_squeeze, 200,000 rows, one writer) 3 to 7 of 12 runs of every release from 3.6.0 to 4.0.1 lost a whole buffered batch (28 to 39 of about 130 inserted rows), with `track_changes` on or off, and 3.5.3 lost none in 12 runs. The aggregates and reads that go through the index then undercount. Affected: only a table that pg_squeeze rewrites. Not affected: a `sift` (BM25) index, a `sift_table` (pg_squeeze refuses it), pg_repack, and logical replication apply. Check: `SELECT sift.col_verify('', true)` returns no row for a good index (it flagged all 12 bad runs and none of the 12 good ones). Repair: `REINDEX INDEX `. On 4.0.1, do not run pg_squeeze on a table with a `sift_col` index, or run `REINDEX INDEX ` after every run and check with `col_verify`. With `track_changes` on, pg_squeeze also failed every squeeze in the tests with `track_changes: column "id" can be NULL`, and after a squeeze that completes the log is incomplete until `REINDEX INDEX `. ## Highlights - **Fixed: wrong grouped reads of a table attached as a partition or made an inheritance child.** For a table whose `sift_col` index tracks its changes and that is attached afterwards (`ATTACH PARTITION`, `INHERIT`), a statement on the parent fires none of the table's statement triggers, so the deletes and key changes it made were not in the log, yet a read took the log for complete and a grouped read counted rows that were gone (in a test, 10 of 10 groups differed from the heap, whose answer was right). A read now leaves the log, and the heap answers, while the table is a partition or a child, as it already did for a subscription target. INSERT and statements on the child itself were not affected in 4.0.0. - **Fixed: wrong grouped reads after a primary key was dropped and added again.** With the key dropped, the DELETEs and UPDATEs that ran were not in the log, and `ADD PRIMARY KEY` left the log marked complete: a grouped read counted the deleted rows (10 of 10 groups). A statement that finds no unique index to look its rows up by now ends the completeness of the log, so a read checks the heap until `REINDEX INDEX ` makes the log whole again. - **Fixed: a dump of a table with a primary key and a default `sift_col` index could not be restored reliably, and `pg_upgrade` failed.** The default `track_changes = 'auto'` is stored as the name of the key (`track_changes = id`), and `CREATE INDEX` refused it with `column "id" has no unique index of its own` when the index was made before the key, which `pg_restore -j` does for most tables and the restore of `pg_upgrade` did for all of them (`pg_restore -j8` failed 19 to 21 of 24 `CREATE INDEX`). `CREATE INDEX` and `REINDEX` now accept an explicit key column that has no unique index yet, with a NOTICE; `ALTER INDEX ... SET` keeps the error, and a column that does not exist, is not in the index or can be NULL is still an error at `CREATE INDEX`. Reads take the log only when a valid unique index on that column exists, and check the heap until then. A dump made by 4.0.0 needs nothing: the fix is in the library that restores it. - **Fixed: `UPDATE` and `DELETE` failed after a concurrent reindex (`could not open relation with OID n`).** `REINDEX INDEX CONCURRENTLY` or `REINDEX TABLE CONCURRENTLY` of a `sift_col` index with `track_changes` left the two statement triggers with the oid of the dropped index, so every UPDATE and DELETE of the table failed afterwards. A trigger now serves the index it depends on, and the swap moves that dependency to the new index. INSERT, reads, aggregates and the data were not affected and nothing was damaged. - **Fixed: after `pg_repack` of a table, the log of its index was off until the next `REINDEX`.** `sift.col_tracking` said the triggers and the heap-free reads were off, so reads of rows changed and not yet vacuumed went to the heap. The index built on the copy now takes the identities of the table's own triggers, which `pg_repack` leaves in place, so the log is complete at the swap. ## Who is affected and what to do - **Partitions and inheritance children:** a `sift_col` index with `track_changes` on a table attached (`ATTACH PARTITION`, `INHERIT`) after the index was created, on 4.0.0 (an index created after the attach is untracked and was not affected). With 4.0.1 the answers are right while the table is a child. **Known limit (fixed in 4.0.2 for tables attached after that update):** after `DETACH PARTITION` or `NO INHERIT` of a table that was attached and written through its parent, the log is stale until `REINDEX INDEX `: run it after the detach (it is also the cure for a table written through its parent on 4.0.0, and for any table in doubt). - **A primary key that was dropped and added again** on a table with a tracked index, on 4.0.0: run `REINDEX INDEX ` once after installing 4.0.1 for each such table (or for every table whose key was ever dropped); nothing else is needed after that. - **Dump, `pg_restore` and `pg_upgrade`:** a database on 4.0.0 with a table that has a primary key and a default `sift_col` index. With 4.0.1 installed a dump made by 4.0.0 restores as it is (an index made before its key gets a NOTICE and reads check the heap until the key exists). On 4.0.0, `ALTER INDEX SET (track_changes = 'off')` before the dump or `pg_upgrade` and `ALTER INDEX SET (track_changes = 'id')` after. - **Concurrent reindex:** repair a table that already failed (on 4.0.0, or after installing 4.0.1 on a table that ran the command before) with `REINDEX INDEX ` (the plain form), or `ALTER INDEX SET (track_changes = 'off')` and then `ALTER INDEX SET (track_changes = 'auto')`. After a concurrent build the log of the new index is not complete, as for `CREATE INDEX CONCURRENTLY`: a read takes the log only where it is complete, and `sift.col_tracking(index)` says whether it is. - **What fails loudly, not wrong:** `DROP INDEX CONCURRENTLY` of an index with `track_changes` and `pg_repack --only-indexes` fail with `must be first action in transaction`; run `ALTER INDEX SET (track_changes = 'off')` first. After `REINDEX CONCURRENTLY`, `pg_restore --disable-triggers`, or `ENABLE TRIGGER ALL` after `DISABLE TRIGGER ALL`, the heap-free reads of the table stay off (reads go to the heap: slower, not wrong) until a plain `REINDEX INDEX `. - **Update:** install the files and restart the server (a standby too), or reconnect every pool: only the library changes, and a backend that already loaded the old library keeps it until its connection ends. No format change, no `ALTER EXTENSION UPDATE` step. - **Nothing else changes:** plans, speeds and answers outside the cases above are those of 4.0.0. ## Tests `col_track_attach` (partition and inheritance: ten groups differed before the fix), `col_track_restore` (restore with the index before the key: `pg_restore -j8` in five of five runs with no error, `pg_dump | psql`, a dump with the indexes before the keys), `col_pg_repack`, `pg_upgrade` (a tracked index, PostgreSQL 18 to 18: 25 checks) and the new block of `col_track_deletes` (`REINDEX INDEX CONCURRENTLY`, then an UPDATE and a DELETE). `col_heapfree_replication` passes on a physical standby, after a promote and on a logical subscriber. The full matrix ran on both trees, 78 cells each, with no wrong answer. ## Compatibility - PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with `make USE_PGXS=1`). A drop-in replacement of 4.0.0: extension SQL 4.0 (`ALTER EXTENSION pg_sift UPDATE` has nothing to do and there is no `pg_sift--4.0--4.0.1.sql`); 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.1-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.0.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.0.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.0.1/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.0.1 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.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.0.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.0.1/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.1/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.0.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.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.0.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.0.1/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.1/mariadb/CHANGELOG.md).