sql - 垂直中的 ALTER 表

Question

我在 Vertica 中有一个表，它具有 time_stamp:int 值 Unix 时间戳，我想将此数据类型从 int 更改为 TIMESTAMPTZ;

我试过了

ALTER TABLE exploded_names ALTER COLUMN time_stamp SET DATA TYPE TIMESTAMPTZ;

但是出现如下错误

ROLLBACK 2353:  Cannot alter type of column "time_stamp" since it is referenced in the segmentation expression of projection "exploded_names_b0"

then I tried to drop that projection :

drop projection exploded_names_b0 CASCADE;

ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection exploded_names_b0HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead

我不想放下 table 。

Answer 1

您收到的错误告诉您该列用于投影的分段表达式，因此无法更改。这是对 SET DATA TYPE 选项的限制。您可以创建新的 super 投影并省略分段子句中的列，或者创建一个新表和具有新列类型的投影。

由于您提到当前列是 int 类型，因此它不会转换为 TIMESTAMPTZ。这是我会做的:

第 1 步 - 设置示例数据

CREATE TABLE public.exploded_names (
   id int,
   time_stamp int
);

INSERT INTO public.exploded_names (id, time_stamp) VALUES (1, 1403635837);

COMMIT;

第 2 步 - 添加新列

ALTER TABLE public.exploded_names ADD COLUMN iso_time TIMESTAMPTZ;

第 3 步 - 创建新的 super 投影

我们将使用 SELECT EXPORT_OBJECTS('', 'public.exploded_names');

获取现有的 super 投影

CREATE PROJECTION public.exploded_names /*+createtype(L)*/
(
 id,
 time_stamp,
 iso_time
)
AS
 SELECT exploded_names.id,
        exploded_names.time_stamp,
        exploded_names.iso_time
 FROM public.exploded_names
 ORDER BY exploded_names.id,
          exploded_names.time_stamp
SEGMENTED BY hash(exploded_names.id, exploded_names.time_stamp) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

我们需要删除 time_stamp 列并在分段子句上添加 iso_time 并更改新 super 投影的名称:

CREATE PROJECTION public.exploded_names_2
(
 id,
 time_stamp,
 iso_time
)
AS
 SELECT exploded_names.id,
        exploded_names.time_stamp,
        exploded_names.iso_time
 FROM public.exploded_names
 ORDER BY exploded_names.id,
          exploded_names.time_stamp
SEGMENTED BY hash(exploded_names.id, exploded_names.iso_time) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

第 4 步 - 填充新列

在这里，我们将使用转换后的 Unix 时间戳更新 iso_time 列，然后刷新新的 super 投影。

UPDATE public.exploded_names SET iso_time = TO_TIMESTAMP(time_stamp);

COMMIT;

SELECT REFRESH('public.exploded_names');

第 5 步 - 丢弃旧的超投影

我们需要推进古代历史标记，然后放下旧的 super 投影:

SELECT MAKE_AHM_NOW();

DROP PROJECTION public.exploded_names;

第 6 步 - 验证预测

让我们确保我们有正确的投影设置 SELECT GET_PROJECTIONS('public.exploded_names');:

 Current system K is 1.# of Nodes: 3.Table public.exploded_names has 2 projections.Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]----------------------------------------------------------------------------------------------------public.exploded_names_2_b1 [Segmented: Yes] [Seg Cols: "public.exploded_names.id", "public.exploded_names.iso_time"] [K: 1] [public.exploded_names_2_b0] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]public.exploded_names_2_b0 [Segmented: Yes] [Seg Cols: "public.exploded_names.id", "public.exploded_names.iso_time"] [K: 1] [public.exploded_names_2_b1] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

附带说明一下，如果您还没有运行数据库设计器，则应该运行数据库设计器以获得优化的投影。