pg_sift 3.5.3: fixes a crash of a backend (signal 11) and wrong rows for a text column tested with = ANY ($1) or <> ALL ($1) when the parameter is a NULL array or a packed array a PL/pgSQL variable holds and the plan is generic; the .deb installs on Debian 12 and Ubuntu 22.04 again, and arm64 packages are built from the tag for plain ARMv8-A. Update with the files of this release and restart the server; no ALTER EXTENSION step, no REINDEX.

Highlights

  • Fixed: a crash and wrong rows for a text list that is a parameter. A text column of a sift_col index tested with kind = ANY ($1) or kind <> ALL ($1) ended the backend with signal 11 when the parameter was NULL and the plan generic (a prepared statement once its plan turns generic, a PL/pgSQL variable). A packed or expanded array in a PL/pgSQL variable could also give no row: the hash of the list is built at the first test from the array of that test, and the array was freed after it (visible on a server that clobbers freed memory, as one built with assertions on). The list is now copied, flat, where the hash lives, and a NULL list has no hash; the rows are those of the normal plan. Test agg_set_array (text lists, <> ALL, a NULL element, a NULL list, a packed and an expanded PL/pgSQL variable).
  • Fixed: the .deb needed glibc 2.38. The packages of 3.5.2 (libc6 (>= 2.38)) did not install on Debian 12 or Ubuntu 22.04 (GLIBC_2.38 not found). The .deb is built on Ubuntu 22.04 (glibc 2.35, GCC 11) now and installed in fresh Debian 12, Debian 13, Ubuntu 22.04 and Ubuntu 24.04 containers of its architecture before the release, so one package serves all four.
  • Fixed: arm64 builds. The library is compiled for plain armv8-a (NEON) whatever flags the server’s pg_config carries (a build against Amazon Linux 2023’s PostgreSQL inherited -march=armv8.2-a+crypto and used LSE atomics directly, which do not run on every Arm CPU), and scripts/check-aarch64-baseline.sh fails a library that holds an instruction of a later extension (it passes on the library of every arm64 package attached here). The build with GCC 14.2 on arm64 (Debian 13) no longer ends with an internal compiler error in the chunk codec, so the arm64 packages for PostgreSQL 17, 18 and 19 are built from the tag as it is.
  • Added: an .rpm for Amazon Linux 2023 (x86_64 and aarch64, PostgreSQL 17 and 18; the distribution has no 19). The PGDG el9 package installs on Amazon Linux 2023, but CREATE EXTENSION pg_sift then fails with extension "pg_sift" is not available: the distribution’s server reads /usr/lib64/pgsql and /usr/share/pgsql, the PGDG one /usr/pgsql-N. This package is built against the distribution’s own postgresqlN-server and installed in a fresh amazonlinux:2023 container of its architecture before the release.

Who is affected and what to do

  • The crash and the wrong rows: every release from 3.2.0 to 3.5.2 that runs a prepared statement or a PL/pgSQL function with col = ANY ($1) or col <> ALL ($1) on a text column of a sift_col index, on a generic plan with a NULL array. The server restarts on its own after the crash; an application sees a dropped connection. Install 3.5.3 and restart the server (a standby too), or reconnect every pool and worker: a backend that already loaded the old library keeps it until its connection ends. The function that builds the hash (hash_param_saop) is in the code since 3.2.0; only 3.5.2 and the current tree were run against the statements.
  • Until then: SET sift.enable_agg = off for the role restores PostgreSQL’s plan for the statements affected.
  • Debian 12 and Ubuntu 22.04: the .deb of 3.5.2 does not install there; use this one, or a build from source with make USE_PGXS=1.
  • arm64: the packages of 3.5.2 were built before the baseline check existed. The libraries of all six (.deb and .rpm for PostgreSQL 17, 18 and 19) were checked against it after the release and hold only ARMv8-A and NEON instructions, so they need no replacement for that; they still need glibc 2.38 (.deb).
  • Nothing else changes: plans, answers and formats are those of 3.5.2.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A drop-in replacement of 3.5.2: extension SQL 3.5 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--3.5--3.5.3.sql), no change of the BM25, sift_col or sift_table formats, no setting changes its default. pg_get_loaded_modules() on PostgreSQL 18 says 3.5.3+<commit>; the extension version stays 3.5.

Packages

16 packages are attached below (SHA256SUMS covers every asset): x86-64 and arm64 .deb and .rpm for PostgreSQL 17, 18 and 19 (12), and the Amazon Linux 2023 .rpm for PostgreSQL 17 and 18 on both architectures (4); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd. The 19 ones are built and tested against PGDG’s 19~rc1. Each package was installed in fresh containers of its architecture, where CREATE EXTENSION, an update from 1.0, a sift index and its queries ran: the .deb in Debian 12, Debian 13, Ubuntu 22.04 and Ubuntu 24.04, the .rpm in Rocky 9, Alma 9 and Alma 10, the Amazon Linux 2023 one in amazonlinux:2023 (the arm64 ones on a hosted arm64 runner). Nothing is patched outside the tag.

  • 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_3.5.3-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.5.3-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-3.5.3-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: AUR pg_sift.

Also on main since 3.5.2

Tests and tools only; the library is the same as the fixes above:

  • agg_visibility_refusal is deterministic: its statistics are those of every row, and the decisive key bound sits 16% from the cost of the other plan, not 1%.
  • make check on macOS arm64: the scripts use the BSD tools, the library suffix of the platform and Python 3.9.
  • scripts/check-aarch64-baseline.sh; the packaging scripts list the architectures and majors of a release and refuse a set that lacks one (scripts/package.sh verify).

Every change: CHANGELOG.md.

SQLite extension

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

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

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

  • No change: the fixes of this release are in the PostgreSQL column index, the aggregate planner and the packages, 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; a server of another distribution or of a series not listed is built from source (make -C mariadb).
  • Index format 2: an index of 3.4.0 to 3.5.2 opens as it is, nothing to rebuild.

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