Skip to content

Commit 894be11

Browse files
peterepetere
committed
Put generated_stored test objects in a schema
This avoids naming conflicts with concurrent tests with similarly named objects. Currently, there are none, but a tests for virtual generated columns are planned to be added. Reviewed-by: Corey Huinker <corey.huinker@gmail.com> Reviewed-by: Tomasz Rybak <tomasz.rybak@post.pl> Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
1 parent b9ed496 commit 894be11

File tree

2 files changed

+43
-36
lines changed

2 files changed

+43
-36
lines changed

‎src/test/regress/expected/generated_stored.out

+37-34
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,12 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
44
----------+---------+--------------
55
(0 rows)
66

7+
CREATE SCHEMA generated_stored_tests;
8+
GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
9+
SET search_path = generated_stored_tests;
710
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
811
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
9-
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_name LIKE 'gtest_' ORDER BY 1, 2;
12+
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
1013
table_name | column_name | column_default | is_nullable | is_generated | generation_expression
1114
------------+-------------+----------------+-------------+--------------+-----------------------
1215
gtest0 | a | | NO | NEVER |
@@ -15,14 +18,14 @@ SELECT table_name, column_name, column_default, is_nullable, is_generated, gener
1518
gtest1 | b | | YES | ALWAYS | (a * 2)
1619
(4 rows)
1720

18-
SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
21+
SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
1922
table_name | column_name | dependent_column
2023
------------+-------------+------------------
2124
gtest1 | a | b
2225
(1 row)
2326

2427
\d gtest1
25-
Table "public.gtest1"
28+
Table "generated_stored_tests.gtest1"
2629
Column | Type | Collation | Nullable | Default
2730
--------+---------+-----------+----------+------------------------------------
2831
a | integer | | not null |
@@ -270,7 +273,7 @@ SELECT * FROM gtest1_1;
270273
(0 rows)
271274

272275
\d gtest1_1
273-
Table "public.gtest1_1"
276+
Table "generated_stored_tests.gtest1_1"
274277
Column | Type | Collation | Nullable | Default
275278
--------+---------+-----------+----------+------------------------------------
276279
a | integer | | not null |
@@ -312,7 +315,7 @@ ERROR: column "b" inherits from generated column but specifies identity
312315
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
313316
NOTICE: merging column "b" with inherited definition
314317
\d+ gtestx
315-
Table "public.gtestx"
318+
Table "generated_stored_tests.gtestx"
316319
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
317320
--------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
318321
a | integer | | not null | | plain | |
@@ -348,7 +351,7 @@ NOTICE: merging multiple inherited definitions of column "b"
348351
NOTICE: moving and merging column "b" with inherited definition
349352
DETAIL: User-specified column moved to the position of the inherited column.
350353
\d gtest1_y
351-
Table "public.gtest1_y"
354+
Table "generated_stored_tests.gtest1_y"
352355
Column | Type | Collation | Nullable | Default
353356
--------+---------+-----------+----------+------------------------------------
354357
a | integer | | not null |
@@ -523,7 +526,7 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
523526
ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
524527
NOTICE: drop cascades to column c of table gtest10
525528
\d gtest10
526-
Table "public.gtest10"
529+
Table "generated_stored_tests.gtest10"
527530
Column | Type | Collation | Nullable | Default
528531
--------+---------+-----------+----------+---------
529532
a | integer | | not null |
@@ -622,7 +625,7 @@ CREATE INDEX gtest22c_b_idx ON gtest22c (b);
622625
CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
623626
CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
624627
\d gtest22c
625-
Table "public.gtest22c"
628+
Table "generated_stored_tests.gtest22c"
626629
Column | Type | Collation | Nullable | Default
627630
--------+---------+-----------+----------+------------------------------------
628631
a | integer | | |
@@ -726,7 +729,7 @@ CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR
726729
ERROR: invalid ON DELETE action for foreign key constraint containing generated column
727730
CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));
728731
\d gtest23b
729-
Table "public.gtest23b"
732+
Table "generated_stored_tests.gtest23b"
730733
Column | Type | Collation | Nullable | Default
731734
--------+---------+-----------+----------+------------------------------------
732735
a | integer | | not null |
@@ -805,7 +808,7 @@ DROP TABLE gtest_child3;
805808
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
806809
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
807810
\d gtest_child
808-
Table "public.gtest_child"
811+
Table "generated_stored_tests.gtest_child"
809812
Column | Type | Collation | Nullable | Default
810813
--------+--------+-----------+----------+-------------------------------------
811814
f1 | date | | not null |
@@ -814,7 +817,7 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
814817
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
815818

816819
\d gtest_child2
817-
Table "public.gtest_child2"
820+
Table "generated_stored_tests.gtest_child2"
818821
Column | Type | Collation | Nullable | Default
819822
--------+--------+-----------+----------+--------------------------------------
820823
f1 | date | | not null |
@@ -823,7 +826,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
823826
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
824827

