Re: Materialized views WIP patch

Hi Kevin,

On 15/11/2012 03:28, Kevin Grittner wrote:
> Attached is a patch that is still WIP but that I think is getting
> pretty close to completion.

I've been looking at this, but I unfortunately haven't had as much time
as I had hoped for, and have not looked at the code in detail yet. It's
also a relatively big patch, so I wouldn't mind another pair of eyes on it.

I have been testing the patch a bit, and I'm slightly disappointed by
the fact that it still doesn't solve this problem (and I apologize if I
have missed discussion about this in the docs or in this thread):

<assume "foo" is a non-empty materialized view>

T1: BEGIN;
T1: LOAD MATERIALIZED VIEW foo;

T2: SELECT * FROM foo;

T1: COMMIT;

<T2 sees an empty table>

As others have pointed out, replacing the contents of a table is
something which people have been wanting to do for a long time, and I
think having this ability would make this patch a lot better; now it
just feels like syntactic sugar.

> 1. CREATE MATERIALIZED VIEW syntax is stolen directly from CREATE
> TABLE AS, with all the same clauses supported. That includes
> declaring a materialized view to be temporary or unlogged.
> 2. MVs don't support inheritance.
> 3. MVs can't define foreign keys.
> 4. MVs can't be the target of foreign keys.
> 5. MVs can't have triggers.
> 6. Users can't create rules which reference MVs (although MVs
> [ab]use the rules mechanism internally, similar to how views do).
> 7. MVs can't be converted to views, nor vice versa.
> 8. Users may not directly use INSERT/UPDATE/DELETE on an MV.
> 9. MVs can't directly be used in a COPY statement, but can be the
> source of data using a SELECT.
> 10. MVs can't own sequences.
> 11. MVs can't be the target of LOCK statements, although other
> statements get locks just like a table.
> 12. MVs can't use data modifying CTEs in their definitions.
> 13. pg_class now has a relisvalid column, which is true if an MV is
> truncated or created WITH NO DATA. You can not scan a relation
> flagged as invalid.
> 14. ALTER MATERIALIZED VIEW is supported for the options that seemed
> to make sense. For example, you can change the tablespace or
> schema, but you cannot add or drop column with ALTER.
> 16. To get new data into the MV, the command is LOAD MATERIALIZED
> VIEW mat view_name. This seemed more descriptive to me that the
> alternatives and avoids declaring any new keywords beyond
> MATERIALIZED. If the MV is flagged as relisvalid == false, this
> will change it to true.
> 17. Since the data viewed in an MV is not up-to-date with the latest
> committed transaction, it didn't seem to make any sense to try to
> apply SERIALIZABLE transaction semantics to queries looking at
> the contents of an MV, although if LMV is run in a SERIALIZABLE
> transaction the MV data is guaranteed to be free of serialization
> anomalies. This does leave the transaction running the LOAD
> command vulnerable to serialization failures unless it is also
> READ ONLY DEFERRABLE.
> 18. Bound parameters are not supported for the CREATE MATERIALIZED
> VIEW statement.

I believe all of these points have been under discussion, and I don't
have anything to add to the ongoing discussions.

> 19. LMV doesn't show a row count. It wouldn't be hard to add, it just
> seemed a little out of place to do that, when CLUSTER, etc.,
> don't.

This sounds like a useful feature, but your point about CLUSTER and
friends still stands.

> In the long term, we will probably need to separate the
> implementation of CREATE TABLE AS and CREATE MATERIALIZED VIEW, but
> for now there is so little that they need to do differently it seemed
> less evil to have a few "if" clauses that that much duplicated code.

Seems sensible.

I'll get back when I manage to get a better grasp of the code.

Regards,
Marko Tiikkaja