pg_sift 3.2.1: fixes for bugs of 3.2.0: a `sift_table` row that could be lost, a new `sift_table` that could be left unreadable, wrong or missing results, errors and a crash in queries answered by the `SiftAgg` scan, two slowdowns against 3.1 (large `IN` lists, planning), and a false corruption report of `sift.col_verify` on 3.1. 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` is required.

## Highlights

- **Fixed: a `sift_table` could be left unreadable by its first writes.** The first write to a new `sift_table` first writes the metapage and then the tombstone segment that lays the table out. A session that read the table in between (a `SELECT` or a `VACUUM`) cached it as laid out, and its own first write then added a write buffer to a directory that was never flagged as a `sift_table`. Every later read of the table failed with `The metapage does not carry the sift_table flag`. The first write now checks that the tombstone segment exists, not only that the table was read before. A test-only setting (`sift.table_init_gate`, in test builds only) holds the first writer at that point so that the regression test reproduces the window without timing.
- **Fixed: a wrong count on PostgreSQL 18 for two `EXISTS` (or `IN`) over the same table.** Two sub-queries on the unique key of one table with different conditions (`EXISTS (SELECT 1 FROM items i WHERE i.id = e.parent_id AND i.kind = 'k1') AND EXISTS (.. i.kind = 'k4')`) are merged by the PostgreSQL 18 planner into one scan of the table (self-join elimination), and `SiftAgg` applied the conditions of only one of them: it counted 1,200 rows where the normal plan finds 0. A semi, anti or preserved join whose table the planner removed this way now keeps the normal plan. PostgreSQL 17 does not merge the joins and is not affected.
- **Fixed: `ERROR: sift: top-N row (p,o) is not in the heap` after a HOT update.** An `ORDER BY .. LIMIT n` answered from the column units failed after an `UPDATE` that left every indexed value as it was (a `NULL` set to `NULL`, or a change of an unindexed column that stays in its page): the index entry names the root of the HOT chain, which is a redirect once the page is pruned, and the scan read the row without following the chain. It now reads the row as an index scan does. The query returned an error, never a wrong row.
- **Fixed: a crash of a parallel worker over a partitioned table.** The leader hands the workers the superblock offsets of every partition of a `SiftAgg` scan over a partitioned table whose partitions carry the `sift_col` index, but a worker opened the first partition only: an item of its work in another partition ran on a source that was not there and the worker crashed (the server restarts every session). A worker now opens the partition it moves to. Hash, range and list partitioned tables, with one, two and four workers and with the leader scanning or not, are in the new test. The same cause crashed a worker of a join over a hash-partitioned table of 4,096 partitions with the leader scanning.
- **Fixed: a row inserted into a `sift_table` could be lost although its insert was reported done.** The writer that closes a write buffer swaps it in the directory and flags its pages sealed afterwards. A session that held a page of the closed buffer kept appending to it until the flag was there, and a promotion that ran in between (`VACUUM`, `sift.table_maintain`, a maintenance worker, or the promotion a writer does when it closes a buffer) released the page with the new row on it. The promotion now flags every page sealed before it reads the buffer, as the promotion of a BM25 or column write buffer does. A test-only setting (`sift.table_seal_gate`, in test builds only) holds the closing writer before it flags, and the regression test reproduces the loss without timing. No format change.
- **Fixed: `sift.col_verify` of 3.1 reported a `sift_col` index written for 3.1 readers as corrupt.** A column segment written under `sift.col_format_minor = 4` or 5 (the setting that keeps new segments readable by 3.1) carried the zone flag of a JSON null that only 3.2 knows, and the zone check of 3.1 compares the flags exactly. The flag is now written from column format minor 6 on. The answers of 3.1 on such an index were right; only its `col_verify` failed.
- **Fixed: a query with a `LEFT JOIN LATERAL (.. LIMIT 1)` child returned no rows.** 3.2.0 reads such a child (no `ORDER BY`, a key the table does not repeat) as a lookup table (`First Row Lookup Tables`). When the query grouped by a column of the lookup row and summed or averaged a `float8` column, also with a `FILTER` (`SELECT l.tier, count(*), sum(e.amount) FROM events e LEFT JOIN LATERAL (SELECT d.tier FROM dims d WHERE d.id = e.parent_id LIMIT 1) l ON true GROUP BY 1`), the scan chose the order of its lanes before the child became a lookup table, and every group was dropped: the query returned no rows where the normal plan returns them. `count(*)`, `count(col)`, `count(DISTINCT ..)`, `min`, `max`, integer `sum` and `avg`, and a group key from the fact rows were never affected. The scan now chooses the order after the child is a table.
- **Fixed: large `IN` lists on a `sift_col` index were slower than in 3.1.** A set filter (`col = ANY($1)`, `col IN (..)`) of thousands of ids ran up to 12 times slower in 3.2.0 than in 3.1.0, because the sorted members of every list were sent along with the zone test and the column scan rebuilt that test (copy, sort) at each range of units it began. A list of more than 256 members meets the zone of nearly every unit and is tested by its range again, as in 3.1. On 3 million fact rows and 100,000 ids: a generic plan 125.7 ms to 9.8 ms, a literal list 239 ms to 123 ms (3.1.0: 9.5 and 122 ms), `IN (SELECT ..)` of 100,000 members 141 ms to 27 ms (3.1.0: 71 ms).
- **Fixed: planning of queries on a table with a `sift` index took a few percent more time than in 3.1.** The check of a `sift.score(ctid) > c` cutoff read the operator class of `float8` from the catalog before it looked at the operands of each clause, so every query on such a table scanned `pg_opclass` several times while it was planned. A short ranked query cost 6% to 23% more CPU than in 3.1.0; it is now within 1% to 3%.

## Who is affected and what to do

- **`sift_table` first write:** every 3.2.0 server that creates `sift_table` tables (`CREATE TABLE .. USING sift_table`) while other sessions already read them. The window is the time between the first writer's metapage and its tombstone segment, on a table that has never been written; a table whose first write has completed, and `sift_col`, BM25 (`sift`) and heap tables, cannot hit it. If the first write of a new table is always done before any other session reads it (the table is created and loaded in one deployment step, for instance), nothing can go wrong. Check an existing table by reading it: `SELECT count(*) FROM t;` (a table in this state fails on every read with the message above; `sift.verify` and `sift.col_verify` check the indexes of a table, not the table's own layout). The fix prevents the state; it does not repair a table that is already in it, because the check that raises this error is unchanged. Restore such a table from a backup, or reload it from its source rows into a new `sift_table`; do not rely on `REINDEX` or `VACUUM (FULL)`, which read the table first.
- **Lost `sift_table` row:** `sift_table` users with concurrent writers while a promotion runs (`VACUUM`, `sift.table_maintain`, a worker, or another writer closing a buffer). The window is the time between a writer's swap of a closed write buffer and its flagging of the pages, usually short, longer if that writer is cancelled in between. No setting removes it: `sift.table_inline_promote = off` and running `VACUUM` and `sift.table_maintain` outside heavy concurrent inserts narrow it. A row lost before the upgrade cannot be found from the table; compare a table that took concurrent inserts with its source row count.
- **Zone flag at minor 4 or 5:** only servers that set `sift.col_format_minor` to 4 or 5 to keep indexes readable by a 3.1 library (a standby, or a downgrade path). Segments written by 3.2.0 keep the flag: `REINDEX` the index under the same setting after installing 3.2.1 if a 3.1 library has to `col_verify` it. Nothing changes for an index at minor 6, the default.
- **`LATERAL .. LIMIT 1` with float sums:** queries on a table with a `sift_col` index with a `LEFT JOIN LATERAL (.. LIMIT 1)` child without `ORDER BY`, grouped by a column of the child, that sum or average a `float8` column. There is no setting that covers this shape alone; until the new library is installed, `SET sift.enable_agg = off` runs such a query with the normal plan and its correct answer. The failure is an empty result, not a wrong number.
- **Large `IN` lists and planning time:** queries with `IN` or `= ANY` lists of more than 256 members on a `sift_col` index, and every query on a table with a `sift` index (the planning cost is small, a few percent of a short ranked query). No setting is needed; installing the new library fixes both.
- **Two `EXISTS` over one table:** PostgreSQL 18 servers with a `sift_col` index that run a query of this shape. Until the new library is installed, `SET sift.enable_agg = off` runs the query with the normal plan and its correct answer.
- **Top-N after a HOT update:** queries `ORDER BY <column> LIMIT n` on a table with a `sift_col` index and `UPDATE`s that change no indexed value. Until the new library is installed, `SET sift.enable_topn = off` avoids the error.
- **Parallel workers over partitions:** queries on a partitioned table whose partitions carry the same `sift_col` index and that run with parallel workers. Until the new library is installed, `SET max_parallel_workers_per_gather = 0` keeps the query out of the workers.

## Compatibility

- PostgreSQL 17 and 18; PostgreSQL 19 is experimental. A drop-in replacement of 3.2.0: 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.0 needs nothing.

## 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.1-1_amd64.deb`
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.2.1-1.el9.x86_64.rpm`
- Arch Linux: AUR `pg_sift`.

Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.1/CHANGELOG.md).

## SQLite extension

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

- **No change:** the fixes of this release are in `sift_table` and the aggregate pushdown, which are PostgreSQL features and 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.0 open as they are, nothing to rebuild.

Download `sift-sqlite-3.2.1-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.1/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.1/sqlite/CHANGELOG.md).

## MariaDB extension

pg_sift for MariaDB 3.2.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.2.1 release: no change to the index format, the SQL files or the shared core.

- **No change:** the fixes of this release are in `sift_table` and the aggregate pushdown, which are PostgreSQL features and 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.0 opens as it is, nothing to rebuild.

Download `sift-mariadb-3.2.1-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.1/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.1/mariadb/CHANGELOG.md).