<div dir="ltr"><div><br></div><div>I think that a convention of {table_name}_MVIEW should be clear enough to prevent us from trying to write insert/delete/update on it.<br><br></div><div>In general I like the idea and I wonder if it will help with the vms,vds tables under load (could be worse to keep the view refreshed in fact because of frequent updates) <br></div></div><br><div class="gmail_quote"><div dir="ltr">On Tue, Jul 18, 2017 at 12:11 PM Eli Mesika <<a href="mailto:emesika@redhat.com">emesika@redhat.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <span dir="ltr"><<a href="mailto:didi@redhat.com" target="_blank">didi@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span>On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <<a href="mailto:mperina@redhat.com" target="_blank">mperina@redhat.com</a>> wrote:<br>
> Hello,<br>
><br>
> to make things completely clear: any developer which will perform any<br>
> changes around permissions tables need to use only predefined stored<br>
> procedures for permissions handling. If for some reason direct SQL update is<br>
> performed, then materialized view will not be refreshed until some<br>
> permission stored procedure is called, which could cause strange results.<br>
<br>
</span>Isn't it possible to prevent such accidents somehow?<br>
<br>
E.g., is it possible that:<br>
1. We rename current table ("permissions") to some "private"<br>
name (e.g. "permissions_tab")<br></blockquote></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><div style="font-size:large" class="gmail_default">This is possible </div><br> </div></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
2. We create the materialized view having the name of the<br>
original table ("permissions")<br></blockquote></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><br><div style="font-size:large" class="gmail_default">The MV replaces the views that uses the permissions table.<br></div><div style="font-size:large" class="gmail_default">The plan is to rename the original view to something else and have the created MV with the original view name <br></div><br> </div></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
3. We do what's needed (?) so that direct inserts/updates/deletes<br>
on the view either fail or do the right thing.<br></blockquote></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><br><div style="font-size:large;display:inline" class="gmail_default">See my answer in 1)<br></div> </div></div></div></div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="m_-2021737174417624578HOEnZb"><div class="m_-2021737174417624578h5"><br>
><br>
> Eli has already removed all such code within patch [3], so this is just a<br>
> warning for future.<br>
><br>
> Thanks<br>
><br>
> Martin<br>
><br>
><br>
> On Mon, Jul 17, 2017 at 9:47 PM, Eli Mesika <<a href="mailto:emesika@redhat.com" target="_blank">emesika@redhat.com</a>> wrote:<br>
>><br>
>><br>
>> Materialized Views [1] can be used to reduce query time on complex queries<br>
>> with low data update<br>
>><br>
>> The first candidates to use this feature are all the *permission* views<br>
>><br>
>> There is already a RFE [2] opened for that.<br>
>><br>
>> Please make sure that each call that handles the permissions table data is<br>
>> using the corresponding SP in dbscripts/multi_level_administration.sql<br>
>> No direct access to the permissions table is allowed !<br>
>><br>
>> In case that a direct access to the permissions table is used, you should<br>
>> replace the code in a call to the corresponding SP as you can see in [3]<br>
>><br>
>> A direct use that will not be replaced with a call to the corresponding SP<br>
>> may cause that direct changes to the permissions table will not be reflected<br>
>> in the<br>
>> *permission* Materialized Views and the views will remain dirty until a<br>
>> change that is calling one of the SPs that handle the data of the<br>
>> permissions table is issued and cause the Materialized Views to be refreshed<br>
>><br>
>> Please check your code for direct use of the permissions table and consult<br>
>> with me if you have any questions or issues.<br>
>><br>
>> Thanks<br>
>><br>
>> Eli Mesika<br>
>><br>
>> [1] <a href="https://wiki.postgresql.org/wiki/Materialized_Views" rel="noreferrer" target="_blank">https://wiki.postgresql.org/wiki/Materialized_Views</a><br>
>> [2] <a href="https://bugzilla.redhat.com/show_bug.cgi?id=1470991" rel="noreferrer" target="_blank">https://bugzilla.redhat.com/show_bug.cgi?id=1470991</a><br>
>> [3] <a href="https://gerrit.ovirt.org/#/c/79287/" rel="noreferrer" target="_blank">https://gerrit.ovirt.org/#/c/79287/</a><br>
>><br>
>><br>
>> _______________________________________________<br>
>> Devel mailing list<br>
>> <a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br>
>> <a href="http://lists.ovirt.org/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman/listinfo/devel</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> Devel mailing list<br>
> <a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br>
> <a href="http://lists.ovirt.org/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman/listinfo/devel</a><br>
<br>
<br>
<br>
</div></div><span class="m_-2021737174417624578HOEnZb"><font color="#888888">--<br>
Didi<br>
</font></span></blockquote></div></div></div>
_______________________________________________<br>
Devel mailing list<br>
<a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman/listinfo/devel</a></blockquote></div>