summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/update.out
blob: c809f88f5461f41d94a7aee86936bd1d4512c9dd (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
--
-- UPDATE syntax tests
--
CREATE TABLE update_test (
    a   INT DEFAULT 10,
    b   INT,
    c   TEXT
);
CREATE TABLE upsert_test (
    a   INT PRIMARY KEY,
    b   TEXT
);
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
  5 | 10 | foo
 10 | 15 | 
(2 rows)

UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
 a  | b |  c  
----+---+-----
 10 |   | foo
 10 |   | 
(2 rows)

-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
 10 | 10 | foo
 10 | 10 | 
(2 rows)

UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
 10 | 20 | foo
 10 | 20 | 
(2 rows)

--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
  WHERE update_test.b = v.j;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 20 | foo
 100 | 20 | 
(2 rows)

-- fail, wrong data type:
UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j)
  WHERE update_test.b = v.j;
ERROR:  column "a" is of type integer but expression is of type record
LINE 1: UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i...
                                   ^
HINT:  You will need to rewrite or cast the expression.
--
-- Test multiple-set-clause syntax
--
INSERT INTO update_test SELECT a,b+1,c FROM update_test;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 20 | foo
 100 | 20 | 
 100 | 21 | foo
 100 | 21 | 
(4 rows)

UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
  a  | b  |   c   
-----+----+-------
 100 | 20 | 
 100 | 21 | 
  10 | 31 | bugle
  10 | 32 | bugle
(4 rows)

UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 20 | 
 100 | 21 | 
  11 | 41 | car
  11 | 42 | car
(4 rows)

-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR:  multiple assignments to same column "b"
-- uncorrelated sub-select:
UPDATE update_test
  SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
  WHERE a = 100 AND b = 20;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 21 | 
  11 | 41 | car
  11 | 42 | car
  41 | 11 | 
(4 rows)

-- correlated sub-select:
UPDATE update_test o
  SET (b,a) = (select a+1,b from update_test i
               where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
SELECT * FROM update_test;
 a  |  b  |  c  
----+-----+-----
 21 | 101 | 
 41 |  12 | car
 42 |  12 | car
 11 |  42 | 
(4 rows)

-- fail, multiple rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test);
ERROR:  more than one row returned by a subquery used as an expression
-- set to null if no rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
  WHERE a = 11;
SELECT * FROM update_test;
 a  |  b  |  c  
----+-----+-----
 21 | 101 | 
 41 |  12 | car
 42 |  12 | car
    |     | 
(4 rows)

-- *-expansion should work in this context:
UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j)
  WHERE update_test.a = v.i;
-- you might expect this to work, but syntactically it's not a RowExpr:
UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j)
  WHERE update_test.a = v.i;
ERROR:  source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
LINE 1: UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) ...
                                        ^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ERROR:  invalid reference to FROM-clause entry for table "update_test"
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
                                        ^
HINT:  Perhaps you meant to reference the table alias "t".
-- Make sure that we can update to a TOASTed value.
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
SELECT a, b, char_length(c) FROM update_test;
 a  |  b  | char_length 
----+-----+-------------
    |     |            
 21 | 100 |            
 41 |  12 |       10000
 42 |  12 |       10000
(4 rows)

-- Check multi-assignment with a Result node to handle a one-time filter.
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE update_test t
  SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
  WHERE CURRENT_USER = SESSION_USER;
                         QUERY PLAN                          
-------------------------------------------------------------
 Update on public.update_test t
   ->  Result
         Output: $1, $2, (SubPlan 1 (returns $1,$2)), t.ctid
         One-Time Filter: (CURRENT_USER = SESSION_USER)
         ->  Seq Scan on public.update_test t
               Output: t.a, t.ctid
         SubPlan 1 (returns $1,$2)
           ->  Seq Scan on public.update_test s
                 Output: s.b, s.a
                 Filter: (s.a = t.a)
(10 rows)

UPDATE update_test t
  SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
  WHERE CURRENT_USER = SESSION_USER;
SELECT a, b, char_length(c) FROM update_test;
  a  | b  | char_length 
-----+----+-------------
     |    |            
 100 | 21 |            
  12 | 41 |       10000
  12 | 42 |       10000
(4 rows)

-- Test ON CONFLICT DO UPDATE
INSERT INTO upsert_test VALUES(1, 'Boo'), (3, 'Zoo');
-- uncorrelated  sub-select:
WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
  VALUES (1, 'Bar') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
 a |  b  
---+-----
 1 | Foo
(1 row)

-- correlated sub-select:
INSERT INTO upsert_test VALUES (1, 'Baz'), (3, 'Zaz') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a)
  RETURNING *;
 a |        b        
---+-----------------
 1 | Foo, Correlated
 3 | Zoo, Correlated
(2 rows)

-- correlated sub-select (EXCLUDED.* alias):
INSERT INTO upsert_test VALUES (1, 'Bat'), (3, 'Zot') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
  RETURNING *;
 a |             b             
---+---------------------------
 1 | Foo, Correlated, Excluded
 3 | Zoo, Correlated, Excluded
(2 rows)

-- ON CONFLICT using system attributes in RETURNING, testing both the
-- inserting and updating paths. See bug report at:
-- https://www.postgresql.org/message-id/73436355-6432-49B1-92ED-1FE4F7E7E100%40finefun.com.au
INSERT INTO upsert_test VALUES (2, 'Beeble') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
  RETURNING tableoid::regclass, xmin = pg_current_xact_id()::xid AS xmin_correct, xmax = 0 AS xmax_correct;
  tableoid   | xmin_correct | xmax_correct 
