Início / dba / Perguntas / 340308
Accepted
Philipp Faster
Asked: 2024-06-18 11:08:01 +0800 CST

Como anexar partição com chave primária

  • 772

Estou lutando para anexar uma partição a uma tabela com uma chave primária.

Eu tenho uma tabela particionada Transactions:

create table "Transactions"
(
    id          bigserial                                                       not null,
    uid         uuid                                                            not null,
    type        varchar(255)                                                    not null,
    amount      numeric(26, 10)                                                 not null,
    "createdAt" timestamp(3) default CURRENT_TIMESTAMP                          not null,
    primary key (id, "createdAt")
) partition by RANGE ("createdAt")

create index "Transactions_createdAt_idx" on "Transactions" ("createdAt" desc);
create index "Transactions_type_idx" on "Transactions" (type);
create index "Transactions_uid_idx" on "Transactions" (uid);

Eu crio uma nova partição todo mês com uma tabela particionada. Cada dia eu crio uma partição por um dia.

CREATE TABLE "Transactions_20240618" (LIKE "Transactions_20240617" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
ALTER TABLE "Transactions_202406_parted" ATTACH PARTITION "Transactions_20240618" FOR VALUES FROM ('2024-06-18') TO ('2024-06-19');

No início do próximo mês, quero eliminar o mês particionado e criar uma partição normal para esse mês para reduzir o número de partições.

Estou tentando usar o seguinte script:

CREATE TABLE "Transactions_202404" (LIKE "Transactions_202404_parted" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202404" SELECT * FROM "Transactions_202404_parted";
alter table "Transactions_202404" add primary key (id, "createdAt");
create index "Transactions_202404_createdAt_idx" on "Transactions_202404" ("createdAt" desc);
create index "Transactions_202404_type_idx" on "Transactions_202404" (type);
create index "Transactions_202404_uid_idx" on "Transactions_202404" (uid);
alter table "Transactions_202404" add constraint "Transactions_202404_check" check ("createdAt">='2024-04-01' and "createdAt"<'2024-05-01');
alter table "Transactions" detach partition "Transactions_202404_parted";
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
alter table "Transactions_202404" drop constraint "Transactions_202404_check";

Na pré-última linha, quando tento anexar a partição recém-criada, o PostgreSQL me culpa por tentar criar uma segunda chave primária na tabela "Transactions_202404":

[42P16] ERROR: multiple primary keys for table "Transactions_202404" are not allowed

Pelo que entendi, o PostgreSQL se recusa a usar a chave primária existente por algum motivo e tenta criar a sua própria como filha da chave primária da tabela "Transações".

Se eu tentar criar uma chave UNIQUE para minha nova partição e conectá-la à tabela principal, ela funcionará, mas estou faltando PK na nova partição.

O problema é que, se eu fizer todos os passos com chave única e depois criar um PK na tabela já anexada, reconecte-o para que o Postgres considere o PK como filho do PK principal, então funciona, verifique:

CREATE TABLE "Transactions_202404" (LIKE "Transactions_202404_parted" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202404" SELECT * FROM "Transactions_202404_parted";
alter table "Transactions_202404" add unique (id, "createdAt");
create index "Transactions_202404_createdAt_idx" on "Transactions_202404" ("createdAt" desc);
create index "Transactions_202404_type_idx" on "Transactions_202404" (type);
create index "Transactions_202404_uid_idx" on "Transactions_202404" (uid);
alter table "Transactions_202404" add constraint "Transactions_202404_check" check ("createdAt">='2024-04-01' and "createdAt"<'2024-05-01');
alter table "Transactions" detach partition "Transactions_202404_parted";
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
-- start of PK fix
create unique index concurrently "Transactions_202404_pkey" on "Transactions_202404" (id, "createdAt");
alter table "Transactions_202404" add primary key using index "Transactions_202404_pkey";
alter table "Transactions" detach partition "Transactions_202404";
alter table "Transactions_202404" drop constraint "Transactions_202404_id_createdAt_key"; -- drop the unnecessary unique key
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
-- end of fix
alter table "Transactions_202404" drop constraint "Transactions_202404_check";

O que estou fazendo de errado? Alguém com bom conhecimento de como funciona poderia me dizer como anexar uma partição à tabela principal sem criar um índice exclusivo duas vezes?

postgresql

1 respostas

  1. Best Answer

    Então, vou responder minha própria pergunta depois que perdi um dia tentando definir uma solução e finalmente a encontrei apenas tentando diferentes combinações de afirmações.

    Na minha pergunta inicial, deixei de fora uma declaração de definição de chave que achei que não desempenhava nenhum papel no problema: outro índice exclusivo nas mesmas colunas do PK.

    Meu script real era:

    CREATE TABLE "Transactions_202405" (LIKE "Transactions" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202405" SELECT * FROM "Transactions_202405_parted";
alter table "Transactions_202405" add primary key (id, "createdAt");
alter table "Transactions_202405" add foreign key (uid) references "User" on update cascade on delete restrict;
create index "Transactions_202405_createdAt_idx" on "Transactions_202405" ("createdAt" desc);
create index "Transactions_202405_type_idx" on "Transactions_202405" (type);
create index "Transactions_202405_uid_idx" on "Transactions_202405" (uid);
create unique index "Transactions_202405_id_createdAt_idx" on "Transactions_202405" (id desc, "createdAt" desc);
alter table "Transactions_202405" add constraint "Transactions_202405_check" check ("createdAt">='2024-05-01' and "createdAt"<'2024-06-01');
alter table "Transactions" attach partition "Transactions_202405" for values from ('2024-05-01') TO ('2024-06-01');
alter table "Transactions_202405" drop constraint "Transactions_202405_check";

    Então, como você pode ver na 8ª linha, há uma definição de outro índice exclusivo. Deixei de fora porque queria fornecer o código mínimo para o problema. Este índice é definido em todas as partições e na tabela particionada principal.

    A solução que encontrei é a seguinte: mover alter table ... add primary key ...a instrução após a definição do índice exclusivo. Depois disso, começa a funcionar perfeitamente. Tentei todas as combinações de ordem de linhas, mas somente quando movo esta linha após um índice único - ela começa a funcionar.

    O interessante é que se eu definir PK antes do índice exclusivo e, em seguida, descartá-lo e redefinir após o índice exclusivo, o código ainda não funcionará. Este comportamento cheira a um bug no lado do PostgreSQL...

    Encontrei um exemplo reproduzível mínimo:

    create table "Transactions"
(
    id          bigserial                                                       not null,
    uid         uuid                                                            not null,
    type        varchar(255)                                                    not null,
    amount      numeric(26, 10)                                                 not null,
    "createdAt" timestamp(3) default CURRENT_TIMESTAMP                          not null
) partition by RANGE ("createdAt");
create unique index "Transactions_id_createdAt_idx" on "Transactions" (id desc, "createdAt" desc);
alter table "Transactions" add primary key (id, "createdAt");

create table "Transactions_202403" (LIKE "Transactions" INCLUDING DEFAULTS);
alter table "Transactions_202403" add primary key (id, "createdAt");
create unique index "Transactions_202403_id_createdAt_idx" on "Transactions_202403" (id desc, "createdAt" desc);
alter table "Transactions" attach partition "Transactions_202403" for values from ('2024-03-01') to ('2024-04-01');

    Se eu alterar a ordem das instruções PK e de índice exclusivo no primeiro ou no segundo bloco - o script será interrompido. Parece que o PostgreSQL exige que você defina restrições e índices exatamente na mesma ordem da tabela particionada. Parece complicado, mas meio lógico.

    Desculpe pelo barulho, obrigado por ler :D

    • 0

relate perguntas