Fwd: [IMPORTANT] Implementing materialized views
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/
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. 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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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") 2. We create the materialized view having the name of the original table ("permissions") 3. We do what's needed (?) so that direct inserts/updates/deletes on the view either fail or do the right thing.
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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
On Tue, Jul 18, 2017 at 12:10 PM, Eli Mesika <emesika@redhat.com> wrote:
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
-- Didi
On Tue, Jul 18, 2017 at 12:17 PM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 12:10 PM, Eli Mesika <emesika@redhat.com> wrote:
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com>
wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
-- Didi
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. 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) On Tue, Jul 18, 2017 at 12:11 PM Eli Mesika <emesika@redhat.com> wrote:
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
On Tue, Jul 18, 2017 at 12:17 PM, Roy Golan <rgolan@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.
MV is done over views not table , so it can be {view_name}_mview
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)
Currently to implement a MV we need two conditions 1) View is based on complex/expensive SQL 2) Data is not updated frequently The vms , vds match only the 1st condition
On Tue, Jul 18, 2017 at 12:11 PM Eli Mesika <emesika@redhat.com> wrote:
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
On Tue, Jul 18, 2017 at 11:17 AM, Roy Golan <rgolan@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@redhat.com> wrote:
On Tue, Jul 18, 2017 at 8:56 AM, Yedidyah Bar David <didi@redhat.com> wrote:
On Tue, Jul 18, 2017 at 1:29 AM, Martin Perina <mperina@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@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
-- Didi
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
participants (4)
-
Eli Mesika -
Martin Perina -
Roy Golan -
Yedidyah Bar David