From 704c9dc00a26007ef4a091d739540ad39af8af33 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 3 Nov 2023 16:18:47 +0100 Subject: [PATCH] Forbid explicitly creating ON SELECT rules Commit b23cd185fd disallowed ON SELECT rules to be created on tables, but the documentation of CREATE RULE was not modified accordingly. Since it is no longer possible to explicitly create ON SELECT rules, we had better remove the option from the grammar and from the documentation of CREATE RULE. The documentation chapter about rules still discusses ON SELECT rules, since they are used in views, but there is now a sentence to make clear that you cannot explicitly create such rules any more. Adapt psql tab completion accordingly. Report: Joshua Uyehara Author: Laurenz Albe Discussion: https://postgr.es/m/18178-05534d7064044d2d%40postgresql.org --- doc/src/sgml/ref/create_rule.sgml | 33 +++++++++++------------------ doc/src/sgml/rules.sgml | 4 ++-- src/backend/parser/gram.y | 3 +-- src/bin/psql/tab-complete.c | 10 ++++----- src/test/regress/expected/rules.out | 22 ------------------- src/test/regress/sql/rules.sql | 21 ------------------ 6 files changed, 20 insertions(+), 73 deletions(-) diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index dbf4c93784..eea3781a9b 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -27,7 +27,7 @@ CREATE [ OR REPLACE ] RULE name AS where event can be one of: - SELECT | INSERT | UPDATE | DELETE + INSERT | UPDATE | DELETE @@ -59,15 +59,9 @@ CREATE [ OR REPLACE ] RULE name AS - Presently, ON SELECT rules must be unconditional - INSTEAD rules and must have actions that consist - of a single SELECT command. Thus, an - ON SELECT rule effectively turns the table into - a view, whose visible contents are the rows returned by the rule's - SELECT command rather than whatever had been - stored in the table (if anything). It is considered better style - to write a CREATE VIEW command than to create a - real table and define an ON SELECT rule for it. + There are also ON SELECT rules, but you can no + longer create them explicitly. An ON SELECT rule + is created implicitly by CREATE VIEW. @@ -134,7 +128,7 @@ CREATE [ OR REPLACE ] RULE name AS event - The event is one of SELECT, + The event is one of INSERT, UPDATE, or DELETE. Note that an INSERT containing an ON @@ -244,24 +238,21 @@ CREATE [ OR REPLACE ] RULE name AS It is very important to take care to avoid circular rules. For - example, though each of the following two rule definitions are + example, though the following rule definition is accepted by PostgreSQL, the - SELECT command would cause + INSERT command would cause PostgreSQL to report an error because of recursive expansion of a rule: -CREATE RULE "_RETURN" AS - ON SELECT TO t1 - DO INSTEAD - SELECT * FROM t2; +CREATE TABLE t1 (i integer); -CREATE RULE "_RETURN" AS - ON SELECT TO t2 +CREATE RULE recurse AS + ON INSERT TO t1 DO INSTEAD - SELECT * FROM t1; + INSERT INTO t1 VALUES (NEW.i + 1); -SELECT * FROM t1; +INSERT INTO t1 (i) VALUES (1); diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index d229b94d39..c9464f6309 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -297,8 +297,8 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; - although you can't actually write that, because tables are not - allowed to have ON SELECT rules. + although you can't actually write that, because you cannot explicitly + create ON SELECT rules. diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c224df4ecc..c6bee8b2c7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10815,8 +10815,7 @@ RuleActionStmtOrEmpty: | /*EMPTY*/ { $$ = NULL; } ; -event: SELECT { $$ = CMD_SELECT; } - | UPDATE { $$ = CMD_UPDATE; } +event: UPDATE { $$ = CMD_UPDATE; } | DELETE_P { $$ = CMD_DELETE; } | INSERT { $$ = CMD_INSERT; } ; diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 93742fc6ac..3bdc8cc250 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3153,16 +3153,16 @@ psql_completion(const char *text, int start, int end) /* * Complete "CREATE [ OR REPLACE ] RULE AS ON" with - * SELECT|UPDATE|INSERT|DELETE + * UPDATE|INSERT|DELETE */ else if (Matches("CREATE", "RULE", MatchAny, "AS", "ON") || Matches("CREATE", "OR", "REPLACE", "RULE", MatchAny, "AS", "ON")) - COMPLETE_WITH("SELECT", "UPDATE", "INSERT", "DELETE"); - /* Complete "AS ON SELECT|UPDATE|INSERT|DELETE" with a "TO" */ - else if (TailMatches("AS", "ON", "SELECT|UPDATE|INSERT|DELETE")) + COMPLETE_WITH("UPDATE", "INSERT", "DELETE"); + /* Complete "AS ON UPDATE|INSERT|DELETE" with a "TO" */ + else if (TailMatches("AS", "ON", "UPDATE|INSERT|DELETE")) COMPLETE_WITH("TO"); /* Complete "AS ON TO" with a table name */ - else if (TailMatches("AS", "ON", "SELECT|UPDATE|INSERT|DELETE", "TO")) + else if (TailMatches("AS", "ON", "UPDATE|INSERT|DELETE", "TO")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables); /* CREATE SCHEMA [ ] [ AUTHORIZATION ] */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2c60400ade..fca63b941f 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2743,28 +2743,6 @@ ERROR: cannot drop rule _RETURN on view rules_fooview because view rules_foovie HINT: You can drop view rules_fooview instead. drop view rules_fooview; -- --- We used to allow converting a table to a view by creating a "_RETURN" --- rule for it, but no more. --- -create table rules_fooview (x int, y text); -create rule "_RETURN" as on select to rules_fooview do instead - select 1 as x, 'aaa'::text as y; -ERROR: relation "rules_fooview" cannot have ON SELECT rules -DETAIL: This operation is not supported for tables. -drop table rules_fooview; --- likewise, converting a partitioned table or partition to view is not allowed -create table rules_fooview (x int, y text) partition by list (x); -create rule "_RETURN" as on select to rules_fooview do instead - select 1 as x, 'aaa'::text as y; -ERROR: relation "rules_fooview" cannot have ON SELECT rules -DETAIL: This operation is not supported for partitioned tables. -create table rules_fooview_part partition of rules_fooview for values in (1); -create rule "_RETURN" as on select to rules_fooview_part do instead - select 1 as x, 'aaa'::text as y; -ERROR: relation "rules_fooview_part" cannot have ON SELECT rules -DETAIL: This operation is not supported for tables. -drop table rules_fooview; --- -- check for planner problems with complex inherited UPDATES -- create table id (id serial primary key, name text); diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 8b7e255dcd..e3f8e10c33 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -883,27 +883,6 @@ create view rules_fooview as select 'rules_foo'::text; drop rule "_RETURN" on rules_fooview; drop view rules_fooview; --- --- We used to allow converting a table to a view by creating a "_RETURN" --- rule for it, but no more. --- - -create table rules_fooview (x int, y text); -create rule "_RETURN" as on select to rules_fooview do instead - select 1 as x, 'aaa'::text as y; -drop table rules_fooview; - --- likewise, converting a partitioned table or partition to view is not allowed -create table rules_fooview (x int, y text) partition by list (x); -create rule "_RETURN" as on select to rules_fooview do instead - select 1 as x, 'aaa'::text as y; - -create table rules_fooview_part partition of rules_fooview for values in (1); -create rule "_RETURN" as on select to rules_fooview_part do instead - select 1 as x, 'aaa'::text as y; - -drop table rules_fooview; - -- -- check for planner problems with complex inherited UPDATES -- -- 2.41.0