Re: Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data - Mailing list pgsql-hackers

Hi all

We recently moved to using 9.5 and were hoping to use the new upsert functionality, but unfortunately it doesn’t quite do what we need.

Our setup is something like this:

CREATE TABLE t1 (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  bk1 INT,
  bk2 UUID
  — other columns
);
CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2);

CREATE TABLE t2 (
  t1_id BIGINT NOT NULL REFERENCES t1
 — other stuff
);

Data comes in as inserts of one tuple each of t1 and t2. We expect inserts to t1 to be heavily duplicated. That is, for stuff coming in we expect a large number of rows to have duplicate (bk1, bk2), and we wish to discard those, but not discard the t2 tuple - those should always be inserted and reference the correct t1 record.

So we currently have an insertion function that does this:

BEGIN
  INSERT INTO t1 (bk1, bk2, other columns)
  VALUES (bk1val, bk2val, other values)
  RETURNING id
  INTO t1_id;
EXCEPTION WHEN unique_violation THEN
  SELECT id
  FROM t1
  WHERE bk1 = bk1val AND bk2 = bk2val
  INTO t1_id;
END;

INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);

We were hoping that we’d be able to do something like this:

INSERT INTO t1 (bk1, bk2, other columns)
  VALUES (bk1val, bk2val, other values)
  ON CONFLICT (bk1val, bk2val) DO NOTHING
  RETURNING id
  INTO t1_id;
INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);

But unfortunately it seems that the RETURNING clause returns null when there’s a conflict, rather than the existing row’s value.

I understand that there is ambiguity if there were multiple rows that were in conflict. I think this sort of functionality really only makes sense where the conflict target is a unique constraint, so IMO it would make sense to only support returning columns in that case.

I imagine that this would be possible to do more efficiently than the subsequent query that we are currently doing given that postgres has already found the rows in question, in the index at least. I have no idea how hard it would actually be to implement though. FWIW my use-case would be supported even if this only worked for indexes where the to-be-returned columns were stored in the index using Anastasia’s covering + unique index patch, when that lands.

Thoughts?

Tom



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers