Contrib: alternative MATERIALIZED VIEWs

From: Nico Williams <nico(at)cryptonector(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Contrib: alternative MATERIALIZED VIEWs
Date: 2017-01-23 23:38:20
Message-ID: 20170123233819.GD1838@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Attached is an alternative implementation of MATERIALIZED VIEWs.

The idea is to explore possible enahncements to the PostgreSQL


- All SQL-coded.

- Keeps history of deltas computed at each refresh.

- Allows DMLs of the materialized view, recording the changes in the
same way as deltas from refreshes.

This allows one to code TRIGGERs which update materialized views

Where synchronous updates of an MV can be fast, a TRIGGER can do it
by querying the source view with additional constraints derived from
the OLD/NEW rows, and then apply DMLs to the materialization table.

- MVs can be marked as needing a refresh.

This is useful where a synchronous update would be too slow. Use a
TRIGGER to mark the MV as needing a refresh and NOTIFY a waiting

- Refreshes have the same concurrency semantics as REFRESH MATERIALIZED

- Allows indices and constraints to be added to the materialzation


- NULLs in columns of the VIEW cause spurious deltas to be recorded for
every refresh. This is because two rows which are equal when
considering NULLs to be equal are... not equal as far as SQL goes,
thus such rows always appear to be deleted and inserted.

This implementation uses a NATURAL FULL OUTER JOIN to compute the
deltas between a before and an after materialization of a view during
a refresh. This avoids having to generate a USING(), which is a very
convenient simplification, but also part of the source of this
problem with NULLs. (The history table has two record-type columns
to hold entire rows.)

- No integration.

- Wonky column names in the history table ("awld", "noo").


- CREATE MATERIALIZED VIEW should have these additional options, and
ALTER MATERIALIZED VIEW should allow these to be specified after

- WITH [UNLOGGED] HISTORY TABLE schema_name.table_name

- WITH PRIMARY KEY (column_list) -- probably not in ALTER MV though

A PK on an MV does make sense when one considers the admonition in
the PG docs to not have duplicate rows in the view... Besides, an
MV has a materialization table, and tables generally should have

- WITH CONSTRAINT <constraint> (same as in ALTER TABLE ADD

Also, a new type of JOIN might be useful: one that joins using only
columns that are part of the PK of both table sources. Obviously this
would not be a generally-applicable JOIN type, as it would not work for
table sources that are subqueries or plain views... But it would be
useful here for generating the FULL OUTER JOIN needed for computing
deltas between tables of the same form.


Attachment Content-Type Size
pseudo_mat_views.sql application/x-sql 38.9 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Nico Williams 2017-01-23 23:40:35 Contrib: pqasyncnotifier.c -- a shell command client for LISTEN
Previous Message Jim Nasby 2017-01-23 23:37:25 Re: Proposal : For Auto-Prewarm.