pg_ivm 1.9 (2024-07-31)

New feature

• Add support for PostgreSQL 17 by @yugo-n in #92, @thoshiai in #84, and @reshke in #76

  This contains the following changes:

  • Change functions to use a safe search_path during maintenance operations when used with PostgreSQL 17 or later

    This prevents maintenance operations (automatic maintenance of IMMVs and refresh_immv) from performing unsafe access. Functions used by IMMVs that need to reference non-default schemas must specify a search path during function creation.

  • refresh_immv can be executed by users with the MAINTAIN privilege when used with PostgreSQL 17 or later

New Contributors

• @reshke made their first contribution in #76

Full Changelog: v1.8...v1.9

pg_ivm 1.8 (2024-03-01)

Bug fixes

• Consider tuple duplicity in maintenance of EXISTS views by @yugo-n in #83

  Previously, the duplicity of row was not considered and only one tuple was inserted even when duplicated tuples are generated as a result of inserting a tuple into a table in an EXISTS subquery.

• Fix checking for expressions containing an EXISTS subquery by @thoshiai in #71

  EXISTS subquery is currently allowed only directly under WHERE clause or in AND expression that is directly under WHERE. However, the check was insufficient previously so that views using expressions other than AND containing an EXISTS subquery could be created without an error and it caused incorrect maintenance results.

• Check if PgIvmImmvRelationId is invalid before open it by @bytezzz in #78

  Fix an error that occurs at dropping tables that occurs when pg_ivm is installed by shared_preload_libraries without executing CREATE EXTENSION command.

• Fix an error raised when dropping pg_ivm extension by @yugo-n in d67995c

  Previously, DROP EXTENSION pg_ivm failed due to the failure of opening the index on the pg_ivm_immv catalog table, because the index had been already dropped at that time.

• Fix typo in README heading by @coox in #81

New Contributors

• @bytezzz made their first contribution in #78
• @coox made their first contribution in #81

Full Changelog: v1.7...v1.8

pg_ivm 1.7 (2023-09-13)

New feature

• Add support for PostgreSQL 16 by @yugo-n in #70

  Now you can build and use pg_ivm with PostgreSQL 13, 14, 15, and 16.

Full Changelog: v1.6...v1.7

pg_ivm 1.6 (2023-08-31)

New feature

• Add EXISTS clause support in IVM by @thoshiai in #53

  Correlated subqueries using EXISTS in WHERE clause are supported.

  There are following restrictions :

  • EXISTS subqueries are allowed only in WHERE clause.
  • aggregate functions are not supported together with EXISTS.
  • nested EXISTS subqueries in a subquery are not supported.
  • EXISTS condition can use only with AND Expression, OR or NOT is not allowed.

Bug fixes

• Prohibit types without default btree opclass in the target list by @yugo-n in #67

  Currently, types that does not have an equality operator cannot be used in the target list of the view definition, because all of target list entries are used for comparison to identify rows to be updated or deleted in the view. Previously, an error is raised at the time such view is incrementally maintained, this is fixed to check that at the view creation time.

  This restriction may be relaxed in future after we can use an index to identify rows in the view.

New Contributors

• @Valefant made their first contribution in #55

Full Changelog: v1.5.1...v1.6

pg_ivm 1.5.1 (2023-03-02)

This release contains security fixes.

• Exposure of sensitive information to an unauthorized actor (CWE-200) - CVE-2023-22847
  An Incrementally Maintainable Materialized View (IMMV) created by pg_ivm may reflect rows with Row-Level Security that the owner of the IMMV should not have access to.

• Uncontrolled search path element (CWE-427) - CVE-2023-23554
  When refreshing an IMMV, pg_ivm executes functions without specifying schema names. Under certain conditions, pg_ivm may be tricked to execute unexpected functions from other schemas with the IMMV owner's privilege.

Bug fixes

• Fix row level security checks during view maintenance by @yugo-n (CVE-2023-22847)

  The view maintenance is performed under the view owner privilege. If a modified table has a (Row Level Security) RLS policy, rows in that table must be accessed by the privilege of the IMMV owner during view maintenance, so rows invisible to the view owner must not appear in the IMMV. However, the security check was not properly handled, and rows that must not be accessed from the view owner could appear in the view contents when the view was refreshed incrementally during a query that contains multiple types of commands, like a modifying CTE that contains INSERT and UPDATE, or a MERGE command.

• Fix to use qualified function names during maintenance by @yugo-n (CVE-2023-23554)

  Previously, functions names in pg_catalog schema that were used during view maintenance were not qualified. This is problematic because functions in other schema could be referenced unintentionally. Moreover, that could result in privilege escalation that if a nefarious user who can create a function, arbitrary functions could be executed under IMMV owner's privilege.

