I'm thinking of adding the following kind of configuration parameter.

isolation
An enumeration option specifying how the cache should behave when transactions that are not explicitly read-only are used:

• read_committed: The cache will return cached data until the first statement that modifies the database. Thereafter all statements are forwarded to the backend.
• repeatable_read: The cache will never return cached data, but all statements are always forwarded to the backend.

isolation=repeatable_read

Default is read_committed.

How does that sound?

This issue is in fact not limited to read/write transactions, but applies to read-only as well.

Suppose a transaction is started and something is selected and suppose the result was found from the cache. Then suppose the same thing is selected again, but this time the ttl has been reached and the data is fetched from the server. There is no guarantee that the result will be identical. Thus, if read_committed behaviour is required, then caching cannot be used during transactions.

So, I think I will still introduce that isolation configuration parameter and if its value is set to repeatable_read then no data will be returned from the cache during transactions. It will be populated though, so selects done with autocommit being enabled will be served from the cache.

There is now a new configuration parameter cache_in_transactions that can take the following values:

• never: During transactions, nothing is ever returned from the cache. The cache is populated in explicitly read-only transactions. In not explcitly read-only transactions the cache is populated until the first non-SELECT statement.
• read_only_transactions: During explicitly read-only transactions, the cache is used and populated. In not explicitly read-only transactions the cache is populated until the first non-SELECT statement.
• all_transactions: During explicitly read-only transactions, the cache is used and populated. In not explicitly read-only transactions the cache is used and populated until the first non-SELECT statement.

With read_only_transactions, explicitly read only transactions behave as if the isolation level of the server were read committed. With all_transactions all transactions behave as if the isolation level of the server were read committed.