On Mon, Jun 8, 2015 at 10:15 AM, Dave Johansen <davejohan...@gmail.com> wrote:
> I'm using Postgres 8.4 on RHEL 6 and I need to support inserting data into > a table from multiple processes with there occasionally being duplicates > from the different processes. Here's a simple example table: > CREATE TABLE test (tutc TIMESTAMP WITHOUT TIME ZONE, id INTEGER, value > INTEGER, PRIMARY KEY (tutc, id)); > If I do the following query from 2 processes, then it's fine: > INSERT INTO test (tutc, id, value) SELECT '2015-01-01 01:02:03', 4, 5 > WHERE NOT EXISTS (SELECT 1 FROM test WHERE tutc='2015-01-01 01:02:03' AND > id=4); > > But if I put the operation in a transaction, then the second process will > block until the transaction of the first is commited (which is fine) but > then the insert fails with a "duplicate key value violation". I'm guessing > that this is because the transaction is making it so that the SELECT only > sees the values from before the transaction of the second process began. > > Using an "upsert" type of function, like the one shown in the > documentation ( see > http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > ) seems like it might work, but I don't need to support updating and was > hoping to not have to use a custom function. So is there some way to catch > the unique_violation exception without creating a function? Or some other > solution to this? > For the sake of documentation, here's the function that I used to accomplish this: CREATE FUNCTION insert_test_no_dup(tutc_ TIMESTAMP WITHOUT TIME ZONE, id_ INTEGER, value_ INTEGER) RETURNS VOID AS $$ BEGIN BEGIN INSERT INTO test(tutc, id, value) VALUES (tutc_, id_, value_); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing because the record already exists END; END; $$ LANGUAGE plpgsql; Both using a rule and using a trigger had the same issue with transactions. Here's the declarations for documentation: CREATE RULE ignore_duplicate_inserts AS ON INSERT TO test WHERE (EXISTS (SELECT 1 FROM test WHERE tutc=new.tutc AND id=new.id)) DO INSTEAD NOTHING; CREATE FUNCTION tf_insert_test_ignore_duplicates() RETURNS trigger AS $$ DECLARE found BOOLEAN; BEGIN SELECT 1 INTO found FROM test WHERE tutc=new.tutc AND id=new.id; IF found THEN RETURN NULL; ELSE RETURN new; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_test_before BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE tf_insert_test_ignore_duplicates();