-------------+--------------+--------------
 upsert_test | t            | t
(1 row)

-- currently xmax is set after a conflict - that's probably not good,
-- but it seems worthwhile to have to be explicit if that changes.
INSERT INTO upsert_test VALUES (2, 'Brox') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
  RETURNING tableoid::regclass, xmin = pg_current_xact_id()::xid AS xmin_correct, xmax = pg_current_xact_id()::xid AS xmax_correct;
  tableoid   | xmin_correct | xmax_correct 
-------------+--------------+--------------
 upsert_test | t            | t
(1 row)

DROP TABLE update_test;
DROP TABLE upsert_test;
-- Test ON CONFLICT DO UPDATE with partitioned table and non-identical children
CREATE TABLE upsert_test (
    a   INT PRIMARY KEY,
    b   TEXT
) PARTITION BY LIST (a);
CREATE TABLE upsert_test_1 PARTITION OF upsert_test FOR VALUES IN (1);
CREATE TABLE upsert_test_2 (b TEXT, a INT PRIMARY KEY);
ALTER TABLE upsert_test ATTACH PARTITION upsert_test_2 FOR VALUES IN (2);
INSERT INTO upsert_test VALUES(1, 'Boo'), (2, 'Zoo');
-- uncorrelated sub-select:
WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
  VALUES (1, 'Bar') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
 a |  b  
---+-----
 1 | Foo
(1 row)

-- correlated sub-select:
WITH aaa AS (SELECT 1 AS ctea, ' Foo' AS cteb) INSERT INTO upsert_test
  VALUES (1, 'Bar'), (2, 'Baz') ON CONFLICT(a)
  DO UPDATE SET (b, a) = (SELECT upsert_test.b||cteb, upsert_test.a FROM aaa) RETURNING *;
 a |    b    
---+---------
 1 | Foo Foo
 2 | Zoo Foo
(2 rows)

DROP TABLE upsert_test;
---------------------------
-- UPDATE with row movement
---------------------------
-- When a partitioned table receives an UPDATE to the partitioned key and the
-- new values no longer meet the partition's bound, the row must be moved to
-- the correct partition for the new partition key (if one exists). We must
-- also ensure that updatable views on partitioned tables properly enforce any
-- WITH CHECK OPTION that is defined. The situation with triggers in this case
-- also requires thorough testing as partition key updates causing row
-- movement convert UPDATEs into DELETE+INSERT.
CREATE TABLE range_parted (
	a text,
	b bigint,
	c numeric,
	d int,
	e varchar
) PARTITION BY RANGE (a, b);
-- Create partitions intentionally in descending bound order, so as to test
-- that update-row-movement works with the leaf partitions not in bound order.
CREATE TABLE part_b_20_b_30 (e varchar, c numeric, a text, b bigint, d int);
ALTER TABLE range_parted ATTACH PARTITION part_b_20_b_30 FOR VALUES FROM ('b', 20) TO ('b', 30);
CREATE TABLE part_b_10_b_20 (e varchar, c numeric, a text, b bigint, d int) PARTITION BY RANGE (c);
CREATE TABLE part_b_1_b_10 PARTITION OF range_parted FOR VALUES FROM ('b', 1) TO ('b', 10);
ALTER TABLE range_parted ATTACH PARTITION part_b_10_b_20 FOR VALUES FROM ('b', 10) TO ('b', 20);
CREATE TABLE part_a_10_a_20 PARTITION OF range_parted FOR VALUES FROM ('a', 10) TO ('a', 20);
CREATE TABLE part_a_1_a_10 PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('a', 10);
-- Check that partition-key UPDATE works sanely on a partitioned table that
-- does not have any child partitions.
UPDATE part_b_10_b_20 set b = b - 6;
-- Create some more partitions following the above pattern of descending bound
-- order, but let's make the situation a bit more complex by having the
-- attribute numbers of the columns vary from their parent partition.
CREATE TABLE part_c_100_200 (e varchar, c numeric, a text, b bigint, d int) PARTITION BY range (abs(d));
ALTER TABLE part_c_100_200 DROP COLUMN e, DROP COLUMN c, DROP COLUMN a;
ALTER TABLE part_c_100_200 ADD COLUMN c numeric, ADD COLUMN e varchar, ADD COLUMN a text;
ALTER TABLE part_c_100_200 DROP COLUMN b;
ALTER TABLE part_c_100_200 ADD COLUMN b bigint;
CREATE TABLE part_d_1_15 PARTITION OF part_c_100_200 FOR VALUES FROM (1) TO (15);
CREATE TABLE part_d_15_20 PARTITION OF part_c_100_200 FOR VALUES FROM (15) TO (20);
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_100_200 FOR VALUES FROM (100) TO (200);
CREATE TABLE part_c_1_100 (e varchar, d int, c numeric, b bigint, a text);
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_1_100 FOR VALUES FROM (1) TO (100);
\set init_range_parted 'truncate range_parted; insert into range_parted VALUES (''a'', 1, 1, 1), (''a'', 10, 200, 1), (''b'', 12, 96, 1), (''b'', 13, 97, 2), (''b'', 15, 105, 16), (''b'', 17, 105, 19)'
\set show_data 'select tableoid::regclass::text COLLATE "C" partname, * from range_parted ORDER BY 1, 2, 3, 4, 5, 6'
:init_range_parted;
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 12 |  96 |  1 | 
 part_c_1_100   | b | 13 |  97 |  2 | 
 part_d_15_20   | b | 15 | 105 | 16 | 
 part_d_15_20   | b | 17 | 105 | 19 | 
