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

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

Редактировал(а) Alexandr Fokin 2026/05/28 10:57
Скрыть последних авторов
Alexandr Fokin 2.27 1
2
Alexandr Fokin 2.51 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
Alexandr Fokin 2.3 8 (id)
9 SELECT 1
10 WHERE
11 NOT EXISTS (
12 SELECT 1
13 FROM table1
14 WHERE id = 1
Alexandr Fokin 2.11 15 -- for share
Alexandr Fokin 2.3 16 )
Alexandr Fokin 2.27 17 ON CONFLICT DO NOTHING{{/code}}|(% style="width:919px" %)(((
Alexandr Fokin 2.21 18 |(% style="width:115px" %)Избыточная генерация PK.|(% style="width:924px" %)(((
Alexandr Fokin 2.4 19 Конструкция {{code language="sql"}}ON CONFLICT{{/code}} не защищает от обращения к генератору ключа (sequence). И это может привести к тому, что счетчик будет крутиться впустую.
Alexandr Fokin 2.21 20 Использование выборки с условием вроде не убирает полностью ложные срабатывания (если они проходя одновременно), но после вставки записи, обращения к генератору прекратятся.
21
22 PostgreSQL Antipatterns: накручиваем себе проблемы
23 [[https:~~/~~/habr.com/ru/companies/tensor/articles/507688/>>url:https://habr.com/ru/companies/tensor/articles/507688/]]
Alexandr Fokin 2.12 24 )))
Alexandr Fokin 2.15 25 |(% style="width:115px" %) |(% style="width:924px" %)Postgres: INSERT if does not exist already
Alexandr Fokin 2.4 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]]
Alexandr Fokin 2.23 27 |(% style="width:115px" %)Взаимодействие с блокировками|(% style="width:924px" %)Поведение параллельных транзакций (Read Commited):(((
Alexandr Fokin 2.20 28 |(% style="width:248px" %)Параллельные транзакции|(% style="width:303px" %)select for update|(% style="width:348px" %)update или delete
Alexandr Fokin 2.24 29 |(% style="width:248px" %)Без where exists|(% style="width:303px" %)Не блокируется.|(% style="width:348px" %)Блокируется.
Alexandr Fokin 2.20 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 (чтобы они они дождались завершения текущей транзакции), причем в том числе возможно перед проверкой условий.
Alexandr Fokin 2.4 35 )))
Alexandr Fokin 2.27 36 | |
Alexandr Fokin 2.20 37 )))
Alexandr Fokin 2.51 38 |(% style="width:141px" %)Merge|(% style="width:259px" %) |(% style="width:919px" %)Не ждет блокировок.
39 |(% style="width:141px" %) |(% style="width:259px" %) |(% style="width:919px" %)
Alexandr Fokin 2.27 40 )))
Alexandr Fokin 2.51 41 |(% style="width:100px" %)(((
42 === Get or insert ===
43 )))|(% style="width:1381px" %)(((
Alexandr Fokin 2.27 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 )
Alexandr Fokin 1.1 49
Alexandr Fokin 2.27 50 select *
51 from insert_cte
52
53 union all
54
55 select id
56 from test_table
Alexandr Fokin 2.40 57 where id = 1;{{/code}}|(% style="width:599px" %)(((
Alexandr Fokin 2.35 58 * Не работает:
59 ** Это union - один statement:
60 *** select не увидит результат текущего insert.
61 *** select не увидит результаты параллельных транзакций (если
Alexandr Fokin 2.27 62 * Без флага.
63 * 1 result set.
Alexandr Fokin 2.40 64 )))|(% style="width:478px" %)
Alexandr Fokin 2.27 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
Alexandr Fokin 2.40 79 where id = 1;{{/code}}|(% style="width:599px" %)(((
Alexandr Fokin 2.35 80 * Не работает:
81 ** Это union - один statement:
82 *** select не увидит результаты параллельных транзакций (если текущая транзакция будет ожидать блокировку на insert)
Alexandr Fokin 2.27 83 * Возвращает флаг.
Alexandr Fokin 2.44 84 * 1 result set.
Alexandr Fokin 2.32 85 ** Возвращается 1 строка т.к. один statement и мы видим либо только результат insert-returning, либо только результат select.
Alexandr Fokin 2.40 86 )))|(% style="width:478px" %)
Alexandr Fokin 2.35 87 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
Alexandr Fokin 2.40 88 values (1)
89 on conflict do nothing
90 returning id, true as is_new;
Alexandr Fokin 2.35 91
92 select id, false as is_new
93 from test_table
Alexandr Fokin 2.40 94 where id = 1;{{/code}}|(% style="width:599px" %)(((
Alexandr Fokin 2.35 95 * Работает.
96 * Возвращает флаг.
Alexandr Fokin 2.44 97 * 2 result set
98 ((нужно делать 2 DbCommand)
99 или (multiple result sets)).
Alexandr Fokin 2.40 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(
Alexandr Fokin 2.41 113 $@"
Alexandr Fokin 2.40 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 )))
Alexandr Fokin 2.39 137 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
138 values (1)
139 on conflict do nothing;
140
Alexandr Fokin 2.43 141 select id
Alexandr Fokin 2.39 142 from test_table
Alexandr Fokin 2.40 143 where id = 1;{{/code}}|(% style="width:599px" %)(((
Alexandr Fokin 2.39 144 * Работает.
Alexandr Fokin 2.44 145 * Флага нет явно, но его можно вычислить через счетчик.
146 * 1 result set.
Alexandr Fokin 2.40 147 )))|(% style="width:478px" %){{code language="c#"}}// Флаг можно получить через счетчик
Alexandr Fokin 2.39 148 // statement[0] - insert, statement[1] - select.
149 var isNew = reader.Statements[0].RecordsAffected != 0;{{/code}}
Alexandr Fokin 2.49 150 |(% style="width:304px" %){{code language="sql"}}insert into test_table (id)
151 values (1)
152 on conflict do nothing;
153
Alexandr Fokin 2.50 154 select id, xmin = pg_current_xact_id()::xid as is_new
Alexandr Fokin 2.49 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" %)
Alexandr Fokin 2.44 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 * Работает.
Alexandr Fokin 2.46 171 ** Минус: требует отдельный столбец (insert_key)
172 ** Плюс: легко встраивается (например там где ORM).
Alexandr Fokin 2.44 173 * Возвращает флаг.
174 * 1 result set.
175 )))|(% style="width:478px" %){{code language="c#"}}// Вычисляем флаг
176 var currentInsertKey = Guid.NewGuid();
Alexandr Fokin 2.47 177 var insertedRow = await ExecuteInsertQueryAsync(data, currentInsertKey);
Alexandr Fokin 2.44 178 var isNew = insertedRow.InsertKey == currentInsertKey;{{/code}}
Alexandr Fokin 2.48 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" %)
Alexandr Fokin 2.27 193 )))
194 |(% style="width:100px" %) |(% style="width:1381px" %)
195
Alexandr Fokin 2.8 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 ----