PostgreSQL skapar extra rader i katalogen för främmande nycklar eftersom en referens till en partitionerad tabell också måste fungera över dess fysiska partitioner. För att avgöra om raderna representerar en relation eller separata deklarationer undersöker du deras överordnade länkar och refererade tabeller.

Den här genomgången vänder sig till en utvecklare som använder en Mac för att undersöka en oväntad begränsningslista i en databläddrare eller ett Node.js-program. Uppgiften är att återskapa beteendet med syntetiska artikelrader, följa begränsningsfamiljen och avgöra vad som hör hemma i en logisk schemarapport.

Varför ger en främmande nyckel upphov till flera begränsningsrader?

En partitionerad tabell utgör en logisk tabell vars rader lagras i dess partitioner. Den överordnade tabellen har ingen egen lagring. Den skillnaden spelar roll när en annan tabell refererar till den: PostgreSQL måste upprätthålla relationen när rader i de fysiska partitionerna ändras. Se den officiella dokumentationen om tabellpartitionering.

Implementationen i PostgreSQL 18 tydliggör mekanismen. Rutinen addFkRecurseReferenced skapar utlösare för åtgärder på främmande nycklar och skapar rekursivt ytterligare en rad av typen pg_constraint för varje refererad partition. Valideringskoden förklarar att dessa rader på den refererade sidan stöder åtgärdsutlösare i stället för separata valideringsgenomsökningar. Se PostgreSQLs implementation för skapande och validering av främmande nycklar.

Raderna innebär alltså inte att varje refererande värde måste finnas i varje partition. Den deklarerade relationen pekar fortfarande på den partitionerade överordnade tabellen. PostgreSQL måste också hantera ändringar på den refererade sidan, däribland det konfigurerade beteendet när en refererad rad tas bort eller dess nyckel ändras. Dessa beteenden beskrivs i Främmande nycklar.

Vilka versioner av PostgreSQL stöder detta?

Möjligheten att referera till en partitionerad tabell med främmande nycklar infördes i PostgreSQL 12. Tidigare versioner kan inte använda samma deklaration som riktas mot den överordnade tabellen. PostgreSQL 12 listas nu som en version utan support; det är den historiska gränsen för när funktionen infördes, inte en rekommenderad installationsversion. Den här artikeln använder den aktuella dokumentationen för PostgreSQL 18 och dess underhållna källkodsgren. Se versionsinformationen för PostgreSQL 12.

Återskapa relationen med syntetiska artikelrader

Använd en befintlig lokal PostgreSQL-utvecklingsdatabas och en fungerande installation av psql. Öppna Terminal på din Mac, anslut med psql -d postgres och kontrollera servern med SHOW server_version;. Byt till namnet på din utvecklingsdatabas om det behövs. Den officiella dokumentationen för psql beskriver val av databas och körning av SQL interaktivt.

Kör hela den här konfigurationen en gång. Den skapar ett dedikerat schema utan att ta bort befintliga objekt. Om schemat redan finns ska du avbryta och använda en ny utvecklingsdatabas eller konsekvent byta namn på schemat i alla exempel.

BEGIN;

CREATE SCHEMA fk_partition_demo;

CREATE TABLE fk_partition_demo.articles (
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    title text NOT NULL,
    PRIMARY KEY (article_id, publication_year)
) PARTITION BY RANGE (publication_year);

CREATE TABLE fk_partition_demo.articles_2025
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2025) TO (2026);

CREATE TABLE fk_partition_demo.articles_2026
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2026) TO (2027);

CREATE TABLE fk_partition_demo.article_notes (
    note_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    note_text text NOT NULL,
    CONSTRAINT article_notes_article_fk
        FOREIGN KEY (article_id, publication_year)
        REFERENCES fk_partition_demo.articles
            (article_id, publication_year)
);

INSERT INTO fk_partition_demo.articles
    (article_id, publication_year, title)
VALUES
    (101, 2025, 'A guide to paper models'),
    (102, 2026, 'Choosing a notebook layout');

INSERT INTO fk_partition_demo.article_notes
    (note_id, article_id, publication_year, note_text)
VALUES
    (1, 101, 2025, 'Add a materials list'),
    (2, 102, 2026, 'Include a grid example');