(6 rows)

-- The order of subplans should be in bound order
EXPLAIN (costs off) UPDATE range_parted set c = c - 50 WHERE c > 97;
                      QUERY PLAN                       
-------------------------------------------------------
 Update on range_parted
   Update on part_a_1_a_10 range_parted_1
   Update on part_a_10_a_20 range_parted_2
   Update on part_b_1_b_10 range_parted_3
   Update on part_c_1_100 range_parted_4
   Update on part_d_1_15 range_parted_5
   Update on part_d_15_20 range_parted_6
   Update on part_b_20_b_30 range_parted_7
   ->  Append
         ->  Seq Scan on part_a_1_a_10 range_parted_1
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_a_10_a_20 range_parted_2
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_b_1_b_10 range_parted_3
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_c_1_100 range_parted_4
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_d_1_15 range_parted_5
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_d_15_20 range_parted_6
               Filter: (c > '97'::numeric)
         ->  Seq Scan on part_b_20_b_30 range_parted_7
               Filter: (c > '97'::numeric)
(23 rows)

-- fail, row movement happens only within the partition subtree.
UPDATE part_c_100_200 set c = c - 20, d = c WHERE c = 105;
ERROR:  new row for relation "part_c_100_200" violates partition constraint
DETAIL:  Failing row contains (105, 85, null, b, 15).
-- fail, no partition key update, so no attempt to move tuple,
-- but "a = 'a'" violates partition constraint enforced by root partition)
UPDATE part_b_10_b_20 set a = 'a';
ERROR:  new row for relation "part_b_10_b_20" violates partition constraint
DETAIL:  Failing row contains (null, 96, a, 12, 1).
-- ok, partition key update, no constraint violation
UPDATE range_parted set d = d - 10 WHERE d > 10;
-- ok, no partition key update, no constraint violation
UPDATE range_parted set e = d;
-- No row found
UPDATE part_c_1_100 set c = c + 20 WHERE c = 98;
-- ok, row movement
UPDATE part_b_10_b_20 set c = c + 20 returning c, b, a;
  c  | b  | a 
-----+----+---
 116 | 12 | b
 117 | 13 | b
 125 | 15 | b
 125 | 17 | b
(4 rows)

:show_data;
    partname    | a | b  |  c  | d | e 
----------------+---+----+-----+---+---
 part_a_10_a_20 | a | 10 | 200 | 1 | 1
 part_a_1_a_10  | a |  1 |   1 | 1 | 1
 part_d_1_15    | b | 12 | 116 | 1 | 1
 part_d_1_15    | b | 13 | 117 | 2 | 2
 part_d_1_15    | b | 15 | 125 | 6 | 6
 part_d_1_15    | b | 17 | 125 | 9 | 9
(6 rows)

-- fail, row movement happens only within the partition subtree.
UPDATE part_b_10_b_20 set b = b - 6 WHERE c > 116 returning *;
ERROR:  new row for relation "part_b_10_b_20" violates partition constraint
DETAIL:  Failing row contains (2, 117, b, 7, 2).
-- ok, row movement, with subset of rows moved into different partition.
UPDATE range_parted set b = b - 6 WHERE c > 116 returning a, b + c;
 a | ?column? 
---+----------
 a |      204
 b |      124
 b |      134
 b |      136
(4 rows)

:show_data;
   partname    | a | b  |  c  | d | e 
---------------+---+----+-----+---+---
 part_a_1_a_10 | a |  1 |   1 | 1 | 1
 part_a_1_a_10 | a |  4 | 200 | 1 | 1
 part_b_1_b_10 | b |  7 | 117 | 2 | 2
 part_b_1_b_10 | b |  9 | 125 | 6 | 6
 part_d_1_15   | b | 11 | 125 | 9 | 9
 part_d_1_15   | b | 12 | 116 | 1 | 1
(6 rows)

-- Common table needed for multiple test scenarios.
CREATE TABLE mintab(c1 int);
INSERT into mintab VALUES (120);
-- update partition key using updatable view.
CREATE VIEW upview AS SELECT * FROM range_parted WHERE (select c > c1 FROM mintab) WITH CHECK OPTION;
-- ok
UPDATE upview set c = 199 WHERE b = 4;
-- fail, check option violation
UPDATE upview set c = 120 WHERE b = 4;
ERROR:  new row violates check option for view "upview"
DETAIL:  Failing row contains (a, 4, 120, 1, 1).
-- fail, row movement with check option violation
UPDATE upview set a = 'b', b = 15, c = 120 WHERE b = 4;
ERROR:  new row violates check option for view "upview"
DETAIL:  Failing row contains (b, 15, 120, 1, 1).
-- ok, row movement, check option passes
UPDATE upview set a = 'b', b = 15 WHERE b = 4;
:show_data;
   partname    | a | b  |  c  | d | e 
