Vertica 9.3 Supports UPDATE and DELETE Operations on Tables Having Live Aggregate Projections!

Jim_Knicely

A live aggregate projection contains columns with values that are aggregated from columns in its anchor table. When you load data into the table, Vertica aggregates the data before loading it into the live aggregate projection. On subsequent loads (for example, through INSERT or COPY) Vertica recalculates aggregations with the new data and updates the projection.

Prior to Vertica 9.3, a major restriction for a LAP was the inability to perform DELETE, UPDATE or MERGE operations on anchor table data. To modify existing anchor table data, you had to first drop all live aggregate projections that were associated with it.

Yikes!

As of Vertica 9.3, you can now UPDATE and DELETE data in tables that have Live Aggregate Projections!

Example:

dbadmin=> SELECT * FROM lap_test ORDER BY 1, 2;
 c1 | c2
----+----
  1 |  1
  1 |  2
  1 |  3
  2 |  1
  2 |  2
(5 rows)

dbadmin=> CREATE PROJECTION lap_test_new_lap_pr AS SELECT c1, SUM(c2) c2 FROM lap_test GROUP BY c1;
WARNING 4468:  Projection <public.lap_test_new_lap_pr> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <public.lap_test_new_lap_pr_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> SELECT REFRESH('lap_test');
                                                                                                                                                                                        REFRESH                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."lap_test_new_lap_pr_b1": [lap_test] [refreshed] [scratch] [0] [0]
"public"."lap_test_new_lap_pr": [lap_test] [refreshed] [scratch] [0] [0]

(1 row)

dbadmin=> SELECT c1, SUM(c2) FROM lap_test GROUP BY c1;
 c1 | SUM
----+-----
  1 |   6
  2 |   3
(2 rows)

dbadmin=> DELETE FROM lap_test WHERE c1 = 1 AND c2 = 2;
WARNING 9127:  UPDATE/DELETE a table with aggregate projections (LAPs) will automatically run refresh on the LAPs when this transaction commits
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT c1, SUM(c2) FROM lap_test GROUP BY c1;
 c1 | SUM
----+-----
  1 |   4
  2 |   3
(2 rows)

Have fun!

Helpful Links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AnalyzingData/AggregatedData/LiveAggregateProjections.htm

Sudhakar_B

THANK YOU Jim!
This was one of the MAIN reason at my customer site's, I was not able to use LAPs. LAPs are awesome, and I used to end up implementing "poor mans" LAP by building pre-aggregate table within ETL (sigh).
I'll try these out. Is 9.3 VMware available, yet?
Excellent justification for 9.3 upgrade.

Jim_Knicely

Be careful with this feature as Vertica will refresh that LAP after a COMMIT. Depending on the anchor table size, that could take a long time. Make sure to apply all DML in bulk prior to a COMMIT.
Vertica 9.3 CE should be available to test!