Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit07ff701

Browse files
committed
Expand tests of test_ddl_deparse/ for ALTER TABLE
This module is expanded to track the description of the objects changedin the subcommands of ALTER TABLE by reworking the functionget_altertable_subcmdtypes() (now named get_altertable_subcmdinfo) usedin the event trigger of the test. It now returns a set of rows made of(subcommand type, object description) instead of a text array with onlythe information about the subcommand type.The tests have been lacking a lot of the subcommands added toAlterTableType over the years. All the missing subcommands are added,and the code is now structured so as the addition of a new subcommandis detected by removing the default clause used in the switch for thesubcommand types.The coverage of the module is increased from roughly 30% to 50%. Morecould be done but this is already a nice improvement.Author: Michael Paquier, Hou ZhijieReviewed-by: Álvaro Herrera, Amit Kapila, Hayato KurodaDiscussion:https://postgr.es/m/OS0PR01MB571626984BD099DADF53F38394899@OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parent6a1f082 commit07ff701

File tree

8 files changed

+250
-29
lines changed

8 files changed

+250
-29
lines changed

‎src/test/modules/test_ddl_deparse/expected/alter_table.out

Lines changed: 121 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2,28 +2,144 @@ CREATE TABLE parent (
22
a int
33
);
44
NOTICE: DDL test: type simple, tag CREATE TABLE
5+
ALTER TABLE parent SET (fillfactor = 50);
6+
NOTICE: DDL test: type alter table, tag ALTER TABLE
7+
NOTICE: subcommand: type SET RELOPTIONS desc <NULL>
8+
ALTER TABLE parent RESET (fillfactor);
9+
NOTICE: DDL test: type alter table, tag ALTER TABLE
10+
NOTICE: subcommand: type RESET RELOPTIONS desc <NULL>
11+
ALTER TABLE parent SET UNLOGGED;
12+
NOTICE: DDL test: type alter table, tag ALTER TABLE
13+
NOTICE: subcommand: type SET UNLOGGED desc <NULL>
14+
ALTER TABLE parent SET LOGGED;
15+
NOTICE: DDL test: type alter table, tag ALTER TABLE
16+
NOTICE: subcommand: type SET LOGGED desc <NULL>
17+
CREATE INDEX parent_index ON parent(a);
18+
NOTICE: DDL test: type simple, tag CREATE INDEX
19+
ALTER TABLE parent CLUSTER ON parent_index;
20+
NOTICE: DDL test: type alter table, tag ALTER TABLE
21+
NOTICE: subcommand: type CLUSTER desc index parent_index
22+
DROP INDEX parent_index;
523
CREATE TABLE child () INHERITS (parent);
624
NOTICE: DDL test: type simple, tag CREATE TABLE
725
CREATE TABLE grandchild () INHERITS (child);
826
NOTICE: DDL test: type simple, tag CREATE TABLE
927
ALTER TABLE parent ADD COLUMN b serial;
1028
NOTICE: DDL test: type simple, tag CREATE SEQUENCE
1129
NOTICE: DDL test: type alter table, tag ALTER TABLE
12-
NOTICE: subcommand: ADD COLUMN (and recurse)
30+
NOTICE: subcommand:typeADD COLUMN (and recurse) desc column b of table parent
1331
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
1432
ALTER TABLE parent RENAME COLUMN b TO c;
1533
NOTICE: DDL test: type simple, tag ALTER TABLE
34+
-- Constraint, no recursion
35+
ALTER TABLE ONLY grandchild ADD CONSTRAINT a_pos CHECK (a > 0);
36+
NOTICE: DDL test: type alter table, tag ALTER TABLE
37+
NOTICE: subcommand: type ADD CONSTRAINT desc constraint a_pos on table grandchild
38+
-- Constraint, with recursion
1639
ALTER TABLE parent ADD CONSTRAINT a_pos CHECK (a > 0);
40+
NOTICE: merging constraint "a_pos" with inherited definition
1741
NOTICE: DDL test: type alter table, tag ALTER TABLE
18-
NOTICE: subcommand: ADD CONSTRAINT (and recurse)
42+
NOTICE: subcommand:typeADD CONSTRAINT (and recurse) desc constraint a_pos on table parent
1943
CREATE TABLE part (
2044
a int
2145
) PARTITION BY RANGE (a);
2246
NOTICE: DDL test: type simple, tag CREATE TABLE
2347
CREATE TABLE part1 PARTITION OF part FOR VALUES FROM (1) to (100);
2448
NOTICE: DDL test: type simple, tag CREATE TABLE
49+
CREATE TABLE part2 (a int);
50+
NOTICE: DDL test: type simple, tag CREATE TABLE
51+
ALTER TABLE part ATTACH PARTITION part2 FOR VALUES FROM (101) to (200);
52+
NOTICE: DDL test: type alter table, tag ALTER TABLE
53+
NOTICE: subcommand: type ATTACH PARTITION desc <NULL>
54+
ALTER TABLE part DETACH PARTITION part2;
55+
NOTICE: DDL test: type alter table, tag ALTER TABLE
56+
NOTICE: subcommand: type DETACH PARTITION desc <NULL>
57+
DROP TABLE part2;
2558
ALTER TABLE part ADD PRIMARY KEY (a);
2659
NOTICE: DDL test: type alter table, tag ALTER TABLE
27-
NOTICE: subcommand: SET NOT NULL
28-
NOTICE: subcommand: SET NOT NULL
29-
NOTICE: subcommand: ADD INDEX
60+
NOTICE: subcommand: type SET NOT NULL desc column a of table part
61+
NOTICE: subcommand: type SET NOT NULL desc column a of table part1
62+
NOTICE: subcommand: type ADD INDEX desc index part_pkey
63+
ALTER TABLE parent ALTER COLUMN a SET NOT NULL;
64+
NOTICE: DDL test: type alter table, tag ALTER TABLE
65+
NOTICE: subcommand: type SET NOT NULL desc column a of table parent
66+
NOTICE: subcommand: type SET NOT NULL desc column a of table child
67+
NOTICE: subcommand: type SET NOT NULL desc column a of table grandchild
68+
ALTER TABLE parent ALTER COLUMN a DROP NOT NULL;
69+
NOTICE: DDL test: type alter table, tag ALTER TABLE
70+
NOTICE: subcommand: type DROP NOT NULL desc column a of table parent
71+
NOTICE: subcommand: type DROP NOT NULL desc column a of table child
72+
NOTICE: subcommand: type DROP NOT NULL desc column a of table grandchild
73+
ALTER TABLE parent ALTER COLUMN a SET NOT NULL;
74+
NOTICE: DDL test: type alter table, tag ALTER TABLE
75+
NOTICE: subcommand: type SET NOT NULL desc column a of table parent
76+
NOTICE: subcommand: type SET NOT NULL desc column a of table child
77+
NOTICE: subcommand: type SET NOT NULL desc column a of table grandchild
78+
ALTER TABLE parent ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
79+
NOTICE: DDL test: type simple, tag CREATE SEQUENCE
80+
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
81+
NOTICE: DDL test: type alter table, tag ALTER TABLE
82+
NOTICE: subcommand: type ADD IDENTITY desc column a of table parent
83+
ALTER TABLE parent ALTER COLUMN a SET GENERATED BY DEFAULT;
84+
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
85+
NOTICE: DDL test: type alter table, tag ALTER TABLE
86+
NOTICE: subcommand: type SET IDENTITY desc column a of table parent
87+
ALTER TABLE parent ALTER COLUMN a DROP IDENTITY;
88+
NOTICE: DDL test: type alter table, tag ALTER TABLE
89+
NOTICE: subcommand: type DROP IDENTITY desc column a of table parent
90+
ALTER TABLE parent ALTER COLUMN a SET STATISTICS 100;
91+
NOTICE: DDL test: type alter table, tag ALTER TABLE
92+
NOTICE: subcommand: type SET STATS desc column a of table parent
93+
NOTICE: subcommand: type SET STATS desc column a of table child
94+
NOTICE: subcommand: type SET STATS desc column a of table grandchild
95+
ALTER TABLE parent ALTER COLUMN a SET STORAGE PLAIN;
96+
NOTICE: DDL test: type alter table, tag ALTER TABLE
97+
NOTICE: subcommand: type SET STORAGE desc column a of table parent
98+
NOTICE: subcommand: type SET STORAGE desc column a of table child
99+
NOTICE: subcommand: type SET STORAGE desc column a of table grandchild
100+
ALTER TABLE parent ENABLE ROW LEVEL SECURITY;
101+
NOTICE: DDL test: type alter table, tag ALTER TABLE
102+
NOTICE: subcommand: type ENABLE ROW SECURITY desc <NULL>
103+
ALTER TABLE parent NO FORCE ROW LEVEL SECURITY;
104+
NOTICE: DDL test: type alter table, tag ALTER TABLE
105+
NOTICE: subcommand: type NO FORCE ROW SECURITY desc <NULL>
106+
ALTER TABLE parent FORCE ROW LEVEL SECURITY;
107+
NOTICE: DDL test: type alter table, tag ALTER TABLE
108+
NOTICE: subcommand: type FORCE ROW SECURITY desc <NULL>
109+
ALTER TABLE parent DISABLE ROW LEVEL SECURITY;
110+
NOTICE: DDL test: type alter table, tag ALTER TABLE
111+
NOTICE: subcommand: type DISABLE ROW SECURITY desc <NULL>
112+
CREATE STATISTICS parent_stat (dependencies) ON a, c FROM parent;
113+
NOTICE: DDL test: type simple, tag CREATE STATISTICS
114+
ALTER TABLE parent ALTER COLUMN c TYPE numeric;
115+
NOTICE: DDL test: type alter table, tag ALTER TABLE
116+
NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table parent
117+
NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table child
118+
NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table grandchild
119+
NOTICE: subcommand: type (re) ADD STATS desc statistics object parent_stat
120+
ALTER TABLE parent ALTER COLUMN c SET DEFAULT 0;
121+
NOTICE: DDL test: type alter table, tag ALTER TABLE
122+
NOTICE: subcommand: type ALTER COLUMN SET DEFAULT desc column c of table parent
123+
NOTICE: subcommand: type ALTER COLUMN SET DEFAULT desc column c of table child
124+
NOTICE: subcommand: type ALTER COLUMN SET DEFAULT desc column c of table grandchild
125+
CREATE TABLE tbl (
126+
a int generated always as (b::int * 2) stored,
127+
b text
128+
);
129+
NOTICE: DDL test: type simple, tag CREATE TABLE
130+
ALTER TABLE tbl ALTER COLUMN a DROP EXPRESSION;
131+
NOTICE: DDL test: type alter table, tag ALTER TABLE
132+
NOTICE: subcommand: type DROP EXPRESSION desc column a of table tbl
133+
ALTER TABLE tbl ALTER COLUMN b SET COMPRESSION pglz;
134+
NOTICE: DDL test: type alter table, tag ALTER TABLE
135+
NOTICE: subcommand: type SET COMPRESSION desc column b of table tbl
136+
CREATE TYPE comptype AS (r float8);
137+
NOTICE: DDL test: type simple, tag CREATE TYPE
138+
CREATE DOMAIN dcomptype AS comptype;
139+
NOTICE: DDL test: type simple, tag CREATE DOMAIN
140+
ALTER DOMAIN dcomptype ADD CONSTRAINT c1 check ((value).r > 0);
141+
NOTICE: DDL test: type simple, tag ALTER DOMAIN
142+
ALTER TYPE comptype ALTER ATTRIBUTE r TYPE bigint;
143+
NOTICE: DDL test: type alter table, tag ALTER TYPE
144+
NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column r of composite type comptype
145+
NOTICE: subcommand: type (re) ADD DOMAIN CONSTRAINT desc type dcomptype

