Início / user-287792

Philipp Faster's questions

Martin Hope
Philipp Faster
Asked: 2024-06-18 11:08:01 +0800 CST
  • 6

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
Martin Hope
Philipp Faster
Asked: 2024-03-01 17:44:52 +0800 CST
  • 5

Tenho muitos lugares em meu aplicativo onde os usuários estão fazendo algo que não deveria ser simultâneo em bases por usuário e namespace. Então isso significa que:

  • Dois usuários podem pressionar o mesmo botão ao mesmo tempo
  • Um usuário não pode pressionar o mesmo botão uma segunda vez até que o primeiro toque seja concluído
  • Um usuário pode pressionar botões diferentes ao mesmo tempo

Quais soluções eu tentei:

  1. Bloqueio consultivo com função hash no usuário uuid - tem colisões e esse bloqueio consultivo, mas o bloqueio bloqueia todas as interações do usuário - então ele não pode pressionar dois botões ao mesmo tempo
  2. INSERT& SELECT FOR UPDATE- bom, mas preciso preencher previamente a tabela com linha "pendente" para poluir o esquema da tabela com colunas desnecessárias.
  3. Deseja implementar: locktable. Então, o que quero dizer é criar uma tabela com bloqueios do tipo consultivo. Terá duas colunas namespacee id- chave primária composta. Cada vez que o usuário pressionar o botão nº 1, haverá INSERT& FOR UPDATEpara namespace= button#1e id= {userid}.

Eu sinto que existem algumas pedras escondidas nesta (3ª) abordagem - alguém sabe disso? Você poderia gentilmente listar esses problemas?

Tenho certeza de que será pior em termos de desempenho do que os bloqueios de aconselhamento, mas será muito pior? Além disso, não consegui encontrar implementações dessa abordagem - parece que tive uma ideia estúpida que ninguém usa por algum motivo importante.

Confira esta conversa interessante em 2009 sobre este problema: https://www.postgresql.org/message-id/ [email protegido]

Por INSERT& FOR UPDATEquero dizer:

INSERT INTO adv_locks VALUES ('btn1', '123') ON CONFLICT ON CONSTRAINT adv_locks_pk DO NOTHING;
SELECT 1 FROM adv_locks WHERE namespace='btn1' and id='123' FOR UPDATE;

Dessa forma sempre haverá uma linha em uma tabela com esta tabela que pode ser bloqueada. (Ainda não me importo muito com o tamanho da mesa)

Entendo que uma ordem dessa pode ser quebrada (quero dizer, a solicitação nº 1 cria uma linha e a solicitação nº 2 seleciona a linha mais rapidamente), mas não é um problema para mim.

postgresql