# pg_ivm 1.17 repairs PostgreSQL incremental-view restore bugs that could duplicate maintenance triggers The October 26 PostgreSQL extension release fixes an incorrect restored-view state that could create duplicate triggers and bad incremental maintenance, while adding pg_upgrade-specific metadata restoration and improving index derivation. It matters to operators relying on immediately updated materialised views. pg_ivm 1.17 fixes restore state, duplicated incremental-maintenance triggers and upgrade metadata handling, plus index inference and LATERAL crash bugs. - Status: Active - Published: 2026-10-27T16:24:48+13:00 - Updated: 2026-10-27T16:24:48+13:00 - Categories: Web Development, Cloud & Infrastructure, Developer Tools, Databases & Storage - Tags: database operations, materialized views, open source, PostgreSQL - Canonical HTML: https://beyondthe.news/dossiers/pg-ivm-1-17-postgresql-incremental-view-maintenance-restore-upgrade-fixes ## What changed The pg_ivm development group announced version 1.17 on October 26, 2026. It adds a replace option to restore_immv() and a --pg-upgrade mode for pg_ivm_dump_metadata to address metadata entries left behind during PostgreSQL upgrades. It fixes restore_immv() incorrectly leaving the ispopulated flag false, which could cause a later refresh_immv() to create a duplicate set of incremental-view triggers and lead to incorrect maintenance. The release also improves automatic unique-index derivation, fixes a crash involving LATERAL subqueries during index creation and addresses compatibility assertions. ## Why it matters pg_ivm maintains materialised views immediately through triggers when base tables change, avoiding full refreshes for some workloads. That convenience depends on correct metadata and trigger state, especially across dump/restore and pg_upgrade. A restore that appears successful but later creates duplicate triggers is an operational correctness risk, not merely a performance issue. Administrators using pg_ivm should evaluate 1.17 before upgrade or restore work, verify trigger state and view results, and test under their supported PostgreSQL versions. The community announcement is authoritative, but the GitHub release listing inspected during this pass had not yet shown a v1.17 tag. ## The data-correctness fix is the headline Previously restore_immv() could leave ispopulated=false after restoring an incrementally maintainable materialised view. A subsequent refresh_immv() might then create duplicate maintenance triggers. The release states that this could cause incorrect view maintenance. Operators should treat the bug as a reason to check restored view consistency and triggers, not assume every pre-1.17 database is corrupted. ## pg_upgrade needs different metadata restoration pg_ivm 1.15 introduced metadata dump/restore helpers, but upgrading with pg_upgrade could retain old IMMV metadata entries. Version 1.17 adds replace semantics and a --pg-upgrade option to generate commands suitable for that path. The correct procedure depends on the installed extension and PostgreSQL versions; use current project instructions and test on a copy first. ## Indexes and crashes also receive fixes The extension now derives unique keys from the original view definition instead of only a rewritten query, helping views containing EXISTS clauses obtain automatic indexes when uniqueness is provable. It also rejects set-returning target expressions that cannot provide unique keys and fixes a crash during index creation with LATERAL subqueries. ## Immediate maintenance still has workload limits pg_ivm updates views in the same transaction as base-table changes. This can be efficient for small incremental modifications but costly for large or frequent bulk writes. Its supported SQL shapes and preload configuration remain constrained; version 1.17 does not turn it into general-purpose automatic materialised-view maintenance. ## Key details - Release announced October 26, 2026 by pg_ivm Development Group via PostgreSQL News. - restore_immv() now supports replacing existing IMMV metadata. - pg_ivm_dump_metadata gains --pg-upgrade output mode. - Fixes restored ispopulated state that could duplicate triggers and cause incorrect maintenance. - Improves unique-key derivation and fixes LATERAL index-creation crash. - Upstream GitHub releases list had not yet displayed 1.17 at inspection time; release packaging should be verified before upgrade. ## Builder takeaways - Before a PostgreSQL upgrade, test pg_ivm metadata export/restore using the new --pg-upgrade path. - Audit restored IMMVs and maintenance triggers if you previously used restore_immv() and refresh_immv(). - Verify view results against base tables before relying on materialised aggregates after migration. - Check actual package/tag availability and your PostgreSQL major-version compatibility before updating. - Remember immediate maintenance can be slower than full refresh for bulk changes. ## What to watch - Availability of the v1.17 upstream release tag and distribution packages. - Follow-up fixes for restored IMMV consistency or trigger duplication. - PostgreSQL 19 compatibility and real-world upgrade reports. ## Uncertainties - The PostgreSQL community announcement precedes a visible 1.17 GitHub release tag in the indexed listing. - The release notice does not quantify how many installations were affected. - No independent production reproduction of the restored-state bug was located. ## Sources - [pg_ivm 1.17 released](https://www.postgresql.org/about/news/pg_ivm-117-released-3420/) — PostgreSQL News / IVM Development Group · primary release announcement · 2026-10-26T00:00:00+13:00. Canonical description of restore, index and crash fixes. - [pg_ivm upstream repository and documentation](https://github.com/sraoss/pg_ivm) — SRA OSS · primary repository · 2026-10-26T00:00:00+13:00. Architecture, immediate maintenance, metadata dump and limitations. - [pg_ivm release history](https://github.com/sraoss/pg_ivm/releases) — SRA OSS · primary release history · 2026-10-26T00:00:00+13:00. Previous 1.15 and 1.16 release context; 1.17 tag not visible at inspection.