---------------+---+----+-----+---+---
 part_a_1_a_10 | a |  1 |   1 | 1 | 1
 part_b_1_b_10 | b |  7 | 117 | 2 | 2
 part_b_1_b_10 | b |  9 | 125 | 6 | 6
 part_d_1_15   | b | 11 | 125 | 9 | 9
 part_d_1_15   | b | 12 | 116 | 1 | 1
 part_d_1_15   | b | 15 | 199 | 1 | 1
(6 rows)

-- cleanup
DROP VIEW upview;
-- RETURNING having whole-row vars.
:init_range_parted;
UPDATE range_parted set c = 95 WHERE a = 'b' and b > 10 and c > 100 returning (range_parted), *;
 range_parted  | a | b  | c  | d  | e 
---------------+---+----+----+----+---
 (b,15,95,16,) | b | 15 | 95 | 16 | 
 (b,17,95,19,) | b | 17 | 95 | 19 | 
(2 rows)

:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 12 |  96 |  1 | 
 part_c_1_100   | b | 13 |  97 |  2 | 
 part_c_1_100   | b | 15 |  95 | 16 | 
 part_c_1_100   | b | 17 |  95 | 19 | 
(6 rows)

-- Transition tables with update row movement
:init_range_parted;
CREATE FUNCTION trans_updatetrigfunc() RETURNS trigger LANGUAGE plpgsql AS
$$
  begin
    raise notice 'trigger = %, old table = %, new table = %',
                 TG_NAME,
                 (select string_agg(old_table::text, ', ' ORDER BY a) FROM old_table),
                 (select string_agg(new_table::text, ', ' ORDER BY a) FROM new_table);
    return null;
  end;
$$;
CREATE TRIGGER trans_updatetrig
  AFTER UPDATE ON range_parted REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
UPDATE range_parted set c = (case when c = 96 then 110 else c + 1 end ) WHERE a = 'b' and b > 10 and c >= 96;
NOTICE:  trigger = trans_updatetrig, old table = (b,12,96,1,), (b,13,97,2,), (b,15,105,16,), (b,17,105,19,), new table = (b,12,110,1,), (b,13,98,2,), (b,15,106,16,), (b,17,106,19,)
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 13 |  98 |  2 | 
 part_d_15_20   | b | 15 | 106 | 16 | 
 part_d_15_20   | b | 17 | 106 | 19 | 
 part_d_1_15    | b | 12 | 110 |  1 | 
(6 rows)

:init_range_parted;
-- Enabling OLD TABLE capture for both DELETE as well as UPDATE stmt triggers
-- should not cause DELETEd rows to be captured twice. Similar thing for
-- INSERT triggers and inserted rows.
CREATE TRIGGER trans_deletetrig
  AFTER DELETE ON range_parted REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
CREATE TRIGGER trans_inserttrig
  AFTER INSERT ON range_parted REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
UPDATE range_parted set c = c + 50 WHERE a = 'b' and b > 10 and c >= 96;
NOTICE:  trigger = trans_updatetrig, old table = (b,12,96,1,), (b,13,97,2,), (b,15,105,16,), (b,17,105,19,), new table = (b,12,146,1,), (b,13,147,2,), (b,15,155,16,), (b,17,155,19,)
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_d_15_20   | b | 15 | 155 | 16 | 
 part_d_15_20   | b | 17 | 155 | 19 | 
 part_d_1_15    | b | 12 | 146 |  1 | 
 part_d_1_15    | b | 13 | 147 |  2 | 
(6 rows)

DROP TRIGGER trans_deletetrig ON range_parted;
DROP TRIGGER trans_inserttrig ON range_parted;
-- Don't drop trans_updatetrig yet. It is required below.
-- Test with transition tuple conversion happening for rows moved into the
-- new partition. This requires a trigger that references transition table
-- (we already have trans_updatetrig). For inserted rows, the conversion
-- is not usually needed, because the original tuple is already compatible with
-- the desired transition tuple format. But conversion happens when there is a
-- BR trigger because the trigger can change the inserted row. So install a
-- BR triggers on those child partitions where the rows will be moved.
CREATE FUNCTION func_parted_mod_b() RETURNS trigger AS $$
BEGIN
   NEW.b = NEW.b + 1;
   return NEW;
END $$ language plpgsql;
CREATE TRIGGER trig_c1_100 BEFORE UPDATE OR INSERT ON part_c_1_100
   FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
CREATE TRIGGER trig_d1_15 BEFORE UPDATE OR INSERT ON part_d_1_15
   FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
CREATE TRIGGER trig_d15_20 BEFORE UPDATE OR INSERT ON part_d_15_20
   FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
:init_range_parted;
UPDATE range_parted set c = (case when c = 96 then 110 else c + 1 end) WHERE a = 'b' and b > 10 and c >= 96;
NOTICE:  trigger = trans_updatetrig, old table = (b,13,96,1,), (b,14,97,2,), (b,16,105,16,), (b,18,105,19,), new table = (b,15,110,1,), (b,15,98,2,), (b,17,106,16,), (b,19,106,19,)
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 15 |  98 |  2 | 
 part_d_15_20   | b | 17 | 106 | 16 | 
 part_d_15_20   | b | 19 | 106 | 19 | 
 part_d_1_15    | b | 15 | 110 |  1 | 
(6 rows)

