Bug 1007564

Summary: Database functions not fully optimized
Product: Red Hat Enterprise Virtualization Manager Reporter: rhev-integ
Component: ovirt-engineAssignee: Eli Mesika <emesika>
Status: CLOSED ERRATA QA Contact: Pavel Stehlik <pstehlik>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 3.2.0CC: acathrow, amureini, bazulay, emesika, iheim, jkt, lpeer, mkalinin, pstehlik, Rhev-m-bugs, yeylon
Target Milestone: ---Keywords: ZStream
Target Release: 3.2.4   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard: infra
Fixed In Version: Doc Type: Bug Fix
Doc Text:
This update takes advantage of PostgreSQL's ability to mark the volatility and strictness of a function, by setting all functions that only select data as stable, and by setting all functions that do not change the database as immutable.
 Story Points: ---
Clone Of: Environment:
Last Closed: 2013-10-25 18:43:54 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: Infra RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On: 905383    
Bug Blocks:    

Description rhev-integ 2013-09-12 18:40:38 UTC 
+++ This bug is a RHEV-M zstream clone. The original bug is: +++
+++   https://bugzilla.redhat.com/show_bug.cgi?id=905383. +++

======================================================================



----------------------------------------------------------------------
Following comment by lvernia on January 29 at 09:57:33, 2013

Description of problem:

PostgreSQL enables marking the volatility and strictness of a function, which in turn might improve the running time of queries. We currently do not take advantage of this, and it might be worthwhile to mark functions this way.

Note on compatibility: while this is specific to PostgreSQL, it should be easy to write a script that drops all occurrences of IMMUTABLE, STABLE etc. if we ever move to another database implementation.


Additional info:

Volatility
----------
* A function should be marked as IMMUTABLE if it doesn't change the database, and if it doesn't perform any lookups (even for database configuration values) during its operation.
* A function should be marked STABLE if it doesn't change the database, but might perform lookups (IMMUTABLE is preferable if function meets the requirements).
* A function doesn't need to be marked VOLATILE, because that's the default.

STRICTNESS
----------
A function should be marked STRICT if it should return NULL when it is passed a NULL argument, and then the function won't even be called if it is indeed passed a NULL argument.

----------------------------------------------------------------------
Following comment by bazulay on June 30 at 14:36:38, 2013

The fix for that requires pre-integration as it touches many of our functions & stored-procedures.

----------------------------------------------------------------------
Following comment by amureini on August 21 at 12:42:06, 2013

Barak/Eli, is there any chance to get this for z-stream too (perhaps 3.2.4?).

There are several z-stream issues around DB performance, and this can be a considerable boost, especially considering the slow-changing nature of our system.

----------------------------------------------------------------------
Following comment by emesika on August 21 at 14:11:20, 2013

(In reply to Allon Mureinik from comment #2)
> Barak/Eli, is there any chance to get this for z-stream too (perhaps 3.2.4?).
> 
> There are several z-stream issues around DB performance, and this can be a
> considerable boost, especially considering the slow-changing nature of our
> system.

I think that it could be done

----------------------------------------------------------------------
Following comment by emesika on August 26 at 09:42:57, 2013

fixed in commit : df5cf3a
Comment 4 errata-xmlrpc 2013-10-25 18:43:54 UTC 
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

http://rhn.redhat.com/errata/RHBA-2013-1468.html