forked frompostgres/postgres
- Notifications
You must be signed in to change notification settings - Fork0
Commit2a46bfc

Peter Geoghegan
Support INSERT ... ON CONFLICT {UPDATE | IGNORE}
This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken. Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs. No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion". (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking). Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot. Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple. When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions). This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation. Previous revisions ofthe patch didn't mandate this. However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan. Rather, it's an independently planned subquery, whose executionis tightly driven by its parent. Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage. One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause. UPDATEs may not reference anyother table, and UPDATE FROM is disallowed. INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).1 parent78139bb commit2a46bfc
File tree
54 files changed
+1901
-122
lines changed- contrib
- pg_stat_statements
- postgres_fdw
- src
- backend
- access
- heap
- nbtree
- catalog
- commands
- executor
- nodes
- optimizer
- path
- plan
- util
- parser
- rewrite
- storage
- ipc
- lmgr
- utils
- adt
- time
- include
- access
- executor
- nodes
- optimizer
- parser
- storage
- utils
Some content is hidden
Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.
54 files changed
+1901
-122
lines changedLines changed: 3 additions & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
2198 | 2198 |
| |
2199 | 2199 |
| |
2200 | 2200 |
| |
| 2201 | + | |
| 2202 | + | |
| 2203 | + | |
2201 | 2204 |
| |
2202 | 2205 |
| |
2203 | 2206 |
| |
|
Lines changed: 5 additions & 2 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
847 | 847 |
| |
848 | 848 |
| |
849 | 849 |
| |
850 |
| - | |
851 |
| - | |
| 850 | + | |
| 851 | + | |
852 | 852 |
| |
853 | 853 |
| |
854 | 854 |
| |
| |||
892 | 892 |
| |
893 | 893 |
| |
894 | 894 |
| |
| 895 | + | |
| 896 | + | |
| 897 | + | |
895 | 898 |
| |
896 | 899 |
| |
897 | 900 |
| |
|
Lines changed: 14 additions & 2 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
1170 | 1170 |
| |
1171 | 1171 |
| |
1172 | 1172 |
| |
| 1173 | + | |
1173 | 1174 |
| |
1174 | 1175 |
| |
1175 | 1176 |
| |
| |||
1204 | 1205 |
| |
1205 | 1206 |
| |
1206 | 1207 |
| |
1207 |
| - | |
| 1208 | + | |
1208 | 1209 |
| |
1209 | 1210 |
| |
1210 | 1211 |
| |
| |||
1221 | 1222 |
| |
1222 | 1223 |
| |
1223 | 1224 |
| |
| 1225 | + | |
| 1226 | + | |
| 1227 | + | |
| 1228 | + | |
| 1229 | + | |
| 1230 | + | |
| 1231 | + | |
| 1232 | + | |
| 1233 | + | |
| 1234 | + | |
| 1235 | + | |
1224 | 1236 |
| |
1225 | 1237 |
| |
1226 | 1238 |
| |
1227 | 1239 |
| |
1228 | 1240 |
| |
1229 | 1241 |
| |
1230 | 1242 |
| |
1231 |
| - | |
| 1243 | + | |
1232 | 1244 |
| |
1233 | 1245 |
| |
1234 | 1246 |
| |
|
Lines changed: 1 addition & 1 deletion
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
60 | 60 |
| |
61 | 61 |
| |
62 | 62 |
| |
63 |
| - | |
| 63 | + | |
64 | 64 |
| |
65 | 65 |
| |
66 | 66 |
| |
|
Lines changed: 75 additions & 19 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
2043 | 2043 |
| |
2044 | 2044 |
| |
2045 | 2045 |
| |
| 2046 | + | |
| 2047 | + | |
| 2048 | + | |
2046 | 2049 |
| |
2047 | 2050 |
| |
2048 | 2051 |
| |
| |||
2191 | 2194 |
| |
2192 | 2195 |
| |
2193 | 2196 |
| |
| 2197 | + | |
| 2198 | + | |
| 2199 | + | |
| 2200 | + | |
| 2201 | + | |
| 2202 | + | |
| 2203 | + | |
2194 | 2204 |
| |
2195 | 2205 |
| |
2196 | 2206 |
| |
| |||
2611 | 2621 |
| |
2612 | 2622 |
| |
2613 | 2623 |
| |
| 2624 | + | |
| 2625 | + | |
| 2626 | + | |
| 2627 | + | |
| 2628 | + | |
| 2629 | + | |
2614 | 2630 |
| |
2615 | 2631 |
| |
2616 | 2632 |
| |
2617 | 2633 |
| |
2618 |
| - | |
| 2634 | + | |
2619 | 2635 |
| |
2620 | 2636 |
| |
2621 | 2637 |
| |
| |||
2673 | 2689 |
| |
2674 | 2690 |
| |
2675 | 2691 |
| |
2676 |
| - | |
| 2692 | + | |
| 2693 | + | |
| 2694 | + | |
| 2695 | + | |
| 2696 | + | |
| 2697 | + | |
| 2698 | + | |
| 2699 | + | |
| 2700 | + | |
| 2701 | + | |
2677 | 2702 |
| |
2678 | 2703 |
| |
2679 | 2704 |
| |
| |||
2821 | 2846 |
| |
2822 | 2847 |
| |
2823 | 2848 |
| |
2824 |
| - | |
| 2849 | + | |
| 2850 | + | |
| 2851 | + | |
2825 | 2852 |
| |
2826 |
| - | |
2827 |
| - | |
2828 |
| - | |
2829 |
| - | |
| 2853 | + | |
| 2854 | + | |
| 2855 | + | |
| 2856 | + | |
| 2857 | + | |
2830 | 2858 |
| |
2831 | 2859 |
| |
2832 | 2860 |
| |
| |||
2853 | 2881 |
| |
2854 | 2882 |
| |
2855 | 2883 |
| |
2856 |
| - | |
2857 |
| - | |
| 2884 | + | |
| 2885 | + | |
| 2886 | + | |
| 2887 | + | |
| 2888 | + | |
| 2889 | + | |
| 2890 | + | |
| 2891 | + | |
| 2892 | + | |
| 2893 | + | |
| 2894 | + | |
| 2895 | + | |
| 2896 | + | |
| 2897 | + | |
| 2898 | + | |
2858 | 2899 |
| |
2859 | 2900 |
| |
2860 | 2901 |
| |
| |||
2870 | 2911 |
| |
2871 | 2912 |
| |
2872 | 2913 |
| |
2873 |
| - | |
| 2914 | + | |
| 2915 | + | |
| 2916 | + | |
| 2917 | + | |
| 2918 | + | |
2874 | 2919 |
| |
2875 | 2920 |
| |
2876 | 2921 |
| |
| |||
2975 | 3020 |
| |
2976 | 3021 |
| |
2977 | 3022 |
| |
2978 |
| - | |
| 3023 | + | |
2979 | 3024 |
| |
2980 | 3025 |
| |
2981 | 3026 |
| |
| |||
4061 | 4106 |
| |
4062 | 4107 |
| |
4063 | 4108 |
| |
| 4109 | + | |
4064 | 4110 |
| |
4065 | 4111 |
| |
4066 | 4112 |
| |
4067 | 4113 |
| |
4068 |
| - | |
4069 |
| - | |
4070 |
| - | |
4071 |
| - | |
4072 |
| - | |
| 4114 | + | |
| 4115 | + | |
| 4116 | + | |
| 4117 | + | |
| 4118 | + | |
4073 | 4119 |
| |
4074 | 4120 |
| |
4075 | 4121 |
| |
| |||
4106 | 4152 |
| |
4107 | 4153 |
| |
4108 | 4154 |
| |
4109 |
| - | |
4110 |
| - | |
| 4155 | + | |
| 4156 | + | |
| 4157 | + | |
| 4158 | + | |
| 4159 | + | |
| 4160 | + | |
| 4161 | + | |
| 4162 | + | |
| 4163 | + | |
4111 | 4164 |
| |
4112 | 4165 |
| |
4113 | 4166 |
| |
| |||
7263 | 7316 |
| |
7264 | 7317 |
| |
7265 | 7318 |
| |
7266 |
| - | |
| 7319 | + | |
| 7320 | + | |
| 7321 | + | |
| 7322 | + | |
7267 | 7323 |
| |
7268 | 7324 |
| |
7269 | 7325 |
| |
|
Lines changed: 25 additions & 7 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
22 | 22 |
| |
23 | 23 |
| |
24 | 24 |
| |
| 25 | + | |
| 26 | + | |
25 | 27 |
| |
26 | 28 |
| |
27 | 29 |
| |
| |||
51 | 53 |
| |
52 | 54 |
| |
53 | 55 |
| |
54 |
| - | |
| 56 | + | |
| 57 | + | |
55 | 58 |
| |
56 | 59 |
| |
57 | 60 |
| |
| |||
160 | 163 |
| |
161 | 164 |
| |
162 | 165 |
| |
| 166 | + | |
163 | 167 |
| |
164 | 168 |
| |
165 | 169 |
| |
166 |
| - | |
| 170 | + | |
167 | 171 |
| |
168 | 172 |
| |
169 | 173 |
| |
170 | 174 |
| |
171 | 175 |
| |
172 |
| - | |
| 176 | + | |
| 177 | + | |
| 178 | + | |
| 179 | + | |
| 180 | + | |
| 181 | + | |
| 182 | + | |
| 183 | + | |
| 184 | + | |
| 185 | + | |
173 | 186 |
| |
174 | 187 |
| |
175 | 188 |
| |
| |||
211 | 224 |
| |
212 | 225 |
| |
213 | 226 |
| |
214 |
| - | |
215 |
| - | |
216 |
| - | |
| 227 | + | |
| 228 | + | |
| 229 | + | |
| 230 | + | |
| 231 | + | |
| 232 | + | |
217 | 233 |
| |
218 | 234 |
| |
219 | 235 |
| |
| |||
223 | 239 |
| |
224 | 240 |
| |
225 | 241 |
| |
226 |
| - | |
| 242 | + | |
| 243 | + | |
227 | 244 |
| |
228 | 245 |
| |
229 | 246 |
| |
| |||
340 | 357 |
| |
341 | 358 |
| |
342 | 359 |
| |
| 360 | + | |
343 | 361 |
| |
344 | 362 |
| |
345 | 363 |
| |
|
0 commit comments
Comments
(0)