:init_range_parted;
UPDATE range_parted set c = c + 50 WHERE a = 'b' and b > 10 and c >= 96;
NOTICE:  trigger = trans_updatetrig, old table = (b,13,96,1,), (b,14,97,2,), (b,16,105,16,), (b,18,105,19,), new table = (b,15,146,1,), (b,16,147,2,), (b,17,155,16,), (b,19,155,19,)
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_d_15_20   | b | 17 | 155 | 16 | 
 part_d_15_20   | b | 19 | 155 | 19 | 
 part_d_1_15    | b | 15 | 146 |  1 | 
 part_d_1_15    | b | 16 | 147 |  2 | 
(6 rows)

-- Case where per-partition tuple conversion map array is allocated, but the
-- map is not required for the particular tuple that is routed, thanks to
-- matching table attributes of the partition and the target table.
:init_range_parted;
UPDATE range_parted set b = 15 WHERE b = 1;
NOTICE:  trigger = trans_updatetrig, old table = (a,1,1,1,), new table = (a,15,1,1,)
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_10_a_20 | a | 15 |   1 |  1 | 
 part_c_1_100   | b | 13 |  96 |  1 | 
 part_c_1_100   | b | 14 |  97 |  2 | 
 part_d_15_20   | b | 16 | 105 | 16 | 
 part_d_15_20   | b | 18 | 105 | 19 | 
(6 rows)

DROP TRIGGER trans_updatetrig ON range_parted;
DROP TRIGGER trig_c1_100 ON part_c_1_100;
DROP TRIGGER trig_d1_15 ON part_d_1_15;
DROP TRIGGER trig_d15_20 ON part_d_15_20;
DROP FUNCTION func_parted_mod_b();
-- RLS policies with update-row-movement
-----------------------------------------
ALTER TABLE range_parted ENABLE ROW LEVEL SECURITY;
CREATE USER regress_range_parted_user;
GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
CREATE POLICY seeall ON range_parted AS PERMISSIVE FOR SELECT USING (true);
CREATE POLICY policy_range_parted ON range_parted for UPDATE USING (true) WITH CHECK (c % 2 = 0);
:init_range_parted;
SET SESSION AUTHORIZATION regress_range_parted_user;
-- This should fail with RLS violation error while moving row from
-- part_a_10_a_20 to part_d_1_15, because we are setting 'c' to an odd number.
UPDATE range_parted set a = 'b', c = 151 WHERE a = 'a' and c = 200;
ERROR:  new row violates row-level security policy for table "range_parted"
RESET SESSION AUTHORIZATION;
-- Create a trigger on part_d_1_15
CREATE FUNCTION func_d_1_15() RETURNS trigger AS $$
BEGIN
   NEW.c = NEW.c + 1; -- Make even numbers odd, or vice versa
   return NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trig_d_1_15 BEFORE INSERT ON part_d_1_15
   FOR EACH ROW EXECUTE PROCEDURE func_d_1_15();
:init_range_parted;
SET SESSION AUTHORIZATION regress_range_parted_user;
-- Here, RLS checks should succeed while moving row from part_a_10_a_20 to
-- part_d_1_15. Even though the UPDATE is setting 'c' to an odd number, the
-- trigger at the destination partition again makes it an even number.
UPDATE range_parted set a = 'b', c = 151 WHERE a = 'a' and c = 200;
RESET SESSION AUTHORIZATION;
:init_range_parted;
SET SESSION AUTHORIZATION regress_range_parted_user;
-- This should fail with RLS violation error. Even though the UPDATE is setting
-- 'c' to an even number, the trigger at the destination partition again makes
-- it an odd number.
UPDATE range_parted set a = 'b', c = 150 WHERE a = 'a' and c = 200;
ERROR:  new row violates row-level security policy for table "range_parted"
-- Cleanup
RESET SESSION AUTHORIZATION;
DROP TRIGGER trig_d_1_15 ON part_d_1_15;
DROP FUNCTION func_d_1_15();
-- Policy expression contains SubPlan
RESET SESSION AUTHORIZATION;
:init_range_parted;
CREATE POLICY policy_range_parted_subplan on range_parted
    AS RESTRICTIVE for UPDATE USING (true)
    WITH CHECK ((SELECT range_parted.c <= c1 FROM mintab));
SET SESSION AUTHORIZATION regress_range_parted_user;
-- fail, mintab has row with c1 = 120
UPDATE range_parted set a = 'b', c = 122 WHERE a = 'a' and c = 200;
ERROR:  new row violates row-level security policy "policy_range_parted_subplan" for table "range_parted"
-- ok
UPDATE range_parted set a = 'b', c = 120 WHERE a = 'a' and c = 200;
-- RLS policy expression contains whole row.
RESET SESSION AUTHORIZATION;
:init_range_parted;
CREATE POLICY policy_range_parted_wholerow on range_parted AS RESTRICTIVE for UPDATE USING (true)
   WITH CHECK (range_parted = row('b', 10, 112, 1, NULL)::range_parted);
SET SESSION AUTHORIZATION regress_range_parted_user;
-- ok, should pass the RLS check
UPDATE range_parted set a = 'b', c = 112 WHERE a = 'a' and c = 200;
RESET SESSION AUTHORIZATION;
:init_range_parted;
SET SESSION AUTHORIZATION regress_range_parted_user;
-- fail, the whole row RLS check should fail
UPDATE range_parted set a = 'b', c = 116 WHERE a = 'a' and c = 200;
ERROR:  new row violates row-level security policy "policy_range_parted_wholerow" for table "range_parted"
-- Cleanup
RESET SESSION AUTHORIZATION;
DROP POLICY policy_range_parted ON range_parted;
DROP POLICY policy_range_parted_subplan ON range_parted;
DROP POLICY policy_range_parted_wholerow ON range_parted;
REVOKE ALL ON range_parted, mintab FROM regress_range_parted_user;
DROP USER regress_range_parted_user;
DROP TABLE mintab;
-- statement triggers with update row movement
---------------------------------------------------
:init_range_parted;
CREATE FUNCTION trigfunc() returns trigger language plpgsql as
$$
  begin
    raise notice 'trigger = % fired on table % during %',
                 TG_NAME, TG_TABLE_NAME, TG_OP;
    return null;
  end;
