PostgreSQL kan inte använda ON CONFLICT DO UPDATE som en generell åtgärd för att ”matcha valfri unik nyckel”. Välj en nyckel som bestämmer identiteten, eller kontrollera uttryckligen båda nycklarna i en transaktion och avvisa indata som identifierar två olika rader.

Den här handledningen hjälper en utvecklare som arbetar i Terminal på en Mac att implementera den andra policyn med node-postgres: infoga när ingen av nycklarna matchar, uppdatera när exakt en artikel matchar och returnera en uttrycklig konflikt när nycklarna pekar på olika artiklar. Alla rader är syntetiska.

SQL-exemplen är avsedda för PostgreSQL 18, den version som används i den aktuella dokumentation som hämtades. Implementationen använder en vanlig, icke-partitionerad tabell med två omedelbara unika begränsningar. Kontrollera serverns huvudversion innan du anpassar exempel från en annan versions handbok. PostgreSQL 18 INSERT-dokumentation

Varför två unika begränsningar inte kan bli ett enda konfliktmål

Anta att en artikel har en extern referens och en slug. Båda måste vara unika var för sig. Börja med den här fullständiga konfigurationen i en tom demonstrationsdatabas och spara den som 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');

Dessa deklarationer upprätthåller två separata regler. Om du ersätter dem med UNIQUE (external_key, slug) ändras datamodellen: endast kombinationen måste då vara unik, vilket tillåter upprepade externa nycklar och upprepade sluggar i olika kombinationer. Om båda kolumnerna behålls som NOT NULL slipper exemplet också beteendet för nycklar med nullvärden. PostgreSQL-begränsningar

För DO UPDATE kräver PostgreSQL ett konfliktmål. En kolumnlista anger kolumnerna i ett matchande unikt index; den betyder inte ”endera kolumnen”. Därför kan ON CONFLICT (external_key, slug) inte härleda ett skiljedomsindex från de två separata begränsningarna ovan. Om du anger en begränsning väljs just den, och syntaxen tillhandahåller en ON CONFLICT-sats i stället för en kedja av alternativa hanterare. PostgreSQL INSERT: ON CONFLICT-satsen

Den större utmaningen är att avgöra vilken befintlig artikel den inkommande raden representerar. Tänk på de inkommande värdena feed-101 och green-notes: den externa nyckeln tillhör den blå artikeln, medan sluggen tillhör den gröna artikeln. Om du uppdaterar någon av raderna med båda inkommande nycklarna uppstår en konflikt med den andra raden. Att välja den första matchningen döljer ett olöst beslut om indata.

Bestäm vad som ska hända för varje kombination av matchningar

Använd följande beslutstabell innan du skriver frågan. Det här är en föreslagen policy för applikationen som utgår från schemat, inte ett beteende som PostgreSQL tillhandahåller automatiskt.

Inkommande extern nyckel Inkommande slug Olika matchande rader Åtgärd i den här handledningen
Oanvänd Oanvänd 0 Infoga en artikel
Matchar en artikel Oanvänd 1 Uppdatera artikeln, inklusive dess slug
Oanvänd Matchar en artikel 1 Uppdatera artikeln, inklusive dess externa nyckel
Matchar artikel A Matchar artikel A 1 Uppdatera artikel A
Matchar artikel A Matchar artikel B 2 Avvisa indata och bevara båda artiklarna

I fallen med en matchning tillåts medvetet att endera nyckeln identifierar en artikel och att den andra nyckeln ändras. Bekräfta att importen ska fungera så. Om en extern nyckel aldrig får ändras bör en matchning enbart på slug i stället leda till en konflikt i applikationen.

Om din faktiska regel är enklare — ”den externa nyckeln avgör alltid vilken artikel det gäller” — använder du en vanlig, målinriktad upsert. Den här fullständiga satsen körs efter konfigurationen ovan:

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;

Här tillhandahåller EXCLUDED de föreslagna värdena. Den andra unika begränsningen gäller fortfarande: om den föreslagna sluggen ändras till green-notes uppstår en separat unikhetskonflikt i stället för att åtgärden byter till den andra artikeln. Den målinriktade upsertens garanti om atomär infogning eller uppdatering gäller under förutsättning att inga oberoende fel uppstår. PostgreSQL INSERT-dokumentation

Håll beslutet om valfri nyckel stabilt vid samtidiga skrivningar

En sökning följd av en infogning skapar ett tidsfönster mellan beslutet och skrivningen. En annan transaktion kan hinna infoga en matchande nyckel under det fönstret. Om du lägger till FOR UPDATE i sökningen låses de hämtade raderna. Om ingen rad matchar finns det ingen hämtad rad att låsa. Därför räcker det inte med enbart radlåsning för den här sökvägen där ingen rad matchar. PostgreSQL Explicit Locking

För en liten import eller en enstaka skrivbordsåtgärd är en enkel lösning att ta ett tabellås av typen SHARE ROW EXCLUSIVE innan du kontrollerar någon av nycklarna. Det här läget står i konflikt med ROW EXCLUSIVE-låset som vanliga infogningar, uppdateringar och borttagningar tar, och även med en annan instans av sig självt. Vanliga läsningar är fortfarande kompatibla. PostgreSQL-lås på tabellnivå

Sekvensen blir då: starta transaktionen, ta tabellåset, hitta olika matchande rader, tillämpa beslutstabellen och verkställ transaktionen. Det uttryckliga låset väntar på inkompatibla lås och behålls tills transaktionen avslutas. PostgreSQL LOCK

