Início / dba / Perguntas / 30499
Accepted
Dave Jarvis
Asked: 2012-12-16 14:25:50 +0800 CST

Maneira ideal de ignorar inserções duplicadas? [duplicado]

  • 772

Fundo

Esse problema está relacionado a ignorar inserções duplicadas usando o PostgreSQL 9.2 ou superior. A razão pela qual eu pergunto é por causa deste código:

  -- Ignores duplicates.
  INSERT INTO
    db_table (tbl_column_1, tbl_column_2)
  VALUES (
    SELECT
      unnseted_column,
      param_association
    FROM
      unnest( param_array_ids ) AS unnested_column
  );

O código é livre de verificações de valores existentes. (Nesta situação específica, o usuário não se importa com erros de inserção de duplicatas -- a inserção deve "simplesmente funcionar".) Adicionar código nessa situação para testar explicitamente duplicatas traz complicações.

Problema

No PostgreSQL, encontrei algumas maneiras de ignorar inserções duplicadas.

Ignorar duplicatas #1

Crie uma transação que detecte violações de restrição exclusivas, sem realizar nenhuma ação:

  BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
  EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
  END;

Ignorar duplicatas #2

Crie uma regra para ignorar duplicatas em uma determinada tabela:

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO db_table
   WHERE (EXISTS ( SELECT 1
           FROM db_table
          WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;

Perguntas

Minhas perguntas são principalmente acadêmicas:

  • Qual método é mais eficiente?
  • Qual método é mais sustentável e por quê?
  • Qual é a maneira padrão de ignorar erros de duplicação de inserção com o PostgreSQL?
  • Existe uma maneira tecnicamente mais eficiente de ignorar inserções duplicadas; Se assim for, o que é?

Obrigada!

postgresql constraint

1 respostas

  1. Best Answer

    Como mencionam as respostas da outra pergunta (da qual esta é considerada uma duplicata), existe (desde a versão 9.5) uma UPSERTfuncionalidade nativa. Para versões mais antigas, continue lendo :)

    Eu configurei um teste para verificar as opções. Incluirei o código abaixo, que pode ser executado em psqluma caixa linux/Unix (simplesmente porque, para maior clareza nos resultados, canalizei a saída dos comandos de instalação para /dev/null- em uma caixa do Windows, pode-se escolher um log arquivo em vez disso).

    Tentei tornar diferentes resultados comparáveis ​​usando mais de um (ou seja, 100) INSERTpor tipo, executado a partir de um loop dentro de um plpgsqlprocedimento armazenado. Além disso, antes de cada execução, a tabela é redefinida truncando e reinserindo os dados originais.

    Verificando algumas execuções de teste, parece que usar a regra e adicionar explicitamente WHERE NOT EXISTSa INSERTinstrução gasta tempo semelhante, enquanto capturar uma exceção leva muito mais tempo para ser concluído.

    O último não é tão surpreendente :

    Dica: Um bloco contendo uma cláusula EXCEPTION é significativamente mais caro para entrar e sair do que um bloco sem uma. Portanto, não use EXCEPTION sem necessidade.

    Pessoalmente, devido à legibilidade e manutenção, prefiro adicionar o WHERE NOT EXISTSbit aos INSERTpróprios s. Assim como com os gatilhos (que também podem ser testados aqui), a depuração (ou simplesmente o INSERTcomportamento de rastreamento) é mais complicado com as regras presentes.

    E o código que usei (fique à vontade para apontar equívocos ou outros problemas):

    \o /dev/null
\timing off

-- set up data
DROP TABLE IF EXISTS insert_test;

CREATE TABLE insert_test_base_data (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

CREATE TABLE insert_test (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

INSERT INTO insert_test_base_data
SELECT i, (SELECT random() AS r WHERE s.i = s.i)
FROM 
    generate_series(2, 200, 2) s(i)
;

UPDATE insert_test_base_data
SET col2 = md5(col1::text)
;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;



-- function with exception block to be called later
CREATE OR REPLACE FUNCTION f_insert_test_insert(
    id integer,
    col1 double precision,
    col2 text
)
RETURNS void AS
$body$
BEGIN
    INSERT INTO insert_test
    VALUES ($1, $2, $3)
    ;
EXCEPTION
    WHEN unique_violation
    THEN NULL;
END;
$body$
LANGUAGE plpgsql;



-- function running plain SQL ... WHERE NOT EXISTS ...
CREATE OR REPLACE FUNCTION insert_test_where_not_exists()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        WHERE NOT EXISTS (
            SELECT 1
            FROM insert_test
            WHERE id = i
        )
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;



-- call a function with exception block
CREATE OR REPLACE FUNCTION insert_test_function_with_exception_block()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        PERFORM f_insert_test_insert(i, rnd, md5(rnd::text))
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;



-- leave checking existence to a rule
CREATE OR REPLACE FUNCTION insert_test_rule()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;



\o
\timing on


\echo 
\echo 'check before INSERT'

SELECT insert_test_where_not_exists();

\echo 



\o /dev/null

\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

\timing on

\o

\echo 'catch unique-violation'

SELECT insert_test_function_with_exception_block();

\echo 
\echo 'implementing a RULE'

\o /dev/null
\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO insert_test
    WHERE EXISTS ( 
        SELECT 1
        FROM insert_test
        WHERE id = NEW.id
    ) 
    DO INSTEAD NOTHING;

\o 
\timing on

SELECT insert_test_rule();
    • 23

relate perguntas