Início / dba / Perguntas / 334391
Accepted
Vladimir Prudnikov
Asked: 2023-12-28 21:04:10 +0800 CST

Número incrementado automaticamente dentro do grupo de entidades

  • 772

Não sou um grande especialista em SQL, então preciso da ajuda da comunidade.

Eu tenho as seguintes tabelas simplificadas.

CREATE TABLE "Keyspace" (
    "id" SERIAL NOT NULL,
    "tenantId" INTEGER NOT NULL,
    "name" TEXT NOT NULL,

    CONSTRAINT "Keyspace_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "Project" (
    "id" SERIAL NOT NULL,
    "tenantId" INTEGER NOT NULL,
    "name" TEXT NOT NULL,
    "keyspaceId" INTEGER NOT NULL,

    CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "Entry" (
    "id" SERIAL NOT NULL,
    "tenantId" INTEGER NOT NULL,
    "projectId" INTEGER NOT NULL,

    CONSTRAINT "Entry_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "KeyspaceEntry" (
    "keyspaceId" INTEGER NOT NULL,
    "entryId" INTEGER NOT NULL,
    "number" INTEGER NOT NULL
);

Só se Entrypode estar em um Keyspace. Isso é aplicado por Entry-> Project-> Keyspacereferência.

Um Keyspaceterá muitos Entries.

O campo importante é KeyspaceEntry.number. Este é o número incrementado automaticamente para cada entrada no mesmo Keyspace.

Exemplo:

  • "Keyspace #1" tem 3 entradas com números 1, 2,3
  • "Keyspace #2" tem 2 entradas com números 1,2
  • Quando você cria uma nova entrada no "Projeto X" que faz referência ao "Keyspace #1", então KeyspaceEntry deve ser criado com number=4.

Preciso implementar a inserção atômica para Entry e KeyspaceEntry com incremento numberna mesma transação.

Nota: Pode parecer desnecessário ter a tabela KeyspaceEntry porque você pode simplesmente adicionar numberpropriedades à Entrada, mas este é um modelo simplificado. Quero ter um histórico de todos os relacionamentos Keyspace-Entry para o caso em que uma entrada é movida para um projeto diferente em um keyspace diferente.

Outra opção pode ser criar uma tabela histórica mas mantendo a Entry.numberpropriedade real. Pode ser melhor para consultar entradas (será usado para selecionar entradas individuais por KeyspaceID + EntryNumber). Posso considerar isso mais tarde também. Por favor, compartilhe se você tem opinião sobre esta mudança de design.

Estou usando PostgreSQL 16

postgresql

1 respostas

  1. Best Answer

    Tabelas de teste:

    CREATE TABLE parent( parent_id INTEGER PRIMARY KEY );
CREATE TABLE child( parent_id INTEGER NOT NULL REFERENCES parent(parent_id), number INTEGER NOT NULL );
INSERT INTO parent VALUES (1),(2);

    Em dois terminais diferentes, simultaneamente:

    BEGIN;
SELECT 1 FROM parent WHERE parent_id=1 FOR UPDATE;
INSERT INTO child SELECT 1, (SELECT coalesce(min(number)+1,1) FROM child WHERE parent_id=1);
-- switch to other terminal
COMMIT;

    Resultado:

    SELECT * FROM child;
 parent_id | number
-----------+--------
         1 |      1
         1 |      2

    SELECT FOR UPDATE bloqueará a linha na tabela pai durante a transação, o que força outros SELECTs FOR UPDATE a esperar. Quando a primeira transação (inserir com número 1) é confirmada, a segunda pega o bloqueio, pode ver a linha com número=1 que acabou de ser inserida e insere com número=2.

    Nesses tipos de consultas você deve bloquear a linha na tabela pai (keyspace), não na tabela filha (keyspaceEntry). O principal motivo é que você precisa primeiro incrementar o número antes de fazer a inserção, e precisa do bloqueio para incrementar o número, então a ordem tem que ser: bloquear, incrementar, inserir. Você não pode bloquear uma linha que ainda não foi inserida, e a linha inserida não fica visível para transações simultâneas até a confirmação.

    Bloquear a linha na tabela pai é inequívoco, devido à chave estrangeira que você sabe que ela existe e é única. Esta linha também é visível para todas as transações simultâneas que tentam inserir com o mesmo parent_id (keyspaceid).

    Um índice em (parent_id,number) acelerará max() e evitará a classificação se você quiser selecionar com ORDER BY parent_id, número que presumo ser um dos objetivos da operação.

    As transações devem ser mantidas curtas, pois manter o bloqueio no pai evita a inserção simultânea na tabela filho.

    Para garantir que o id seja sempre gerado, isso deve ir em um gatilho BEFORE INSERT no filho.

    Eu usei algo assim em um fórum. A paginação usava LIMIT e OFFSET, o que era lento. Substituí-o por uma coluna "número da postagem no tópico", permitindo encontrar as postagens desejadas diretamente.

    Excluir linhas criará buracos na sequência.

    Inserir uma nova linha após a exclusão do número mais alto reutilizará o número. Para chaves primárias, isso é indesejável e é bom que as sequências não tenham esse comportamento. Se você não quiser reutilizar o número, será necessário colocar um contador na tabela pai e incrementá-lo toda vez que inserir um filho. Exemplo:

    CREATE TABLE parent( parent_id INTEGER PRIMARY KEY, children_counter INTEGER NOT NULL DEFAULT 0 );
CREATE TABLE child( parent_id INTEGER NOT NULL REFERENCES parent(parent_id), number INTEGER NOT NULL );
INSERT INTO parent VALUES (1),(2);

WITH n AS (UPDATE parent SET children_counter=children_counter+1 WHERE parent_id=1 RETURNING children_counter)
INSERT INTO child SELECT 1, (SELECT children_counter FROM n);

    Com este, não há necessidade de SELECT FOR UPDATE, porque o UPDATE na tabela pai leva o bloqueio. Portanto, se você deseja apenas inserir uma linha na tabela filho, não precisa BEGIN explicitamente a transação, ela faz tudo em uma consulta.

    • 1

relate perguntas