Hello Folks, I'm working on a project where we need to identify when a row was changed (updated fields). I was wondering if ROW_TIMESTAMP would help me to reach this goal.
I created the test table bellow, and inserted some data: create table test( a integer not null, b integer, last_update date not null CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) ); upsert into test (a, b) values (1, 1); upsert into test (a, b) values (2, 2); upsert into test (a, b) values (3, 4); 0: jdbc:phoenix:> select * from test; +----+----+--------------------------+ | A | B | LAST_UPDATE | +----+----+--------------------------+ | 1 | 1 | 2018-02-02 16:33:52.345 | | 2 | 2 | 2018-02-02 16:33:56.714 | | 3 | 4 | 2018-02-02 16:34:00.281 | +----+----+--------------------------+ 3 rows selected (0.041 seconds) So, I've tried to update B value where A = 3; 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); Then, I have one "new" row, not an updated row as I need: 0: jdbc:phoenix:> select * from test; +----+----+--------------------------+ | A | B | LAST_UPDATE | +----+----+--------------------------+ | 1 | 1 | 2018-02-02 16:33:52.345 | | 2 | 2 | 2018-02-02 16:33:56.714 | | 3 | 4 | 2018-02-02 16:34:00.281 | | 3 | 3 | 2018-02-02 16:36:31.890 | +----+----+--------------------------+ 4 rows selected (0.052 seconds) I understand that LAST_UPDATE column is part of the PRIMARY KEY and, from this perspective, it's in fact should be a NEW row. But, on the other hand, this not fits my case, because actually I'll have a new row after each "update" (and I have lots of updates). There's any alternative to this on the Phoenix side? I was not expecting to have to call a now() function from client side all the time to update a last_update field. Maybe another kind of CONSTRAINT that would be used? Phoenix version 4.7 here. Thanks in advanced! Cheers, Alberto
