PostgreSQL nie może używać ON CONFLICT DO UPDATE jako ogólnej operacji „dopasuj dowolny unikalny klucz”. Wybierz klucz definiujący tożsamość albo jawnie sprawdź oba klucze w transakcji i odrzuć dane wejściowe, które wskazują dwa różne wiersze.

Ten samouczek pomaga programiście pracującemu w Terminalu na Macu zaimplementować tę drugą politykę za pomocą node-postgres: wstawić artykuł, jeśli żaden klucz nie pasuje, zaktualizować go, gdy pasuje dokładnie jeden artykuł, i zwrócić jawny konflikt, gdy klucze wskazują różne artykuły. Wszystkie wiersze są syntetyczne.

Przykłady SQL dotyczą PostgreSQL 18, wersji obsługiwanej przez pobraną aktualną dokumentację. Implementacja korzysta ze zwykłej, niepartycjonowanej tabeli z dwoma natychmiastowymi ograniczeniami unikalności. Przed dostosowaniem przykładów z podręcznika dla innej wersji sprawdź główny numer wersji serwera. Dokumentacja INSERT PostgreSQL 18

Dlaczego dwa ograniczenia unikalności nie mogą stać się jednym celem konfliktu

Załóżmy, że artykuł ma zewnętrzny identyfikator i slug. Każdy z nich musi być unikalny niezależnie. Zacznij od tej kompletnej konfiguracji w pustej bazie demonstracyjnej; zapisz ją jako setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Te deklaracje egzekwują dwie odrębne reguły. Zastąpienie ich przez UNIQUE (external_key, slug) zmieniłoby model: unikalna musiałaby być tylko para, co pozwoliłoby na powtarzające się klucze zewnętrzne i slugi w różnych kombinacjach. Utrzymanie obu kolumn jako NOT NULL pozwala też uniknąć wprowadzania do tego przykładu zachowania kluczy nullable. Ograniczenia PostgreSQL

W przypadku DO UPDATE PostgreSQL wymaga wskazania celu konfliktu. Lista kolumn opisuje kolumny pasującego unikalnego indeksu; nie oznacza „dowolna kolumna”. Dlatego ON CONFLICT (external_key, slug) nie może wywnioskować arbitra z dwóch oddzielnych ograniczeń powyżej. Podanie nazwy ograniczenia wybiera właśnie to ograniczenie, a składnia udostępnia jedną klauzulę ON CONFLICT, a nie łańcuch alternatywnych procedur obsługi. INSERT PostgreSQL: klauzula ON CONFLICT

Głębszy problem polega na ustaleniu, który istniejący artykuł reprezentuje przychodzący wiersz. Rozważmy przychodzące wartości feed-101 i green-notes: klucz zewnętrzny należy do niebieskiego artykułu, a slug do zielonego. Zaktualizowanie dowolnego z tych wierszy oboma przychodzącymi kluczami spowodowałoby konflikt z drugim wierszem. Wybranie pierwszego dopasowania ukryłoby nierozstrzygniętą decyzję dotyczącą danych wejściowych.

Zdecyduj, co ma się stać w każdej kombinacji dopasowań

Przed napisaniem zapytania skorzystaj z poniższej tabeli decyzyjnej. Przedstawia ona proponowaną politykę aplikacji wynikającą z tego schematu, a nie zachowanie automatycznie zapewniane przez PostgreSQL.

Przychodzący klucz zewnętrzny Przychodzący slug Liczba różnych pasujących wierszy Działanie w tym samouczku
Nieużywany Nieużywany 0 Wstaw artykuł
Pasuje do artykułu Nieużywany 1 Zaktualizuj ten artykuł, w tym jego slug
Nieużywany Pasuje do artykułu 1 Zaktualizuj ten artykuł, w tym jego klucz zewnętrzny
Pasuje do artykułu A Pasuje do artykułu A 1 Zaktualizuj artykuł A
Pasuje do artykułu A Pasuje do artykułu B 2 Odrzuć dane wejściowe; zachowaj oba artykuły

W przypadkach z jednym dopasowaniem celowo dopuszczamy identyfikację artykułu przez dowolny z kluczy oraz zmianę drugiego klucza. Potwierdź, że import powinien działać właśnie w ten sposób. Jeśli klucz zewnętrzny nigdy nie może się zmienić, dopasowanie wyłącznie po slugu powinno zamiast tego powodować konflikt aplikacji.