Ange READ COMMITTED uttryckligen här. Sökningen ser data som har verkställts innan den börjar, även skrivningar som slutförs medan låsanropet väntar. I kombination med det tagna tabellåset ger det här implementeringen ett stabilt underlag för att fatta beslut och skriva. PostgreSQL-isolering av transaktioner

Det här är ett brett lås: även orelaterade skrivningar till artiklar får vänta. Håll transaktionen kort och undvik interaktiva frågor och externt arbete mellan låsningen och verkställandet. Vid frekventa samtidiga importer bör du överväga om en enda kanonisk nyckel kan bestämma identiteten innan du väljer den här lösningen.

Implementera transaktionen med node-postgres

Du behöver en körande PostgreSQL 18-demonstrationsdatabas, kommandot psql och en Node.js-version som stöds av ditt installerade node-postgres-paket. Installera paketet i Terminal och läs in konfigurationsfilen. Det första kommandot skapar paketmetadata om demonstrationskatalogen inte redan innehåller dem. Installation och kompatibilitet för node-postgres

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

Kommandona förutsätter att dina vanliga lokala anslutningsinställningar redan väljer demonstrationsdatabasen. Programmet använder new Pool(), som läser samma PostgreSQL-anslutningsvariabler som psql. Ange PGDATABASE till demonstrationsdatabasens namn vid behov. Anslutning med node-postgres

Spara det här fullständiga programmet som upsert-articles.mjs. Det förutsätter att konfigurationstabellen inte har några ytterligare utlösare eller regler som ändrar matchnings- eller uppdateringsbeteendet. Värden skickas separat från SQL-texten med positionsparametrar. Frågor med 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();
}

Alla transaktionssatser använder samma incheckade klient, vilket node-postgres kräver. Om du skickar satserna genom separata anrop till pool.query() bevaras inte kravet på samma anslutning. Transaktioner med node-postgres

Kodblocket finally ser alltid till att den förvärvade klienten frigörs. Om återställningen misslyckas kastar release(true) bort anslutningen. Den yttre pool.end() stänger poolen när skrivbordsskriptet avslutas. Pool-API för node-postgres

Kör programmet med node upsert-articles.mjs. Det förväntade resultatet är den blå artikeln med sin reviderade titel och oförändrade nycklar. JavaScript-syntaxen kontrollerades med Node.js. Databasexemplen granskades mot dokumentationen men kördes inte mot en PostgreSQL-server här.

Kontrollera varje gren och hantera fel uttryckligen

Ändra objektet som skickas till upsertArticle() för att testa följande fall med konfigurationsraderna. Kör varje fall med den ursprungliga konfigurationen, eller ta hänsyn till ändringar som gjorts i tidigare fall.

externalKey slug Förväntat resultat
feed-303 amber-notes Infoga en ny artikel
feed-101 blue-revised Uppdatera den blå artikeln och ersätta dess slug
feed-303 blue-notes Uppdatera den blå artikeln och ersätta dess externa nyckel
feed-101 blue-notes Uppdatera den blå artikelns titel
feed-101 green-notes Kasta ARTICLE_KEY_CONFLICT och lämna båda artiklarna oförändrade

Sökningen returnerar rader, så en artikel som matchar båda villkoren räknas bara en gång. Med de här två oberoende unika nycklarna som inte tillåter nullvärden kan högst två olika rader matcha. Koden hanterar aldrig ett resultat med två rader genom att använda LIMIT 1, ta bort en artikel eller tyst hoppa över indata.

ARTICLE_KEY_CONFLICT är en kod som definieras av applikationen. PostgreSQLs 23505 betyder unique_violation; det är ett separat villkor. Om det senare inträffar efter att du har anpassat exemplet bör du undersöka den överträdda begränsningen och eventuellt tillagda tabellbeteenden i stället för att behandla alla dubbletter som samma utfall. PostgreSQL rekommenderar att du kontrollerar felkoder i stället för att tolka meddelandetext. PostgreSQL-felkoder

Låsförvärv kan innebära väntetid. Om du föredrar omedelbart fel för den här skrivbordsuppgiften kan du ersätta låssatsen med LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL ger då ett fel om låset inte kan tas omedelbart, och programmet följer sin återställningsväg. PostgreSQL LOCK

Skulle DO NOTHING eller MERGE förenkla uppgiften?

ON CONFLICT DO NOTHING utan mål kan hoppa över en infogning som står i konflikt med en användbar unik begränsning eller ett användbart unikt index. Välj det när det är avsiktligt att hoppa över infogningen; det implementerar inte uppdateringspolicyn som beskrivs här. PostgreSQL INSERT-dokumentation

MERGE introducerades i PostgreSQL 15. Även om det tillåter ett kopplingsvillkor som omfattar endera nyckeln måste kopplingen fortfarande ha en policy för källrader som matchar två olika målrader. Åtgärderna omfattas fortfarande av de vanliga begränsningarna, och det samtidiga beteendet är inte likvärdigt med en målinriktad upsert. Versionsnytt för PostgreSQL 15, PostgreSQL MERGE

För uppgiften med valfri nyckel gör du antalet matchningar till ett uttryckligt beslut: noll infogar, en uppdaterar och två stoppar med en användbar konflikt. Behåll de unika begränsningarna och välj en strategi för samtidighet som bevarar det beslutet tills skrivningen är klar. Implementationen med tabellås är en konkret utgångspunkt när det är acceptabelt att serialisera artikelskrivningar.

Källor