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

Commit284bef2

Browse files
committed
Fix yet another bug in ON CONFLICT rule deparsing.
Expand testing of rule deparsing a good bit, it's evidently needed.Author: Peter Geoghegan, Andres FreundDiscussion: CAM3SWZQmXxZhQC32QVEOTYfNXJBJ_Q2SDENL7BV14Cq-zL0FLg@mail.gmail.com
1 parent631d749 commit284bef2

File tree

3 files changed

+75
-5
lines changed

3 files changed

+75
-5
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5500,7 +5500,7 @@ get_insert_query_def(Query *query, deparse_context *context)
55005500
get_rule_expr(confl->arbiterWhere,context, false);
55015501
}
55025502
}
5503-
else
5503+
elseif (confl->constraint!=InvalidOid)
55045504
{
55055505
char*constraint=get_constraint_name(confl->constraint);
55065506

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

Lines changed: 53 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2811,14 +2811,26 @@ CREATE TABLE hat_data (
28112811
);
28122812
create unique index hat_data_unique_idx
28132813
on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
2814-
--okay
2814+
--DO NOTHING with ON CONFLICT
28152815
CREATE RULE hat_nosert AS ON INSERT TO hats
28162816
DO INSTEAD
28172817
INSERT INTO hat_data VALUES (
28182818
NEW.hat_name,
28192819
NEW.hat_color)
28202820
ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green'
2821-
DO NOTHING RETURNING *;
2821+
DO NOTHING
2822+
RETURNING *;
2823+
SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
2824+
definition
2825+
---------------------------------------------------------------------------------------------
2826+
CREATE RULE hat_nosert AS +
2827+
ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
2828+
VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
2829+
WHERE (hat_data.hat_color = 'green'::bpchar) DO NOTHING +
2830+
RETURNING hat_data.hat_name, +
2831+
hat_data.hat_color;
2832+
(1 row)
2833+
28222834
-- Works (projects row)
28232835
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
28242836
hat_name | hat_color
@@ -2845,6 +2857,34 @@ SELECT tablename, rulename, definition FROM pg_rules
28452857
(1 row)
28462858

28472859
DROP RULE hat_nosert ON hats;
2860+
-- DO NOTHING without ON CONFLICT
2861+
CREATE RULE hat_nosert_all AS ON INSERT TO hats
2862+
DO INSTEAD
2863+
INSERT INTO hat_data VALUES (
2864+
NEW.hat_name,
2865+
NEW.hat_color)
2866+
ON CONFLICT
2867+
DO NOTHING
2868+
RETURNING *;
2869+
SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
2870+
definition
2871+
------------------------------------------------------------------------------
2872+
CREATE RULE hat_nosert_all AS +
2873+
ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
2874+
VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
2875+
RETURNING hat_data.hat_name, +
2876+
hat_data.hat_color;
2877+
(1 row)
2878+
2879+
DROP RULE hat_nosert_all ON hats;
2880+
-- Works (does nothing)
2881+
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
2882+
hat_name | hat_color
2883+
------------+------------
2884+
h7 | black
2885+
(1 row)
2886+
2887+
-- DO UPDATE with a WHERE clause
28482888
CREATE RULE hat_upsert AS ON INSERT TO hats
28492889
DO INSTEAD
28502890
INSERT INTO hat_data VALUES (
@@ -2855,6 +2895,17 @@ CREATE RULE hat_upsert AS ON INSERT TO hats
28552895
SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color
28562896
WHERE excluded.hat_color <> 'forbidden'
28572897
RETURNING *;
2898+
SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
2899+
definition
2900+
-----------------------------------------------------------------------------------------------------------------------------------------
2901+
CREATE RULE hat_upsert AS +
2902+
ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
2903+
VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO UPDATE SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color+
2904+
WHERE (excluded.hat_color <> 'forbidden'::bpchar) +
2905+
RETURNING hat_data.hat_name, +
2906+
hat_data.hat_color;
2907+
(1 row)
2908+
28582909
-- Works (does upsert)
28592910
INSERT INTO hats VALUES ('h8', 'black') RETURNING *;
28602911
hat_name | hat_color

‎src/test/regress/sql/rules.sql

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1062,14 +1062,16 @@ CREATE TABLE hat_data (
10621062
createunique indexhat_data_unique_idx
10631063
on hat_data (hat_name COLLATE"C" bpchar_pattern_ops);
10641064

1065-
--okay
1065+
--DO NOTHING with ON CONFLICT
10661066
CREATERULEhat_nosertASON INSERT TO hats
10671067
DO INSTEAD
10681068
INSERT INTO hat_dataVALUES (
10691069
NEW.hat_name,
10701070
NEW.hat_color)
10711071
ON CONFLICT (hat_name COLLATE"C" bpchar_pattern_ops)WHERE hat_color='green'
1072-
DO NOTHING RETURNING*;
1072+
DO NOTHING
1073+
RETURNING*;
1074+
SELECT definitionFROM pg_rulesWHERE tablename='hats'ORDER BY rulename;
10731075

10741076
-- Works (projects row)
10751077
INSERT INTO hatsVALUES ('h7','black') RETURNING*;
@@ -1079,6 +1081,22 @@ SELECT tablename, rulename, definition FROM pg_rules
10791081
WHERE tablename='hats';
10801082
DROPRULE hat_nosertON hats;
10811083

1084+
-- DO NOTHING without ON CONFLICT
1085+
CREATERULEhat_nosert_allASON INSERT TO hats
1086+
DO INSTEAD
1087+
INSERT INTO hat_dataVALUES (
1088+
NEW.hat_name,
1089+
NEW.hat_color)
1090+
ON CONFLICT
1091+
DO NOTHING
1092+
RETURNING*;
1093+
SELECT definitionFROM pg_rulesWHERE tablename='hats'ORDER BY rulename;
1094+
DROPRULE hat_nosert_allON hats;
1095+
1096+
-- Works (does nothing)
1097+
INSERT INTO hatsVALUES ('h7','black') RETURNING*;
1098+
1099+
-- DO UPDATE with a WHERE clause
10821100
CREATERULEhat_upsertASON INSERT TO hats
10831101
DO INSTEAD
10841102
INSERT INTO hat_dataVALUES (
@@ -1089,6 +1107,7 @@ CREATE RULE hat_upsert AS ON INSERT TO hats
10891107
SET hat_name=hat_data.hat_name, hat_color=excluded.hat_color
10901108
WHEREexcluded.hat_color<>'forbidden'
10911109
RETURNING*;
1110+
SELECT definitionFROM pg_rulesWHERE tablename='hats'ORDER BY rulename;
10921111

10931112
-- Works (does upsert)
10941113
INSERT INTO hatsVALUES ('h8','black') RETURNING*;

0 commit comments

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