неуникальный пользователь - как обработать нарушение уникальности ключа

Вопрос такой. При попытке регистрации с дублирующимся именем пользователя E-mail я буду пытаться сделать insert в базу. При наличии дубликата возникнет исключение. Я хочу сделать следующее: написать хранимку, которая перехватывает такое исключение, определяет ключ и таблицу, в которых оно случилось, и уже на уровне хранимки генерирует тот текст, который будет показан конечному пользователю.

Зачем это делать в БД? Отлавливай исключение в приложении, в исключении и так должны быть все детали: название таблицы, ограничения, список столбцов, список значений.

А почему перед инсертом не сделать SELECT id FROM users WHERE email=«$1» ? Если вернуло id, то вывести сообщение что такая почта уже зарегана.

Потому что между этим селектом и инсертом может затесаться ещё один инсерт.

Даже если использовать транзакции?

Я хочу сделать следующее: написать хранимку, которая перехватывает такое исключение, определяет ключ и таблицу, в которых оно случилось, и уже на уровне хранимки генерирует тот текст, который будет показан конечному пользователю.

Очень правильно. Особенно когда понадобится добавить другие языки или когда надо будет пакетно такое делать...

Т Р А Н З А К Ц И Я

Я считаю, что это самый правильный подход, поскольку эта информация в структурном виде доступна именно в хранимых процедурах.

Вы правы. Особенно когда надо будет мигрировать на SQLite...

Пока я вижу такой аргумент, что при условии шардинга никакого нарушения ключа может и не быть

Вот тут я даже не знаю что сказать.... Может расскажете как вы понимаете шардинг?

Такой подход хрупок. Только сама БД может гарантировать отсутствие дубликатов. Если мы делаем это руками, то мы плохо делаем руками то, что уже хорошо делается автоматически, и к тому же теряем эффективность. В MySQl с его блокировкой целых таблиц разницы нет. Postgresql гораздо тоньше устроен в плане транзакций из-за многоверсионности.

В чистом виде при шардинге это не может получиться, как я думаю. А вот в ситуации, когда часть таблиц шардится, а часть реплицируется - вполне. Наверняка такие архитектуры тоже есть. И здесь я даже ничего не смогу сказать против слоя абстракции над БД, который позволяет всё это запаковать.

в исключении и так должны быть все детали

Я пока не смотрел, но из опыта я предполагаю, что там, вообще говоря, будет просто текст сообщения об ошибке, который нужно парсить. Что есть уродство. Ну или текст и код ошибки, которого недостаточно. Впрочем, я сейчас посмотрю.

Если я не ошибаюсь, гарантирует это транзакция.

В чистом виде при шардинге это не может получиться, как я думаю.

Что такое шардинг?

В версионных СУБД транзакции изолированы друг от друга. Если два клиента через два коннекта пытаются вставить неуникального пользователя, то select никому из них ничего не покажет, а ошибка возникнет во время commit (кстати, это и для моего подхода будет проблемой). Лечение от этого - либо блокировать таблицу (усложнение, нарушение оптимистичного подхода), либо обрабатывать также ошибки фиксации, либо использовать всегда один коннект и не передавать его другому клиенту, пока не будет завершена логическая транзакция уровня бизнес-логики. Но просто так сама по себе транзакция такой гарантии не даёт.

Ну я википедию уже проглядел, не надо меня экзаменовать. Пример для размышления: соц. сеть. Пользователей мало, сообщений много. Ставим 2 сервера БД, шардим сообщения (по чётным/нечётным датам). А пользователей мало и их шардить не нужно, поэтому их реплицируем.

Соответственно, нужна стратегия того, как это всё будет работать без глюков.

Особенно когда надо будет мигрировать на SQLite...

Когда это понадобится, мы спросим себя «а зачем»?

Я считаю, что это самый правильный подход

нет. правильный вариант - начать транзакцию с уровнем изоляции READ COMMITED, в ней сделать селект по email на предмет «уже есть», и если нет - добавлять запись, а если есть - делать роллбек.

ERROR:  23505: duplicate key value violates unique constraint "foo_bar_key"
DETAIL:  Key (bar)=(test) already exists.
SCHEMA NAME:  public
TABLE NAME:  foo
CONSTRAINT NAME:  foo_bar_key
LOCATION:  _bt_check_unique, nbtinsert.c:433
Time: 1.188 ms

