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

Commit9bc77c4

Browse files
committed
Various fixes around ON CONFLICT for rule deparsing.
Neither the deparsing of the new alias for INSERT's target table, nor ofthe inference clause was supported. Also fixup a typo in an errormessage.Add regression tests to test those code paths.Author: Peter Geoghegan
1 parent0740cbd commit9bc77c4

File tree

5 files changed

+140
-23
lines changed

5 files changed

+140
-23
lines changed

‎src/backend/parser/parse_clause.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2765,7 +2765,7 @@ transformOnConflictArbiter(ParseState *pstate,
27652765
ereport(ERROR,
27662766
(errcode(ERRCODE_SYNTAX_ERROR),
27672767
errmsg("ON CONFLICT DO UPDATE requires inference specification or constraint name"),
2768-
errhint("For example, ON CONFLICTON CONFLICT(<column>)."),
2768+
errhint("For example, ON CONFLICT (<column>)."),
27692769
parser_errposition(pstate,
27702770
exprLocation((Node*)onConflictClause))));
27712771

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

Lines changed: 77 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5392,6 +5392,10 @@ get_insert_query_def(Query *query, deparse_context *context)
53925392
}
53935393
appendStringInfo(buf,"INSERT INTO %s ",
53945394
generate_relation_name(rte->relid,NIL));
5395+
/* INSERT requires AS keyword for target alias */
5396+
if (rte->alias!=NULL)
5397+
appendStringInfo(buf,"AS %s ",
5398+
quote_identifier(rte->alias->aliasname));
53955399

