Обсуждение: Can this function be declared IMMUTABLE?
Hello, I have a question about whether I can safely declare a function IMMUTABLE. Citing the PostgreSQL documentation under "Function Volatility Categories" in the section on "Extending SQL": ---------------------------------------- It is generally unwise to select from database tables within an IMMUTABLE function at all, since the immutability will be broken if the table contents ever change. ---------------------------------------- Well, I am considering a function that does select from a table, but the table contents change extremely infrequently (the table is practically a list of constants). Would it be safe to declare the function IMMUTABLE provided that the table itself is endowed with a trigger that will drop and recreate the function any time the table contents are modified? In this way, it seems that the database would gain the performance benefit of an immutable function for the long stretches of time in between changes to the table. I apologize that I don't have any details -- it is still very early in the development of the database design, and I was just hoping to get a better understanding of whether an immutable function would safely offer any benefit in this scenario. Thanks very much, Bobby
In response to beickhof@Lexmark.com: > Hello, > > I have a question about whether I can safely declare a function IMMUTABLE. > Citing the PostgreSQL documentation under "Function Volatility > Categories" in the section on "Extending SQL": > ---------------------------------------- > It is generally unwise to select from database tables within an IMMUTABLE > function at all, since the immutability will be broken if the table > contents ever change. > ---------------------------------------- > > Well, I am considering a function that does select from a table, but the > table contents change extremely infrequently (the table is practically a > list of constants). Would it be safe to declare the function IMMUTABLE > provided that the table itself is endowed with a trigger that will drop > and recreate the function any time the table contents are modified? In > this way, it seems that the database would gain the performance benefit of > an immutable function for the long stretches of time in between changes to > the table. Is this a table that will only change during upgrades/maintenance? If so, then immutable is probably safe, as the table will change under controlled circumstances. The utmost gauge of this is "what happen if the function is immutable and the data _does_ change?" if the result of such a scenario is acceptable, then you can probably use immutable. Another rule to take into account is the Law of Premature Optimization. The law states that trying to optimize too soon will cause pain. Have you determined that the extra performance gain that immutable will give you is even necessary? If not, then start out with a more conservative approach and approach the immutability problem _if_ you see performance issues. -- Bill Moran http://www.potentialtech.com
beickhof@Lexmark.com writes: > Well, I am considering a function that does select from a table, but the > table contents change extremely infrequently (the table is practically a > list of constants). Would it be safe to declare the function IMMUTABLE > provided that the table itself is endowed with a trigger that will drop > and recreate the function any time the table contents are modified? What you'd want to do is restart all existing sessions so that any plans made using precomputed function values are discarded. The trigger you suggest is fairly pointless because it will not cause regeneration of plans. I concur with Bill's remark that you should first determine if there's a really substantial benefit to marking the function immutable rather than merely stable. regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > beickhof@Lexmark.com writes: >> Well, I am considering a function that does select from a table, but the >> table contents change extremely infrequently (the table is practically a >> list of constants). Would it be safe to declare the function IMMUTABLE >> provided that the table itself is endowed with a trigger that will drop >> and recreate the function any time the table contents are modified? > > What you'd want to do is restart all existing sessions so that any plans > made using precomputed function values are discarded. The trigger you > suggest is fairly pointless because it will not cause regeneration of > plans. The trigger would alert him if there were any indexes built using the function... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com