Persisted computed by columns [CORE2463]

[firebird-automations]

Submitted by: @cincuranet

Votes: 1

It would be nice to have support for persisted computed by columns. Of course works only for "deterministic" expressions.

[firebird-automations]

Modified by: @cincuranet

Component: Engine \[ 10000 \]

[firebird-automations]

Commented by: Claudio Valderrama C. (robocop)

What's the difference with a normal field that's populated by a before insert, before update trigger?

[firebird-automations]

Commented by: @cincuranet

The ease of usage and maintaining.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Jiri,

How would the engine know fi the expression was "deterministic"?

[firebird-automations]

Commented by: @asfernandes

Jiri, if you were talking about performance, I would understand what is a "persisted computed by column".

But as you talked about "ease of usage and maintaining", I feel I don't know what it is. Could you elaborate?

[firebird-automations]

Commented by: @cincuranet

ad deterministic> That's the question for discussion. But taking into account you can create computed index i.e. based on current_timestamp, this can be allowed for persisted computed columns too.

as usage ...>Well, with maintaining I covered in my mind the performace and also the ability to chnge the expression directly where the column is defined without need to change the trigger(s) and looking for these (if it's not one for all).

[firebird-automations]

Commented by: @asfernandes

1) FB 2.5 has ALTER ... COMPUTED.

2) What make you think that a persisted (on disk) column will be faster than a computed?

I'm thinking you want to put heavy queries on them. But if queries depends on another table/records, how the engine can figure out to update the persistent column when that table/records changes?

[firebird-automations]

Commented by: @cincuranet

1) Yes, that's why I'm saying, you can easily change the expression.

2) If the computation is easy, than the computed will be faster. But if it's really hard, like some "long" mathematical formula etc., then I think, fetching this will be faster than computing it again and again.

ad another table> Yes, that's why I was talking about deterministic expressions.

[firebird-automations]

Commented by: @asfernandes

Ok. IMHO, it looks something that triggers can do nice.

I see MSSQL has it. Do you know, if when expression is altered, in the next time the column is read it will return and persist the new value on the fly? If it does, there is one advantage than trigger. But also, it may be problematic, as it will defeat its purpose of performance and someone will note only when running the query.

[firebird-automations]

Commented by: @cincuranet

In MS SQL the computed columns (both persisted and not persisted) cannot be altered.

> if when expression is altered, in the next time the column is read it will return and persist the new value on the fly? Yes, that was what I was thinking about. And if you need to regenerate it immediatelly, just read the whole table.

[firebird-automations]

Commented by: @asfernandes

Don't seams similar for me.