phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alberto Bengoa <albe...@propus.com.br>
Subject ROW_TIMESTAMP
Date Fri, 02 Feb 2018 17:05:53 GMT
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

Mime
View raw message