pg_sift 4.3.2: a patch release of 4.3.1 that fixes a wrong answer of the aggregate scan: a correlated sub-select over a join with a derived table could return the count of an earlier outer row for the later ones. Extension SQL 4.3.2 (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 correlated sub-select over a join with a derived table returned the answer of an earlier outer row (4.0.5, 4.1.4, 4.2.2, 4.3.0 and 4.3.1; the aggregate scan, `sift.enable_agg`, which is on by default).** The shape is a sub-select in the select list or the `WHERE` of an outer query, whose `FROM` joins a table to a derived table that PostgreSQL does not pull up (a sub-select with `GROUP BY`, `DISTINCT`, `UNION` or `HAVING`), and whose condition compares a column of the outer row with a column of the derived table. PostgreSQL pushes that condition into its plan of the derived table as a parameter of the outer query. The aggregate scan did not see it there, read the derived table once and replayed that result for every outer row: the count (or sum) of the first outer row was returned for the later ones. The scan now leaves such a sub-select to PostgreSQL's plan, which asks the derived table once for each outer row. Plans of other queries, and their speed, are those of 4.3.1. Held by the regression test `agg_correlated_levels`. ## 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.1 to 4.3.2 runs an empty script: no object changes; nothing to `REINDEX`, no format change). A database at 4.3 or 4.3.1 goes through the earlier scripts by itself. - **Affected:** a query of the shape above that the aggregate scan plans (`EXPLAIN` shows a `SiftAgg` node). Nothing stored is wrong; only the answer of such a query was. - **If you cannot update yet:** `SET sift.enable_agg = off` for the session or the transaction of such a query (or `ALTER ROLE .. SET`) makes PostgreSQL's own plan answer it; the other queries of that session lose the aggregate scan until the setting is on again. - **Going back:** install the 4.3.0 or 4.3.1 files and restart after `ALTER EXTENSION pg_sift UPDATE TO '4.3'`, which runs the empty script back to 4.3 (4.3.1 itself is reached by `UPDATE TO '4.3.1'` from there). The cause of the wrong answer is back with those libraries. ## 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.1 with the install and a restart; extension SQL 4.3.2 (`pg_sift--4.3.1--4.3.2.sql`, and an empty `pg_sift--4.3.2--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.2-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.3.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.3.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.3.2/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.3.2 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.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.3.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.3.2/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.2/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.3.2 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.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.3.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.3.2/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.2/mariadb/CHANGELOG.md).