pg_sift 4.0.5: a patch release of 4.0.4 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.0.4). 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.0.3, 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 <index> or ALTER INDEX <index> SET (track_changes = '<value>') is run once for each tracked index (see the notes of 4.0.4).
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.0.4.

Known issues

  • Fixed in 4.2, not here: The SQL administration functions (sift.index_advice, sift.col_verify, sift.col_clustering, sift.col_refresh, sift.col_rollup_refresh, sift.col_unit_agg, sift.col_unit_hist, sift.col_tracking, sift.col_stamps, sift.key_cut_decision, and sift.index_choices from 4.0.3) lock the index before its table, so a DROP TABLE or ALTER TABLE that runs at the same moment can be aborted with deadlock detected. It is an error, never a wrong answer, and it has no effect on the data: run the statement again. Present in every release up to 4.1.4; fixed in 4.2, not in a patch release.

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.0.4 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.0.4, 4.0.3, 4.0.2, 4.0.1, 4.0.0 with the install and a restart: extension SQL 4.0.3 (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.0.5-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.0.5-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.5-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: the AUR package follows the newest release; for 4.0.5 build from the tag (make USE_PGXS=1).

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.0.5 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.5 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.5-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.0.5 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.5 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.0.5-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.