Re: Drastic performance loss in assert-enabled build in HEAD - Mailing list pgsql-hackers

2013/4/3 Tom Lane <tgl@sss.pgh.pa.us>:

> Kevin Grittner <kgrittn@ymail.com> writes:
>
>> To be honest, I don't think I've personally seen a single use case
>> for matviews where they could be used if you couldn't count on an
>> error if attempting to use them without the contents reflecting a
>> materialization of the associated query at *some* point in time.
>
> Well, if we remove the WITH NO DATA clause from CREATE MATERIALIZED
> VIEW, that minimum requirement is satisfied no?

An argument against that is that computing the contents may be very expensive.

> Granting that throwing an error is actually of some use to some people,
> I would not think that people would want to turn it on via a command
> that throws away the existing view contents altogether, nor turn it off
> with a full-throated REFRESH.  There are going to need to be ways to
> incrementally update matviews, and ways to disable/enable access that
> are not tied to a complete rebuild, not to mention being based on
> user-determined rather than hard-wired criteria for what's too stale.
> So I don't think this is a useful base to build on.

Am I correct when I think that you are saying here, that the “zero
pages == unscannable” logic is not very future-proof? In that case I
concur, and I also think that this knowledge leaks in way too many
other places (the VACUUM bug mentioned by Kevin is a good example).

> If you feel that scannability disable is an absolute must for version 0,
> let's invent a matview reloption or some such to implement it and let
> users turn it on and off as they wish.  That seems a lot more likely
> to still be useful two years from now.

(In the context of making an unlogged matview unscannable after a crash:)

Is it imaginable that such a reloption could (in a future
implementation) be changed during or right after crash recovery? For
example, by storing the set of “truncated by crash recovery” relations
in a shared catalog table, which is then inspected when connecting to
a database to continue the truncation (in the case of a matview by
making it unscannable)?

> And if you're absolutely convinced that unlogged matviews mustn't work as I
> suggest, we can lose those from 9.3, too.

+1. Having unlogged matviews without having incremental updates yet,
isn’t super useful anyway.

> What I'd actually rather see us spending time on right now is making
> some provision for incremental updates, which I will boldly propose
> could be supported by user-written triggers on the underlying tables
> if we only diked out the prohibitions against INSERT/UPDATE/DELETE on
> matviews, and allowed them to operate on a matview's contents just like
> it was a table.  Now admittedly that would foreclose allowing matviews
> to be updatable in the updatable-view sense, but that's a feature I
> would readily give up if it meant users could build incremental update
> mechanisms this year and not two years down the road.

Please make the syntax for updating the “extent” (physical
representation) of a matview different from updating the view’s
logical contents. Examples:

(1) Require to use a special function to update the extent:

SELECT pg_mv_maintain('INSERT INTO example_matview ...');

While parsing the INSERT, the parser would know that it must interpret
“example_matview” as the matview’s extent; As currently the extent and
the view are the same, nothing must be done except for only allowing
the INSERT when it is parsed in the context of pg_mv_maintain, and
otherwise saying that matviews aren’t updatable yet (“NOTICE: did you
mean to update the extent? in that case use pg_mv_maintain”).

(2) Use a different schema (cf. TOAST) for the extent, e.g., view
“public.example_matview” vs. extent “pg_mv_extent.example_matview”. I
imagine future implementations to possibly require multiple extents
anyway, e.g., for storing the “not yet applied changesets” or other
intermediate things.

> Why exactly do you think that what I'm suggesting would cause a dump and
> reload to not regenerate the data?

Expensiveness: Matviews are used in cases where the data is expensive
to compute.

> We *need* to get rid of that aspect of things.  If you must have
> scannability state in version 0, okay, but it has to be a catalog property
> not this.

+1

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?