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

Commitb1cb32f

Browse files
committed
Fix multiple assignments to a column of a domain type.
We allow INSERT and UPDATE commands to assign to the same column more thanonce, as long as the assignments are to subfields or elements rather thanthe whole column. However, this failed when the target column was a domainover array rather than plain array. Fix by teaching process_matched_tle()to look through CoerceToDomain nodes, and add relevant test cases.Also add a group of test cases exercising domains over array of composite.It's doubtless accidental that CREATE DOMAIN allows this case while notallowing straight domain over composite; but it does, so we'd better makesure we don't break it. (I could not find any documentation mentioningeither side of that, so no doc changes.)It's been like this for a long time, so back-patch to all supportedbranches.Discussion:https://postgr.es/m/4206.1499798337@sss.pgh.pa.us
1 parent42171e2 commitb1cb32f

File tree

3 files changed

+185
-4
lines changed

3 files changed

+185
-4
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -934,6 +934,7 @@ process_matched_tle(TargetEntry *src_tle,
934934
constchar*attrName)
935935
{
936936
TargetEntry*result;
937+
CoerceToDomain*coerce_expr=NULL;
937938
Node*src_expr;
938939
Node*prior_expr;
939940
Node*src_input;
@@ -970,10 +971,30 @@ process_matched_tle(TargetEntry *src_tle,
970971
* For FieldStore, instead of nesting we can generate a single
971972
* FieldStore with multiple target fields. We must nest when
972973
* ArrayRefs are involved though.
974+
*
975+
* As a further complication, the destination column might be a domain,
976+
* resulting in each assignment containing a CoerceToDomain node over a
977+
* FieldStore or ArrayRef. These should have matching target domains,
978+
* so we strip them and reconstitute a single CoerceToDomain over the
979+
* combined FieldStore/ArrayRef nodes. (Notice that this has the result
980+
* that the domain's checks are applied only after we do all the field or
981+
* element updates, not after each one. This is arguably desirable.)
973982
*----------
974983
*/
975984
src_expr= (Node*)src_tle->expr;
976985
prior_expr= (Node*)prior_tle->expr;
986+
987+
if (src_expr&&IsA(src_expr,CoerceToDomain)&&
988+
prior_expr&&IsA(prior_expr,CoerceToDomain)&&
989+
((CoerceToDomain*)src_expr)->resulttype==
990+
((CoerceToDomain*)prior_expr)->resulttype)
991+
{
992+
/* we assume without checking that resulttypmod/resultcollid match */
993+
coerce_expr= (CoerceToDomain*)src_expr;
994+
src_expr= (Node*) ((CoerceToDomain*)src_expr)->arg;
995+
prior_expr= (Node*) ((CoerceToDomain*)prior_expr)->arg;
996+
}
997+
977998
src_input=get_assignment_input(src_expr);
978999
prior_input=get_assignment_input(prior_expr);
9791000
if (src_input==NULL||
@@ -1042,6 +1063,16 @@ process_matched_tle(TargetEntry *src_tle,
10421063
newexpr=NULL;
10431064
}
10441065

1066+
if (coerce_expr)
1067+
{
1068+
/* put back the CoerceToDomain */
1069+
CoerceToDomain*newcoerce=makeNode(CoerceToDomain);
1070+
1071+
memcpy(newcoerce,coerce_expr,sizeof(CoerceToDomain));
1072+
newcoerce->arg= (Expr*)newexpr;
1073+
newexpr= (Node*)newcoerce;
1074+
}
1075+
10451076
result=flatCopyTargetEntry(src_tle);
10461077
result->expr= (Expr*)newexpr;
10471078
returnresult;

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

Lines changed: 106 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,7 @@ INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}');
107107
INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}');
108108
INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}');
109109
ERROR: value too long for type character varying(4)
110+
INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22);
110111
select * from domarrtest;
111112
testint4arr | testchar4arr
112113
---------------+---------------------
@@ -115,7 +116,8 @@ select * from domarrtest;
115116
{2,2} | {{a,b},{c,d},{e,f}}
116117
{2,2} | {{a},{c}}
117118
| {{a,b,c},{d,e,f}}
118-
(5 rows)
119+
{11,NULL,22} |
120+
(6 rows)
119121

120122
select testint4arr[1], testchar4arr[2:2] from domarrtest;
121123
testint4arr | testchar4arr
@@ -125,7 +127,8 @@ select testint4arr[1], testchar4arr[2:2] from domarrtest;
125127
2 | {{c,d}}
126128
2 | {{c}}
127129
| {{d,e,f}}
128-
(5 rows)
130+
11 |
131+
(6 rows)
129132

