postgresql – 忽略重复插入的最佳方法？

我已经设置了检查选项的测试.我将包含下面的代码,它可以在linux / Unix机器上的psql中运行(仅仅因为为了结果清晰起见,我将设置命令的输出传送到/ dev / null – 在Windows机器上可以选择一个日志文件).

我试图通过在plpgsql存储过程中使用一个循环来运行每种类型多个(即100个)INSERT来使不同的结果具有可比性.此外,在每次运行之前,通过截断并重新插入原始数据来重置表.

检查一些测试运行,看起来像使用规则并显式添加WHERE NOT EXISTS INSERT语句花费相似的时间,而导致异常需要花费更多的时间才能完成.

后者不是surprising：

Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one. Therefore,don’t
use EXCEPTION without need.

就个人而言,由于可读性和可维护性,我更喜欢将INSERE NOT EXISTS位添加到INSERT本身.就像触发器(也可以在这里测试)一样,调试(或简单地跟踪INSERT行为)在存在规则时更复杂.

我使用的代码(随意指出误解或其他问题)：

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,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,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,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,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();