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)