Re: Alternative MATERIALIZED VIEW design and implementation with history table and other features - Mailing list pgsql-hackers

From Jim Nasby
Subject Re: Alternative MATERIALIZED VIEW design and implementation with history table and other features
Date
Msg-id f7d5353c-2045-31b7-94bf-6214d9b14d58@BlueTreble.com
Whole thread Raw
In response to Alternative MATERIALIZED VIEW design and implementation with history table and other features  (Nico Williams <nico@cryptonector.com>)
Responses Re: Alternative MATERIALIZED VIEW design and implementation with history table and other features  (Nico Williams <nico@cryptonector.com>)
List pgsql-hackers
On 11/22/16 9:11 PM, Nico Williams wrote:
> 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.

Getting history tracking included in core is going to take a LOT of 
effort; not from a code standpoint, but to get everyone to agree on the 
use cases, interface, etc. In short: I wouldn't go there.

What *would* be useful is work on generating delta information from a 
set of changes to a table: see below.

> 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.

Just looking at the commend block in the file, it's not clear what you 
mean by this. Does this mean if you ALTER the "materialization table" 
(which I assume is the materialized output?), does the view somehow get 
modified?

> 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.

Well, you could potentially just cast the composite types to text and 
join on that, but...

Kevin Grittner (author of the original matviews patch) has mentioned 
detecting changes to underlying relations previously[1]. Getting even a 
simple form of that working is going to be critical for any kind of 
incremental matview updating. It sounds like you're doing something 
different from incremental update, but the core problem is still the 
same: how to efficiently identify changes to underlying matview data and 
apply those changes to the view.

I suggest taking a look at what Kevin's written about that, as well as 
the paper he mentioned. Some of this does assume that you have the 
equivalent to NEW and OLD, but IIRC those are not actually mandatory 
(ie: you could use the current matview contents as OLD and the dynamic 
view as NEW). Even a pure SQL/plpgsql implementation of what's in the 
paper that Kevin mentioned would probably be valuable to ongoing work, 
as well as being applicable to what you've done.

1: 
https://www.postgresql.org/message-id/1371480075.55528.YahooMailNeo@web162901.mail.bf1.yahoo.com
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)



pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: Parallel bitmap heap scan
Next
From: Tom Lane
Date:
Subject: Re: [sqlsmith] Failed assertion in TS_phrase_execute