The Vertica Database Forums

Hi,
Playing with Live Aggregate Projection for the first time and failed at the first hurdle, is it obvious what I'm doing wrong:-

CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id;

vsql:cre_point_max_lastupdated_agg.sql:3: WARNING 6852: Live Aggregate Projection "point_max_lastupdated_agg" will be created for "point". Data in "point" will be neither updated nor deleted
vsql:cre_point_max_lastupdated_agg.sql:3: WARNING 4468: Projection <PHOENIX1.point_max_lastupdated_agg> 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
select refresh('PHOENIX1.point');
refresh
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"PHOENIX1"."point_max_lastupdated_agg": [point] [failed: projection is unsafe] [] [1] [0]

(1 row)


The projection creates ok, but won't refresh as the projection is unsafe!

The projections on the point table are safe:-
SELECT MARK_DESIGN_KSAFE(1);
MARK_DESIGN_KSAFE
----------------------
Marked design 1-safe
(1 row)

How many nodes do you have? Are all your nodes up?

Try adding KSAFE or KSAFE 1 at the end of the CREATE PROJECTION statement.

—Sharon

All nodes up ok, everything is currently KSafe.

Tried the following syntax, but no luck:-
CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id
ALL NODES KSAFE;
vsql:cre_point_max_lastupdated_agg.sql:4: ERROR 4856: Syntax error at or near "ALL" at character 172
vsql:cre_point_max_lastupdated_agg.sql:4: LINE 4: ALL NODES KSAFE;
vsql:cre_point_max_lastupdated_agg.sql:4: ^
select refresh('PHOENIX1.point');

CREATE PROJECTION point_max_lastupdated_agg AS
SELECT instrument_id, element_id ,max(last_updated) AS max_timestamp FROM Phoenix1.Point
group by instrument_id, element_id
SEGMENTED BY MODULARHASH (instrument_id, element_id) ALL NODES KSAFE;
ERROR 6394: SEGMENTED BY / UNSEGMENTED is not allowed in aggregate projection. The aggregate projection is automatically segmented on group by columns

What version are you on?

vsql --version
vsql version 07.02.0000, built for Linux64, contains support for command-line editing

Use just KSAFE at the end of the definition, not ALL NODES KSAFE.

Btw - on the vertica version number check, use “select version()”. You posted your vsql client version, which doesn’t necessarily match the server version.

—Sharon