Is a stable function guaranteed to produce the same result in a query?-CodePudding

Let's say I run the following query:

SELECT * FROM
   (SELECT COUNT(1) FROM tbl) tbl1,
   (SELECT COUNT(1) FROM tbl) tbl2

Is the count guaranteed to be the same in the following function (let's say on a table that averages 1M inserts per minute, and the above query takes 10s to complete). I know if the function isIMMUTABLE it will return the same, and if it isVOLATILE it will (most likely) not return the same, but I'm curious how a STABLE function would work: https://www.postgresql.org/docs/current/xfunc-volatility.html.

CodePudding user response：

A query (statement) sees a consistent view of the database as it was at the point in time when the execution started - regardless of how many sub-queries or derived tables it contains.

Any concurrent modification to the table is not visible to the running query.

So yes, it is guaranteed that bot sub-queries will return the same count.

This has nothing to do with "volatile" or "stable" functions. Those attributes are typically only important for the optimizer to take shortcuts. E.g.: the database free to cache function results per value for functions declared as immutable or stable for the duration of the query. But this is independent of the content that the query (and the functions) see.

Btw (unrelated to your question): it is a myth that count(1) is faster than count(*). There is no database where this is true. In Postgres count(1) is even slightly slower.