Supabase "duplicate key value violates unique constraint": пояснення

Хтось заповнює форму вашого списку очікування, чекає й натискає кнопку ще раз. Перше натискання вже пройшло. Друге повертається помилкою, і консоль браузера показує її повністю:

{
  "code": "23505",
  "details": "Key (email)=(maria@example.com) already exists.",
  "hint": null,
  "message": "duplicate key value violates unique constraint \"waitlist_email_key\""
}

Supabase "duplicate key value violates unique constraint" означає, що Postgres відмовився додати рядок, бо інший рядок уже має те саме значення в стовпці, який приймає кожне значення лише раз. Тут це стовпець email, і Марія вже є у вашому списку.

Порада, яку для цієї помилки передають з рук у руки, звучить так: замінити insert на upsert. Іноді це правильно. Але на таблиці, куди відвідувачі можуть лише додавати, upsert із браузера потребує правил, які дозволяють їм читати таблицю, а рядок з адресою Марії ваш застосунок не мав би показувати відвідувачеві взагалі.

Що означає "duplicate key value violates unique constraint"?

Значення, яке ви хотіли зберегти, уже стоїть у стовпці, що приймає кожне значення один раз, тож Postgres відхилив увесь рядок. Нічого не записано, а рядок, який був там першим, не змінився.

Уявіть таблицю як список гостей біля дверей, по одному рядку на ім'я. Обмеження унікальності працює як правило охоронця: ім'я потрапляє в список лише раз. Ваш застосунок прийшов з іменем, яке там уже записане, і охоронець його розвернув.

Кожна частина помилки каже одну річ:

Формулювання належить самому Postgres. Код, який викидає помилку, додає рядок Key ... already exists лише тоді, коли має опис ключа, і розділ нижче пояснює, коли опису немає.

Який стовпець спричинив помилку?

Це каже назва в лапках, бо Postgres складає її з таблиці й стовпців, якщо ніхто не вибрав іншої. Його код для назв дає три форми:

Повідомлення каже "unique constraint" в усіх трьох випадках. Щоб побачити, що саме охоплює назва, виконайте це в SQL Editor з назвою з вашого повідомлення:

select tablename, indexdef
from pg_indexes
where indexname = 'waitlist_email_key';

indexdef повернеться приблизно таким: CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email), і стовпці в дужках не можуть повторюватися. Правило на двох стовпцях, як-от (team_id, email), пускає ту саму адресу у дві команди й відхиляє її вдруге в тій самій.

"Key (id)=(1) already exists", хоча ви ніколи не надсилали id

Ця помилка означає, що лічильник id таблиці відстає від рядків, які в ній уже є, тож він видав зайнятий номер. Ваш застосунок не надіслав жодного id. Postgres узяв наступний номер із лічильника, а рядок із таким номером уже існував.

У списку гостей охоронець ще й роздає нумеровані браслети з рулону. Хтось переписав перших двісті гостей вручну, разом із номерами браслетів, і ніхто не прокрутив рулон далі. Він і далі починає з 1.

Стовпець, який нумерує себе сам, бере кожен номер із послідовності, так Postgres називає цей лічильник. Сторінка Postgres про identity-стовпці каже, що такий стовпець "does not guarantee uniqueness", і серед причин називає саме цей випадок: значення "could be inserted manually into the identity column". Рядки приходять із уже заповненими номерами з імпорту CSV, де є стовпець id, з копії з іншого проєкту або з seed-скрипта. Лічильник не дізнається про жоден із них. Сторінка Supabase з усунення проблем про послідовності id розбирає саме це повідомлення на _pkey, і її виправлення теж зводиться до setval.

Виправлення займає одну інструкцію. Підставте свою таблицю й стовпець замість orders і id; таблиця стоїть у назві перед _pkey:

select setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (select max(id) from public.orders)
);

pg_get_serial_sequence знаходить лічильник за стовпцем, і документація Postgres каже, що функція працює для обох видів: "If the column is an identity column, the associated sequence is the sequence internally created for that column." Далі setval із двома аргументами ставить лічильник так, що "the next nextval will advance the sequence before returning a value". Наступний новий рядок отримує найбільший id плюс один. Якщо Postgres відповідає, що relation не існує, назву таблиці написано інакше, вона має велику літеру або лежить в іншій схемі, і про цю помилку є окремий допис.

