Hi Alberto, Sounds like you need PHOENIX-4552. If you agree, let's continue the discussion over there. Thanks, James
On Fri, Feb 2, 2018 at 9:05 AM, Alberto Bengoa <albe...@propus.com.br> wrote: > 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 > >
