Início / dba / Perguntas / 73334
Accepted
Adam Matan
Asked: 2014-08-07 09:13:53 +0800 CST

PostgreSQL 9.3: Chave primária violada por um gatilho INSERT

  • 772

Meu problema

Considere uma tabela tcom muitas atualizações frequentes de usuários, das quais apenas as últimas são relevantes.

Para manter o tamanho da tabela razoável, sempre que uma nova linha é inserida, as linhas antigas da mesma user_idsão excluídas. Para manter um arquivo, a linha também é gravada em t_history.

Ambos te t_historytêm o mesmo esquema, no qual idé a bigserialcom uma restrição de chave primária.

Implementação

Procedimento armazenado

CREATE FUNCTION update_t_history()
RETURNS trigger
AS
$$
declare
BEGIN
    -- Insert the row to the t_history table. `id` is autoincremented
    INSERT INTO t_history (a, b, c, ...)
    VALUES (NEW.a, NEW.b, NEW.c, ...);

    -- Delete old rows from the t table, keep the newest 10 
    DELETE FROM t WHERE id IN (
                  SELECT id FROM t 
                  WHERE user_id = NEW.user_id 
                  ORDER BY id DESC
                  OFFSET 9);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Gatilho de inserção correspondente:

CREATE TRIGGER t_insertion_trigger
AFTER INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE update_t_history();

O erro

O gatilho funciona bem, mas quando executo algumas dezenas de inserções em uma única transação, recebo o seguinte erro:

BEGIN
ERROR:  duplicate key value violates unique constraint "t_history_pkey"
DETAIL:  Key (id)=(196) already exists.

Atualizações

  • O idcampo em ambas as tabelas (de \d+ t):
    • id|bigint|not null default nextval('t_id_seq'::regclass)
    • "t_pkey" PRIMARY KEY, btree (id)
  • A versão do PostgreSQL é 9.3.

Alguma ideia de por que o procedimento armazenado quebra a restrição de chave primária nas transações?

stored-procedures postgresql

1 respostas

  1. Best Answer

    Por que é t_history.idauto-incrementado em primeiro lugar? Se "ambos te t_historytiverem o mesmo esquema" e t.idfor um PK serial, basta copiar linhas inteiras.

    Também sugiro que você copie apenas as linhas que realmente exclui de tpara t_history- em um CTE de modificação de dados. Dessa forma, você não terá linhas sobrepostas (o que pode ser parte do problema).

    CREATE FUNCTION update_t_history()
  RETURNS trigger AS
$func$
BEGIN
   -- Keep the newest 10, move older rows to t_history
   WITH del AS (
      DELETE FROM t
      USING (
         SELECT id
         FROM   t 
         WHERE  user_id = NEW.user_id 
         ORDER  BY id DESC
         OFFSET 10      -- to keep 10 (not 9)
         FOR UPDATE     -- avoid race condition
         ) d
      WHERE t.id = d.id
      RETURNING t.*
      )
   INSERT INTO t_history 
   SELECT * FROM del;   -- copy whole row

   RETURN NULL;         -- irrelevant in AFTER trigger
END
$func$  LANGUAGE plpgsql;

    A nova linha já está visível em um AFTERgatilho.

    • 2

relate perguntas