pg_sift 3.2.3: three fixes to memory that the relation cache can free (a backend crash or corrupted heap memory in a narrow window) and a fix for the first statement of every new backend, which ran the ordinary plan. Library only: install the new files and restart the server; the extension SQL does not change (it stays 3.2), so there is no ALTER EXTENSION step and no REINDEX.

Highlights

  • Fixed: memory the relation cache can free. A catalog read can reload the cached state of an index while an index function still holds it. The insert of a row that flags a leaf (a value the index cannot store, or a JSON null) wrote its write-buffer hint into the freed state; the heap check of sift.col_verify(index, true) and the building of an index row of a table with a toasted value read it. Each now works on a copy or reads the fields it needs first. Found with debug_discard_caches and AddressSanitizer; the three crashes are not reproduced on a server without assertions, because the window needs a concurrent VACUUM, ANALYZE or ALTER of the table (autovacuum is enough) to land at the wrong call.
  • Fixed: the first statement of a new backend uses the aggregate pushdown. The library loads while the planner opens the first column index of a backend, after the planner hook had its chance, so the CTE-inline and lateral-flatten decisions were skipped: a statement of two arms over one CTE took 233 ms as the first statement of a backend and 3.7 ms as the second, rows equal. The executor start now plans a plain SELECT again through the hook, once per backend. Pools that recycle connections meet the case on every new backend. This release does not cover a first statement with bound parameters (a driver on the extended protocol, EXECUTE with arguments, pgbench -M extended): on a fresh backend it still plans without the hook once per backend, 220 to 260 ms against 3 to 5 ms for the next statement; preloading the library (below) avoids it.
  • Fixed: macOS. scripts/tap.sh runs on the BSD sha256sum and the SQLite build’s portable-list filter runs on GNU make 3.81. Both are build and test scripts; no product behavior changes.

Who is affected and what to do

  • Memory fixes: every PostgreSQL 17 and 18 server with a sift_col index that takes inserts or runs sift.col_verify(index, true); the exposure is a backend crash in a narrow window, and the heap memory of that backend can be corrupted before it. No privilege is needed to be exposed to it. Update when convenient.
  • First statement: pools that open a new backend per request without session_preload_libraries = 'pg_sift'; that setting also avoids the cost for a statement with bound parameters, which this release does not fix: set session_preload_libraries = 'pg_sift' (ALTER DATABASE mydb SET session_preload_libraries = 'pg_sift', new sessions only) or list the library in shared_preload_libraries.

Known issue of 3.2.0 to 3.2.3: a role cannot drop anything

The event trigger function sift.table_paths_drop() is not SECURITY DEFINER in the 3.2 extension SQL, so in a database where pg_sift is installed, a DROP by a role that is neither a superuser nor the owner of the extension fails with permission denied for schema sift (any table, index or schema, also one without pg_sift). Nothing is lost; the statement fails. The SQL of the extension does not change in this release, so 3.2.3 does not fix it; ALTER EXTENSION pg_sift UPDATE to 3.3 does. Until then, run this once in each database that has the extension, as a superuser (and again after restoring a database from a dump, which creates the extension from the 3.2 script):

ALTER FUNCTION sift.table_paths_drop() SECURITY DEFINER SET search_path = pg_catalog, pg_temp;

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental. A drop-in replacement of 3.2.2: extension SQL 3.2, no change of the BM25, sift_col or sift_table formats. Install the new files (a library swap) and restart the server; going back to 3.2.2 needs nothing. docs/UPGRADING.md has the steps.

Packages

x86-64 packages for PostgreSQL 18 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd.

  • Debian 13, Ubuntu 24.04 (PGDG postgresql-18): sudo apt install ./postgresql-18-pg-sift_3.2.3-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.2.3-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 3.2.3 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.2.3 release: no change to the storage format, the SQL interface or the shared core.

  • No change: the fixes of this release are in the PostgreSQL column index and the aggregate planner, which are 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.2.2 open as they are, nothing to rebuild.

Download sift-sqlite-3.2.3-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 3.2.3 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.2.3 release: no change to the index format, the SQL files or the shared core.

  • No change: the fixes of this release are in the PostgreSQL column index and the aggregate planner, which are 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.2.2 opens as it is, nothing to rebuild.

Download sift-mariadb-3.2.3-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.