130133
select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
131134
array_dims | array_dims
@@ -135,7 +138,8 @@ select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
135138
[1:2] | [1:3][1:2]
136139
[1:2] | [1:2][1:1]
137140
| [1:2][1:3]
138-
(5 rows)
141+
[1:3] |
142+
(6 rows)
139143

140144
COPY domarrtest FROM stdin;
141145
COPY domarrtest FROM stdin;-- fail
@@ -149,9 +153,21 @@ select * from domarrtest;
149153
{2,2} | {{a,b},{c,d},{e,f}}
150154
{2,2} | {{a},{c}}
151155
| {{a,b,c},{d,e,f}}
156+
{11,NULL,22} |
152157
{3,4} | {q,w,e}
153158
|
154-
(7 rows)
159+
(8 rows)
160+
161+
update domarrtest set
162+
testint4arr[1] = testint4arr[1] + 1,
163+
testint4arr[3] = testint4arr[3] - 1
164+
where testchar4arr is null;
165+
select * from domarrtest where testchar4arr is null;
166+
testint4arr | testchar4arr
167+
------------------+--------------
168+
{12,NULL,21} |
169+
{NULL,NULL,NULL} |
170+
(2 rows)
155171

156172
drop table domarrtest;
157173
drop domain domainint4arr restrict;
@@ -182,6 +198,92 @@ select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia
182198
(1 row)
183199

184200
drop domain dia;
201+
-- Test domains over arrays of composite
202+
create type comptype as (r float8, i float8);
203+
create domain dcomptypea as comptype[];
204+
create table dcomptable (d1 dcomptypea unique);
205+
insert into dcomptable values (array[row(1,2)]::dcomptypea);
206+
insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]);
207+
insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]);
208+
insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness
209+
ERROR: duplicate key value violates unique constraint "dcomptable_d1_key"
210+
DETAIL: Key (d1)=({"(1,2)"}) already exists.
211+
insert into dcomptable (d1[1]) values(row(9,10));
212+
insert into dcomptable (d1[1].r) values(11);
213+
select * from dcomptable;
214+
d1
215+
--------------------
216+
{"(1,2)"}
217+
{"(3,4)","(5,6)"}
218+
{"(7,8)","(9,10)"}
219+
{"(9,10)"}
220+
{"(11,)"}
221+
(5 rows)
222+
223+
select d1[2], d1[1].r, d1[1].i from dcomptable;
224+
d1 | r | i
225+
--------+----+----
226+
| 1 | 2
227+
(5,6) | 3 | 4
228+
(9,10) | 7 | 8
229+
| 9 | 10
230+
| 11 |
231+
(5 rows)
232+
233+
update dcomptable set d1[2] = row(d1[2].i, d1[2].r);
234+
select * from dcomptable;
235+
d1
236+
--------------------
237+
{"(1,2)","(,)"}
238+
{"(3,4)","(6,5)"}
239+
{"(7,8)","(10,9)"}
240+
{"(9,10)","(,)"}
241+
{"(11,)","(,)"}
242+
(5 rows)
243+
244+
update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0;
245+
select * from dcomptable;
246+
d1
247+
--------------------
248+
{"(11,)","(,)"}
249+
{"(2,2)","(,)"}
250+
{"(4,4)","(6,5)"}
251+
{"(8,8)","(10,9)"}
252+
{"(10,10)","(,)"}
253+
(5 rows)
254+
255+
alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i);
256+
alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail
257+
ERROR: column "d1" of table "dcomptable" contains values that violate the new constraint
258+
select array[row(2,1)]::dcomptypea; -- fail
259+
ERROR: value for domain dcomptypea violates check constraint "c1"
260+
insert into dcomptable values (array[row(1,2)]::comptype[]);
261+
insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail
262+
ERROR: value for domain dcomptypea violates check constraint "c1"
263+
insert into dcomptable (d1[1].r) values(99);
264+
insert into dcomptable (d1[1].r, d1[1].i) values(99, 100);
265+
insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail
266+
ERROR: value for domain dcomptypea violates check constraint "c1"
267+
update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail
268+
ERROR: value for domain dcomptypea violates check constraint "c1"
269+
update dcomptable set d1[1].r = d1[1].r - 1 where d1[1].i > 0;
270+
select * from dcomptable;
271+
d1
272+
--------------------
273+
{"(11,)","(,)"}
274+
{"(99,)"}
275+
{"(1,2)","(,)"}
276+
{"(3,4)","(6,5)"}
277+
{"(7,8)","(10,9)"}
278+
{"(9,10)","(,)"}
279+
{"(0,2)"}
280+
{"(98,100)"}
281+
(8 rows)
282+
283+
drop table dcomptable;
284+
drop type comptype cascade;
285+
NOTICE: drop cascades to type dcomptypea
286+
-- Test not-null restrictions
185287
create domain dnotnull varchar(15) NOT NULL;
186288
create domain dnull varchar(15);
187289
create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');

