CODEWITHSUNDEEP
vertica
Maxim
Maxim

Reputation: 9961

ERROR: Unsupported access to table with projection expressions or aggregates

I want to remove partitioning from table:

ALTER TABLE rosing_watch_sessions REMOVE PARTITIONING

but it raise error:

Severity: ROLLBACK, 
Message: Unsupported access to table with projection expressions or aggregates, 
Sqlstate: 0A000, 
Routine: checkUnsupportedMaVeriCKTableError, 
File: /scratch_a/release/16125/vbuild/vertica/Catalog/CatalogLookup.cpp, 
Line: 1383

What does it mean this error message?

P.S.

Result of select export_objects('', 'rosing_watch_sessions'):

CREATE TABLE staging.rosing_watch_sessions
(
    id  IDENTITY ,
    session_uid varchar(255) NOT NULL,
    ...
)
PARTITION BY (rosing_watch_sessions.requested_day);

ALTER TABLE staging.rosing_watch_sessions ADD CONSTRAINT C_PRIMARY PRIMARY KEY (id); 

CREATE PROJECTION staging.rosing_watch_sessions_super /*+basename(rosing_watch_sessions),createtype(P)*/ 
(
 id,
 session_uid,
 ...
)
AS
 SELECT rosing_watch_sessions.id,
        rosing_watch_sessions.session_uid,
        ...
 FROM staging.rosing_watch_sessions
 ORDER BY rosing_watch_sessions.id
SEGMENTED BY hash(rosing_watch_sessions.requested_day) ALL NODES ;

CREATE PROJECTION staging.channel_coverage
(
 resource_uid,
 device_uid,
 request_date,
 num_requests,
 __partition_key_value__ ENCODING RLE
)
AS
 SELECT rosing_watch_sessions.resource_uid,
        rosing_watch_sessions.device_uid,
        date("timezone"('UTC'::varchar(3), rosing_watch_sessions.requested_at)) AS request_date,
        count(rosing_watch_sessions.session_uid) AS num_requests,
        max(rosing_watch_sessions.requested_day) AS __partition_key_value__
 FROM staging.rosing_watch_sessions
 GROUP BY rosing_watch_sessions.resource_uid,
          rosing_watch_sessions.device_uid,
          date("timezone"('UTC'::varchar(3), rosing_watch_sessions.requested_at))
;


SELECT MARK_DESIGN_KSAFE(0);

Upvotes: 1

Views: 608

Answers (1)

woot
woot

Reputation: 7606

Live aggregate projections do not support certain operations (yet). 

DROP PROJECTION staging.channel_coverage;
ALTER TABLE rosing_watch_sessions REMOVE PARTITIONING;

Then rebuild staging.channel_coverage using the DDL you have.

Upvotes: 2

Related Questions