Как минимум ERROR и DETAIL я в приложении видел.

я не специалист, но почему нельзя в базу добавлять два поля
1. имя_пользователя+cookies (или sesion_id) 2. имя_пользователя

первое поле будет всегда уникально
по второму можно агрегировать все коннекты одного пользователя

READ COMMITTED

https://postgrespro.ru/docs/postgrespro/9.5/transaction-iso#xact-read-committed

Также заметьте, что два последовательных оператора SELECT могут видеть разные данные даже в рамках одной транзакции, если какие-то другие транзакции зафиксируют изменения после запуска первого SELECT, но до запуска второго.

Это очень херовая ситуация, когда данные практически не под контролем. В частности, можно провести предлагаемый тобой поиск дважды в рамках одной транзакции и получить разные результаты. Т.е. мне кажется, что это не будет работать вообще, хотя я пока только начинаю работать с postgres.

Это ты _где_ увидел? Я сейчас пытаюсь посмотреть, что возвращает sqlx.

правильный вариант - начать транзакцию с уровнем изоляции READ COMMITED, в ней сделать селект по email на предмет «уже есть», и если нет - добавлять запись, а если есть - делать роллбек.

Как это поможет? Пример (промт 1> — первое соединение, 2> — второе).

Таблица: create table test (col text unique);

Открываем транзакцию в первом соединении:

1> begin;
BEGIN
Time: 0.093 ms
1> set transaction isolation level read committed; -- default, but set just in case
SET
Time: 0.101 ms
1> select * from test where col = 'value';
 col
═════
(0 rows)

Time: 35.604 ms

В это время выполняется транзакция во втором соединении:

2> begin;
BEGIN
Time: 0.184 ms
2> set transaction isolation level read committed; -- default, but set just in case
SET
Time: 0.104 ms
2> select * from test where col = 'value';
 col
═════
(0 rows)

Time: 0.972 ms

Первое соединение делает инсерт:

1> insert into test (col) values ('value');
INSERT 0 1
Time: 2.346 ms

Второе соединение пытается сделать инсерт и блокируется:

2> insert into test (col) values ('value');

Делаем коммит транзакции в первом соединении:

1> commit;
COMMIT
Time: 0.582 ms

После этого разблокируется инсерт во втором соединении и кидает ошибку (потому что мы добавили ограничение). Оба селекта нам сказали, что дубликатов нет, зачем они тогда нужны? Лучше уж положиться на ограничение.

dnb anonymous8

Это через psql. В Ruby у меня кидается примерно такое исключение, которому доступны ERROR и DETAIL.

#<PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "educators_pkey"
DETAIL:  Key (id)=(1fd50b46-8259-4b5e-9ab5-09dd4ae8c8f9) already exists.
>

Этого мне достаточно, чтобы определить, какое именно ограничение на уникальность зафейлилось.

Ты прав, кстати. Вот что sqlx возвращает:

Severity:"ERROR"
Code:"23505"
Message:"duplicate key value violates unique constraint "zz1_id""
Detail:"Key (id)=(1) already exists."
Hint:""
Position:""
InternalPosition:""
InternalQuery:""
Where:""
Schema:"public"
Table:"zz1"
Column:""
DataTypeName:""
Constraint:"zz1_id"
File:"nbtinsert.c"
Line:"433"
Routine:"_bt_check_unique"

почему нельзя в базу добавлять два поля

Тут вопрос про обработку ошибок вообще и про попытке повторно зарегить уже существующий E-mail

Жаль на ЛОРе нет сердечек. Пока я думал, как бы это посподручнее проверить, ты запустил psql и проверил :)

Ставим 2 сервера БД, шардим сообщения

Воу воу воу. 2 сервера и шардинг это разное. Шардинг это разнесение данных по нескольким дискам машинам и т.д. При этом не нарушаются основные принципы - если был ACID то ACID и остался...

Когда это понадобится, мы спросим себя «а зачем»?

Нет когда это понадобится - это понадобится.... Триггеры и хранимки это очень мощное и хорошее средство, но надо думать как это можно отыграть.