Jeśli Twoja faktyczna reguła jest prostsza — „klucz zewnętrzny zawsze określa artykuł” — użyj zwykłego upsertu z określonym celem. To kompletne polecenie uruchom po powyższej konfiguracji:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Tutaj EXCLUDED dostarcza proponowane wartości. Nadal obowiązuje drugie ograniczenie unikalności: zmiana proponowanego sluga na green-notes spowoduje niezależny konflikt unikalności zamiast przełączenia na inny artykuł. Gwarancja atomowego wstawienia lub aktualizacji przez upsert z określonym celem obowiązuje pod warunkiem braku niezależnych błędów. Dokumentacja INSERT PostgreSQL

Zapewnij stabilność decyzji o dopasowaniu dowolnego klucza podczas równoczesnych zapisów

Wyszukanie, po którym następuje wstawienie, tworzy przerwę między podjęciem decyzji a zapisem. W tej przerwie inna transakcja może wstawić pasujący klucz. Dodanie FOR UPDATE do wyszukiwania blokuje pobrane wiersze; jeśli nie pasuje żaden wiersz, nie ma pobranego wiersza, który można zablokować. Samo blokowanie wierszy nie wystarcza więc w tej konkretnej ścieżce bez dopasowań. Jawne blokowanie PostgreSQL

W przypadku niewielkiego importu lub sporadycznej operacji na komputerze prostym rozwiązaniem jest zablokowanie tabeli poleceniem SHARE ROW EXCLUSIVE przed sprawdzeniem obu kluczy. Ten tryb koliduje z blokadą ROW EXCLUSIVE uzyskiwaną przez zwykłe wstawienia, aktualizacje i usunięcia, a także z inną blokadą tego samego trybu. Zwykłe odczyty pozostają zgodne. Tryby blokad tabel PostgreSQL

Powstała sekwencja wygląda następująco: rozpocznij transakcję, zablokuj tabelę, znajdź różne pasujące wiersze, zastosuj tabelę decyzyjną i zatwierdź transakcję. Jawna blokada czeka na niezgodne blokady i pozostaje utrzymana do końca transakcji. LOCK PostgreSQL

Jawnie użyj tutaj poziomu izolacji READ COMMITTED. Wyszukiwanie widzi dane zatwierdzone przed jego rozpoczęciem, w tym zapisy ukończone podczas oczekiwania na uzyskanie blokady. Połączenie tego zachowania migawki z uzyskaną blokadą tabeli zapewnia tej implementacji stabilną podstawę do podjęcia decyzji i wykonania zapisu. Izolacja transakcji PostgreSQL

To szeroka blokada: wstrzymuje także niezwiązane zapisy do artykułów. Transakcja powinna być krótka — bez interaktywnych monitów ani zewnętrznych operacji między blokowaniem a zatwierdzeniem. W przypadku częstych równoczesnych importów, zanim przyjmiesz ten projekt, rozważ, czy jeden kanoniczny klucz może definiować tożsamość.

Zaimplementuj transakcję za pomocą node-postgres

Potrzebujesz działającej demonstracyjnej bazy PostgreSQL 18, polecenia psql oraz wersji Node.js obsługiwanej przez zainstalowany pakiet node-postgres. W Terminalu zainstaluj pakiet i wczytaj plik konfiguracyjny. Pierwsze polecenie utworzy metadane pakietu, jeśli nie ma ich jeszcze w katalogu demonstracyjnym. Instalacja i zgodność node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Te polecenia zakładają, że zwykłe lokalne ustawienia połączenia już wybierają demonstracyjną bazę danych. Program używa new Pool(), które odczytuje te same zmienne środowiskowe połączenia PostgreSQL co psql; w razie potrzeby ustaw PGDATABASE na nazwę demonstracyjnej bazy danych. Łączenie node-postgres

Zapisz ten kompletny program jako upsert-articles.mjs. Zakłada on, że tabela utworzona podczas konfiguracji nie ma dodatkowych wyzwalaczy ani reguł zmieniających zachowanie dopasowania lub aktualizacji. Wartości są przekazywane za pomocą parametrów pozycyjnych, niezależnie od tekstu SQL. Zapytania node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Każde polecenie transakcji korzysta z tego samego klienta pobranego z puli, czego wymaga node-postgres. Wysyłanie tych poleceń przez osobne wywołania pool.query() nie zachowałoby tego wymogu dotyczącego połączenia. Transakcje node-postgres

