I PostgreSQL 15 eller senare använder du en UNIQUE NULLS NOT DISTINCT-begränsning deklarerad som DEFERRABLE och skjuter sedan upp den inom omordningstransaktionen. Då kan artikelrader byta positioner samtidigt som den slutliga ordningen förblir unik, även för rader vars förälder är NULL. Syntax för PostgreSQL-begränsningar

Den här handledningen riktar sig till en utvecklare som arbetar i Mac Terminal och behöver ordna om syntetiska artikelrader inom en föräldragrupp. Den innehåller en SQL-demonstration, ett komplett node-postgres-program som validerar den begärda ordningen och ett alternativ för PostgreSQL 14. Att flytta artiklar till en annan förälder ingår inte i exemplet.

Varför komplicerar en nullable förälder positionsunikheten?

Anta att varje artikel har ett id, ett nullable parent_id och ett positivt heltal position. Artiklar med samma förälder bildar en ordnad syskongrupp. I den här modellen tillhör alla artiklar med en null-förälder en gemensam rotgrupp.

En vanlig UNIQUE (parent_id, position) tillämpar inte den regeln för rotgruppen. PostgreSQL behandlar normalt null-värden som olika vid unikhetskontroll, så två rotartiklar kan båda ha position 1. Med NULLS NOT DISTINCT jämförs dessa null-värden för föräldern som likvärdiga när den sammansatta nyckeln kontrolleras. Olika föräldrar som inte är null kan fortfarande ha varsin position 1. PostgreSQL: unika begränsningar

Omordning skapar ett separat tidsproblem. Om artikel 1 har position 1 och artikel 2 har position 2, leder en flytt av artikel 2 till position 1 till att artikel 1s position tillfälligt dupliceras. En begränsning som inte kan skjutas upp kontrollerar varje infogad eller ändrad rad omedelbart. En begränsning som kan skjutas upp kan vänta tills satsen är slut, eller tills transaktionen avslutas om den uttryckligen har skjutits upp. PostgreSQL: CREATE TABLE

För en omordning som består av flera uppdateringar spelar båda egenskaperna roll: unikhet som tar hänsyn till null-värden definierar de korrekta grupperna, och uppskjuten kontroll tillåter tillfälliga kollisioner mellan uppdateringar.

Vilken definition ska du använda med din PostgreSQL-version?

PostgreSQL 15 introducerade NULLS NOT DISTINCT för unika begränsningar och index. Exemplen nedan kräver serverversion 15 eller senare; PostgreSQL 14 kräver alternativet som visas längre fram. Kontrollera den anslutna servern i stället för att dra slutsatser om versionen utifrån den installerade kommandoradsklienten. Versionsnyheter för PostgreSQL 15

Situation Metod som stöds
PostgreSQL 15 eller senare, ny tabell Namngiven UNIQUE NULLS NOT DISTINCT-begränsning med DEFERRABLE INITIALLY IMMEDIATE
Befintlig vanlig unik begränsning Ersätt definitionen; ALTER CONSTRAINT kan inte ändra uppskjutbarheten för en unik begränsning
Befintligt uttrycksbaserat eller partiellt unikt index Ersätt lösningen för upprätthållande; det kan inte kopplas till som en vanlig unik begränsning
PostgreSQL 14 Använd genererade grupperingskolumner med en uppskjutbar unik begränsning

Begränsningarna för befintliga objekt dokumenteras i ALTER TABLE: på det här sättet kan begränsningsattribut bara ändras för främmande nycklar, och ett index som är kopplat som en unik begränsning får inte innehålla uttryck eller vara partiellt. En fristående CREATE UNIQUE INDEX har inte heller någon DEFERRABLE-sats. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Hur byter du plats på två rotartiklar i SQL?

Förutsättningarna är en befintlig lokal PostgreSQL-databas med version 15 eller senare och en fungerande psql-anslutning. Öppna Mac Terminal och starta klienten med dina konfigurerade anslutningsinställningar. Följande kommando hoppar över anpassningar vid start och avbryter skriptkörningen vid fel. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Klistra in hela SQL-demonstrationen nedan i sessionen. Den skapar en tillfällig tabell med enbart syntetiska artiklar; tillfälliga tabeller försvinner när sessionen avslutas. Tabellnamnet får inte redan användas i sessionen. PostgreSQL: tillfälliga tabeller

SHOW server_version;

