From 7241911782a7420e38b6e50b57d304ea48bc5362 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 1 Apr 2019 10:46:37 +0200 Subject: [PATCH] Catch syntax error in generated column definition The syntax GENERATED BY DEFAULT AS (expr) is not allowed but we have to accept it in the grammar to avoid shift/reduce conflicts because of the similar syntax for identity columns. The existing code just ignored this, incorrectly. Add an explicit error check and a bespoke error message. Reported-by: Justin Pryzby --- src/backend/parser/gram.y | 13 +++++++++++++ src/test/regress/expected/generated.out | 5 +++++ src/test/regress/sql/generated.sql | 3 +++ 3 files changed, 21 insertions(+) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 01521789e8..b51f12dc23 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3505,6 +3505,19 @@ ColConstraintElem: n->raw_expr = $5; n->cooked_expr = NULL; n->location = @1; + + /* + * Can't do this in the grammar because of shift/reduce + * conflicts. (IDENTITY allows both ALWAYS and BY + * DEFAULT, but generated columns only allow ALWAYS.) We + * can also give a more useful error message and location. + */ + if ($2 != ATTRIBUTE_IDENTITY_ALWAYS) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("for a generated column, GENERATED ALWAYS must be specified"), + parser_errposition(@2))); + $$ = (Node *)n; } | REFERENCES qualified_name opt_column_list key_match key_actions diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index e0d1d88f17..d4ed3f7ae1 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -85,6 +85,11 @@ CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generat ERROR: set-returning functions are not allowed in column generation expressions LINE 1: ...7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_s... ^ +-- GENERATED BY DEFAULT not allowed +CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED); +ERROR: for a generated column, GENERATED ALWAYS must be specified +LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT... + ^ INSERT INTO gtest1 VALUES (1); INSERT INTO gtest1 VALUES (2, DEFAULT); INSERT INTO gtest1 VALUES (3, 33); -- error diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index 6d71594dc4..da11b8c9b8 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -37,6 +37,9 @@ CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_num CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) STORED); CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) STORED); +-- GENERATED BY DEFAULT not allowed +CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED); + INSERT INTO gtest1 VALUES (1); INSERT INTO gtest1 VALUES (2, DEFAULT); INSERT INTO gtest1 VALUES (3, 33); -- error -- 2.40.0