pg_sift 3.6.3: `REINDEX INDEX CONCURRENTLY` and `CREATE INDEX CONCURRENTLY` of a `sift_col` index with `key_cut` give the index a file of its live size also while a physical standby streams (a space fix, no wrong result). Update with the files of this release and restart the server; no `ALTER EXTENSION` step, no `REINDEX`. ## Highlights - **Fixed: the file of a concurrently built `key_cut` index kept the free blocks of the build while a standby streams.** The build cuts the file behind the segments it merged, and it did so only under an `AccessExclusiveLock`, which a standby replays by waiting for every query of the standby that planned on the table since the index entered the catalog; so while a physical standby streams, the cut was skipped and the file stayed with the blocks that the participants' segments left free (1,000,000 rows, 4 workers: 7.6 MB for 2.5 MB of live segments, 3.0 times; now 2.5 MB). The new index of a concurrent build is not ready until its build ends, so no query reads it and no insert reaches it: the cut takes an `ExclusiveLock`, which a standby does not replay. The non-concurrent `CREATE INDEX` and `REINDEX` already cut the file (their new file is not visible either). A `VACUUM` of an index that is ready still does not cut its file while a standby streams: that is unchanged. Test: the TAP test `col_build_standby_space` (2 more checks: `REINDEX INDEX CONCURRENTLY` leaves the file at most 1.1 times its live bytes and nothing to rewrite). ## Who is affected and what to do **Affected:** a `sift_col` index with `key_cut` built by `CREATE INDEX CONCURRENTLY` or `REINDEX INDEX CONCURRENTLY` on a primary that has a physical standby streaming at the time of the build, on 3.6.1 or 3.6.2 (the gap the build reserves came with 3.6.1; earlier releases were not measured for this case). The effect is disk space only (the index is valid and every answer is right): a file up to 3 times its live bytes on the test table. **How to detect:** compare the file with the live bytes of the index, `SELECT pg_relation_size('idx'), (SELECT bytes FROM sift.col_index_info('idx'))`: a ratio well above 1.1 after a concurrent build with a standby streaming is this case. **To get the space back on 3.6.2:** a non-concurrent `REINDEX INDEX idx` cuts the file (it takes a lock that blocks writes while it runs), or update and run `REINDEX INDEX CONCURRENTLY idx` again. - **No format change, no `REINDEX`:** the stored index is valid. Install the files and restart the server (a standby too), or reconnect every pool: a backend that already loaded the old library keeps it until its connection ends. - **Nothing else changes:** plans, answers and formats are those of 3.6.2. ## Regression tests The TAP test `col_build_standby_space` fails on a build of the tag `v3.6.2` and passes on this release (table: `docs/releases/3.6.3-REGRESSIONS.md`); PostgreSQL 17, 18 and 19 are those of the quick-tier run of the release. Two quick-tier results of this branch are not defects of the release: - **`col_build_standby_space`, the bound of the file (PostgreSQL 17, one red in seven runs).** One quick run failed the check "the file is at most 1.1 times its live bytes" after a `REINDEX` with a gap of 0 blocks: the file was 1,466,368 bytes against 1,318,912 live (1.112, 18 blocks over; the bound is 1.1). The test index is 160 blocks, and the blocks above the live bytes (metadata, the free list, the few free blocks a merge leaves) are 3 to 13 blocks whatever the size; 6 serial runs of the test on PostgreSQL 17.11 passed, 3 on this branch and 3 on `v3.6.2`, so it is not a regression. Since the bound has an absolute allowance of 32 blocks (256 kB) next to the 10%: it is there to catch a file that doubles. - **`leakcheck_gate` on macOS arm64 (one flake).** The job failed once in the second `make check` and passed in the rerun of the failed job alone; its cause, the cluster check that lands while a client's backend still holds its temporary-table locks, is fixed by 0c81bb74c, which this branch has. ## 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.6.2: column format minors 3 to 32 are read, extension SQL 3.6 (`ALTER EXTENSION pg_sift UPDATE` has nothing to do and there is no `pg_sift--3.6--3.6.3.sql`), no setting changes its default. The SQLite and MariaDB extensions: no change since 3.6.2. ## 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 (built on Ubuntu 22.04, glibc 2.35), 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). The arm64 libraries are built for plain ARMv8-A and NEON, and the smoke test of each arm64 package runs `scripts/check-aarch64-baseline.sh` on the installed library. - 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.6.3-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-3.6.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.6.3-1.amzn2023.x86_64.rpm` (`.aarch64.rpm` on arm64) - Arch Linux: AUR `pg_sift`. Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.3/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 3.6.3 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.6.3 release: no change to the SQL interface or the shared format. - **No change:** the changes 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.6.2 open as they are, nothing to rebuild. Download `sift-sqlite-3.6.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.6.3/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.3/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 3.6.3 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.3 release: no change to the SQL files. - **No change:** the changes 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:` 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.6.2 opens as it is, nothing to rebuild. Download `sift-mariadb-3.6.3-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib//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.6.3/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.3/mariadb/CHANGELOG.md).