• Rebuild the query for recalculating min/max after search_path change by @yugo-n (CVE-2023-23554)

  Cached plans for recalculating min/max values are built from the view definition query text. Therefore, when the search_path was changed, the query text was analyzed again, and tables or functions in a wrong schema could be referenced in the plan. This is fixed so that, after search_path is changed, a plan is rebuilt from a new query string where table / function names are properly qualified under the latest search_path are used.

Full Changelog: v1.5...v1.5.1

pg_ivm 1.5 (2023-01-30)

New feature

• Add CTE support by @yugo-n in #47

  Simple CTEs (WITH queries) which do not contain aggregates or DISTINCT are supported similarly to simple sub-queries.

Bug fixes

• Fix automatic index creation on views containing a subquery by @yugo-n in #46

  Previously, a unique index could not be created properly when an IMMV containing a subquery was created even if all primary key attributes appeared in the target list.

• Fix to allow generate_series in FROM clause by @yugo-n in #50

  Using generate_series caused an error due to an ambiguous reference at the maintenance time because this function is used internally.

• Prohibit to create IMMVs using subqueries in a function argument or in an expression by @yugo-n in #49

  For example, views like

  • SELECT ... FROM func(..., (SELECT ... FROM ...), ..) ...;
  • SELECT expr(SELECT ... FROM ...) FROM ...;

  cannot be maintained correctly, so they are prohibited.

Full Changelog: v1.4.1...v1.5

v1.4.1

This release is for fixing the problem of pg_ivm 1.4 release that the update script pg_ivm--1.3--1.4.sql was not installed.

What's Changed

• Fix small typos in README by @0xflotus in #44
• Fix Makefile to install pg_ivm--1.3--1.4.sql by @yugo-n in #45

pg_ivm 1.4 (2022-12-16)

Bug fixes

• Prohibit to create IMMVs including other IMMV by @thoshiai in #31

  IMMVs that contain other IMMV or materialized view cannot be maintained for now when a table underlying inner IMMV or materialized view is modified.

• Fix segmentation fault when non-SELECT query is specified in create_immv by @thoshiai in #39

• Fix a bug that a unique index could be created improperly by @yugo-n in #40

  It is intended that a unique index is created only if all primary keys of tables in FROM clause appear in the target list. But, due to this bug, an index could be created even when there were pkey attributes from just one of relations in FROM clause.

• Fix bugs that occur when column names are specified in an aggregate view by @yugo-n in #41

  When the number of specified column names and the length of the target list of the view definition query didn't match, segmentation fault occurred or incremental maintenance of the view failed.

• Use exclusive lock for view maintenance caused by UPDATE or DELETE by @yugo-n in #42

  When a table was modified by DELETE or UPDATE in concurrent transactions, incremental view maintenance could end in wrong results, which is avoided by using the lock for now. Note that affected IMMVs are those that contain only one table and do not use aggregate or DISTINCT, because IMMVs with more than one table, or using aggregate or DISTINCT, are already maintained in exclusive manner.

Full Changelog: 1.3...v1.4

pg_ivm 1.3 (2022-9-30)

New feature

• Add get_immv_def function by @thoshiai in #23

  This function reconstructs the underlying SELECT command for an IMMV.

Bug fixes

• Fix a maintenance failure of MIN/MAX aggregate views whose columns had alias names by @yugo-n in #26

  Views that have a min/max aggregate with an alias name (e.g. min(j) as min_j) were not correctly handled and an error could be raised when a tuple was deleted from a base table.

• Fix possible incorrect maintenance due to XID wraparound or using sub-transactions by @yugo-n in #28

  For maintaining self-join views or handling multiple tables modification, we need to calculate table states that was before it is modified. For get the pre-update state, previously, we used xmin and cmin system columns, but this was problematic because after a tuple is frozen and XID wraparound, xmin column no longer has any meaning. Also, it is not safe to use xmin colmun for this purpose with sub-transaction either. To fix this, we use a snapshot that taken just before the table is modified for checking tuple visibility in pre-state table instead of using xmin and cmin system columns.

• Fix a bug that any table cannot be dropped by non-superuser by @yugo-n in #29

  Previously, we used an event trigger and executed DELETE command to remove an entry, but it caused a privilege error when non-superuser dropped a table even if it is irrelevant to IMMV. To fix it, we now drop an entry in a way that doesn't need the superuser privilege in the object_access_hook function.

• Fix a test failure for aggregates without GROUP BY by @yugo-n in #24

• Fix a compiler warning by @marcocitus in #19

Full Changelog: v1.2...v1.3

pg_ivm 1.2 (2022-7-25)

• Add support for PostgreSQL 15 (Takuma Hoshiai, Yugo Nagata)

• Add support for simple sub-queries in FROM clause (Takuma Hoshiai)

  Simple sub-queries that do not contain DISTINCT or an aggregate function can be used in FROM clause. Sub-queries in a target-list or WHERE clause (including EXISTS sub-queries) are not allowed.

• Add support for built-in min and max aggregate functions (Yugo Nagata)