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

[Eli Mesika]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20170718/cc9daf45/attachment-0001.html>