$$;
-- Triggers on root partition
CREATE TRIGGER parent_delete_trig
  AFTER DELETE ON range_parted for each statement execute procedure trigfunc();
CREATE TRIGGER parent_update_trig
  AFTER UPDATE ON range_parted for each statement execute procedure trigfunc();
CREATE TRIGGER parent_insert_trig
  AFTER INSERT ON range_parted for each statement execute procedure trigfunc();
-- Triggers on leaf partition part_c_1_100
CREATE TRIGGER c1_delete_trig
  AFTER DELETE ON part_c_1_100 for each statement execute procedure trigfunc();
CREATE TRIGGER c1_update_trig
  AFTER UPDATE ON part_c_1_100 for each statement execute procedure trigfunc();
CREATE TRIGGER c1_insert_trig
  AFTER INSERT ON part_c_1_100 for each statement execute procedure trigfunc();
-- Triggers on leaf partition part_d_1_15
CREATE TRIGGER d1_delete_trig
  AFTER DELETE ON part_d_1_15 for each statement execute procedure trigfunc();
CREATE TRIGGER d1_update_trig
  AFTER UPDATE ON part_d_1_15 for each statement execute procedure trigfunc();
CREATE TRIGGER d1_insert_trig
  AFTER INSERT ON part_d_1_15 for each statement execute procedure trigfunc();
-- Triggers on leaf partition part_d_15_20
CREATE TRIGGER d15_delete_trig
  AFTER DELETE ON part_d_15_20 for each statement execute procedure trigfunc();
CREATE TRIGGER d15_update_trig
  AFTER UPDATE ON part_d_15_20 for each statement execute procedure trigfunc();
CREATE TRIGGER d15_insert_trig
  AFTER INSERT ON part_d_15_20 for each statement execute procedure trigfunc();
-- Move all rows from part_c_100_200 to part_c_1_100. None of the delete or
-- insert statement triggers should be fired.
UPDATE range_parted set c = c - 50 WHERE c > 97;
NOTICE:  trigger = parent_update_trig fired on table range_parted during UPDATE
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 150 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 12 |  96 |  1 | 
 part_c_1_100   | b | 13 |  97 |  2 | 
 part_c_1_100   | b | 15 |  55 | 16 | 
 part_c_1_100   | b | 17 |  55 | 19 | 
(6 rows)

DROP TRIGGER parent_delete_trig ON range_parted;
DROP TRIGGER parent_update_trig ON range_parted;
DROP TRIGGER parent_insert_trig ON range_parted;
DROP TRIGGER c1_delete_trig ON part_c_1_100;
DROP TRIGGER c1_update_trig ON part_c_1_100;
DROP TRIGGER c1_insert_trig ON part_c_1_100;
DROP TRIGGER d1_delete_trig ON part_d_1_15;
DROP TRIGGER d1_update_trig ON part_d_1_15;
DROP TRIGGER d1_insert_trig ON part_d_1_15;
DROP TRIGGER d15_delete_trig ON part_d_15_20;
DROP TRIGGER d15_update_trig ON part_d_15_20;
DROP TRIGGER d15_insert_trig ON part_d_15_20;
-- Creating default partition for range
:init_range_parted;
create table part_def partition of range_parted default;
\d+ part_def
                                       Table "public.part_def"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
 a      | text              |           |          |         | extended |              | 
 b      | bigint            |           |          |         | plain    |              | 
 c      | numeric           |           |          |         | main     |              | 
 d      | integer           |           |          |         | plain    |              | 
 e      | character varying |           |          |         | extended |              | 
Partition of: range_parted DEFAULT
Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a = 'a'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'a'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'b'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '20'::bigint) AND (b < '30'::bigint)))))

insert into range_parted values ('c', 9);
-- ok
update part_def set a = 'd' where a = 'c';
-- fail
update part_def set a = 'a' where a = 'd';
ERROR:  new row for relation "part_def" violates partition constraint
DETAIL:  Failing row contains (a, 9, null, null, null).
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 12 |  96 |  1 | 
 part_c_1_100   | b | 13 |  97 |  2 | 
 part_d_15_20   | b | 15 | 105 | 16 | 
 part_d_15_20   | b | 17 | 105 | 19 | 
 part_def       | d |  9 |     |    | 
(7 rows)

-- Update row movement from non-default to default partition.
-- fail, default partition is not under part_a_10_a_20;
UPDATE part_a_10_a_20 set a = 'ad' WHERE a = 'a';
ERROR:  new row for relation "part_a_10_a_20" violates partition constraint
DETAIL:  Failing row contains (ad, 10, 200, 1, null).
-- ok
UPDATE range_parted set a = 'ad' WHERE a = 'a';
UPDATE range_parted set a = 'bd' WHERE a = 'b';
:show_data;
 partname | a  | b  |  c  | d  | e 
