[ovirt-devel] Fwd: [IMPORTANT] Implementing materialized views

[Martin Perina]

On Tue, Jul 18, 2017 at 11:17 AM, Roy Golan <rgolan at redhat.com> wrote:

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

+1
​


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

Well, not sure if vms, vds​

​views are good candidates for MV as changes to dynamics/statistics are
quite often (so we would need to refresh also MV quite often), so we would
need to do some measurement about those.
​

>
> On Tue, Jul 18, 2017 at 12:11 PM Eli Mesika <emesika at redhat.com> wrote:
>
>> On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi at redhat.com>
>> wrote:
>>
>>> On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina at redhat.com>
>>> wrote:
>>> > Hello,
>>> >
>>> > to make things completely clear: any developer which will perform any
>>> > changes around permissions tables need to use only predefined stored
>>> > procedures for permissions handling. If for some reason direct SQL
>>> update is
>>> > performed, then materialized view will not be refreshed until some
>>> > permission stored procedure is called, which could cause strange
>>> results.
>>>
>>> Isn't it possible to prevent such accidents somehow?
>>>
>>> E.g., is it possible that:
>>> 1. We rename current table ("permissions") to some "private"
>>> name (e.g. "permissions_tab")
>>>
>> ​This is possible ​
>>
>>
>>
>>> 2. We create the materialized view having the name of the
>>> original table ("permissions")
>>>
>>
>> ​The MV replaces the views that uses the permissions table.
>> The plan is to rename the original view to something else and have the
>> created MV with the original view name
>>
>>
>>
>>> 3. We do what's needed (?) so that direct inserts/updates/deletes
>>> on the view either fail or do the right thing.
>>>
>>
>> ​See my answer in 1)
>> ​
>>
>>
>>>
>>> >
>>> > Eli has already removed all such code within patch [3], so this is
>>> just a
>>> > warning for future.
>>> >
>>> > Thanks
>>> >
>>> > Martin
>>> >
>>> >
>>> > On Mon, Jul 17, 2017 at 9:47 PM, Eli Mesika <emesika at redhat.com>
>>> wrote:
>>> >>
>>> >>
>>> >> Materialized Views [1] can be used to reduce query time on complex
>>> queries
>>> >> with low data update
>>> >>
>>> >> The first candidates to use this feature are all the *permission*
>>> views
>>> >>
>>> >> There is already a RFE [2] opened for that.
>>> >>
>>> >> Please make sure that each call that handles the permissions table
>>> data is
>>> >> using the corresponding SP in dbscripts/multi_level_
>>> administration.sql
>>> >> No direct access to the permissions table is allowed !
>>> >>
>>> >> In case that a direct access to the permissions table is used, you
>>> should
>>> >> replace the code in a call to the corresponding SP as you can see in
>>> [3]
>>> >>
>>> >> A direct use that will not be replaced with a call to the
>>> corresponding SP
>>> >> may cause that direct changes to the permissions table will not be
>>> reflected
>>> >> in the
>>> >> *permission* Materialized Views and the views will remain dirty until
>>> a
>>> >> change that is calling one of the SPs that handle the data of the
>>> >> permissions table is issued and cause the Materialized Views to be
>>> refreshed
>>> >>
>>> >> Please check your code for direct use of the permissions table and
>>> consult
>>> >> with me if you have any questions or issues.
>>> >>
>>> >> Thanks
>>> >>
>>> >> Eli Mesika
>>> >>
>>> >> [1] https://wiki.postgresql.org/wiki/Materialized_Views
>>> >> [2] https://bugzilla.redhat.com/show_bug.cgi?id=1470991
>>> >> [3] https://gerrit.ovirt.org/#/c/79287/
>>> >>
>>> >>
>>> >> _______________________________________________
>>> >> Devel mailing list
>>> >> Devel at ovirt.org
>>> >> http://lists.ovirt.org/mailman/listinfo/devel
>>> >
>>> >
>>> >
>>> > _______________________________________________
>>> > Devel mailing list
>>> > Devel at ovirt.org
>>> > http://lists.ovirt.org/mailman/listinfo/devel
>>>
>>>
>>>
>>> --
>>> Didi
>>>
>> _______________________________________________
>> Devel mailing list
>> Devel at ovirt.org
>> http://lists.ovirt.org/mailman/listinfo/devel
>
>
> _______________________________________________
> Devel mailing list
> Devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20170718/cb4b3fba/attachment.html>