v3-0002-Put-generated_stored-test-objects-in-a-schema.patch
text/plain
Filename: v3-0002-Put-generated_stored-test-objects-in-a-schema.patch
Type: text/plain
Part: 1
Message:
Re: Virtual generated columns
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v3-0002
Subject: Put generated_stored test objects in a schema
| File | + | − |
|---|---|---|
| src/test/regress/expected/generated_stored.out | 37 | 34 |
| src/test/regress/sql/generated_stored.sql | 6 | 2 |
From 4cd22fc28030984a87cca2577b319dd4b1108422 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 8 Aug 2024 08:13:48 +0200
Subject: [PATCH v3 2/3] 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.
Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
---
.../regress/expected/generated_stored.out | 71 ++++++++++---------
src/test/regress/sql/generated_stored.sql | 8 ++-
2 files changed, 43 insertions(+), 36 deletions(-)
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 44058db7c1d..dd74a1a9f0b 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -4,9 +4,12 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
----------+---------+--------------
(0 rows)
+CREATE SCHEMA generated_stored_tests;
+GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
+SET search_path = generated_stored_tests;
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
-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;
+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;
table_name | column_name | column_default | is_nullable | is_generated | generation_expression
------------+-------------+----------------+-------------+--------------+-----------------------
gtest0 | a | | NO | NEVER |
@@ -15,14 +18,14 @@ SELECT table_name, column_name, column_default, is_nullable, is_generated, gener
gtest1 | b | | YES | ALWAYS | (a * 2)
(4 rows)
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
table_name | column_name | dependent_column
------------+-------------+------------------
gtest1 | a | b
(1 row)
\d gtest1
- Table "public.gtest1"
+ Table "generated_stored_tests.gtest1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -270,7 +273,7 @@ SELECT * FROM gtest1_1;
(0 rows)
\d gtest1_1
- Table "public.gtest1_1"
+ Table "generated_stored_tests.gtest1_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -312,7 +315,7 @@ ERROR: column "b" inherits from generated column but specifies identity
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
NOTICE: merging column "b" with inherited definition
\d+ gtestx
- Table "public.gtestx"
+ Table "generated_stored_tests.gtestx"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
a | integer | | not null | | plain | |
@@ -348,7 +351,7 @@ NOTICE: merging multiple inherited definitions of column "b"
NOTICE: moving and merging column "b" with inherited definition
DETAIL: User-specified column moved to the position of the inherited column.
\d gtest1_y
- Table "public.gtest1_y"
+ Table "generated_stored_tests.gtest1_y"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -523,7 +526,7 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
NOTICE: drop cascades to column c of table gtest10
\d gtest10
- Table "public.gtest10"
+ Table "generated_stored_tests.gtest10"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
@@ -622,7 +625,7 @@ CREATE INDEX gtest22c_b_idx ON gtest22c (b);
CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
\d gtest22c
- Table "public.gtest22c"
+ Table "generated_stored_tests.gtest22c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -726,7 +729,7 @@ CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR
ERROR: invalid ON DELETE action for foreign key constraint containing generated column
CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));
\d gtest23b
- Table "public.gtest23b"
+ Table "generated_stored_tests.gtest23b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
@@ -805,7 +808,7 @@ DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -814,7 +817,7 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -823,7 +826,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -855,7 +858,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_parent
- Partitioned table "public.gtest_parent"
+ Partitioned table "generated_stored_tests.gtest_parent"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -865,7 +868,7 @@ Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -874,7 +877,7 @@ Number of partitions: 3 (Use \d+ to list them.)
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -883,7 +886,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
@@ -902,7 +905,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
- Partitioned table "public.gtest_parent"
+ Partitioned table "generated_stored_tests.gtest_parent"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -912,7 +915,7 @@ Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "public.gtest_child"
+ Table "generated_stored_tests.gtest_child"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -921,7 +924,7 @@ Number of partitions: 3 (Use \d+ to list them.)
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "public.gtest_child2"
+ Table "generated_stored_tests.gtest_child2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -930,7 +933,7 @@ Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "public.gtest_child3"
+ Table "generated_stored_tests.gtest_child3"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-------------------------------------
f1 | date | | not null |
@@ -987,7 +990,7 @@ SELECT * FROM gtest25 ORDER BY a;
(2 rows)
\d gtest25
- Table "public.gtest25"
+ Table "generated_stored_tests.gtest25"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+------------------------------------------------------
a | integer | | not null |
@@ -1011,7 +1014,7 @@ ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------------
a | integer | | |
@@ -1037,7 +1040,7 @@ ALTER TABLE gtest27
ALTER COLUMN b TYPE bigint,
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+------------------------------------------
a | bigint | | |
@@ -1051,7 +1054,7 @@ ALTER TABLE gtest27
ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
\d gtest27
- Table "public.gtest27"
+ Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+------------------------------------------
a | bigint | | |
@@ -1079,7 +1082,7 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1101,7 +1104,7 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1120,7 +1123,7 @@ SELECT * FROM gtest29;
(4 rows)
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1129,7 +1132,7 @@ SELECT * FROM gtest29;
-- check that dependencies between columns have also been removed
ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
\d gtest29
- Table "public.gtest29"
+ Table "generated_stored_tests.gtest29"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
b | integer | | |
@@ -1142,7 +1145,7 @@ CREATE TABLE gtest30 (
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
\d gtest30
- Table "public.gtest30"
+ Table "generated_stored_tests.gtest30"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1150,7 +1153,7 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "public.gtest30_1"
+ Table "generated_stored_tests.gtest30_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
@@ -1167,7 +1170,7 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
\d gtest30
- Table "public.gtest30"
+ Table "generated_stored_tests.gtest30"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1175,7 +1178,7 @@ ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "public.gtest30_1"
+ Table "generated_stored_tests.gtest30_1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
@@ -1336,14 +1339,14 @@ CREATE TABLE gtest28a (
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
\d gtest28*
- Table "public.gtest28a"
+ Table "generated_stored_tests.gtest28a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
b | integer | | |
c | integer | | |
x | integer | | | generated always as (b * 2) stored
- Table "public.gtest28b"
+ Table "generated_stored_tests.gtest28b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
b | integer | | |
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index cb55d77821f..c18e0e1f655 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -2,12 +2,16 @@
SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's');
+CREATE SCHEMA generated_stored_tests;
+GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
+SET search_path = generated_stored_tests;
+
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
-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;
+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;
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
\d gtest1
--
2.46.0