[Site Bug] Issue with the page: /api/latest/hypertable/set_integer_now_func/

[Splarv]

Why in example you create the function with attribute IMMUTABLE? And with "Important" that this will incorrectly work. The function that return current time always will be not IMMUTABLE (return value depend only on arguments). It will be STABLE (if this time from the begin of a transaction) or UNSTABLE (if this time is very current). And in documentation must be said what varian is correct.

[billy-the-fish]

@jonatas could you suggest a improved example for https://docs.timescale.com/api/latest/hypertable/set_integer_now_func/#sample-usage please.

[jonatas]

Hi @Splarv, thanks for the comment, I think we have reasons why it needs to be immutable. Here's an example of query plan slowing down issue that can help you understand.

The reason for the lateness is your unix_now() function. Constraints in postgres can be constified at different stages in the planning process. now() for example can only be constified during execution as planning time and execution time can differ due to prepared statements. If you were to precalculate unix_now() outside of the query or mark unix_now() as immutable (PostgreSQL: Documentation: 14: 38.7. Function Volatility Categories ) the chunk exclusion could happen much earlier and you should see reduction in planning time.

About the "important", I'd love to involve @mkindahl here, to share more technical details that can enrich the conversation and later the docs 🤓

[mkindahl]

Quickly sorting through the different issues here:

• The function to set_integer_now_func has to be either IMMUTABLE or STABLE (and take no arguments), which is not clear. I think we should make that clear in the documentation.
• The case for using an IMMUTABLE function is when you will execute the query each time and not prepare it beforehand. In this case, the value for the function will be computed before the plan is generated, which will generate a significantly smaller plan, especially if you have a lot of chunks. However, the context for this is missing and it might be good to first state why you would like to do anything like this. Right now it comes out of the blue.
• Normally, time functions are STABLE since they need to be evaluated just before execution starts. This will do the chunk pruning at runtime instead, which will generate a correct result, but might increase planning time. We should probably have this as an example as well. In this case, it is more interesting when you have a time representation that is not a normal time type and instead, e.g., an integer.

[billy-the-fish]

Hi,
I've updated the docs and created a DRAFT PR: https://github.com/timescale/docs/pull/3135/files. Could one of you supply an example for a STABLE function please. Review comments all welcome :-).