Відновлення з виводу pg_dump сюди не приводить. Його документація перелічує, що містить дамп даних: "Table data, large objects, and sequence values are dumped." Кожен лічильник повертається разом зі своїми рядками.

Три способи впоратися з дублікатом

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

Відхиліть його власними словами. Це підходить, коли другий рядок був би помилкою: адреса вже зареєстрована, ім'я користувача вже зайняте. Insert падає, ваш застосунок читає код, а людина бачить речення, яке написали ви:

const { error } = await supabase.from('profiles').insert({ id: user.id, username })

if (error?.code === '23505') {
  setMessage("Це ім'я користувача вже зайняте. Спробуйте інше.")
} else if (error) {
  setMessage('Щось пішло не так. Спробуйте ще раз.')
}

Дозвольте йому замінити старий. Це підходить, коли має перемогти найновіша версія: рядок налаштувань для кожного користувача, кошик для кожної сесії. Це upsert, і наступний розділ пояснює, коли він ламається.

Не надсилайте його. Іноді другого рядка взагалі не мало бути, і він приходить з одного з таких місць:

• кнопка, яка надсилає форму вдруге, коли її натискають удруге. Вимкніть її, доки не відповість перший запит.
• повторна спроба після повільної відповіді, коли перша вже пройшла
• два записувачі для одного рядка, як-от тригер, що створює профіль для кожного нового користувача, і код застосунку, що вставляє той самий профіль після реєстрації

Випадок із тригером варто впізнавати з першого погляду. Тригер, який копіює нових користувачів у вашу таблицю, падає з цією помилкою, коли значення, яке він копіює, там уже є, і Supabase повідомляє про всю реєстрацію як "Database error saving new user". Сам 23505 лежить у логах Postgres, і той допис показує, де саме.

Чи варто використовувати upsert?

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

.upsert() у supabase-js працює як insert, що робить щось інше, коли рядок уже існує. Його вихідний код каже це у двох рядках: "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." Це опція ignoreDuplicates, і Postgres виконує обидві версії як insert ... on conflict.

1. onConflict має називати стовпець із вашої помилки.Документація PostgREST каже: "By default, upsert operates based on the primary key columns". Upsert { email } у таблицю з первинним ключем id шукає зіткнення по id, не знаходить його, бо id новий, вставляє рядок і натрапляє на правило для email з тим самим 23505. Довідка Postgres про INSERT каже, що альтернативна дія спрацьовує, "if an arbiter constraint or index specified by conflict_target is violated". Зіткнення в будь-якому іншому стовпці відхиляє рядок, як і раніше.

await supabase
  .from('settings')
  .upsert({ user_id: user.id, theme: 'dark' }, { onConflict: 'user_id' })

2. Цьому стовпцю потрібне власне правило унікальності. Без нього Postgres нема з чим зіставляти, і він відповідає there is no unique or exclusion constraint matching the ON CONFLICT specification, код 42P10 (рядок, який це викидає).

3. З браузера Row Level Security читає більше ваших правил. Row Level Security охоплює правила для окремих рядків, які Supabase перевіряє в кожному запиті з вашим публічним ключем. Для upsert довідка Postgres про policies каже: "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." Коли рядок існує, а upsert зливає дані, наявний рядок ще й перевіряють за правилами оновлення таблиці. Рядок, що не проходить будь-яку з цих перевірок, завершує запит помилкою new row violates row-level security policy.

Тож у списку очікування upsert із браузера падає, доки таблиця не матиме правила читання, яке проходить відвідувач. Правило читання, яке незнайомець проходить для будь-якої адреси, дозволяє незнайомцям читати ваш список. Біля дверей переписати рядок можна лише тоді, коли вам спершу показали список. Для такої таблиці краще відхиляйте дублікат або скористайтеся функцією з наступного розділу.

Чи безпечно показувати цю помилку користувачам?

Ні. Показуйте власне речення, а сиру помилку тримайте в логах, із двох причин: рядок деталей може повернути значення, а сама помилка підтверджує, що значення існує, хоч би що ви показали.