53965400
/*
53975401
* Add the insert-column-names list. To handle indirection properly, we
@@ -5479,13 +5483,38 @@ get_insert_query_def(Query *query, deparse_context *context)
54795483
{
54805484
OnConflictExpr*confl=query->onConflict;
54815485

5486+
appendStringInfo(buf," ON CONFLICT");
5487+
5488+
if (confl->arbiterElems)
5489+
{
5490+
/* Add the single-VALUES expression list */
5491+
appendStringInfoChar(buf,'(');
5492+
get_rule_expr((Node*)confl->arbiterElems,context, false);
5493+
appendStringInfoChar(buf,')');
5494+
5495+
/* Add a WHERE clause (for partial indexes) if given */
5496+
if (confl->arbiterWhere!=NULL)
5497+
{
5498+
appendContextKeyword(context," WHERE ",
5499+
-PRETTYINDENT_STD,PRETTYINDENT_STD,1);
5500+
get_rule_expr(confl->arbiterWhere,context, false);
5501+
}
5502+
}
5503+
else
5504+
{
5505+
char*constraint=get_constraint_name(confl->constraint);
5506+
5507+
appendStringInfo(buf," ON CONSTRAINT %s",
5508+
quote_qualified_identifier(NULL,constraint));
5509+
}
5510+
54825511
if (confl->action==ONCONFLICT_NOTHING)
54835512
{
5484-
appendStringInfoString(buf,"ON CONFLICTDO NOTHING");
5513+
appendStringInfoString(buf," DO NOTHING");
54855514
}
54865515
else
54875516
{
5488-
appendStringInfoString(buf,"ON CONFLICTDO UPDATE SET ");
5517+
appendStringInfoString(buf," DO UPDATE SET ");
54895518
/* Deparse targetlist */
54905519
get_update_query_targetlist_def(query,confl->onConflictSet,
54915520
context,rte);
@@ -7886,6 +7915,52 @@ get_rule_expr(Node *node, deparse_context *context,
78867915
}
78877916
break;
78887917

7918+
caseT_InferenceElem:
7919+
{
7920+
InferenceElem*iexpr= (InferenceElem*)node;
7921+
boolvarprefix=context->varprefix;
7922+
boolneed_parens;
7923+
7924+
/*
7925+
* InferenceElem can only refer to target relation, so a
7926+
* prefix is never useful.
7927+
*/
7928+
context->varprefix= false;
7929+
7930+
/*
7931+
* Parenthesize the element unless it's a simple Var or a bare
7932+
* function call. Follows pg_get_indexdef_worker().
7933+
*/
7934+
need_parens= !IsA(iexpr->expr,Var);
7935+
if (IsA(iexpr->expr,FuncExpr)&&
7936+
((FuncExpr*)iexpr->expr)->funcformat==
7937+
COERCE_EXPLICIT_CALL)
7938+
need_parens= false;
7939+
7940+
if (need_parens)
7941+
appendStringInfoChar(buf,'(');
7942+
get_rule_expr((Node*)iexpr->expr,
7943+
context, false);
7944+
if (need_parens)
7945+
appendStringInfoChar(buf,')');
7946+
7947+
context->varprefix=varprefix;
7948+
7949+
if (iexpr->infercollid)
7950+
appendStringInfo(buf," COLLATE %s",
7951+
generate_collation_name(iexpr->infercollid));
7952+
7953+
/* Add the operator class name, if not default */
7954+
if (iexpr->inferopclass)
7955+
{
7956+
Oidinferopclass=iexpr->inferopclass;
7957+
Oidinferopcinputtype=get_opclass_input_type(iexpr->inferopclass);
7958+
7959+
get_opclass_name(inferopclass,inferopcinputtype,buf);
7960+
}
7961+
}
7962+
break;
7963+
78897964
caseT_List:
78907965
{
78917966
char*sep;

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -208,7 +208,7 @@ insert into insertconflicttest values (1, 'Apple') on conflict do update set fru
208208
ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
209209
LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic...
210210
^
211-
HINT: For example, ON CONFLICTON CONFLICT(<column>).
211+
HINT: For example, ON CONFLICT (<column>).
212212
-- inference succeeds:
213213
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit;
214214
insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit;

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

Lines changed: 49 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -2676,6 +2676,34 @@ Rules:
26762676
ON DELETE TO rules_src DO
26772677
NOTIFY rules_src_deletion
26782678

2679+
--
2680+
-- Ensure a aliased target relation for insert is correctly deparsed.
2681+
--
2682+
create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
2683+
create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
2684+
\d+ rules_src
2685+
Table "public.rules_src"
2686+
Column | Type | Modifiers | Storage | Stats target | Description
2687+
--------+---------+-----------+---------+--------------+-------------
2688+
f1 | integer | | plain | |
2689+
f2 | integer | | plain | |
2690+
Rules:
2691+
r1 AS
2692+
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
2693+
r2 AS
2694+
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
2695+
r3 AS
2696+
ON DELETE TO rules_src DO
2697+
NOTIFY rules_src_deletion
2698+
r4 AS
2699+
ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
2700+
new.f2
2701+
RETURNING trgt.f1,
2702+
trgt.f2
2703+
r5 AS
2704+
ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
2705+
WHERE trgt.f1 = new.f1
2706+
26792707
--
26802708
-- check alter rename rule
26812709
--
@@ -2778,16 +2806,19 @@ CREATE TABLE hats (
27782806
hat_color char(10) -- hat color
27792807
);
27802808
CREATE TABLE hat_data (
2781-
hat_name char(10) primary key,
2809+
hat_name char(10),
27822810
hat_color char(10) -- hat color
27832811
);
2812+
create unique index hat_data_unique_idx
2813+
on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
27842814
-- okay
27852815
CREATE RULE hat_nosert AS ON INSERT TO hats
27862816
DO INSTEAD
27872817
INSERT INTO hat_data VALUES (
27882818
NEW.hat_name,
27892819
NEW.hat_color)
2790-
ON CONFLICT (hat_name) DO NOTHING RETURNING *;
2820+
ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green'
2821+
DO NOTHING RETURNING *;
27912822
-- Works (projects row)
27922823
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
27932824
hat_name | hat_color
@@ -2803,12 +2834,13 @@ INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
28032834

28042835
SELECT tablename, rulename, definition FROM pg_rules
28052836
WHERE tablename = 'hats';
2806-
tablename | rulename | definition
2807-
-----------+------------+------------------------------------------------------------------------------
2808-
hats | hat_nosert | CREATE RULE hat_nosert AS +
2809-
| | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
2810-
| | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
2811-
| | RETURNING hat_data.hat_name, +
2837+
tablename | rulename | definition
2838+
-----------+------------+---------------------------------------------------------------------------------------------
2839+
hats | hat_nosert | CREATE RULE hat_nosert AS +
2840+
| | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
2841+
| | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
2842+
| | WHERE (hat_data.hat_color = 'green'::bpchar) DO NOTHING +
2843+
| | RETURNING hat_data.hat_name, +
28122844
| | hat_data.hat_color;
28132845
(1 row)
28142846

@@ -2861,13 +2893,13 @@ SELECT * FROM hat_data WHERE hat_name = 'h8';
28612893

28622894
SELECT tablename, rulename, definition FROM pg_rules
28632895
WHERE tablename = 'hats';
2864-
tablename | rulename | definition
2865-
-----------+------------+-------------------------------------------------------------------------------------------------------------------------------
2866-
hats | hat_upsert | CREATE RULE hat_upsert AS +
2867-
| | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
2868-
| | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO UPDATE SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color+
2869-
| | WHERE (excluded.hat_color <> 'forbidden'::bpchar) +
2870-
| | RETURNING hat_data.hat_name, +
2896+
tablename | rulename |definition
2897+
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------
2898+
hats | hat_upsert | CREATE RULE hat_upsert AS+
2899+
| | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
2900+
| | 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+
2901+
| | WHERE (excluded.hat_color <> 'forbidden'::bpchar)+
2902+
| | RETURNING hat_data.hat_name,+
28712903
| | hat_data.hat_color;
28722904
(1 row)
28732905

@@ -2877,7 +2909,7 @@ explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
28772909
----------------------------------------------------------------
28782910
Insert on hat_data
28792911
Conflict Resolution: UPDATE
2880-
Conflict Arbiter Indexes:hat_data_pkey
2912+
Conflict Arbiter Indexes:hat_data_unique_idx
28812913
Conflict Filter: (excluded.hat_color <> 'forbidden'::bpchar)
28822914
-> Result
28832915
(5 rows)
@@ -2909,7 +2941,7 @@ RETURNING *;
29092941
----------------------------------------------------------------
29102942
Insert on hat_data
29112943
Conflict Resolution: UPDATE
2912-
Conflict Arbiter Indexes:hat_data_pkey
2944+
Conflict Arbiter Indexes:hat_data_unique_idx
29132945
Conflict Filter: (excluded.hat_color <> 'forbidden'::bpchar)
29142946
CTE data
29152947
-> Values Scan on "*VALUES*"

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

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -996,6 +996,13 @@ select * from rules_log;
996996
createruler3ason delete to rules_src do notify rules_src_deletion;
997997
\d+ rules_src
998998

999+
--
1000+
-- Ensure a aliased target relation for insert is correctly deparsed.
1001+
--
1002+
createruler4ason insert to rules_src do insteadinsert into rules_logAS trgtSELECT NEW.* RETURNINGtrgt.f1,trgt.f2;
1003+
createruler5asonupdate to rules_src do insteadUPDATE rules_logAS trgtSET tag='updated'WHEREtrgt.f1=new.f1;
1004+
\d+ rules_src
1005+
9991006
--
10001007
-- check alter rename rule
10011008
--
@@ -1049,17 +1056,20 @@ CREATE TABLE hats (
10491056
);
10501057

10511058
CREATETABLEhat_data (
1052-
hat_namechar(10)primary key,
1059+
hat_namechar(10),
10531060
hat_colorchar(10)-- hat color
10541061
);
1062+
createunique indexhat_data_unique_idx
1063+
on hat_data (hat_name COLLATE"C" bpchar_pattern_ops);
10551064

10561065
-- okay
10571066
CREATERULEhat_nosertASON INSERT TO hats
10581067
DO INSTEAD
10591068
INSERT INTO hat_dataVALUES (
10601069
NEW.hat_name,
10611070
NEW.hat_color)
1062-
ON CONFLICT (hat_name) DO NOTHING RETURNING*;
1071+
ON CONFLICT (hat_name COLLATE"C" bpchar_pattern_ops)WHERE hat_color='green'
1072+
DO NOTHING RETURNING*;
10631073

10641074
-- Works (projects row)
10651075
INSERT INTO hatsVALUES ('h7','black') RETURNING*;

0 commit comments

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