Timeline for PostgreSQL: Immutable, Volatile, Stable
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 15 at 10:16 | comment | added | Craig Ringer | @Azerum The strictest correct level. Falsely declaring a function IMMUTABLE can have ... exciting ... effects on your data consistency and query correctness. I'm a bit surprised that changing the definition of your jsonb_array_to_array changed this specific execution, but I'm a bit outdated these days. | |
| Jun 10 at 21:45 | comment | added | Azerum | I had a query like select xyz from foo where bar_id = any(jsonb_array_to_array('["some", "data"]')), jsonb_array_to_array being custom pure function. Without adding IMMUTABLE, the query did not use index on bar_id. I'm guessing that since default is VOLATILE, Postgres assumed that the function were supposed to run for every row in foo. A reason to choose strictest level | |
| Jun 9, 2024 at 23:56 | comment | added | Craig Ringer | @PeterKrauss Not at present. Some kind of prover would be possible but I don't think anything like that has been implemented. | |
| Jun 9, 2024 at 11:17 | comment | added | Peter Krauss | In nowadays, with a sophisticated parser, or another possible "external tools" (with some AI), is possible to automate this IMMUTABLE/STABLE/VOLATILE "function tag"? PS: seems that is also important for CPU performance, when the JIT parser rewrite IMMUTABLE SQL-function as part of the query, or parallelize it. | |
| Jun 1, 2023 at 11:41 | comment | added | Rafs | As to: In general, just leave everything VOLATILE unless you have a good reason not to Postgres recommends: "For best optimization results, you should label your functions with the strictest volatility category that is valid for them." src | |
| S Sep 22, 2020 at 20:57 | history | suggested | jberryman | CC BY-SA 4.0 | add link for "pure function" for those who might not catch that this has a formal meaning |
| Sep 22, 2020 at 17:40 | review | Suggested edits | |||
| S Sep 22, 2020 at 20:57 | |||||
| Sep 8, 2017 at 13:29 | vote | accept | Brooks | ||
| Sep 6, 2017 at 0:51 | comment | added | Craig Ringer | Don't remember off the top of my head, would have to check docs/code. | |
| Sep 5, 2017 at 15:12 | comment | added | Brooks | If STABLE allows table access, are there any optimizations over/above VOLATILE...? | |
| Sep 5, 2017 at 3:26 | comment | added | Evan Carroll | "they cannot access tables." In fairness, they can and they do. I think a more general rule is that the tables are not supposed to meaningfully mutate without database-restart. | |
| Sep 5, 2017 at 2:31 | history | answered | Craig Ringer | CC BY-SA 3.0 |