pg_sift 4.3.1: a patch release of 4.3.0 that fixes a wrong answer of sift_col indexes that track their changes, present since 4.0.0: a read made while a DELETE, UPDATE of the key column or MERGE was running (from an AFTER ROW trigger, an AFTER STATEMENT trigger that fires before the index’s own, or a function the statement calls) counted the rows that statement had just ended. It also fixes every DROP failing on a server with a FOR ALL TABLES publication (4.2.0 to 4.3.0), a warning of the parallel VACUUM of a sift (BM25) index and a rare second load of a shared lookup table; extension SQL 4.3.1 (one statement, no new object, no format change): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

  • Fixed: a read inside a statement that deletes rows counted the rows it had deleted (4.0.0 to 4.3.0; sift_col with track_changes, which is the default). An index with track_changes hears the versions a statement ends (the rows of a DELETE, the old versions of an UPDATE of the key column or of a MERGE or INSERT .. ON CONFLICT DO UPDATE) from its own statement triggers, which fire after the statement has run. A read of the same transaction that runs before them took the log for the whole truth and counted those versions: an AFTER ROW trigger, an AFTER STATEMENT trigger of the table whose name sorts before the index’s own (sift_track_*), a function the statement calls (in a WHERE clause or a MERGE condition) and a BEFORE trigger of the same statement. The wrong answers were counts, sums and other aggregates, filtered counts and top-N reads that the index answers without the heap (a count of 50,000 rows against 47,500 in a statement trigger that had deleted 2,500). The next statement, an AFTER trigger with a later name and the commit were right, and no stored byte is wrong: nothing to rebuild. The index now reads the heap for a table while a statement that ends row versions of it runs. Held by the regression test col_track_own_statement_reads.
  • Fixed: every DROP failed on a server with a publication that publishes deletes and covers the extension’s tables (4.2.0 to 4.3.0). With CREATE PUBLICATION .. FOR ALL TABLES, any DROP of a relation (DROP INDEX, DROP TABLE, a plain table too) failed with cannot delete from table "table_option_values" because it does not have a replica identity and publishes deletes: the event trigger that removes the options of a dropped table deletes from sift.table_option_values, which had no replica identity. ALTER EXTENSION pg_sift UPDATE makes the unique key index of that table its replica identity (the one statement of the 4.3 to 4.3.1 script; a fresh install has it); the other tables of the extension have a primary key. A server without such a publication was never affected. Held by the TAP test publication_all_tables (the update from 4.2.0 and from 4.3.0, a fresh install, a subscriber).
  • Fixed: a VACUUM that vacuums the b-trees of a table with parallel workers no longer warns for a sift (BM25) index (4.0.0 to 4.3.0). The merge of the segments by workers needs a transaction id before a parallel mode of its own, which the leader of that VACUUM cannot take: the step logged maintenance failed: cannot assign transaction IDs during a parallel operation, counted a failure in sift.workers and left the segments to a retry after a delay. One process merges them in the same VACUUM now. The answers were right in both. Held by the TAP test vacuum_parallel_merge.
  • Fixed: sessions that run one query at once could load the rows of a joined table more than once (the shared lookup). A session that missed the shared rows and asked for the load’s claim just after the loader had kept the rows and dropped its claim found no claim to wait for, and loaded the table again (the answers were right; the load was wasted work). It looks for the rows again once it holds the claim. Held by the TAP test lookup_shared_claim (PostgreSQL 18 and later built with injection points).

Who is affected and what to do

  • 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; then ALTER EXTENSION pg_sift UPDATE in each database (4.3 to 4.3.1 runs an empty script: no object changes; nothing to REINDEX, no format change). A database at 4.2.1 or 4.2.2 goes through 4.2 and 4.3 by itself.
  • Affected by the failing DROP: a database that has the extension at 4.2.0 to 4.3.0 on a server with a publication that publishes deletes and covers the extension’s tables. Until the update, ALTER TABLE sift.table_option_values REPLICA IDENTITY USING INDEX table_option_values_key (as a superuser or the owner) does what the update does.
  • Affected by the wrong answer: a table with a sift_col index that tracks its changes (every index made with the default track_changes, since 4.0.0) whose AFTER ROW triggers, AFTER STATEMENT triggers or functions called from a DELETE, UPDATE or MERGE read the same table through an aggregate or a top-N. A table nothing reads from inside its own writing statements gives the same answers as before. Plans, speeds and answers outside this case are those of 4.3.0.
  • If you cannot update yet: ALTER INDEX <index> SET (track_changes = 'off') on the indexes of the tables that are read that way makes those reads check the heap (the setting that keeps the index as it was before 4.0; the heap-free reads that track_changes gives are lost until ALTER INDEX <index> SET (track_changes = '<key column>') after the update). A trigger whose name sorts after the index’s sift_track_* triggers and fires AFTER STATEMENT is not affected, but an AFTER ROW trigger or a function the statement calls cannot be ordered after them.
  • Going back: install the 4.3.0 files and restart; ALTER EXTENSION pg_sift UPDATE TO '4.3' runs the empty script back to 4.3. The cause of the wrong answer is back with that library.

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.3.0 with the install and a restart; extension SQL 4.3.1 (pg_sift--4.3--4.3.1.sql, and an empty pg_sift--4.3.1--4.3.sql back); no column format minor and no BM25 metapage minor changes.

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

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.3.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.1 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.3.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. Every change: sqlite/CHANGELOG.md.

MariaDB extension

pg_sift for MariaDB 4.3.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.1 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.3.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. Every change: mariadb/CHANGELOG.md.