COMMIT;

Den sammansatta primärnyckeln är avsiktlig. PostgreSQL kräver att en partitionerad tabells primärnyckel eller unika begränsning omfattar alla kolumner i partitioneringsnyckeln, med ytterligare begränsningar för uttryck i partitioneringsnycklar. Eftersom den här tabellen partitioneras efter publication_year omfattar primärnyckeln den kolumnen, och den främmande nyckeln refererar till hela paret. Se Deklarativ partitionering: begränsningar.

Exemplet identifierar därför en artikel med (article_id, publication_year). Det fastslår inte att article_id ensamt är globalt unikt. Om programmet behöver referenser som bara använder article_id bör du se över partitioneringen och nyckeldesignen innan du använder exemplet.

Följ begränsningsfamiljen i stället för att jämföra namn

De användbara katalogfälten är conrelid, tabellen som innehåller den refererande kolumnen; confrelid, den refererade tabellen; och conparentid, OID:t för den överordnade begränsningen. Begränsningsnamn är inte nödvändigtvis unika, så enbart namnen kan inte visa om två rader är oberoende av varandra. Dessa fält dokumenteras i pg_constraint.

Kör frågan här efter att du har kört konfigurationen. Den utgår från rotbegränsningar för främmande nycklar deklarerade på article_notes och följer deras underordnade begränsningar rekursivt.

WITH RECURSIVE fk_family AS (
    SELECT c.oid, c.conparentid, c.oid AS root_oid, 0 AS depth
    FROM pg_catalog.pg_constraint AS c
    WHERE c.contype = 'f'
      AND c.conrelid =
          'fk_partition_demo.article_notes'::regclass
      AND c.conparentid = 0

    UNION ALL

    SELECT child.oid, child.conparentid,
           parent.root_oid, parent.depth + 1
    FROM pg_catalog.pg_constraint AS child
    JOIN fk_family AS parent
      ON child.conparentid = parent.oid
    WHERE child.contype = 'f'
)
SELECT
    family.root_oid,
    family.depth,
    c.oid AS constraint_oid,
    c.conname,
    c.conparentid,
    c.conrelid::regclass AS referencing_table,
    c.confrelid::regclass AS referenced_table,
    pg_get_constraintdef(c.oid) AS definition
FROM fk_family AS family
JOIN pg_catalog.pg_constraint AS c ON c.oid = family.oid
ORDER BY family.root_oid, family.depth, c.oid;

För det här exemplet med en enda partitionsnivå innebär implementationen tre rader för främmande nycklar: en som riktar sig mot articles och en som riktar sig mot vardera av dess två partitioner. Det här är ett förväntat resultat som härletts från källkoden, inte resultat från en genomförd körning. Genererade namn och OID-värden ska inte hårdkodas. Samma källkod skapar också begränsningar på den refererade sidan när en partition skapas eller kopplas till senare. Se tablecmds.c.

Rad i det här exemplet Refererande tabell Refererad tabell Överordnad länk
Rotrelation article_notes articles conparentid = 0
Underordnad rad för refererad partition article_notes articles_2025 Den överordnade begränsningens OID
Underordnad rad för refererad partition article_notes articles_2026 Den överordnade begränsningens OID

Den detalj som är lätt att missa är att den refererande tabellen är article_notes i alla tre raderna. Undersök båda tabellkolumnerna och den överordnade länken; om du bara tittar på tabellen som är kopplad till begränsningen kan strukturen med refererade partitioner döljas.

Frågan visar också återskapade definitioner genom pg_get_constraintdef(). PostgreSQL dokumenterar den här funktionen som en rekonstruktion, inte som den ursprungliga SQL som skickades in. Använd den för att förstå relationen, inte för att återfå den exakta migreringstexten. Se Systeminformationsfunktioner och operatorer.

Granska samma rader med node-postgres på din Mac

node-postgres returnerar serverns frågeresultat; de partitionsrelaterade raderna kommer från PostgreSQLs begränsningsmekanism. Dess dokumenterade fråge-API tar emot SQL-text och en separat array med parametervärden. Här skickas relationsnamnet som ett textvärde som PostgreSQL konverterar till regclass. Se Frågor.

