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.

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. Every change: 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:<series> 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/<series>/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. Every change: mariadb/CHANGELOG.md.