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