Re: Inserting from multiple processes?

[Dave Johansen <davejohansen@xxxxxxxxx>]

On Mon, Jun 8, 2015 at 10:15 AM, Dave Johansen <davejohansen@xxxxxxxxx> 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();