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.yahoomail...@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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
