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

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 LAP’s 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  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  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

Related Posts:
Announcing Vertica Version 9.3 – Ride the Winds of Change
Announcing Vertica Version 9.2.1 – Take Analytics Efficiency to the Next Level
Vertica in Eon Mode Depot – Where Did You Go?
Introducing an Industry First – Vertica in Eon Mode for Pure Storage