‎src/test/modules/test_ddl_deparse/expected/create_table.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -77,16 +77,16 @@ NOTICE: DDL test: type simple, tag CREATE TABLE
7777
NOTICE: DDL test: type simple, tag CREATE INDEX
7878
NOTICE: DDL test: type simple, tag CREATE INDEX
7979
NOTICE: DDL test: type alter table, tag ALTER TABLE
80-
NOTICE: subcommand: ADD CONSTRAINT (and recurse)
81-
NOTICE: subcommand: ADD CONSTRAINT (and recurse)
80+
NOTICE: subcommand:typeADD CONSTRAINT (and recurse) desc constraint fkey_table_datatype_id_fkey on table fkey_table
81+
NOTICE: subcommand:typeADD CONSTRAINT (and recurse) desc constraint fkey_big_id on table fkey_table
8282
-- Typed table
8383
CREATE TABLE employees OF employee_type (
8484
PRIMARY KEY (name),
8585
salary WITH OPTIONS DEFAULT 1000
8686
);
8787
NOTICE: DDL test: type simple, tag CREATE TABLE
8888
NOTICE: DDL test: type alter table, tag ALTER TABLE
89-
NOTICE: subcommand: SET NOT NULL
89+
NOTICE: subcommand:typeSET NOT NULL desc column name of table employees
9090
NOTICE: DDL test: type simple, tag CREATE INDEX
9191
-- Inheritance
9292
CREATE TABLE person (
@@ -136,7 +136,7 @@ CREATE TABLE like_fkey_table (
136136
);
137137
NOTICE: DDL test: type simple, tag CREATE TABLE
138138
NOTICE: DDL test: type alter table, tag ALTER TABLE
139-
NOTICE: subcommand: ALTER COLUMN SET DEFAULT (precooked)
139+
NOTICE: subcommand:typeALTER COLUMN SET DEFAULT (precooked) desc column id of table like_fkey_table
140140
NOTICE: DDL test: type simple, tag CREATE INDEX
141141
NOTICE: DDL test: type simple, tag CREATE INDEX
142142
-- Volatile table types

‎src/test/modules/test_ddl_deparse/expected/create_view.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ CREATE OR REPLACE VIEW static_view AS
88
SELECT 'bar'::TEXT AS col;
99
NOTICE: DDL test: type simple, tag CREATE VIEW
1010
NOTICE: DDL test: type alter table, tag CREATE VIEW
11-
NOTICE: subcommand: REPLACE RELOPTIONS
11+
NOTICE: subcommand:typeREPLACE RELOPTIONS desc <NULL>
1212
CREATE VIEW datatype_view AS
1313
SELECT * FROM datatype_table;
1414
NOTICE: DDL test: type simple, tag CREATE VIEW

‎src/test/modules/test_ddl_deparse/expected/test_ddl_deparse.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,9 +28,9 @@ BEGIN
2828
-- if alter table, log more
2929
IF cmdtype = 'alter table' THEN
3030
FOR r2 IN SELECT *
31-
FROMunnest(public.get_altertable_subcmdtypes(r.command))
31+
FROM public.get_altertable_subcmdinfo(r.command)
3232
LOOP
33-
RAISE NOTICE ' subcommand:%', r2.unnest;
33+
RAISE NOTICE ' subcommand:type % desc %', r2.cmdtype, r2.objdesc;
3434
END LOOP;
3535
END IF;
3636
END LOOP;

‎src/test/modules/test_ddl_deparse/sql/alter_table.sql

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,15 @@ CREATE TABLE parent (
22
aint
33
);
44

5+
ALTERTABLE parentSET (fillfactor=50);
6+
ALTERTABLE parent RESET (fillfactor);
7+
ALTERTABLE parentSET UNLOGGED;
8+
ALTERTABLE parentSET LOGGED;
9+
10+
CREATEINDEXparent_indexON parent(a);
11+
ALTERTABLE parent CLUSTERON parent_index;
12+
DROPINDEX parent_index;
13+
514
CREATETABLEchild () INHERITS (parent);
615

716
CREATETABLEgrandchild () INHERITS (child);
@@ -10,6 +19,9 @@ ALTER TABLE parent ADD COLUMN b serial;
1019

1120
ALTERTABLE parent RENAME COLUMN b TO c;
1221

22+
-- Constraint, no recursion
23+
ALTERTABLE ONLY grandchild ADDCONSTRAINT a_posCHECK (a>0);
24+
-- Constraint, with recursion
1325
ALTERTABLE parent ADDCONSTRAINT a_posCHECK (a>0);
1426

1527
CREATETABLEpart (
@@ -18,4 +30,48 @@ CREATE TABLE part (
1830

1931
CREATETABLEpart1 PARTITION OF part FORVALUESFROM (1) to (100);
2032

33+
CREATETABLEpart2 (aint);
34+
ALTERTABLE part ATTACH PARTITION part2 FORVALUESFROM (101) to (200);
35+
ALTERTABLE part DETACH PARTITION part2;
36+
DROPTABLE part2;
37+
2138
ALTERTABLE part ADDPRIMARY KEY (a);
39+
40+
ALTERTABLE parent ALTER COLUMN aSETNOT NULL;
41+
ALTERTABLE parent ALTER COLUMN a DROPNOT NULL;
42+
ALTERTABLE parent ALTER COLUMN aSETNOT NULL;
43+
44+
ALTERTABLE parent ALTER COLUMN a ADD GENERATED ALWAYSAS IDENTITY;
45+
46+
ALTERTABLE parent ALTER COLUMN aSET GENERATED BY DEFAULT;
47+
48+
ALTERTABLE parent ALTER COLUMN a DROP IDENTITY;
49+
50+
ALTERTABLE parent ALTER COLUMN aSET STATISTICS100;
51+
52+
ALTERTABLE parent ALTER COLUMN aSET STORAGE PLAIN;
53+
54+
ALTERTABLE parent ENABLE ROW LEVEL SECURITY;
55+
ALTERTABLE parent NO FORCE ROW LEVEL SECURITY;
56+
ALTERTABLE parent FORCE ROW LEVEL SECURITY;
57+
ALTERTABLE parent DISABLE ROW LEVEL SECURITY;
58+
59+
CREATE STATISTICS parent_stat (dependencies)ON a, cFROM parent;
60+
61+
ALTERTABLE parent ALTER COLUMN c TYPEnumeric;
62+
63+
ALTERTABLE parent ALTER COLUMN cSET DEFAULT0;
64+
65+
CREATETABLEtbl (
66+
aint generated alwaysas (b::int*2) stored,
67+
btext
68+
);
69+
70+
ALTERTABLE tbl ALTER COLUMN a DROP EXPRESSION;
71+
72+
ALTERTABLE tbl ALTER COLUMN bSET COMPRESSION pglz;
73+
74+
CREATETYPEcomptypeAS (r float8);
75+
CREATEDOMAINdcomptypeAS comptype;
76+
ALTERDOMAIN dcomptype ADDCONSTRAINT c1check ((value).r>0);
77+
ALTERTYPE comptype ALTER ATTRIBUTE r TYPEbigint;

‎src/test/modules/test_ddl_deparse/sql/test_ddl_deparse.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,9 +29,9 @@ BEGIN
2929
-- if alter table, log more
3030
IF cmdtype='alter table' THEN
3131
FOR r2INSELECT*
32-
FROMunnest(public.get_altertable_subcmdtypes(r.command))
32+
FROMpublic.get_altertable_subcmdinfo(r.command)
3333
LOOP
34-
RAISE NOTICE' subcommand:%',r2.unnest;
34+
RAISE NOTICE' subcommand:type % desc %',r2.cmdtype,r2.objdesc;
3535
END LOOP;
3636
END IF;
3737
END LOOP;

‎src/test/modules/test_ddl_deparse/test_ddl_deparse--1.0.sql

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,8 @@ CREATE FUNCTION get_command_tag(pg_ddl_command)
1111
RETURNStext IMMUTABLE STRICT
1212
AS'MODULE_PATHNAME' LANGUAGE C;
1313

14-
CREATEFUNCTIONget_altertable_subcmdtypes(pg_ddl_command)
15-
RETURNStext[] IMMUTABLE STRICT
14+
CREATEFUNCTIONget_altertable_subcmdinfo(IN cmd pg_ddl_command,
15+
OUT cmdtypetext,
16+
OUT objdesctext)
17+
RETURNS SETOF record IMMUTABLE STRICT
1618
AS'MODULE_PATHNAME' LANGUAGE C;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp