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):

```sql
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](https://github.com/brauliobo/pg_sift/blob/v3.2.3/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](https://github.com/brauliobo/pg_sift/blob/v3.2.3/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](https://github.com/brauliobo/pg_sift/blob/v3.2.3/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.3/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](https://github.com/brauliobo/pg_sift/blob/v3.2.3/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.3/mariadb/CHANGELOG.md).