Alternative MATERIALIZED VIEW design and implementation with history table and other features

From: Nico Williams <nico(at)cryptonector(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Alternative MATERIALIZED VIEW design and implementation with history table and other features
Date: 2016-11-23 03:11:28
Message-ID: 20161123031126.GQ32683@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


But we needed a method for recording deltas from REFRESHes, and that's
not supported. So I coded up my own version of materialized views, in
PlPgSQL, that does provide a history feature.

Besides a history feature, this includes the ability to record changes
made to a materialized view's materialization table, which means I can
have triggers that update the materialized view.

We use this for updating a view whose query is a bit slow. Some
triggers are also slow (well, they're fast, but used in transactions
that might potentially run fire these triggers many times), in which
case I mark a "view" as needing a refresh. Other triggers are fast and
directly update the "view".

I'd be willing to do some of the work of integrating this more closely
with PG, but I may need some pointers (but hopefully not much hand-
holding). Ideally we could have CREATE MATERIALIZED VIEW syntax like

CREATE MATERIALIZED VIEW schema_name.view_name
[ ( <column-name> [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS <query>
WITH [ [ UNLOGGED ] HISTORY TABLE [ schema_name.view_name_history ], ]
[ PRIMARY KEY ( <column-name> [, ...] ), ]
[ [ NO ] DATA ];

Of particular interest may be the fact that the FULL OUTER JOIN that PG
does for REFRESH CONCURRENTLY, and which I copied here, doesn't deal
well with views that have NULLs in any columns used in the join. It
would be nice to have an equijoin that uses IS NOT DISTINCT FROM rather
than just =, and then refreshing could use such a join in order to deal
properly with NULLs.

Any help with integration, or comments, even flames, are welcomed, but
keep in mind that this is my first foray into making a contribution to
PG, so please do be kind. Pointers to C and SQL style guides and
standards for in-tree code would be particularly helpful. Thanks!



Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2016-11-23 03:27:31 Re: [WIP] [B-Tree] Keep indexes sorted by heap physical location
Previous Message Tom Lane 2016-11-23 03:04:37 Re: pgsql: Doc: improve documentation about composite-value usage.