Question Re: Does ALTER TABLE impact DEFAULT TIMESTAMP columns? in Technology Q&A

Does ALTER TABLE impact DEFAULT TIMESTAMP columns?

VolkerBarth — Wed, 05 Jan 2011 15:20:15 GMT

I'm just altering the COMPUTE expression of a table with existing data. (This is V12.0.0.2601.)

As documented, this re-computes not only the particular column addressed in the ALTER TABLE ... SET COMPUTE statement but does re-compute all computed columns in this table (and in this case, this is absolutely wanted behaviour:).

Since the table has the usual DEFAULT TIMESTAMP column, I wasn't sure if this column is changed, too - however, it isn't.

Is this expected behaviour for DEFAULT TIMESTAMP, i.e. will those values only change by UPDATE statements, but not by those data modifications as result of an ALTER TABLE statement (e.g. altering computed values, adding columns with defaults and the like)?

I'm just asking for a better understanding...

Re: Does ALTER TABLE impact DEFAULT TIMESTAMP columns?

VolkerBarth — Fri, 07 Jan 2011 22:28:08 GMT

Being no iAnywhere staff member, obviously I can't claim whether this is intended behaviour or not.

That being said, I suggest that the current behaviour should be documented, as changing rows by ALTERing the table seems quite similar to changing rows by updates. Therefore I guess my question is not too uncommon.

However, I bet in most cases DEFAULT TIMESTAMP is used to track changes by ordinary DML statements - in the sense of "when was that row modified at last". It's usually meant for changes by users / application programs.

When doing clean-up or other administrative tasks in such tables, I often want the particular DEFAULT TIMESTAMP column to stay unchanged to "hide" my adminstrative changes. That's easily done with an UPDATE statement that sets the DEFAULT TIMESTAMP column to its current value. And as usually, if the UPDATE statement specifies a value for this column, the default value isn't applied.

So, with UPDATE, I'm quite free to choose whether I want DEFAULT TIMESTAMP to be updated or not.

However, if ALTER TABLE would change the DEFAULT TIMESTAMP, there wouldn't be a way to prevent this change (at least without an enhanced syntax). Therefore the current behaviour seems reasonable as

table alterations (as truly administrative tasks) might usually not be meant to change the timestamp and
in case the timestamp should be changed, one can still achieve this by adding an according UPDATE STATEMENT afterwards.
Furthermore an ALTER TABLE with impact on DEFAULT TIMESTAMP could raise problems in replication/synchronization setups: In such setups, ALTER TABLE is usually done at different times at different locations for the same (distributed) rows, and that would lead to multiple changes of the timestamp column for the same row. That might be difficult to handle. At least, such a behaviour doesn't seem senseful to me.

Re: Does ALTER TABLE impact DEFAULT TIMESTAMP columns?

former_SQLA_member1694880 — Sun, 09 Jan 2011 17:15:44 GMT

I think ALTER TABLE should not affect DEFAULT TIMESTAMP columns for the very reason yo've described ;).

Re: Does ALTER TABLE impact DEFAULT TIMESTAMP columns?

VolkerBarth — Sun, 09 Jan 2011 20:56:25 GMT

@Dmitri: That's exactly what I have tried to express: Yes, the current behaviour seems senseful - it should just be documented...