Re: - GSoC - snapshot materialized view (work-in-progress) patch - Mailing list pgsql-hackers

<div class="moz-text-flowed" lang="x-central-euro" style="font-family: -moz-fixed; font-size: 12px;">Dne 9.7.2010
21:33,Robert Haas napsal(a): <br /><blockquote style="color: rgb(0, 0, 0);" type="cite">2010/7/8 Pavel Baroš<a
class="moz-txt-link-rfc2396E"href="mailto:baros.p@seznam.cz"><baros.p@seznam.cz></a>: <br /><blockquote
style="color:rgb(0, 0, 0);" type="cite">Description of patch: <br /> 1) can create MV, and is created uninitialized
withdata <br />    CREATE MATERIALIZED VIEW mvname AS SELECT ... <br /></blockquote><br /> This doesn't seem
acceptable. It should populate it on creation. <br /><br /></blockquote><br /> Yes, it would be better, in addition,
trueis, this behavior will be required if is expected to implement incremental MV in the close future. <br /><br
/><blockquotestyle="color: rgb(0, 0, 0);" type="cite"><blockquote style="color: rgb(0, 0, 0);" type="cite">2) can
refreshMV <br />    ALTER MATERIALIZED VIEW mvname REFRESH <br /><br /> 3) MV cannot be modified by DML commands
(INSERT,UPDATE and DELETE are not <br /> permitted) <br /><br /> 4) index can be created and used with MV <br /><br />
5)pg_dump is repaired, in previous patch dump threw error, now dont, but it <br /> is sort of dummy, I want to reach
state,where refreshing command will be <br /> posed after all COPY statements (when all data are in tables). In this
patch<br /> REFRESH command is right behind CREATE MV command. <br /></blockquote><br /> Hmm... ISTM that you probably
needsome kind of dependency stuff in <br /> here to make the materialized view get created after the tables it <br />
dependson have been populated with data.  It needs to work with <br /> parallel restore, too.  I'm not sure exactly how
thedependency stuff <br /> in pg_dump works, though. <br /><br /></blockquote><br /> never mind in case MV will be
populatedon creation. <br /><br /><blockquote style="color: rgb(0, 0, 0);" type="cite">A subtle point here is that if
youdump and restore a database <br /> containing a materialized view, the new database might not be quite <br /> the
sameas the old one, because the materialized view might have been <br /> out of date before, and when you recreate it,
it'llget refreshed. <br /> I'm not sure there's much we can/should do about that, though. <br /><br /></blockquote><br
/>yes, it is interesting, of course, there can be real-life example, where population on creating is needed and is not,
andI'm thinking of solution similar to Oracle or DB2. Add some option to creating MV, that enable/disable population on
creating:<br /><br /><a class="moz-txt-link-freetext"
href="http://www.ibm.com/developerworks/data/library/techarticle/dm-0708khatri/">http://www.ibm.com/developerworks/data/library/techarticle/dm-0708khatri/</a><br
/><br/> Oracle: <br />   CREATE MATERIALIZED VIEW mvname <br />   [ BUILD [IMMEDIATE | DEFERRED] ] <br />   AS SELECT
..<br /><br /> DB2: <br />   CREATE TABLE mvname <br />   AS SELECT ... <br />   [ INITIALLY DEFERRED | IMMEDIATE ] <br
/><br/><blockquote style="color: rgb(0, 0, 0);" type="cite"><blockquote style="color: rgb(0, 0, 0);" type="cite">6)
psqlworks too, new command \dm[S+] was added to the list <br />   \d[S+] [PATTERN]   - lists all db objects like
tables,view, materialized <br /> view and sequences <br />   \dm[S+] [PATTERN]  - lists all materialized views <br
/><br/></blockquote></blockquote><br /> I also noticed I forgot handle options \dp and \dpp, this should be OK in next
versionof patch. <br /><br /><blockquote style="color: rgb(0, 0, 0);" type="cite"><blockquote style="color: rgb(0, 0,
0);"type="cite">7) there are some docs too, but I guess it is not enough, at least my <br /> english will need to
correct<br /></blockquote><br /> If we're going to treat materialized views as a separate object type, <br /> you
probablyneed to break out the docs for CREATE MATERIALIZED VIEW, <br /> ALTER MATERIALIZED VIEW, and DROP MATERIALIZED
VIEWinto their own <br /> pages, rather than having then mixed up with corresponding pages for <br /> regular views.
<br/><br /></blockquote><br /> Yeah, that was problem I just solved like that here, but I confess this would be better.
<br/><br /><br /><blockquote style="color: rgb(0, 0, 0);" type="cite"><blockquote style="color: rgb(0, 0, 0);"
type="cite">Inprogress: <br /> - regression tests <br /> - behavior of various ALTER commands, ie SET STATISTIC,
CLUSTERON, <br /> ENABLE/DISABLE RULE, etc. <br /></blockquote><br /> This isn't right: <br /><br /> rhaas=# create
viewv as select * from t; <br /> CREATE VIEW <br /> rhaas=# alter view v refresh; <br /> ERROR:  unrecognized alter
tabletype: 41 <br /><br /></blockquote><br /> I know, cases like that will be more than that. Thats why I work on good
testsnow. <br /><br /><blockquote style="color: rgb(0, 0, 0);" type="cite">Please add your patch here, so that it will
bereviewed during the <br /> about-to-begin CommitFest. <br /><br /><a class="moz-txt-link-freetext"
href="https://commitfest.postgresql.org/action/commitfest_view/open">https://commitfest.postgresql.org/action/commitfest_view/open</a><br
/><br/></blockquote><br /> OK, but will you help me with that form? Do you think I can fill it like that? I'm not sure
aboutfew fields .. <br /><br /> Name:             Snapshot materialized views <br /> CommitFest Topic: [ Miscellaneous
|SQL Features ] ??? <br /> Patch Status:     Needs review <br /> Author:           me <br /> Reviewers:        You? <br
/>Commiters:        who? <br /><br /> and I quess fields 'Date Closed' and 'Message-ID for Original Patch' will be
filledlater. <br /><br /><br /> thanks a lot <br /><br /><br /> Pavel Baros <br /><br /></div>