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
>
>

Reply via email to