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

[Eli Mesika]

On Tue, Jul 18, 2017 at 12:17 PM, Yedidyah Bar David <didi at redhat.com>
wrote:

> On Tue, Jul 18, 2017 at 12:10 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
>
> OK. Are we going to? Is there a downside?
>

​Yes we can :-) , no downsides as much as I concern ​


>
> We also might still have a view 'permissions' if we want to support
> old code that reads from it.
>

​Old views will remain the same and you can read from them as usual ​


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