у кукисов есть время жизни?

а как же SELECT … FOR UPDATE ?

Транзакции?

Мускуль умеет и не всю таблицу лочить. Ты с какой пещеры вылез?

+

При получении ошибки тупо откати транзакцию. И все это на уровне приложения и не бд

Я пока не понял, поддерживается ли шардинг в postgresql на уровне движка базы данных. Тут пишут что-то, надо смотреть по версиям:

There are over a dozen forks of Postgres which implement sharding. While many of these forks have been successful, they often lag behind the community release of Postgres. By implementing sharding in community Postgres, this feature will be available to all users in current releases of Postgres.

Т.е. либо вики не поддерживается, либо шардинга в psql нет. Его можно эмулировать на прикладном уровне. В этом случае поддержка ACID по всей базе (собранной из кусочков на разных машинах) из коробки не предоставляется и на уровне языка невыразима.

Я пока не понял, поддерживается ли шардинг в postgresql на уровне движка базы данных.

Я немного буду сейчас делать смешно - да поддерживается. Часть таблиц можно держать на одном диске - часть на другом.

собранной из кусочков на разных машинах

CAP теорема.

SELECT … FOR UPDATE

Про него там тоже что-то написано, пока не смотрел.

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Я немного буду сейчас делать смешно - да поддерживается.

Тогда давай поговорим об определениях, как ты предлагал. А вот определение шардинга из англоязычной википедии. Так себе источник, конечно, но тем не менее:

A database shard is a horizontal partition of data in a database or search engine. Each of multiple shards is held on a separate database server instance, to spread load.

Не путать с partitioning, когда

Horizontal partitioning splits one or more tables by row, usually within a single instance of a schema and a database server.

Уел. Признаю. Но думаю с коробки шардинга ты не получишь. Какие условия? 2 машины на инфинибэнд? 2 машины в локалке 2 машины в WAN? Явно тут РАЗНЫЙ шардинг

Вообще не праздный вопрос, получу ли. В postgress, по твоей страничке, получается, что есть какой-то Citus. Но можно то же самое спросить про другие сервера и вдруг окажется, что postgresql не надо использовать. Мне он нравится за версионную архитектуру. Если сравнивать блокировочную и версионную, то версионная выглядит вменяемой, а блокировочная - нет. Т.к. только у блокировочной есть нормальная семантика транзакции. Но масшатбируемость важнее.

Драйвер БД должен предоставлять эту информацию. JDBC предоставляет. Твой подход избыточен.

По большому счёту при вставке в базу варианта проблем всего два: либо нарушен уникальный ключ, либо какая то жопа. Т.е. нужно лишь уметь определять проблема в «жопе» или «уникальном ключе». Лично я пренебрегаю вариантом «жопа» и показываю пользователю ошибку «дубликат имени» если ен удалось сделать инсерт.

См. выше.

Я правильно понял, что если если «жопа», то ты говоришь «дубликат имени»? Как-то не комильфо.

Поправил вопрос. Собственно, изначальный вопрос решён, теперь так, за жизнь беседуем.

Вообще по-моему с JDBC надо парсить строку, но я точно не знаю, лень проверять. Большой проблемы не вижу, очевидно, что это все делают, поэтому драйвер строку менять не будет. Хотя лучше, конечно, было бы, если бы эта информация была структурирована, в жаве всё так, вроде и неплохо сделано, но не до конца по уму. Хоть свой драйвер пиши.

Я все выше просмотрел и остался при своём мнении.

В этой пещере было не так уж и плохо.

Да, ты понял верно.
Пока было лишь два варианта «жопы»:
1) не могу поключиться к базе, но эта ошибка обрабатывается в другом месте (при попытке подключиться), поэтому её в этом случае видно редко

2) закончилось место на диске. тут нужен мониторинг.

Вообще, драйвер базы возвращает код ошибки. Можно просто сравнивать числовое значение с конкретным (т.е. для мускуля я глянул это 1062), и показывать более точно.

В любом случае я бы делил на две категории : ошибка пользователя (дубликат имени) и всё остальное. И точно делал бы ошибку в приложении когда ловил бы исключиение, а не в базе (в том числе потому что переводить на другие языки проще).