Query to get last time of a DML operation on a table

Hi Mohamed. I don't think Vertica system tables/metadata will be of much help here. However, what about checking query_requests for last successful DML on a given table? Though DC retention might be a limiting factor.

Hi,

I always recommend adding 4 columns to a table (CREATED_DATE, CREATED_BY, MODIFIED_DATE and MODIFIED_BY). They'll help you track DML operations and COPY statements at the row level.

Another option is to map the table's row EPOCH to the EPOCHS system table:

dbadmin=> CREATE TABLE test (c INT);
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT c, epoch, epoch_close_time last_modified_date
dbadmin->   FROM test
dbadmin->   JOIN epochs
dbadmin->     ON epoch_number = epoch;
 c | epoch |      last_modified_date
---+-------+-------------------------------
 1 |  3014 | 2019-01-16 08:46:01.586039-05
(1 row)

But note that the EPOCHS system table is frequently purged by Vertica. You can create you own version and populate it just after a DML or COPY. That way you can keep a history.

Example:

dbadmin=> UPDATE test SET c = 2 WHERE c = 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> INSERT INTO my_epochs (SELECT * FROM epochs MINUS SELECT * FROM my_epochs);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT c, test.epoch, my_epochs.epoch_close_time last_modified_date
dbadmin->   FROM test
dbadmin->   JOIN my_epochs
dbadmin->     ON my_epochs.epoch_number = test.epoch;
 c | epoch |      last_modified_date
---+-------+-------------------------------
 2 |  3029 | 2019-01-16 09:05:34.116915-05
(1 row)

Note that the problem here is that you've lost the original INSERT epoch as the EPOCH column on the TEST table is now gone.

Epochs only goes back about 3 minutes - the default duration of the "advance_ahm_interval", which is used to advance the epoch to the beginning of the ahm interval window. So, that table would be a bit tricky to use, I think.

Couldn't you just track this in query_profiles? Just check for queries that start with "delete%", "insert%", or "update%", and you could include load_streams to get COPY operations as well.

@Vertica_Curtis - That's why I suggested maintaining a local copy of EPOCHS as MY_EPOCHS, but that too is unwieldy, so not the best solution. But using QUERY_PROFILES is a great idea!

Simple Example:

dbadmin=> DROP TABLE test;
DROP TABLE

dbadmin=> CREATE TABLE test(c INT);
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 3;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> UPDATE test SET c = 4 WHERE c = 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT epoch, c, table_name, query_type, query
dbadmin->   FROM test
dbadmin->   JOIN query_profiles
dbadmin->     ON epoch = query_start_epoch
dbadmin->  WHERE table_name = 'test'
dbadmin->    AND (query ILIKE 'INSERT%'
dbadmin(>     OR query ILIKE 'UPDATE%')
dbadmin->  LIMIT 1 OVER(PARTITION BY epoch ORDER BY query_start DESC);
 epoch | c | table_name | query_type |               query
-------+---+------------+------------+------------------------------------
  3034 | 1 | test       | QUERY      | INSERT INTO test SELECT 3;
  3035 | 4 | test       | QUERY      | UPDATE test SET c = 4 WHERE c = 2;
(2 rows)

However, that could get complex if the SQL DML is more complicated.

I would just do this:

dbadmin=> DROP TABLE test;
DROP TABLE

dbadmin=> CREATE TABLE test (c INT,
dbadmin(>                    created_by VARCHAR(128) DEFAULT user,
dbadmin(>                    created_date TIMESTAMP DEFAULT sysdate,
dbadmin(>                    updated_by VARCHAR(128) DEFAULT user,
dbadmin(>                    updated_date TIMESTAMP DEFAULT sysdate);
CREATE TABLE

dbadmin=> INSERT INTO test (c) SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test;
 c | created_by |        created_date        | updated_by |        updated_date
---+------------+----------------------------+------------+----------------------------
 1 | dbadmin    | 2019-01-16 13:44:21.122525 | dbadmin    | 2019-01-16 13:44:21.122525
(1 row)

dbadmin=> UPDATE test SET c = 2, updated_by = user, updated_date = sysdate;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test;
 c | created_by |        created_date        | updated_by |        updated_date
---+------------+----------------------------+------------+----------------------------
 2 | dbadmin    | 2019-01-16 13:44:21.122525 | dbadmin    | 2019-01-16 13:45:08.934056
(1 row)

But I am from the "old school" data warehouse world