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

[Yedidyah Bar David]

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?

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

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