How to trigger Client side computed columns STORED

[Omar Aloraini]

I'm reading the documentation for 'Client side computed columns' and looking at our non-jOOQ codebase and how it could be useful. As of yet, I see two use cases: 1) simplifying (and caching) 'count(*) filter' queries. 2) simplifying (and caching) joins, or the result of multiset(is that possible?).

The VIRTUAL part is clear. jOOQ will render an additional select-part when fetching the the column.

When using STORED how can I trigger the computation of a specific(or all) STORED columns? An an example, suppose I have:

create table books (....) 

create table author (id, name, books_count)

 

Where books_count is a client side computed column with the expression (select count(*) where book.author_id = id)  

In Java code I might have:

BOOK.insert(....)

I don't see how this code will trigger the update on the author table 🤔.

I assumed there would be an API such as:

AUTHOR.where(...).recomputeColumns()

But I couldn't find it

Regards

[Lukas Eder]

Hello,

On Wed, Dec 31, 2025 at 10:21 AM Omar Aloraini <alorai...@gmail.com> wrote:

I'm reading the documentation for 'Client side computed columns' and looking at our non-jOOQ codebase and how it could be useful. As of yet, I see two use cases: 1) simplifying (and caching) 'count(*) filter' queries. 2) simplifying (and caching) joins, or the result of multiset(is that possible?).

There's this projection cache lookup feature request, which might match what you're looking for:

https://github.com/jOOQ/jOOQ/issues/14020

Personally, I'm a bit reluctant to implement such caching in jOOQ as jOOQ can't really make the best decisions about when to compute things in such a query (some decisions might depend on statistics / distribution of values in a column, for example). If jOOQ makes poor decisions here, then you'll quickly run into N+1 problems that are hard to spot, because the query looks atomic, but it's really 1 parent query and N child queries (if there are tons of cache misses).

Some RDBMS implement "scalar subquery caching" (Oracle and others) or "memoization" (PostgreSQL) to some extent, in order to cache some subquery values on a per-query execution basis. I haven't checked RDBMS capabilities in this area anymore in recent time, but in principle, it should be possible to cache things also with aggregate or MULTISET subqueries.

 

The VIRTUAL part is clear. jOOQ will render an additional select-part when fetching the the column.

When using STORED how can I trigger the computation of a specific(or all) STORED columns? An an example, suppose I have:

create table books (....) 

create table author (id, name, books_count)

 

Where books_count is a client side computed column with the expression (select count(*) where book.author_id = id)  

In Java code I might have:

BOOK.insert(....)

I don't see how this code will trigger the update on the author table 🤔.

I assumed there would be an API such as:

AUTHOR.where(...).recomputeColumns()

But I couldn't find it

That's an interesting use-case, which isn't covered by the client side computed columns feature. The feature mimics server side computed columns, adding some additional capabilities as we can allow more sophisticated, non-row-based expressions that will be embedded in all DML queries. We can do this because we make no up-to-date guarantee about computed columns that are manipulated outside of jOOQ (e.g. a non-jOOQ batch job won't trigger any re-compute).

Additionally, we cannot currently model table inter-dependencies of such computational expressions, i.e. when updating BOOK, you cannot trigger a dependent update of AUTHOR. That kind of thing also isn't possible with any server side computed columns that I've encountered so far. It would be possible to implement this feature, e.g. using some background job that gets triggered with update computations (for both same-table and dependent-table re-computations), but for now, this is out of scope.

What you're looking for here is a materialised view, which has wide RDBMS support, and is probably a much faster way to implement this anyway, as RDBMS can use delta computation for most of these aggregate functions (e.g. an INSERT to BOOK doesn't require to completely recompute the AUTHOR.BOOKS_COUNT value. The materialised view can just +1 on the COUNT(*) value, if the INSERT succeeds, or a PREVIOUS_SUM(x)+NEW_VALUE(x) for SUM(x) values, GREATEST(PREVIOUS_MAX(x), NEW_VALUE(x)) for MAX(x) values).

I hope this helps,

Lukas