‎src/test/regress/sql/domain.sql

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,7 @@ INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}');
8585
INSERT INTO domarrtestvalues ('{2,2}','{{"a"},{"c"}}');
8686
INSERT INTO domarrtestvalues (NULL,'{{"a","b","c"},{"d","e","f"}}');
8787
INSERT INTO domarrtestvalues (NULL,'{{"toolong","b","c"},{"d","e","f"}}');
88+
INSERT INTO domarrtest (testint4arr[1], testint4arr[3])values (11,22);
8889
select*from domarrtest;
8990
select testint4arr[1], testchar4arr[2:2]from domarrtest;
9091
select array_dims(testint4arr), array_dims(testchar4arr)from domarrtest;
@@ -100,6 +101,13 @@ COPY domarrtest FROM stdin;-- fail
100101

101102
select*from domarrtest;
102103

104+
update domarrtestset
105+
testint4arr[1]= testint4arr[1]+1,
106+
testint4arr[3]= testint4arr[3]-1
107+
where testchar4arr isnull;
108+
109+
select*from domarrtestwhere testchar4arr isnull;
110+
103111
droptable domarrtest;
104112
dropdomain domainint4arr restrict;
105113
dropdomain domainchar4arr restrict;
@@ -111,6 +119,46 @@ select pg_typeof('{1,2,3}'::dia);
111119
select pg_typeof('{1,2,3}'::dia||42);-- should be int[] not dia
112120
dropdomain dia;
113121

122+
123+
-- Test domains over arrays of composite
124+
125+
createtypecomptypeas (r float8, i float8);
126+
createdomaindcomptypeaas comptype[];
127+
createtabledcomptable (d1 dcomptypea unique);
128+
129+
insert into dcomptablevalues (array[row(1,2)]::dcomptypea);
130+
insert into dcomptablevalues (array[row(3,4), row(5,6)]::comptype[]);
131+
insert into dcomptablevalues (array[row(7,8)::comptype, row(9,10)::comptype]);
132+
insert into dcomptablevalues (array[row(1,2)]::dcomptypea);-- fail on uniqueness
133+
insert into dcomptable (d1[1])values(row(9,10));
134+
insert into dcomptable (d1[1].r)values(11);
135+
136+
select*from dcomptable;
137+
select d1[2], d1[1].r, d1[1].ifrom dcomptable;
138+
update dcomptableset d1[2]= row(d1[2].i, d1[2].r);
139+
select*from dcomptable;
140+
update dcomptableset d1[1].r= d1[1].r+1where d1[1].i>0;
141+
select*from dcomptable;
142+
143+
alterdomain dcomptypea addconstraint c1check (value[1].r<= value[1].i);
144+
alterdomain dcomptypea addconstraint c2check (value[1].r> value[1].i);-- fail
145+
146+
select array[row(2,1)]::dcomptypea;-- fail
147+
insert into dcomptablevalues (array[row(1,2)]::comptype[]);
148+
insert into dcomptablevalues (array[row(2,1)]::comptype[]);-- fail
149+
insert into dcomptable (d1[1].r)values(99);
150+
insert into dcomptable (d1[1].r, d1[1].i)values(99,100);
151+
insert into dcomptable (d1[1].r, d1[1].i)values(100,99);-- fail
152+
update dcomptableset d1[1].r= d1[1].r+1where d1[1].i>0;-- fail
153+
update dcomptableset d1[1].r= d1[1].r-1where d1[1].i>0;
154+
select*from dcomptable;
155+
156+
droptable dcomptable;
157+
droptype comptype cascade;
158+
159+
160+
-- Test not-null restrictions
161+
114162
createdomaindnotnullvarchar(15)NOT NULL;
115163
createdomaindnullvarchar(15);
116164
createdomaindcheckvarchar(15)NOT NULLCHECK (VALUE='a'OR VALUE='c'OR VALUE='d');

0 commit comments

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