----------+----+----+-----+----+---
 part_def | ad |  1 |   1 |  1 | 
 part_def | ad | 10 | 200 |  1 | 
 part_def | bd | 12 |  96 |  1 | 
 part_def | bd | 13 |  97 |  2 | 
 part_def | bd | 15 | 105 | 16 | 
 part_def | bd | 17 | 105 | 19 | 
 part_def | d  |  9 |     |    | 
(7 rows)

-- Update row movement from default to non-default partitions.
-- ok
UPDATE range_parted set a = 'a' WHERE a = 'ad';
UPDATE range_parted set a = 'b' WHERE a = 'bd';
:show_data;
    partname    | a | b  |  c  | d  | e 
----------------+---+----+-----+----+---
 part_a_10_a_20 | a | 10 | 200 |  1 | 
 part_a_1_a_10  | a |  1 |   1 |  1 | 
 part_c_1_100   | b | 12 |  96 |  1 | 
 part_c_1_100   | b | 13 |  97 |  2 | 
 part_d_15_20   | b | 15 | 105 | 16 | 
 part_d_15_20   | b | 17 | 105 | 19 | 
 part_def       | d |  9 |     |    | 
(7 rows)

-- Cleanup: range_parted no longer needed.
DROP TABLE range_parted;
CREATE TABLE list_parted (
	a text,
	b int
) PARTITION BY list (a);
CREATE TABLE list_part1  PARTITION OF list_parted for VALUES in ('a', 'b');
CREATE TABLE list_default PARTITION OF list_parted default;
INSERT into list_part1 VALUES ('a', 1);
INSERT into list_default VALUES ('d', 10);
-- fail
UPDATE list_default set a = 'a' WHERE a = 'd';
ERROR:  new row for relation "list_default" violates partition constraint
DETAIL:  Failing row contains (a, 10).
-- ok
UPDATE list_default set a = 'x' WHERE a = 'd';
DROP TABLE list_parted;
-- Test retrieval of system columns with non-consistent partition row types.
-- This is only partially supported, as seen in the results.
create table utrtest (a int, b text) partition by list (a);
create table utr1 (a int check (a in (1)), q text, b text);
create table utr2 (a int check (a in (2)), b text);
alter table utr1 drop column q;
alter table utrtest attach partition utr1 for values in (1);
alter table utrtest attach partition utr2 for values in (2);
insert into utrtest values (1, 'foo')
  returning *, tableoid::regclass, xmin = pg_current_xact_id()::xid as xmin_ok;
 a |  b  | tableoid | xmin_ok 
---+-----+----------+---------
 1 | foo | utr1     | t
(1 row)

insert into utrtest values (2, 'bar')
  returning *, tableoid::regclass, xmin = pg_current_xact_id()::xid as xmin_ok;  -- fails
ERROR:  cannot retrieve a system column in this context
insert into utrtest values (2, 'bar')
  returning *, tableoid::regclass;
 a |  b  | tableoid 
---+-----+----------
 2 | bar | utr2
(1 row)

update utrtest set b = b || b from (values (1), (2)) s(x) where a = s.x
  returning *, tableoid::regclass, xmin = pg_current_xact_id()::xid as xmin_ok;
 a |   b    | x | tableoid | xmin_ok 
---+--------+---+----------+---------
 1 | foofoo | 1 | utr1     | t
 2 | barbar | 2 | utr2     | t
(2 rows)

update utrtest set a = 3 - a from (values (1), (2)) s(x) where a = s.x
  returning *, tableoid::regclass, xmin = pg_current_xact_id()::xid as xmin_ok;  -- fails
ERROR:  cannot retrieve a system column in this context
update utrtest set a = 3 - a from (values (1), (2)) s(x) where a = s.x
  returning *, tableoid::regclass;
 a |   b    | x | tableoid 
---+--------+---+----------
 2 | foofoo | 1 | utr2
 1 | barbar | 2 | utr1
(2 rows)

delete from utrtest
  returning *, tableoid::regclass, xmax = pg_current_xact_id()::xid as xmax_ok;
 a |   b    | tableoid | xmax_ok 
---+--------+----------+---------
 1 | barbar | utr1     | t
 2 | foofoo | utr2     | t
(2 rows)

drop table utrtest;
--------------
-- Some more update-partition-key test scenarios below. This time use list
-- partitions.
--------------
-- Setup for list partitions
CREATE TABLE list_parted (a numeric, b int, c int8) PARTITION BY list (a);
CREATE TABLE sub_parted PARTITION OF list_parted for VALUES in (1) PARTITION BY list (b);
CREATE TABLE sub_part1(b int, c int8, a numeric);
ALTER TABLE sub_parted ATTACH PARTITION sub_part1 for VALUES in (1);
CREATE TABLE sub_part2(b int, c int8, a numeric);
ALTER TABLE sub_parted ATTACH PARTITION sub_part2 for VALUES in (2);
CREATE TABLE list_part1(a numeric, b int, c int8);
ALTER TABLE list_parted ATTACH PARTITION list_part1 for VALUES in (2,3);
INSERT into list_parted VALUES (2,5,50);
INSERT into list_parted VALUES (3,6,60);
INSERT into sub_parted VALUES (1,1,60);
INSERT into sub_parted VALUES (1,2,10);
-- Test partition constraint violation when intermediate ancestor is used and
-- constraint is inherited from upper root.
UPDATE sub_parted set a = 2 WHERE c = 10;
ERROR:  new row for relation "sub_parted" violates partition constraint
DETAIL:  Failing row contains (2, 2, 10).
-- Test update-partition-key, where the unpruned partitions do not have their
-- partition keys updated.
SELECT tableoid::regclass::text, * FROM list_parted WHERE a = 2 ORDER BY 1;
  tableoid  | a | b | c  