825828
\d gtest_child3
826-
Table "public.gtest_child3"
829+
Table "generated_stored_tests.gtest_child3"
827830
Column | Type | Collation | Nullable | Default
828831
--------+--------+-----------+----------+--------------------------------------
829832
f1 | date | | not null |
@@ -855,7 +858,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
855858
ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
856859
ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
857860
\d gtest_parent
858-
Partitioned table "public.gtest_parent"
861+
Partitioned table "generated_stored_tests.gtest_parent"
859862
Column | Type | Collation | Nullable | Default
860863
--------+--------+-----------+----------+-------------------------------------
861864
f1 | date | | not null |
@@ -865,7 +868,7 @@ Partition key: RANGE (f1)
865868
Number of partitions: 3 (Use \d+ to list them.)
866869

867870
\d gtest_child
868-
Table "public.gtest_child"
871+
Table "generated_stored_tests.gtest_child"
869872
Column | Type | Collation | Nullable | Default
870873
--------+--------+-----------+----------+--------------------------------------
871874
f1 | date | | not null |
@@ -874,7 +877,7 @@ Number of partitions: 3 (Use \d+ to list them.)
874877
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
875878

876879
\d gtest_child2
877-
Table "public.gtest_child2"
880+
Table "generated_stored_tests.gtest_child2"
878881
Column | Type | Collation | Nullable | Default
879882
--------+--------+-----------+----------+--------------------------------------
880883
f1 | date | | not null |
@@ -883,7 +886,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
883886
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
884887

885888
\d gtest_child3
886-
Table "public.gtest_child3"
889+
Table "generated_stored_tests.gtest_child3"
887890
Column | Type | Collation | Nullable | Default
888891
--------+--------+-----------+----------+--------------------------------------
889892
f1 | date | | not null |
@@ -902,7 +905,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
902905
-- alter generation expression of parent and all its children altogether
903906
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
904907
\d gtest_parent
905-
Partitioned table "public.gtest_parent"
908+
Partitioned table "generated_stored_tests.gtest_parent"
906909
Column | Type | Collation | Nullable | Default
907910
--------+--------+-----------+----------+-------------------------------------
908911
f1 | date | | not null |
@@ -912,7 +915,7 @@ Partition key: RANGE (f1)
912915
Number of partitions: 3 (Use \d+ to list them.)
913916

914917
\d gtest_child
915-
Table "public.gtest_child"
918+
Table "generated_stored_tests.gtest_child"
916919
Column | Type | Collation | Nullable | Default
917920
--------+--------+-----------+----------+-------------------------------------
918921
f1 | date | | not null |
@@ -921,7 +924,7 @@ Number of partitions: 3 (Use \d+ to list them.)
921924
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
922925

923926
\d gtest_child2
924-
Table "public.gtest_child2"
927+
Table "generated_stored_tests.gtest_child2"
925928
Column | Type | Collation | Nullable | Default
926929
--------+--------+-----------+----------+-------------------------------------
927930
f1 | date | | not null |
@@ -930,7 +933,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
930933
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
931934

932935
\d gtest_child3
933-
Table "public.gtest_child3"
936+
Table "generated_stored_tests.gtest_child3"
934937
Column | Type | Collation | Nullable | Default
935938
--------+--------+-----------+----------+-------------------------------------
936939
f1 | date | | not null |
@@ -987,7 +990,7 @@ SELECT * FROM gtest25 ORDER BY a;
987990
(2 rows)
988991

989992
\d gtest25
990-
Table "public.gtest25"
993+
Table "generated_stored_tests.gtest25"
991994
Column | Type | Collation | Nullable | Default
992995
--------+------------------+-----------+----------+------------------------------------------------------
993996
a | integer | | not null |
@@ -1011,7 +1014,7 @@ ERROR: cannot alter type of a column used by a generated column
10111014
DETAIL: Column "a" is used by generated column "x".
10121015
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
10131016
\d gtest27
1014-
Table "public.gtest27"
1017+
Table "generated_stored_tests.gtest27"
10151018
Column | Type | Collation | Nullable | Default
10161019
--------+---------+-----------+----------+--------------------------------------------
10171020
a | integer | | |
@@ -1038,7 +1041,7 @@ ALTER TABLE gtest27
10381041
ALTER COLUMN b TYPE bigint,
10391042
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
10401043
\d gtest27
1041-
Table "public.gtest27"
1044+
Table "generated_stored_tests.gtest27"
10421045
Column | Type | Collation | Nullable | Default
10431046
--------+--------+-----------+----------+------------------------------------------
10441047
a | bigint | | |
@@ -1052,7 +1055,7 @@ ALTER TABLE gtest27
10521055
ERROR: cannot alter type of a column used by a generated column
10531056
DETAIL: Column "a" is used by generated column "x".
10541057
\d gtest27
1055-
Table "public.gtest27"
1058+
Table "generated_stored_tests.gtest27"
10561059
Column | Type | Collation | Nullable | Default
10571060
--------+--------+-----------+----------+------------------------------------------
10581061
a | bigint | | |
@@ -1080,7 +1083,7 @@ SELECT * FROM gtest29;
10801083
(2 rows)
10811084

