Since I'm about to have to edit pg_proc.h to add a namespace column, I thought this would be a good time to revise the current proiscachable column into the three-way cachability distinction we've discussed before. But I need some names for the values, and I'm not satisfied with the ideas I've had so far.
To refresh people's memory: what we want is to be able to distinguish between functions that are: 1. Strictly cachable (a/k/a constant-foldable): given fixed input values, the same result value will always be produced, for ever and ever, amen. Examples: addition operator, sin(x). Given a call of such a function with all-constant input values, the system is entitled to fold the function call to a constant on sight. 2. Cachable within a single command: given fixed input values, the result will not change if the function were to be repeatedly evaluated within a single SQL command; but the result could change over time. Examples: now(); datetime-related operations that depend on the current timezone (or other SET-able variables); any function that looks in database tables to determine its result. 3. Totally non-cachable: result may change from one call to the next, even within a single SQL command. Examples: nextval(), random(), timeofday(). (Yes, timeofday() and now() are in different categories. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) Currently the system can only distinguish cases 1 and 3, so functions that are really case 2 have to be labeled as case 3; this prevents a lot of useful optimizations. In particular, it is safe to use expressions involving only case-1 and case-2 functions as indexscan conditions, whereas case-3 functions cannot be optimized into an indexscan. So this is an important fix to make. BTW, because of MVCC semantics, case 2 covers more ground than you might think. We are interested in functions whose values cannot change during a single "scan", ie, while the intra-transaction command counter does not increment. So functions that do SELECTs are actually guaranteed to be case 2, even if stuff outside the function is changing the table being looked at. My problem is picking names for the three categories of functions. Currently we use "with (isCachable)" to identify category 1, but it seems like this name might actually be more sensible for category 2. I'm having a hard time picking simple names that convey these meanings accurately, or even with a reasonable amount of suggestiveness. Comments, ideas? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