------------+---+---+----
 list_part1 | 2 | 5 | 50
(1 row)

UPDATE list_parted set b = c + a WHERE a = 2;
SELECT tableoid::regclass::text, * FROM list_parted WHERE a = 2 ORDER BY 1;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 | 52 | 50
(1 row)

-- Test the case where BR UPDATE triggers change the partition key.
CREATE FUNCTION func_parted_mod_b() returns trigger as $$
BEGIN
   NEW.b = 2; -- This is changing partition key column.
   return NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER parted_mod_b before update on sub_part1
   for each row execute procedure func_parted_mod_b();
SELECT tableoid::regclass::text, * FROM list_parted ORDER BY 1, 2, 3, 4;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 | 52 | 50
 list_part1 | 3 |  6 | 60
 sub_part1  | 1 |  1 | 60
 sub_part2  | 1 |  2 | 10
(4 rows)

-- This should do the tuple routing even though there is no explicit
-- partition-key update, because there is a trigger on sub_part1.
UPDATE list_parted set c = 70 WHERE b  = 1;
SELECT tableoid::regclass::text, * FROM list_parted ORDER BY 1, 2, 3, 4;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 | 52 | 50
 list_part1 | 3 |  6 | 60
 sub_part2  | 1 |  2 | 10
 sub_part2  | 1 |  2 | 70
(4 rows)

DROP TRIGGER parted_mod_b ON sub_part1;
-- If BR DELETE trigger prevented DELETE from happening, we should also skip
-- the INSERT if that delete is part of UPDATE=>DELETE+INSERT.
CREATE OR REPLACE FUNCTION func_parted_mod_b() returns trigger as $$
BEGIN
   raise notice 'Trigger: Got OLD row %, but returning NULL', OLD;
   return NULL;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trig_skip_delete before delete on sub_part2
   for each row execute procedure func_parted_mod_b();
UPDATE list_parted set b = 1 WHERE c = 70;
NOTICE:  Trigger: Got OLD row (2,70,1), but returning NULL
SELECT tableoid::regclass::text, * FROM list_parted ORDER BY 1, 2, 3, 4;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 | 52 | 50
 list_part1 | 3 |  6 | 60
 sub_part2  | 1 |  2 | 10
 sub_part2  | 1 |  2 | 70
(4 rows)

-- Drop the trigger. Now the row should be moved.
DROP TRIGGER trig_skip_delete ON sub_part2;
UPDATE list_parted set b = 1 WHERE c = 70;
SELECT tableoid::regclass::text, * FROM list_parted ORDER BY 1, 2, 3, 4;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 | 52 | 50
 list_part1 | 3 |  6 | 60
 sub_part1  | 1 |  1 | 70
 sub_part2  | 1 |  2 | 10
(4 rows)

DROP FUNCTION func_parted_mod_b();
-- UPDATE partition-key with FROM clause. If join produces multiple output
-- rows for the same row to be modified, we should tuple-route the row only
-- once. There should not be any rows inserted.
CREATE TABLE non_parted (id int);
INSERT into non_parted VALUES (1), (1), (1), (2), (2), (2), (3), (3), (3);
UPDATE list_parted t1 set a = 2 FROM non_parted t2 WHERE t1.a = t2.id and a = 1;
SELECT tableoid::regclass::text, * FROM list_parted ORDER BY 1, 2, 3, 4;
  tableoid  | a | b  | c  
------------+---+----+----
 list_part1 | 2 |  1 | 70
 list_part1 | 2 |  2 | 10
 list_part1 | 2 | 52 | 50
 list_part1 | 3 |  6 | 60
(4 rows)

DROP TABLE non_parted;
-- Cleanup: list_parted no longer needed.
DROP TABLE list_parted;
-- create custom operator class and hash function, for the same reason
-- explained in alter_table.sql
create or replace function dummy_hashint4(a int4, seed int8) returns int8 as
$$ begin return (a + seed); end; $$ language 'plpgsql' immutable;
create operator class custom_opclass for type int4 using hash as
operator 1 = , function 2 dummy_hashint4(int4, int8);
create table hash_parted (
	a int,
	b int
) partition by hash (a custom_opclass, b custom_opclass);
create table hpart1 partition of hash_parted for values with (modulus 2, remainder 1);
create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2);
create table hpart3 partition of hash_parted for values with (modulus 8, remainder 0);
create table hpart4 partition of hash_parted for values with (modulus 8, remainder 4);
insert into hpart1 values (1, 1);
insert into hpart2 values (2, 5);
insert into hpart4 values (3, 4);
-- fail
update hpart1 set a = 3, b=4 where a = 1;
ERROR:  new row for relation "hpart1" violates partition constraint
DETAIL:  Failing row contains (3, 4).
-- ok, row movement
update hash_parted set b = b - 1 where b = 1;
-- ok
update hash_parted set b = b + 8 where b = 1;
-- cleanup
drop table hash_parted;
drop operator class custom_opclass using hash;
drop function dummy_hashint4(a int4, seed int8);