Обсуждение: Inserting from multiple processes?

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?

On Mon, Jun 8, 2015 at 10:15 AM, Dave Johansen <davejohansen@gmail.com>

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;

Hi Dave:

On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen <davejohansen@gmail.com> wrote:

> It appears that calling "SELECT insert_test_no_dup('2015-01-01', 1, 1)"
> cause the XID to increment? I'm not sure if it's only when the exception
> happens or all the time, but if there some way to prevent the increment of
> XID because it's causing problems with our system:
> http://www.postgresql.org/message-id/CAAcYxUer3MA=enXvnOwe0oSAA8ComvxCF6OrHp-vUppr56twFg@mail.gmail.com

I, personally, would expect an START TRANSACTION to burn an XID, they
are serial, and they need to be allocated to have transaction
ordering, like the thing which happens with the sequences. I assume
the server can have some optimizations ( like delaying XID adquisition
to the first appropiate statement, which I think depends on your
isolation level ), but I would never expect it to not allocate it
before an insert, it needs it to be sent to the table, in case it
succeeds, and has to acquire it beforehand, in case someone needs to
acquire another xid between the time it starts inserting and the time
it succeeds or fail. Some internals expert may shed some light, but
after reading your link it seems your problem is just you do too many
transactions without a vacuum ( also reading your pointed threas it
sees you do vacuum fulls, which seems unneeded ) and expecting
postgres has some kind of magic to avoid burning the xids.