10821085
\d gtest29
1083-
Table "public.gtest29"
1086+
Table "generated_stored_tests.gtest29"
10841087
Column | Type | Collation | Nullable | Default
10851088
--------+---------+-----------+----------+------------------------------------
10861089
a | integer | | |
@@ -1102,7 +1105,7 @@ SELECT * FROM gtest29;
11021105
(2 rows)
11031106

11041107
\d gtest29
1105-
Table "public.gtest29"
1108+
Table "generated_stored_tests.gtest29"
11061109
Column | Type | Collation | Nullable | Default
11071110
--------+---------+-----------+----------+------------------------------------
11081111
a | integer | | |
@@ -1121,7 +1124,7 @@ SELECT * FROM gtest29;
11211124
(4 rows)
11221125

11231126
\d gtest29
1124-
Table "public.gtest29"
1127+
Table "generated_stored_tests.gtest29"
11251128
Column | Type | Collation | Nullable | Default
11261129
--------+---------+-----------+----------+---------
11271130
a | integer | | |
@@ -1130,7 +1133,7 @@ SELECT * FROM gtest29;
11301133
-- check that dependencies between columns have also been removed
11311134
ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
11321135
\d gtest29
1133-
Table "public.gtest29"
1136+
Table "generated_stored_tests.gtest29"
11341137
Column | Type | Collation | Nullable | Default
11351138
--------+---------+-----------+----------+---------
11361139
b | integer | | |
@@ -1143,15 +1146,15 @@ CREATE TABLE gtest30 (
11431146
CREATE TABLE gtest30_1 () INHERITS (gtest30);
11441147
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
11451148
\d gtest30
1146-
Table "public.gtest30"
1149+
Table "generated_stored_tests.gtest30"
11471150
Column | Type | Collation | Nullable | Default
11481151
--------+---------+-----------+----------+---------
11491152
a | integer | | |
11501153
b | integer | | |
11511154
Number of child tables: 1 (Use \d+ to list them.)
11521155

11531156
\d gtest30_1
1154-
Table "public.gtest30_1"
1157+
Table "generated_stored_tests.gtest30_1"
11551158
Column | Type | Collation | Nullable | Default
11561159
--------+---------+-----------+----------+---------
11571160
a | integer | | |
@@ -1168,15 +1171,15 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
11681171
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
11691172
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
11701173
\d gtest30
1171-
Table "public.gtest30"
1174+
Table "generated_stored_tests.gtest30"
11721175
Column | Type | Collation | Nullable | Default
11731176
--------+---------+-----------+----------+------------------------------------
11741177
a | integer | | |
11751178
b | integer | | | generated always as (a * 2) stored
11761179
Number of child tables: 1 (Use \d+ to list them.)
11771180

11781181
\d gtest30_1
1179-
Table "public.gtest30_1"
1182+
Table "generated_stored_tests.gtest30_1"
11801183
Column | Type | Collation | Nullable | Default
11811184
--------+---------+-----------+----------+------------------------------------
11821185
a | integer | | |
@@ -1337,14 +1340,14 @@ CREATE TABLE gtest28a (
13371340
ALTER TABLE gtest28a DROP COLUMN a;
13381341
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
13391342
\d gtest28*
1340-
Table "public.gtest28a"
1343+
Table "generated_stored_tests.gtest28a"
13411344
Column | Type | Collation | Nullable | Default
13421345
--------+---------+-----------+----------+------------------------------------
13431346
b | integer | | |
13441347
c | integer | | |
13451348
x | integer | | | generated always as (b * 2) stored
13461349

1347-
Table "public.gtest28b"
1350+
Table "generated_stored_tests.gtest28b"
13481351
Column | Type | Collation | Nullable | Default
13491352
--------+---------+-----------+----------+------------------------------------
13501353
b | integer | | |

‎src/test/regress/sql/generated_stored.sql

+6-2
Original file line numberDiff line numberDiff line change
@@ -2,12 +2,16 @@
22
SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's');
33

44

5+
CREATE SCHEMA generated_stored_tests;
6+
GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
7+
SET search_path = generated_stored_tests;
8+
59
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
610
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
711

8-
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_name LIKE 'gtest_' ORDER BY 1, 2;
12+
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
913

10-
SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
14+
SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
1115

1216
\d gtest1
1317

0 commit comments

Comments
 (0)