pg_sift 4.1.4: a patch release of 4.1.3 that fixes two wrong answers of the aggregate scan, both present in earlier releases: `NOT (doc @> '...')` on a `NOT NULL` jsonb column returned the rows that match instead of the rows that do not, and a `sift_col` index scan that PostgreSQL runs again for each row of an outer query returned some rows twice. The library only: install the files and restart the server; no `ALTER EXTENSION`, no `REINDEX`, no format change. ## Highlights - **Fixed: `NOT (doc @> '{"b": "x"}')` returned the rows that match instead of the rows that do not (every release since 3.6.0, reproduced on 4.1.3).** On a `jsonb` column with a `NOT NULL` constraint whose `sift_col` index runs a text member path of the document as an equality of a text leaf (the index declares `doc.b`), the aggregate scan answered `NOT` of the containment, or `NOT` of the path test, by flipping a test it did not know: a statement with that `NOT` in `WHERE` or in `FILTER (WHERE NOT ..)` counted the matching documents (in the test, 2,668 of 6,000 instead of 3,332). A nullable column, and every other test (an `IN` list, an array element test, a NULL test), were right. Only an `IN` list is flipped now, the element test and the NULL test keep their own flip, and any other `NOT` keeps the normal plan. Held by `agg_jsonb_not_exist` (13 tests, each as `NOT`, `NOT` with another condition and `FILTER`, on a `NOT NULL` and on a nullable column). - **Fixed: a `sift_col` index scan run again for each row of an outer query returned some rows twice (present in every release since 4.0.0, reproduced on 4.0.0, 4.0.4, 4.1.0 and 4.1.3).** When PostgreSQL runs an index scan again for each row of an outer query (the inner side of a nested loop, a `LATERAL` join, a correlated subquery) and the index holds rows in its write buffer (after an `INSERT` or `UPDATE`, before the merge), a scan with an equality condition on a plain column could return rows of the buffer twice: the lookup keeps more than a quarter of the buffer rows in one scan and few rows in a later one, and the rows the first scan marked were not cleared for the next (in the test, 242 rows returned where 240 are right). The fault came in with the change that keeps the source of an index scan that starts over; 3.x reopened the source on every rescan and was right. Plain rows from the scan are duplicated, so counts and sums computed over them are too large. Each row is returned once now. Held by `col_wbuf_lookup_rescan`. ## Who is affected and what to do - **A `NOT NULL` jsonb column with a `sift_col` index that declares a text member path (`paths = 'doc.b text'`, or the path the index chose), and a statement that negates a containment or a path test of it** (`WHERE NOT (doc @> '{"b": "x"}')`, `FILTER (WHERE NOT ..)`): update. Until then, run such statements with `SET sift.enable_agg = off` (it gives up the aggregate scan for them), or use an index that does not declare the path, which was right. - **A `sift_col` index with rows in its write buffer that is read through an index scan on the inner side of a rescanning join** (a nested loop, `LATERAL`, a correlated subquery): update. Until you update: `SET sift.enable_col_scan = off` for the statement (verified on 4.0.4 and 4.1.3); `sift.enable_agg = off` and a `VACUUM` do not help. - **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.1, so no `ALTER EXTENSION pg_sift UPDATE` and no `REINDEX`; no format change, going back needs nothing first. - **Still as before:** after `pg_upgrade` (or a dump restore with triggers disabled) the answers are right but the reads of a tracked `sift_col` index check the heap until `REINDEX INDEX ` or `ALTER INDEX SET (track_changes = '')` is run once for each tracked index (an automatic repair is planned for 4.2). - **Nothing else changes:** plans, speeds and answers outside the cases above are those of 4.1.3. ## Tests `agg_jsonb_not_exist` (78 statements: 13 tests x 3 forms x a `NOT NULL` and a nullable column; 9 returned other rows before the change) and `col_wbuf_lookup_rescan`, each failing before the fix; the regression suite on PostgreSQL 18, the upgrade checks (`pg_upgrade`, `upgrade_check`, the library of 4.1.3 reading what this one writes) and the TAP tests of the maintenance workers. ## 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.3, 4.1.2, 4.1.1, 4.1.0, 4.0.x with the install and a restart: extension SQL 4.1 (no change), no column format change, 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.4-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.1.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.1.4-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.4/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.1.4 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.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.1.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.1.4/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.4/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.1.4 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.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.1.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.1.4/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.4/mariadb/CHANGELOG.md).