pg_sift 4.0.2: `pg_squeeze` of a table with a `sift_col` index no longer leaves rows out of the index (every release since 3.6.0 could), and the log of a table that was attached as a partition or inheritance child is ended when the table is attached. A patch release of 4.0.1: the library changes, nothing on disk and no SQL. ## Highlights - **Fixed: `pg_squeeze` lost rows of a `sift_col` index.** When writers inserted or updated rows during the squeeze, the index of the copy could miss a whole batch of them (in the tests about one run in three; `sift.col_verify` said `Heap tuple (..) is not in the index`, and the index answered with fewer rows until `REINDEX`), with `track_changes` on or off, in every release from 3.6.0 to 4.0.1. The squeeze applies the changes it decodes while its decoding session reads the catalogs as of the past, where the copy it buffered rows for does not exist, and the buffer of those rows was taken for the one of a dropped index and cleared. The buffer is now flushed under its own OID there. With a real pg_squeeze, in 12 of 12 runs with `track_changes` on and in 12 of 12 with it off, no row was missing after the fix; before it, 3 to 7 of 12 runs lost rows. - **Fixed: `pg_squeeze` failed on a table with a `sift_col` index with `track_changes` (the default).** The squeeze stopped with `track_changes: column "id" can be NULL`, because its copy (`tmp_`) has no NOT NULL and no key while the index is built. The index is made now. The log of the table is not complete after a squeeze, because no trigger hears the changes the squeeze applies: reads go to the heap until `REINDEX INDEX `. - **Fixed: the log of a table attached as a partition or made an inheritance child is ended at the attach.** In 4.0.1 a read leaves the log while the table is a child, but after `DETACH PARTITION` or `NO INHERIT` the log was stale until `REINDEX INDEX`. The event trigger of `ALTER TABLE` now ends the completeness of the log when the table becomes a child, so the statements of the parent that the table did not hear no longer make a read after the detach leave rows out. A table that was attached before this release still needs `REINDEX INDEX ` at the detach. ## Who is affected and what to do - **`pg_squeeze`:** a database where pg_squeeze rewrote a table with a `sift_col` index, on any release from 3.6.0 to 4.0.1. Check each such index with `SELECT sift.col_verify('', true)`: it returns no row for a good index. Repair with `REINDEX INDEX `. With 4.0.2 installed the rows are no longer lost, and a `REINDEX INDEX ` after a squeeze brings the log back. Not affected: a `sift` (BM25) index, a `sift_table` (pg_squeeze refuses it), pg_repack, and logical replication apply. - **Partitions and inheritance children:** run `REINDEX INDEX ` at the detach for a table that was attached before 4.0.2. - **What fails loudly, not wrong, and the rest of 4.0.1:** unchanged (see the notes of 4.0.1): `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. - **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.1. ## Tests `col_decode_pending` (the rows buffered while a transaction decodes reach an index made in it), `col_track_pending` and `col_track_attach` (the log after `ATTACH PARTITION`, `INHERIT`, `DETACH PARTITION` and `NO INHERIT`). ## 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.1 and 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.2.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.2-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.0.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.0.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.0.2/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.0.2 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.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.0.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.0.2/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.2/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.0.2 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.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.0.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.0.2/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.2/mariadb/CHANGELOG.md).