Перейти к содержимому

Исходный код вики Insert or update. Upsert

Редактировал(а) Alexandr Fokin 2026/05/28 10:57
Последние авторы
1
2
3 |(% style="width:158px" %)(((
4 === Insert if not exist. Waiting lock ===
5 )))|(% style="width:1381px" %)(((
6 |(% style="width:141px" %)insert on conflict
7 If not exist|(% style="width:259px" %){{code language="sql"}}INSERT INTO table1
8 (id)
9 SELECT 1
10 WHERE
11 NOT EXISTS (
12 SELECT 1
13 FROM table1
14 WHERE id = 1
15 -- for share
16 )
17 ON CONFLICT DO NOTHING{{/code}}|(% style="width:919px" %)(((
18 |(% style="width:115px" %)Избыточная генерация PK.|(% style="width:924px" %)(((
19 Конструкция {{code language="sql"}}ON CONFLICT{{/code}} не защищает от обращения к генератору ключа (sequence). И это может привести к тому, что счетчик будет крутиться впустую.
20 Использование выборки с условием вроде не убирает полностью ложные срабатывания (если они проходя одновременно), но после вставки записи, обращения к генератору прекратятся.
21
22 PostgreSQL Antipatterns: накручиваем себе проблемы
23 [[https:~~/~~/habr.com/ru/companies/tensor/articles/507688/>>url:https://habr.com/ru/companies/tensor/articles/507688/]]
24 )))
25 |(% style="width:115px" %) |(% style="width:924px" %)Postgres: INSERT if does not exist already
26 [[https:~~/~~/stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already>>url:https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already]]
27 |(% style="width:115px" %)Взаимодействие с блокировками|(% style="width:924px" %)Поведение параллельных транзакций (Read Commited):(((
28 |(% style="width:248px" %)Параллельные транзакции|(% style="width:303px" %)select for update|(% style="width:348px" %)update или delete
29 |(% style="width:248px" %)Без where exists|(% style="width:303px" %)Не блокируется.|(% style="width:348px" %)Блокируется.
30 |(% style="width:248px" %)С where exsists|(% style="width:303px" %)Не блокируется|(% style="width:348px" %)Не блокируется
31 |(% style="width:248px" %)с where exsists и for share|(% style="width:303px" %)Блокируется|(% style="width:348px" %)Блокируется.
32
33 Важно подчеркнуть, {{code language="sql"}}select for update{{/code}} не всегда блокирует {{code language="sql"}}insert on conflict do nothing{{/code}} (как показано в таблице).
34 В некоторых случаях может потребоваться явное ручное обновление записи для блокировки, чтобы заблокировать параллельные вызовы upsert (чтобы они они дождались завершения текущей транзакции), причем в том числе возможно перед проверкой условий.
35 )))
36 | |
37 )))
38 |(% style="width:141px" %)Merge|(% style="width:259px" %) |(% style="width:919px" %)Не ждет блокировок.
39 |(% style="width:141px" %) |(% style="width:259px" %) |(% style="width:919px" %)
40 )))
41 |(% style="width:100px" %)(((
42 === Get or insert ===
43 )))|(% style="width:1381px" %)(((
44 |(% style="width:304px" %){{code language="sql"}}WITH insert_cte AS (
45 insert into test_table (id)
46 values (1)
47 on conflict do nothing
48 )
49
50 select *
51 from insert_cte
52
53 union all
54
55 select id
56 from test_table
57 where id = 1;{{/code}}|(% style="width:599px" %)(((
58 * Не работает:
59 ** Это union - один statement:
60 *** select не увидит результат текущего insert.
61 *** select не увидит результаты параллельных транзакций (если
62 * Без флага.
63 * 1 result set.
64 )))|(% style="width:478px" %)
65 |(% style="width:304px" %){{code language="sql"}}WITH insert_cte AS (
66 insert into test_table (id)
67 values (1)
68 on conflict do nothing
69 returning id, true as is_new
70 )
71
72 select *
73 from insert_cte
74
75 union all
76
77 select id, false as is_new
78 from test_table
79 where id = 1;{{/code}}|(% style="width:599px" %)(((
80 * Не работает:
81 ** Это union - один statement:
82 *** select не увидит результаты параллельных транзакций (если текущая транзакция будет ожидать блокировку на insert)
83 * Возвращает флаг.
84 * 1 result set.
85 ** Возвращается 1 строка т.к. один statement и мы видим либо только результат insert-returning, либо только результат select.
86 )))|(% style="width:478px" %)
87 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
88 values (1)
89 on conflict do nothing
90 returning id, true as is_new;
91
92 select id, false as is_new
93 from test_table
94 where id = 1;{{/code}}|(% style="width:599px" %)(((
95 * Работает.
96 * Возвращает флаг.
97 * 2 result set
98 ((нужно делать 2 DbCommand)
99 или (multiple result sets)).
100 )))|(% style="width:478px" %)(((
101 [[NpgsqlBatch>>doc:Разработка.NET.Работа с БД.Группа\. Провайдеры\..Npgsql.Сценарии.NpgsqlBatch.WebHome]]
102
103 {{code language="c#"}}
104 await using (var batch = new NpgsqlBatch(...)
105 {
106 var insertCommand = new NpgsqlBatchCommand(
107 $@"
108 insert into test_table (id) values (1)
109 on conflict do nothing
110 returning id, true as is_new;"
111 );
112 var selectCommand = new NpgsqlBatchCommand(
113 $@"
114 select id, false as is_new
115 from test_table
116 where id = 1;"
117 );
118
119 batch.BatchCommands.Add(insertCommand);
120 batch.BatchCommands.Add(selectCommand);
121
122 await using (var reader = await batch.ExecuteReaderAsync())
123 {
124 do
125 {
126 while (await reader.ReadAsync())
127 {
128 var id = await reader.GetFieldValueAsync<int>("id");
129 var isNew = await reader.GetFieldValueAsync<bool>("is_new");
130 }
131 } while (await reader.NextResultAsync());
132 }
133
134 }
135 {{/code}}
136 )))
137 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
138 values (1)
139 on conflict do nothing;
140
141 select id
142 from test_table
143 where id = 1;{{/code}}|(% style="width:599px" %)(((
144 * Работает.
145 * Флага нет явно, но его можно вычислить через счетчик.
146 * 1 result set.
147 )))|(% style="width:478px" %){{code language="c#"}}// Флаг можно получить через счетчик
148 // statement[0] - insert, statement[1] - select.
149 var isNew = reader.Statements[0].RecordsAffected != 0;{{/code}}
150 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
151 values (1)
152 on conflict do nothing;
153
154 select id, xmin = pg_current_xact_id()::xid as is_new
155 from test_table
156 where id = 1;{{/code}}|(% style="width:599px" %)(((
157 * Работает.
158 ** Минусы:
159 *** Вроде не работает, если выше есть хотя бы один savepoint.
160 * Возвращает флаг.
161 * 1 result set.
162 )))|(% style="width:478px" %)
163 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id, insert_key)
164 values (1, @insert_key)
165 on conflict do nothing;
166
167 select id, insert_key
168 from test_table
169 where id = 1;{{/code}}|(% style="width:599px" %)(((
170 * Работает.
171 ** Минус: требует отдельный столбец (insert_key)
172 ** Плюс: легко встраивается (например там где ORM).
173 * Возвращает флаг.
174 * 1 result set.
175 )))|(% style="width:478px" %){{code language="c#"}}// Вычисляем флаг
176 var currentInsertKey = Guid.NewGuid();
177 var insertedRow = await ExecuteInsertQueryAsync(data, currentInsertKey);
178 var isNew = insertedRow.InsertKey == currentInsertKey;{{/code}}
179 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id, is_new)
180 values (1, true)
181 on conflict (id) do update
182 SET
183 is_new = false
184 returning id, is_new;{{/code}}|(% style="width:599px" %)(((
185 * Работает.
186 ** Минус:
187 *** Требует отдельный столбец (is_new)
188 *** Избыточная запись на update.
189 ** Плюс: легко встраивается (например там где ORM).
190 * Возвращает флаг.
191 * 1 result set.
192 )))|(% style="width:478px" %)
193 )))
194 |(% style="width:100px" %) |(% style="width:1381px" %)
195
196 ----
197
198 ==== Внутренние ссылки: ====
199
200 ====== Дочерние страницы: ======
201
202 {{children/}}
203
204 ====== Обратные ссылки: ======
205
206 {{velocity}}
207 #set ($links = $doc.getBacklinks())
208 #if ($links.size() > 0)
209 #foreach ($docname in $links)
210 #set ($rdoc = $xwiki.getDocument($docname).getTranslatedDocument())
211 * [[$escapetool.xml($rdoc.fullName)]]
212 #end
213 #else
214 No back links for this page!
215 #end
216 {{/velocity}}
217
218 ----