Blok finally zawsze zwalnia pobranego klienta. Jeśli wycofanie transakcji również się nie powiedzie, release(true) odrzuca połączenie. Zewnętrzne pool.end() zamyka pulę po zakończeniu skryptu na komputerze. API puli node-postgres

Uruchom program poleceniem node upsert-articles.mjs. Oczekiwanym wynikiem jest niebieski artykuł ze zmienionym tytułem i niezmienionymi kluczami. Składnia JavaScript została sprawdzona za pomocą Node.js; przykłady bazy danych przejrzano pod kątem zgodności z dokumentacją, ale nie uruchomiono ich tutaj na serwerze PostgreSQL.

Sprawdź każdą gałąź i jawnie obsługuj błędy

Korzystając z wierszy utworzonych podczas konfiguracji, zmień obiekt przekazywany do upsertArticle(), aby przetestować poniższe przypadki. Uruchom każdy przypadek na pierwotnym stanie konfiguracji albo uwzględnij zmiany wprowadzone przez wcześniejsze przypadki.

externalKey slug Oczekiwany wynik
feed-303 amber-notes Wstaw nowy artykuł
feed-101 blue-revised Zaktualizuj niebieski artykuł i zastąp jego slug
feed-303 blue-notes Zaktualizuj niebieski artykuł i zastąp jego klucz zewnętrzny
feed-101 blue-notes Zaktualizuj tytuł niebieskiego artykułu
feed-101 green-notes Rzuć ARTICLE_KEY_CONFLICT; nie zmieniaj żadnego artykułu

Wyszukiwanie zwraca wiersze, więc artykuł spełniający oba warunki jest liczony tylko raz. Przy tych dwóch niezależnych, niepustych kluczach unikalnych mogą pasować najwyżej dwa różne wiersze. Kod nigdy nie rozstrzyga wyniku obejmującego dwa wiersze przez użycie LIMIT 1, usunięcie artykułu ani ciche pominięcie danych wejściowych.

ARTICLE_KEY_CONFLICT to kod zdefiniowany przez aplikację. PostgreSQL używa 23505 w znaczeniu unique_violation; jest to odrębny warunek. Jeśli po dostosowaniu przykładu pojawi się ten drugi błąd, sprawdź naruszone ograniczenie i wszelkie dodane zachowanie tabeli, zamiast traktować każdy duplikat jako ten sam przypadek. PostgreSQL zaleca sprawdzanie kodów błędów zamiast analizowania tekstu komunikatu. Kody błędów PostgreSQL

Uzyskanie blokady może wymagać oczekiwania. Jeśli wolisz, aby ta operacja na komputerze nie czekała i od razu zgłaszała błąd, zastąp polecenie blokowania przez LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL zgłosi wtedy błąd, gdy nie będzie mógł natychmiast uzyskać blokady, a program wykona ścieżkę wycofania transakcji. LOCK PostgreSQL

Czy DO NOTHING lub MERGE uprości zadanie?

Bez określenia celu ON CONFLICT DO NOTHING może pominąć wstawienie powodujące konflikt z dowolnym użytecznym ograniczeniem lub indeksem unikalnym. Wybierz tę opcję, gdy pominięcie jest zamierzonym wynikiem; nie implementuje ona opisanej tutaj polityki aktualizacji. Dokumentacja INSERT PostgreSQL

MERGE wprowadzono w PostgreSQL 15. Chociaż pozwala użyć w warunku złączenia dowolnego klucza, takie złączenie nadal wymaga określenia polityki dla wiersza źródłowego pasującego do dwóch różnych artykułów docelowych. Jego działania nadal podlegają zwykłym ograniczeniom, a jego zachowanie przy równoczesnych operacjach nie jest zamienne z upsertem z określonym celem. Informacje o wydaniu PostgreSQL 15, MERGE PostgreSQL

W zadaniu dotyczącym dopasowania dowolnego klucza przyjmij liczbę dopasowań jako jawne kryterium decyzji: zero oznacza wstawienie, jedno — aktualizację, a dwa — przerwanie z użytecznym komunikatem o konflikcie. Zachowaj ograniczenia unikalności i wybierz strategię współbieżności, która utrzyma tę decyzję do zakończenia zapisu. Implementacja z blokadą tabeli to konkretny punkt wyjścia, gdy akceptowalna jest serializacja zapisów artykułów.

Źródła