错误:不支持对带有投影表达式或聚合的表的访问

Question

我想从表中删除分区:

ALTER TABLE rosing_watch_sessions REMOVE PARTITIONING

但是会抛出错误:

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

这个错误信息是什么意思?

公立小学

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);

Answer 1

实时聚合投影不支持某些操作(目前)。

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

然后使用DDL重建staging.channel_coverage。