Re: Materialized views WIP patch

On Fri, Jan 25, 2013 at 01:00:59AM -0500, Kevin Grittner wrote:
> Noah Misch wrote:
> > > *** 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.

Sounds good.

> > 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?

Not that I have heard, for whatever that's worth.

> > > + /*
> > > + * 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.

Ah. Perhaps assert that those RTEs have the aliases "old" and "new"?

> >> + 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.

I see. It seems wrong to check MV validity before the executor locks a table;
if the executor lock were in fact the first lock on the relation, then the
view could become invalid again before we lock it. I don't know a way to
actually make the executor's lock be the first lock; some parser or planner
lock invariably seems to precede it. Is that proper to rely on?

> >> + /* 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?

The code 21 lines back is adding a semicolon to a query being sent to the
server to retrieve the view's definition. Here you're removing the trailing
semicolon from a column of the server's response. Granted, there's not much
reason we'd ever change the server to omit the trailing semicolon, and the
breakage would be relatively obvious even without an explicit test here.

> > 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?

Currently none. If you took my suggestion regarding relisvalid, then MVs
would be considered invalid on the standby. That is one disadvantage of the
suggestion, perhaps.

> > 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 [, ... ] )
> > ??ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
> >
> > 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.

Looks so.

> > 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.

I won't worry about such comments for now; it looks like you're targeting most
of the reasonable-to-support ALTER operations. I just didn't realize that the
docs were out of date in this respect.

By the way, your mailer omits References: and In-Reply-To: headers, breaking
the thread.

nm