CREATE TEMP TABLE article_order_demo (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    CONSTRAINT article_order_demo_parent_position_key
        UNIQUE NULLS NOT DISTINCT (parent_id, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo (id, parent_id, title, position)
VALUES
    (1, NULL, 'Article A', 1),
    (2, NULL, 'Article B', 2),
    (3, NULL, 'Article C', 3),
    (4, 1, 'Article A: Overview', 1),
    (5, 1, 'Article A: Examples', 2);

BEGIN;
SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED;

UPDATE article_order_demo
SET position = 1
WHERE id = 2 AND parent_id IS NULL;

UPDATE article_order_demo
SET position = 2
WHERE id = 1 AND parent_id IS NULL;

SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo
WHERE parent_id IS NULL
ORDER BY position;

Efter den första uppdateringen innehåller transaktionen tillfälligt två rötter med positionen 1. Efter den andra uppdateringen är deras positioner unika igen. När du ändrar tillbaka begränsningen till IMMEDIATE kontrolleras då alla väntande ändringar. Om en dubblett finns kvar misslyckas kommandot; annars kan transaktionen verkställas. Uppskjutning ändrar när valideringen sker. PostgreSQL: SET CONSTRAINTS

Den förväntade slutliga rotordningen är:

Position Artikel-ID Titel
1 2 Artikel B
2 1 Artikel A
3 3 Artikel C

Barnartiklarna behåller sina positioner eftersom ingen av uppdateringarna väljer ut dem. Om ett fel inträffar i en interaktiv transaktion kör du ROLLBACK; innan du försöker igen. En transaktion grupperar relaterade satser fram till COMMIT eller ROLLBACK. PostgreSQL: BEGIN

Hur ordnar du om en komplett syskongrupp med node-postgres?

Använd en och samma klient som du har hämtat ur poolen för hela transaktionen. node-postgres kräver uttryckligen detta: separata anrop till pool.query() passar inte för en transaktion eftersom de kan köras på olika anslutningar. node-postgres: transaktioner

Programmet nedan tar emot en komplett ordnad lista med syskon-ID:n. Den extra beslutsregeln är enkel: den angivna listan måste vara en permutation av den aktuella syskonmängden. Genom att avvisa dubbletter, utelämnade ID:n och ID:n från andra grupper förhindras en skenbart lyckad partiell omordning.

Installera pg i ett lokalt Node.js-projekt, spara följande kompletta program som reorder.cjs och kör det. Programmet förutsätter att dina befintliga lokala anslutningsinställningar är tillgängliga via PostgreSQL-miljövariabler. node-postgres stöder samma anslutningsmiljövariabler som psql. node-postgres: anslutning

npm install pg
node reorder.cjs

const { Pool } = require('pg');

function validateOrder(ids, rows) {
  if (!Array.isArray(ids) || !ids.every(Number.isSafeInteger) ||
      new Set(ids).size !== ids.length) {
    throw new Error('Order must contain distinct integer IDs');
  }
  const existing = new Set(rows.map(row => row.id));
  if (rows.length !== ids.length || ids.some(id => !existing.has(id))) {
    throw new Error('Order must include every sibling exactly once');
  }
}

async function main() {
  const pool = new Pool();
  let client;
  try {
    client = await pool.connect();
    await client.query(`
      CREATE TEMP TABLE article_order_demo (
        id integer PRIMARY KEY,
        parent_id integer REFERENCES article_order_demo(id),
        title text NOT NULL,
        position integer NOT NULL CHECK (position > 0),
        CONSTRAINT article_order_demo_parent_position_key
          UNIQUE NULLS NOT DISTINCT (parent_id, position)
          DEFERRABLE INITIALLY IMMEDIATE
      )
    `);
    await client.query(`
      INSERT INTO article_order_demo (id, parent_id, title, position)
      VALUES (1, NULL, 'Article A', 1),
             (2, NULL, 'Article B', 2),
             (3, NULL, 'Article C', 3),
             (4, 1, 'Article A: Overview', 1),
             (5, 1, 'Article A: Examples', 2)
    `);

    const parentId = null;
    const orderedIds = [2, 1, 3];
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    try {
      await client.query(
        'LOCK TABLE article_order_demo IN SHARE ROW EXCLUSIVE MODE'
      );
      const siblings = await client.query(
        `SELECT id FROM article_order_demo
         WHERE parent_id IS NOT DISTINCT FROM $1::integer`,
        [parentId]
      );
      validateOrder(orderedIds, siblings.rows);
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED'
      );
      for (const [index, id] of orderedIds.entries()) {
        const result = await client.query(
          `UPDATE article_order_demo SET position = $1
           WHERE id = $2
             AND parent_id IS NOT DISTINCT FROM $3::integer`,
          [index + 1, id, parentId]
        );
        if (result.rowCount !== 1) {
          throw new Error('Expected exactly one updated article');
        }
      }
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE'
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }

    const result = await client.query(
      `SELECT id, title, position FROM article_order_demo
       WHERE parent_id IS NOT DISTINCT FROM $1::integer
       ORDER BY position`,
      [parentId]
    );
    console.table(result.rows);
  } finally {
    if (client) client.release();
    await pool.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

IS NOT DISTINCT FROM gör att föräldrafiltret fungerar för både null- och icke-null-värden. Vanlig likhet med en null-parameter väljer inte ut rötterna. Om du i stället vill ordna om exempelbarnen ändrar du parentId till 1 och orderedIds till [5, 4]. PostgreSQL: jämförelsefunktioner och operatorer

Platshållarna i formatet $1 skickar värden separat från SQL-texten. Den uttryckliga heltalskonverteringen anger också den avsedda typen när föräldraparametern är null. node-postgres: frågor

Varför låsa innan du läser in syskonen?

Den tillfälliga demonstrationen har inga konkurrerande sessioner. Tabellåsningen visar ett avsiktligt brett tillvägagångssätt för en liten delad tabell: SHARE ROW EXCLUSIVE står i konflikt med vanliga skrivningar till tabellen och med sig själv, men tillåter vanliga läsningar. Om låset behålls under hela transaktionen förblir syskonmedlemskapet stabilt under validering och uppdateringar. Det blockerar också skrivningar till orelaterade föräldragrupper, så överväg den avvägningen innan du använder det på en belastad tabell. PostgreSQL: uttrycklig låsning

Programmet väljer uttryckligen READ COMMITTED. Med den isoleringsnivån ser syskonfrågan inlästa ändringar som är verkställda när frågan startar, inklusive ändringar som verkställdes medan den föregående låsväntan pågick. PostgreSQL: BEGIN, PostgreSQL: transaktionsisolering

JavaScript-valideringen kontrollerades med en giltig permutation samt med dubbletter, saknade ID:n och ID:n från andra grupper. De deterministiska kontrollerna godkändes. Databasexemplen jämfördes med dokumentationen, men kördes inte mot PostgreSQL i forskningsmiljön.

Vilket alternativ stöds i PostgreSQL 14?

I PostgreSQL 14 materialiserar du grupperingsuttrycket som genererade kolumner och tillämpar sedan en uppskjutbar unik begränsning på dem. Lagrade genererade kolumner beräknas när rader skrivs och kan inte åsidosättas med vanliga värden. PostgreSQL 14: genererade kolumner

Det här fristående SQL-exemplet använder två genererade värden för att representera föräldragruppen. Det booleska värdet skiljer rötter från ett faktiskt föräldra-ID med värdet 0, så reservvärdet COALESCE kräver inte att ett ID reserveras.

CREATE TEMP TABLE article_order_demo14 (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo14(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    parent_is_root boolean
        GENERATED ALWAYS AS (parent_id IS NULL) STORED,
    parent_group integer
        GENERATED ALWAYS AS (COALESCE(parent_id, 0)) STORED,
    CONSTRAINT article_order_demo14_parent_position_key
        UNIQUE (parent_is_root, parent_group, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo14 (id, parent_id, title, position)
VALUES (1, NULL, 'Article A', 1),
       (2, NULL, 'Article B', 2);

BEGIN;
SET CONSTRAINTS article_order_demo14_parent_position_key DEFERRED;
UPDATE article_order_demo14 SET position = 1 WHERE id = 2;
UPDATE article_order_demo14 SET position = 2 WHERE id = 1;
SET CONSTRAINTS article_order_demo14_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo14
WHERE parent_id IS NULL
ORDER BY position;

För en rot är de genererade grupperingsvärdena (true, 0); för föräldern 0 är de (false, 0). Varje förälder som inte är null behåller sitt eget heltalsvärde. Begränsningen uttrycker därför den avsedda grupperingen utan att vara beroende av PostgreSQL 15:s behandling av null-värden. PostgreSQL 14 stöder uppskjutbara unika begränsningar på namngivna kolumner. PostgreSQL 14: CREATE TABLE

Vad bör du kontrollera innan du anpassar en befintlig tabell?

Ett uttrycksindex på COALESCE(parent_id, 0) kan upprätthålla uttrycksbaserad unikhet, men det kan inte göras uppskjutbart eller kopplas till som en vanlig unik begränsning. Ett partiellt index för rotartiklar har samma begränsning. Om något av dessa objekt för omedelbar kontroll lämnas kvar kan det fortfarande förhindra ett byte efter att en uppskjutbar begränsning lagts till. PostgreSQL: index på uttryck, PostgreSQL: ALTER TABLE

Innan du ersätter en befintlig definition kontrollerar du om det finns duplicerade par av förälder och position, särskilt för rötter som tidigare tilläts genom vanlig unikhet med nullable värden. När en unik begränsning läggs till valideras befintliga rader; NOT VALID är inte tillgängligt för unika begränsningar. Lös tvetydiga positioner medvetet innan du försöker göra ändringen. PostgreSQL: ALTER TABLE

Håll begränsningens löfte åtskilt från programmets ordningsregel. Enbart unikhet tillåter luckor, till exempel positionerna 1, 4 och 9. Programmet för kompletta listor tilldelar avsiktligt positioner i följd med början vid 1. Det är medlemskontrollen som säkerställer att alla syskon deltar.

För den här uppgiften väljer du den begränsning som motsvarar föräldragrupper med nullable värden, skjuter upp den före den första kolliderande uppdateringen, validerar hela den begärda syskongruppen och slutför alla uppdateringar med en klient innan du verkställer transaktionen. Den kombinationen stöder omordning av rötter och barn, samtidigt som varje syskon har en unik slutlig position.

Källor