On Fri, Jan 20, 2017 at 2:08 PM, Nico Williams <n...@cryptonector.com> wrote: > On Fri, Jan 20, 2017 at 01:37:33PM -0600, Kevin Grittner wrote:
>> There is currently plenty of room for pseudo-MV implementations, >> and may be for a while. It's a good indication of the need for the >> feature in core. An implementation in the guts of core can have >> advantages that nothing else can, of course. For example, for >> eager application of the deltas, nothing will be able to beat >> capturing tuples already in RAM and being looked at for possible >> trigger firing into a RAM-with-spill-to-disk tuplestore. > > BTW, automatic updates of certain types of MVs should be easy: add > constraints based on NEW/OLD rows from synthetic triggers to the > underlying query. Convincing me that this is a good idea for actual MVs, versus pseudo-MVs using tables, would be an uphill battle. I recognize the need to distinguish between MVs which contain recursive CTEs in their definitions and MVs that don't, so that the DRed algorithm can be used for the former and the counting algorithm for the latter; but firing triggers for row-at-a-time maintenance is not going to be efficient for very many cases, and the cost of identifying those cases to handle them differently is probably going to exceed any gains. Comparative benchmarks, once there is an implementation using set-based techniques, could potentially convince me; but there's not much point arguing about it before that exists. :-) > However, there is a bug in the query planner that prevents this > from being very fast. At some point I want to tackle that bug. What bug is that? > Basically, the planner does not notice that a table source in a > join has a lookup key sufficiently well-specified by those additional > constraints that it should be the first table source in the outermost > loop. Is that a description of what you see as the bug? Can you give an example, to clarify the point? I am dubious, though, of the approach in general, as stated above. >> I don't have time to review what you've done right now, but will >> save that link to look at later, if you give permission to borrow >> from it (with proper attribution, of course) if there is something >> that can advance what I'm doing. If such permission is not >> forthcoming, I will probably avoid looking at it, to avoid any >> possible copyright issues. > > Our intention is to contribute this. We're willing to sign > reasonable contribution agreements. Posting a patch to these lists constitutes an assertion that you have authority to share the IP, and are doing so. Referencing a URL is a bit iffy, since it doesn't leave an archival copy of the contribution under the community's control. > I'd appreciate a review, for sure. Thanks! Would it be possible to get your approach running using tables and/or (non-materialized) views as an extension? A trigger-based way to maintain pseudo-MVs via triggers might make an interesting extension, possibly even included in contrib if it could be shown to have advantages over built-in MVs for some non-trivial applications. > There's a gotcha w.r.t. NULL columns, but it affects the built-in > REFRESH as well, IIRC. The commentary in our implementation > discusses that in more detail. Could you report that on a new thread on the lists? I've seen comments about such a "gotcha", but am not clear on the details. It probably deserves its own thread. Once understood, we can probably fix it. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
