pg_sift 4.0.2: pg_squeeze of a table with a sift_col index no longer leaves rows out of the index (every release since 3.6.0 could), and the log of a table that was attached as a partition or inheritance child is ended when the table is attached. A patch release of 4.0.1: the library changes, nothing on disk and no SQL.

Highlights

  • Fixed: pg_squeeze lost rows of a sift_col index. When writers inserted or updated rows during the squeeze, the index of the copy could miss a whole batch of them (in the tests about one run in three; sift.col_verify said Heap tuple (..) is not in the index, and the index answered with fewer rows until REINDEX), with track_changes on or off, in every release from 3.6.0 to 4.0.1. The squeeze applies the changes it decodes while its decoding session reads the catalogs as of the past, where the copy it buffered rows for does not exist, and the buffer of those rows was taken for the one of a dropped index and cleared. The buffer is now flushed under its own OID there. With a real pg_squeeze, in 12 of 12 runs with track_changes on and in 12 of 12 with it off, no row was missing after the fix; before it, 3 to 7 of 12 runs lost rows.
  • Fixed: pg_squeeze failed on a table with a sift_col index with track_changes (the default). The squeeze stopped with track_changes: column "id" can be NULL, because its copy (tmp_<oid>) has no NOT NULL and no key while the index is built. The index is made now. The log of the table is not complete after a squeeze, because no trigger hears the changes the squeeze applies: reads go to the heap until REINDEX INDEX <index>.
  • Fixed: the log of a table attached as a partition or made an inheritance child is ended at the attach. In 4.0.1 a read leaves the log while the table is a child, but after DETACH PARTITION or NO INHERIT the log was stale until REINDEX INDEX. The event trigger of ALTER TABLE now ends the completeness of the log when the table becomes a child, so the statements of the parent that the table did not hear no longer make a read after the detach leave rows out. A table that was attached before this release still needs REINDEX INDEX <index> at the detach.

Who is affected and what to do

  • pg_squeeze: a database where pg_squeeze rewrote a table with a sift_col index, on any release from 3.6.0 to 4.0.1. Check each such index with SELECT sift.col_verify('<index>', true): it returns no row for a good index. Repair with REINDEX INDEX <index>. With 4.0.2 installed the rows are no longer lost, and a REINDEX INDEX <index> after a squeeze brings the log back. Not affected: a sift (BM25) index, a sift_table (pg_squeeze refuses it), pg_repack, and logical replication apply.
  • Partitions and inheritance children: run REINDEX INDEX <index> at the detach for a table that was attached before 4.0.2.
  • What fails loudly, not wrong, and the rest of 4.0.1: unchanged (see the notes of 4.0.1): DROP INDEX CONCURRENTLY of an index with track_changes and pg_repack --only-indexes fail with must be first action in transaction; run ALTER INDEX <index> SET (track_changes = 'off') first.
  • Update: install the files and restart the server (a standby too), or reconnect every pool: only the library changes, and a backend that already loaded the old library keeps it until its connection ends. No format change, no ALTER EXTENSION UPDATE step.
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.0.1.

Tests

col_decode_pending (the rows buffered while a transaction decodes reach an index made in it), col_track_pending and col_track_attach (the log after ATTACH PARTITION, INHERIT, DETACH PARTITION and NO INHERIT).

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 4.0.1 and 4.0.0: extension SQL 4.0 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--4.0--4.0.2.sql); column format minors 3 to 42 are read and written as in 4.0.0, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.0.

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.0.2-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.0.2-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.0.2-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.0.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.2 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.0.2-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.0.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.2 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.0.2-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.