Рядок деталей повторює значення. Key (email)=(maria@example.com) нешкідливий на екрані самої Марії, але геть інша річ на скриншоті у форумі підтримки, у системі відстеження помилок або на екрані того, хто ввів у вашу форму чужу адресу. Postgres уже його оберігає. Функція, яка будує цей рядок (BuildIndexValueDescription), спершу перевіряє: "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." Запит із вашим публічним ключем до таблиці з увімкненою Row Level Security отримує "details": null.

Якщо прочитати навпаки, рядок каже вам щось про таблицю. Якщо консоль браузера показує Key (...)=(...) для запиту, який ваш застосунок зробив із публічним ключем, Row Level Security на цій таблиці вимкнена. Єдиний виняток становить запит, який ваш застосунок надсилає через серверний код із секретним ключем, що повністю обходить правила. null доводить менше: Postgres прибирає рядок ще й тоді, коли той, хто викликає, не може читати кожен стовпець ключа. Як увімкнути Row Level Security на кожній таблиці розповідає про перемикач і перше правило.

Таблиця з вимкненими правилами відповідає незнайомцеві так само охоче, як і вам. Наше безкоштовне сканування читає ваш живий застосунок тим самим публічним ключем, який отримує відвідувач, повідомляє, які таблиці віддають рядки, і не потребує облікового запису.

Помилка відповідає на запитання, на яке ви, можливо, не хочете відповідати. Навіть без рядка деталей 409 каже тому, хто надіслав рядок, що значення вже є. Сторінка Postgres про безпеку рядків каже, що обмеження унікальності "always bypass row security to ensure that data integrity is maintained", і просить проєктувати таблиці й правила так, щоб ці перевірки не ставали прихованим каналом. У списку очікування будь-хто може дізнатися, чи є адреса у вашому списку, просто спробувавши її додати. Охоронець ніколи не читає ім'я вголос, а коли він когось розвертає, черга однаково знає, що таке ім'я там було.

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

create function public.join_waitlist(new_email text)
returns void
language sql
security definer
set search_path = ''
as $$
  insert into public.waitlist (email)
  values (new_email)
  on conflict (email) do nothing;
$$;

Ваш застосунок викликає supabase.rpc('join_waitlist', { new_email: email }). security definer виконує insert від імені власника функції, тож відвідувачам не потрібне жодне правило на таблиці, а правило, яке дозволяло їм вставляти, можна прибрати, інакше старий шлях лишиться відкритим поруч із новим. Що "Database error saving new user" каже про security definer пояснює, що він передає, а як викликати функцію Supabase показує, як узгодити назви аргументів.

Перш ніж змінювати, як ваш застосунок записує дані

Збережіть копію бази з часу до того, як перейдете з insert на upsert або підправите лічильник, і дізнайтеся, коли якась таблиця почне відповідати незнайомцям.

Reeve Care зберігає копію вашої бази даних Supabase:

• зашифрована копія за розкладом, у власному сховищі Reeve в ЄС, поза вашим обліковим записом Supabase
• кожна таблиця з рядками такими, якими вони були до того, як злиття їх переписало
• лічильник id кожної таблиці повертається разом із її рядками під час відновлення, тож наступний новий рядок отримує вільний номер
• кожна копія перевірена, перш ніж її зараховують, підрахунком рядків у кожній таблиці
• ваші завантажені файли теж, щойно ви підключите облікові дані Storage
• відновлення одним кліком, перед яким береться страхувальна копія поточного стану
• усе, що робить Monitor

Reeve Monitor і далі перевіряє ззовні:

• чи доступний ваш застосунок, з перевіркою щохвилини
• повна перевірка безпеки щогодини, до трьох застосунків
• повідомлення, коли результат змінюється, тож таблиця, яка після деплою почала відповідати незнайомцям, дійде до вас, і вам не доведеться йти дивитися
• щомісячний звіт простою мовою про те, що він побачив

Що зробити зараз

Почніть із назви в лапках, бо вона каже, яке правило відхилило рядок і який стовпець це правило стереже. Перш ніж запускати setval, upsert чи щось інше, що переписує рядки, на які покладаються люди, збережіть копію бази з часу до зміни.