Usage of AT EPOCH LATEST in VIEW

[sarithats]

Hi,

The queries running against views are taking time to execute when CDC is enabled(i.e. Delete/Update/Merge operation for the underlying tables). But Vertica is not allowing to use 'AT EPOCH LATEST' inside VIEW Definition as below:

CREATE OR REPLACE VIEW ACCOUNT_view AS AT EPOCH LATEST SELECT

Is there an alternative for this?

Thanks,
Saritha

[JimKnicely]

Hi,

I think you have to create the view, and then query it at a certain epoch.

Example:

Code: Select all

dbadmin=> select * from epochs;
       epoch_close_time        | epoch_number
-------------------------------+--------------
 2016-02-17 09:41:40.093701-05 |           22
 2016-02-17 09:46:55.158934-05 |           23
 2016-02-17 09:47:26.71037-05  |           24
(3 rows)

dbadmin=> create table temp (c int);
CREATE TABLE

dbadmin=> insert into temp values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> select * from epochs;
       epoch_close_time       | epoch_number
------------------------------+--------------
 2016-02-17 09:47:26.71037-05 |           24
 2016-02-17 09:49:35.69914-05 |           25
(2 rows)

dbadmin=> insert into temp values (2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from temp;
 c
---
 1
 2
(2 rows)

dbadmin=> at epoch 25 select * from temp;
 c
---
 1
(1 row)

dbadmin=> create view temp_vw as select * from temp;
CREATE VIEW

dbadmin=> at epoch 25 select * from temp_vw;
 c
---
 1
(1 row)

dbadmin=> select * from temp_vw;
 c
---
 1
 2
(2 rows)

[NorbertKrupa]

You could also just build a view based on the actual epoch in the table (i.e. SELECT epoch, id FROM public.customers;).

[scutter]

Saritha,

Are your performance issues with CDC enabled because your queries are queueing on the table locks? Normally queries executed in transaction mode READ COMMITTED, which is equivalent to AT EPOCH LATEST. If your queries are queueing on the locks, that suggests that your queries are running in SERIALIZABLE mode. If that’s true - is there a good reason for it? You could just switch them to READ COMMITTED.

—Sharon

[sarithats]

dbadmin=> SHOW TRANSACTION_ISOLATION;
name | setting
-----------------------+----------------
transaction_isolation | READ COMMITTED

The transaction isolation level is already READ_COMMITTED.

[scutter]

So then AT EPOCH LATEST is already effectively in use with READ COMMITTED - no need to use that. It would be worth understanding the actual source of the slowdown when you have the CDC running. Using AT EPOCH LATEST isn’t the solution.

—Sharon

[Julie]

The queries running against views are taking time to execute when CDC is enabled...

Cool topic. I'm sorry to interrupt the flow of this thread, but can someone explain what CDC is?