Noah Misch wrote:

The patch conflicts with git master; I tested against master@{2013-01-20}.
New patch rebased, fixes issues raised by Thom Brown, and addresses
some of your points.
PostgreSQL 8.3 clusters won't contain materialized views, so it doesn't really
matter whether this change happens or not. I suggest adding a comment,
whether or not you keep the code change.
Reverted code changes to version_old_8_3.c; added comments.
*** a/contrib/sepgsql/sepgsql.h
--- b/contrib/sepgsql/sepgsql.h
*** 32,37 ****
--- 32,39 ----

* Internally used code of object classes
+ *
+ * NOTE: Materialized views are treated as tables for now.
This smells like a bypass of mandatory access control. Unless you've
determined that this is correct within the sepgsql security model, I suggest
starting with a draconian policy, like simply crippling MVs. Even if you have
determined that, separating out the nontrivial sepgsql support might be good.
The set of ideal reviewers is quite different.
Robert suggested this way of coping for now. Will post just the
sepgsql separately to try to attract the right crowd to confirm.
It concerns me slightly that older vacuumlo could silently remove large
objects still referenced by MVs. Only slightly, though, because the next MV
refresh would remove those references anyway. Is there anything we should do
to help that situation? If nothing else, perhaps backpatch this patch hunk.
Defensive backpatching of this code sounds like a good idea to me.
I'm open to other opinions on whether we need to defend 9.3 and
later against earler versions of vacuumlo being run against them.
Let's not support OIDs on MVs. They'll be regenerated on every refresh.
Do they have any value for people who might want to use cursors? If
nobody speaks up for them, I will drop OID support for materialized
If I'm reading this right, you always overwrite the passed-in dest without
looking at it. What's the intent here?
Let me get back to you on that one.
+ /*
+ * Kludge here to allow refresh of a materialized view which is invalid
+ * (that is, it was created WITH NO DATA or was TRUNCATED). We flag the
+ * first two RangeTblEntry list elements, which were added to the front
+ * of the rewritten Query to keep the rules system happy, with the
+ * isResultRel flag to indicate that it is OK if they are flagged as
+ * invalid.
+ */
+ rtable = dataQuery->rtable;
+ ((RangeTblEntry *) linitial(rtable))->isResultRel = true;
+ ((RangeTblEntry *) lsecond(rtable))->isResultRel = true;
Is it safe to assume that the first two RTEs are the correct ones to flag?
I'm trying to play along with UpdateRangeTableOfViewParse() in
view.c. See the comment in front of that function for details.
+ finish_heap_swap(matviewOid, OIDNewHeap, false, false, false, RecentXmin);
The check_constraints argument should be "true", because the refresh could
have invalidated a UNIQUE index. Fixed.
If the user desires an actually-clustered MV, he must re-CLUSTER it after each
refresh. That deserves a documentation mention.
That point had not occurred to me. Let me see if I can fix that before changing docs.
+ * Ensure that all referrenced relations are flagged as valid.
+ ExecCheckRelationsValid(rangeTable);
I believe this ought to happen after the executor lock acquisitions, perhaps
right in ExecOpenScanRelation(). Since you'll then have an open Relation,
RelationIsFlaggedAsValid() can use the relcache.
That would break MVs entirely. This probably deserves more
comments. It's a little fragile, but was the best way I found to
handle things. An MV has a rule associated with it, just like a
"regular" view, which is parse-analyzed but not rewritten or
planned. We need to allow the rewrite and planning for statements
which populate the view, but suppress expansion of the rule for
simple references. It is OK for an MV to be invalid if it is being
populated, but not if it is being referenced. Long story short,
this call helps determine which relations will be opened.

If someone can suggest a better alternative, I'll see what I can
do; otherwise I guess I should add comments around the key places.
*** 1591,1596 **** fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
--- 1592,1607 ----
rel = heap_open(rte->relid, NoLock);

+ * Skip materialized view expansion when resultRelation is set.
+ */
+ if (rel->rd_rel->relkind == RELKIND_MATVIEW &&
+ rel->rd_rel->relisvalid)
+ {
+ heap_close(rel, NoLock);
+ break;
+ }
Would you elaborate on this?
It's diretly related to the point immediately preceding. At this
point we have thrown an error if the MV is invalid and being used
as a source of data. If it is the target of data it is flagged as
invalid so that it will not be expanded. Maybe we need a better way
to determine this, but I'm not sure just what to use.
+ /* Strip off the trailing semicolon so that other things may follow. */
+ appendBinaryPQExpBuffer(result, PQgetvalue(res, 0, 0), len - 1);
I suggest verifying that the last character is indeed a semicolon.
How about if I have it exit_horribly if the semicolon added 21
lines up has disappeared? Or use Assert if we have that for the
frontend now?
+ static void
+ dumpMatViewIndex(Archive *fout, IndxInfo *indxinfo)
This is so similar to dumpIndex(); can we avoid this level of duplication?
It is identical except for name. I can only assume that I thought I
needed a modified version and changed my mind. Removed.
Please retain an interesting sample of materialized views in the regression
database. Among other benefits, the pg_upgrade test suite exercises pg_dump
and pg_upgrade for all object types retained in the regression database. OK
The regression tests should probably include a few other wrinkles, like an
index on a MV.
Yeah. Will do.
Creating a RULE on an MV succeeds, but refreshing the view then fails:
Fixed by prohibiting CREATE RULE on an MV.
The documentation is a good start. I would expect a brief introduction in
Tutorial -> Advanced Features and possibly a deeper discussion under The SQL
Language. I suggest updating Explicit Locking to mention the new commands;
users will be interested in the lock level of a refresh.
Yeah, the docs need another pass. It seemed prudent to make sure of
what I was documenting first.
You have chosen to make pg_dump preserve the valid-or-invalid state of each
MV. That seems reasonable, though I'm slightly concerned about the case of a
dump taken from a standby.
I'm not clear on the problem. Could you explain?
Some of the ALTER TABLE variants would make plenty of sense for MVs:

ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

It wouldn't be a problem to skip those for the first patch, though.
Conversely, this syntax is accepted:

ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )

But there are no available options. The only option accepted for regular
views, security_barrier, is rejected. MVs always have security_barrier
semantics, in any event.
I think those are doc problems, not implementation of the
functionality. Will double-check and fix where needed.
Overall, I recommend auditing all the ALTER TABLE and ALTER VIEW options to
determine which ones make sense for MVs. For each one in the sensible set,
either allow it or add a comment indicating that it could reasonably be
allowed in the future. For each one outside the set, forbid it. Verify that
the documentation, the results of your evaluation, and the actual allowed
operations are all consistent.
I have already tried to do that in the coding, although maybe you
think more comments are needed there? The docs definitely need to
catch up. This part isn't in flux, so I'll fix that part of the
docs in the next day or two.

Thanks for the review!


Search Discussions

Discussion Posts


Follow ups

Related Discussions



site design / logo © 2021 Grokbase