测试类型

SQL功能

测试版本

7.0.0LTS

问题描述

A库alter table执行失败

操作系统和硬件信息

不限

测试环境

企业版单机

被测功能

alter table功能

预置条件

无

操作步骤

drop view if exists t1_view1;
drop table if exists t1;
create table t1(f1 int, f2 text, f3 int, f4 bigint, f5 int generated always as (f1 + f3) stored)
    with (storage_type=ustore);
insert into t1 values(1, 'aaa', 3, 1);
insert into t1 values(11, 'bbb', 33, 2);
insert into t1 values(111, 'ccc', 333, 3);
insert into t1 values(1111, 'ddd', 3333, 4);
select * from t1;

create view t1_view1 as select * from t1;
select * from t1_view1;
\d+ t1
\d+ t1_view1
alter table t1 drop f4;
\d+ t1
 --这里预期显式视图引用的具体列名，实际显式*
\d+ t1_view1
alter table t1 add f8 int first, drop f2; --这里预期执行成功，实际执行失败

drop view t1_view1;
drop table t1;

预期输出

执行成功

实际输出

openGauss=# drop view if exists t1_view1;
NOTICE:  view "t1_view1" does not exist, skipping
DROP VIEW
openGauss=# drop table if exists t1;
NOTICE:  table "t1" does not exist, skipping
DROP TABLE
openGauss=# create table t1(f1 int, f2 text, f3 int, f4 bigint, f5 int generated always as (f1 + f3) stored)
openGauss-#     with (storage_type=ustore);
CREATE TABLE
openGauss=# insert into t1 values(1, 'aaa', 3, 1);
INSERT 0 1
openGauss=# insert into t1 values(11, 'bbb', 33, 2);
INSERT 0 1
openGauss=# insert into t1 values(111, 'ccc', 333, 3);
INSERT 0 1
openGauss=# insert into t1 values(1111, 'ddd', 3333, 4);
INSERT 0 1
openGauss=# select * from t1;
  f1  | f2  |  f3  | f4 |  f5
------+-----+------+----+------
    1 | aaa |    3 |  1 |    4
   11 | bbb |   33 |  2 |   44
  111 | ccc |  333 |  3 |  444
 1111 | ddd | 3333 |  4 | 4444
(4 rows)

openGauss=#
openGauss=# create view t1_view1 as select * from t1;
CREATE VIEW
openGauss=# select * from t1_view1;
  f1  | f2  |  f3  | f4 |  f5
------+-----+------+----+------
    1 | aaa |    3 |  1 |    4
   11 | bbb |   33 |  2 |   44
  111 | ccc |  333 |  3 |  444
 1111 | ddd | 3333 |  4 | 4444
(4 rows)

openGauss=# \d+ t1
                                         Table "public.t1"
 Column |  Type   |               Modifiers                | S
torage  | Stats target | Description
--------+---------+----------------------------------------+--
--------+--------------+-------------
 f1     | integer |                                        | p
lain    |              |
 f2     | text    |                                        | e
xtended |              |
 f3     | integer |                                        | p
lain    |              |
 f4     | bigint  |                                        | p
lain    |              |
 f5     | integer | generated always as ((f1 + f3)) stored | p
lain    |              |
Has OIDs: no
Options: orientation=row, storage_type=ustore, compression=no,
 toast.storage_type=ustore

openGauss=# \d+ t1_view1
                View "public.t1_view1"
 Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 f1     | integer |           | plain    |
 f2     | text    |           | extended |
 f3     | integer |           | plain    |
 f4     | bigint  |           | plain    |
 f5     | integer |           | plain    |
View definition:
 SELECT  *
   FROM t1;

openGauss=# alter table t1 drop f4;
ALTER TABLE
openGauss=# \d+ t1
                                         Table "public.t1"
 Column |  Type   |               Modifiers                | S
torage  | Stats target | Description
--------+---------+----------------------------------------+--
--------+--------------+-------------
 f1     | integer |                                        | p
lain    |              |
 f2     | text    |                                        | e
xtended |              |
 f3     | integer |                                        | p
lain    |              |
 f5     | integer | generated always as ((f1 + f3)) stored | p
lain    |              |
Has OIDs: no
Options: orientation=row, storage_type=ustore, compression=no,
 toast.storage_type=ustore

openGauss=#  --这里预期显式视图引用的具体列名，实际显式*
openGauss=# \d+ t1_view1
WARNING:  View t1_view1 references invalid table(s), view(s) or column(s).
CONTEXT:  referenced column: pg_get_viewdef
                View "public.t1_view1"
 Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 f1     | integer |           | plain    |
 f2     | text    |           | extended |
 f3     | integer |           | plain    |
 f4     | bigint  |           | plain    |
 f5     | integer |           | plain    |
View definition:
 SELECT  *
   FROM t1;

openGauss=# alter table t1 add f8 int first, drop f2; --这里预期执行成功，实际执行失败
ERROR:  column t1.f2 does not exist
LINE 1: alter table t1 add f8 int first, drop f2;
                      ^
CONTEXT:  referenced column: f2
openGauss=#
openGauss=# drop view t1_view1;
DROP VIEW
openGauss=# drop table t1;
DROP TABLE

日志信息

见实际输出

提单组织

内部开发

测试代码

alter table t1 drop f4, add f7 int;
视图由valid执行上面sql变成invalid，应该展开视图的列名，不应显式*
select * from pg_get_viewdef('t1_view1');