Installera en aktuell version av Node.js med support, skapa en arbetskatalog och installera pg. Installationskommandot och information om kompatibla körtidsversioner finns i introduktionen till node-postgres.

mkdir pg-fk-inspection
cd pg-fk-inspection
npm init -y
npm install pg

Spara följande fullständiga program som inspect-fks.mjs. Det kräver att SQL-konfigurationen ovan har körts och bekräftats. Till skillnad från den rekursiva SQL-rapporten listar programmet rader som är direkt kopplade till den angivna refererande tabellen; det räcker för den partitionerade tabellen article_notes i det här exemplet.

import pg from 'pg';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const relation = process.argv[2] ??
  'fk_partition_demo.article_notes';

try {
  await client.connect();

  const version = await client.query('SHOW server_version');
  console.log('PostgreSQL:', version.rows[0].server_version);

  const result = await client.query(`
    SELECT oid, conname,
           conrelid::regclass::text AS referencing_table,
           confrelid::regclass::text AS referenced_table,
           conparentid,
           conparentid = 0 AS is_root,
           pg_get_constraintdef(oid) AS definition
    FROM pg_catalog.pg_constraint
    WHERE contype = 'f' AND conrelid = $1::regclass
    ORDER BY conparentid, oid
  `, [relation]);

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Kör programmet mot samma databas som användes för SQL-konfigurationen. node-postgres använder PostgreSQLs miljövariabler för anslutning, bland annat PGDATABASE, och dokumenterar deras standardvärden i Anslutning.

PGDATABASE=postgres node inspect-fks.mjs

Om relationen inte hittas ska du kontrollera att båda sessionerna använder samma databas och att konfigurationen bekräftades. Ett tomt resultat betyder att den här frågan inte hittade några främmande nycklar som är direkt kopplade till den relationen; den rapporterar inte inkommande referenser från andra tabeller. JavaScript-koden klarade en syntaxkontroll när artikeln förbereddes. Det gick inte att köra den mot en databas, så SQL-exemplen och den förväntade katalogstrukturen granskades mot den hämtade dokumentationen och källkoden.

Bestäm vad som ska visas eller undersökas

Följ den här ordningen när en begränsningslista verkar oväntat lång. Den skiljer en rapport över logiska relationer från en inventering av implementationen och behåller samtidigt användbart underlag.

  1. Bekräfta relationen och serverversionen. Använd det schemafullständiga relationsnamnet och notera SHOW server_version; innan du jämför beteendet med dokumentationen.
  2. Följ överordnade länkar. I det här exemplet tillhör underordnade rader som delar rotens OID den deklarerade relationens begränsningsfamilj.
  3. Jämför båda sidorna. Ett ändrat confrelid tillsammans med samma refererande tabell förklarar raderna för refererade partitioner som visas ovan.
  4. Välj syftet med rapporten. För rotrelationer som deklarerats på den valda tabellen använder du filtret contype = 'f' AND conparentid = 0. Behåll den rekursiva rapporten för att visa hela familjen.
  5. Undersök separata rötter var för sig. Jämför kolumnpar, refererade tabeller, åtgärder, valideringsstatus och uppskjutbarhet innan du avgör att två deklarationer är överflödiga.

Den här rapporteringsmetoden följer identifierarna och relationsfälten som dokumenteras i pg_constraint. Filtret för endast rötter har ett avgränsat användningsområde: en partition kan ingå genom ärvda underordnade begränsningar, så om du bara visar rötter för en lövtabell får du inte en fullständig rapport över alla relationer som påverkar den.

Om du behöver underlag om objekten som upprätthåller relationen länkar pg_trigger.tgconstraint en utlösare till dess begränsning, medan tgrelid identifierar tabellen som innehåller utlösaren. Katalogen anger också internt genererade utlösare. Dessa fält ger en ytterligare granskningsväg i pg_trigger.

Behåll de genererade raderna så länge den deklarerade relationen behövs. I det återskapade fallet är den praktiska lösningen att visa rotrelationen i en logisk schemavy och behålla dess underordnade rader i diagnostikvyn. När de överordnade länkarna och båda tabelländarna visas blir det tydligt hur PostgreSQL implementerar referensen över partitioner.

Källor