Re: Materialized views WIP patch - Mailing list pgsql-hackers

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