[GENERAL] Can this function be declared IMMUTABLE?
Hello, I have a question about whether I can safely declare a function IMMUTABLE. Citing the 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 read 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Can this function be declared IMMUTABLE?
On 8/27/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Well, I am considering a function that does read 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. make the function STABLE instead 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. do you know that early optimization is the root of all evil? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can this function be declared IMMUTABLE?
On 8/27/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello, I have a question about whether I can safely declare a function IMMUTABLE. Citing the 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 read 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. Lemme see if I can embarrass myself trying to answer something like this. It seems like your function really ought to be declared STABLE, because during a single transaction MVCC will make sure your function sees the same values in its references table each time you call it, but between transactions the reference table might change, changing the result of the function. The benefits of an IMMUTABLE function over a STABLE one, as far as I know, are these: 1) The planner can take advantage of the fact that this function is IMMUTABLE to evaluate it only once if its arguments are constant, and keep this result throughout the life of the query plan. If you don't cache the query plan (e.g. with PREPARE) this is identical to STABLE, but if you PREPARE a query, for instance, involving a call to an IMMUTABLE function with constant arguments, the system can evaluate the function only once during planning, and never again, whereas for STABLE you'd have to execute the function each time it was called. For this to be a big win over STABLE, you have to both call your function with constant arguments and cache the query plan somehow, such as by having the query inside another pl/pgsql function or by using PREPARE. 2) You can use IMMUTABLE functions, but not STABLE ones, in expression-based indexes. If you declared your function IMMUTABLE, you could build an index on a bunch of data using an index expression involving your function, have your referenced table change somehow, and end up not being able to correctly use that index anymore. There may well be other advantages of IMMUTABLE over STABLE that I don't know about. Were we talking about data I was supposed to care for, I'd make the function STABLE, not IMMUTABLE, because that's the most appropriate for the function. -Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Can this function be declared IMMUTABLE?
In response to [EMAIL PROTECTED]: 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Can this function be declared IMMUTABLE?
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can this function be declared IMMUTABLE?
Tom Lane [EMAIL PROTECTED] writes: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
