Run SQL statements in PL/pgSQL only if a row doesn't exist

Question

I want to do something like this in a PL/pgSQL function in Postgres 9.6:

INSERT INTO table1 (id, value) VALUES (1, 'a') ON CONFLICT DO NOTHING;
--- If the above statement didn't insert a new row
---   because id of 1 already existed, 
---   then run the following statements

INSERT INTO table2 (table1_id, value) VALUES (1, 'a');
UPDATE table3 set (table1_id, time) = (1, now());

However, I don't know how to determine whether the first INSERT actually inserted a new row, or whether the the ON CONFLICT DO NOTHING was done.

I could do a SELECT at the beginning of the function to see whether a record with id of 1 exists in table1 before running all the SQL statements, but this would lead to race conditions I think.

When you write *"Postgres function"* you mean `LANGUAGE plpgsql`? Or `LANGUAGE sql`? Please be clear, and *always* add your version of Postgres. — Erwin Brandstetter, Jan 02 '17 at 18:33
Thanks for the suggestions, will do in future. In this case it's `plpgsql` and `9.6`. — user779159, Jan 02 '17 at 18:39

Erwin Brandstetter · Accepted Answer · 2022-08-16T21:46:43.660

4

For a PL/pgSQL function, use the special variable FOUND:

CREATE FUNCTION foo(int, text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO table1 (id, value)
   VALUES ($1, $2)
   ON CONFLICT DO NOTHING;

   IF NOT FOUND THEN
      INSERT INTO table2 (table1_id, value)
      VALUES ($1, $2);
      UPDATE table3
      SET   (table1_id, time)
          = ($1       , now())
      WHERE  ???;  -- you don't want to update all rows in table3?
   END IF;
END
$func$;

Call:

SELECT foo(1, 'a');

FOUND is set to false if the INSERT does not actually insert any rows.

The manual about the ON CONFLICT Clause:

ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

The manual about Obtaining the Result Status

UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

To be clear, this runs the later statements if a row in table1 does already exist, so the new row is not inserted. (Like you requested, but contrary to your question title.)

If you just want to check whether a row exists:

PostgreSQL IF statement

Race condition?

If subsequent commands in the same transaction depend on the (yet unlocked) existing row in table1 (with a FK for instance), you'll want to lock it to defend against concurrent transactions deleting or updating it in the meantime. One way to do this: instead of DO NOTHING use DO UPDATE, but do not actually update the row. The row is still locked:

INSERT INTO table1 AS t (id, value)
VALUES ($1, $2)
ON     CONFLICT (id) DO UPDATE  -- specify unique column(s) or constraint / index
SET    id = t.id WHERE false;   -- never executed, but locks the row

Obviously, if you can rule out concurrent transactions that might write to the same row in a conflicting manner, then the problem does not exist.

Detailed explanation:

edited Aug 16 '22 at 21:46

answered Jan 02 '17 at 18:48

Erwin Brandstetter

605,456
145
1,078
1,228

Could you give an example of where a race condition might arise in this case? To avoid race conditions, would I need to do something like in @GordonLinoff's answer (CTEs)? – user779159 Jan 02 '17 at 19:44
1

@user779159: Example: Transaction T1 might find that `id = 1` already exists and skip the insert. In the meanwhile, T2 might delete the same row (and commit) - before T1 runs the subsequent insert and update operations ... Writeable CTEs make the time window small, but they are *no* defense against this race condition on principal. The last link in my answer has more details and a *solution* for the problem. – Erwin Brandstetter Jan 02 '17 at 20:28
I've been looking over the "Update for Postgres 9.5+" section of the link from your answer (both the `sql` and `plpgsql` alternatives) but can't figure out how to adapt it for this particular case. Would you be able to update your answer here to include the elements from your solution in that other answer that protect against race conditions? – user779159 Jan 02 '17 at 21:33
1

@user779159: I added some more explanation, code and another link above. – Erwin Brandstetter Jan 03 '17 at 00:23
If I use your `ON CONFLICT DO UPDATE` suggestion to lock the row, what happens in the scenario in your comment above? If transaction T1 finds that `id = 1` exists, it skips the first insert. Meanwhile, T2 tries to delete the same row. Does T2's delete fail immediately, or does it just wait until T1 commits before continuing? (i.e. Waits until T1 does the `INSERT` and `UPDATE` statements within the `IF NOT FOUND THEN` block.) As you suggested I think it should be `IF FOUND THEN`, as I want the 2nd and 3rd statements to happen only if a new row was inserted in the 1st statement. – user779159 Jan 05 '17 at 11:12
1

@user779159: T2's `DELETE` waits until the row lock is released, which happens when T1 is committed or rolled back. Then T2 rechecks condition(s) (the row might have changed) and tries to delete again. If, for instance, T1 added a FK reference to the row in the meanwhile, the `DELETE` is restricted and an exception is raised that the row can't be deleted. T2 is rolled back unless you trap the error.. ***However***, since you clarified you only want to proceed if you actually inserted a row, this scenario does not apply. No obvious race condition after all in this case. – Erwin Brandstetter Jan 05 '17 at 15:47
In the previous case where I mistakenly thought I wanted to proceed if I did **not** insert the row, if I didn't use locking with `ON CONFLICT DO UPDATE` (but instead used `ON CONFLICT DO NOTHING`) what would happen here: transaction T1 finds that `id = 1` exists and skips the first insert. Meanwhile, T2 tries to delete the same row **and succeeds because it isn't locked**. Now T2's 2nd statement (the first `INSERT` within the `IF NOT FOUND THEN` block) would fail (assuming a foreign key constraint to the row deleted by T2) and T1 would roll back? If so, then I think I've finally understood :) – user779159 Jan 05 '17 at 16:29
If all that happens in this case is T1 rolls back and reports an error to the client, rather than leaving things in an inconsistent state, then this is a race condition that definitely isn't important. I'm more worried about things being inadvertently left in an inconsistent state. – user779159 Jan 05 '17 at 16:32
1

@user779159: Everything in your last two comments looks exactly right. As long as you enforce referential integrity with a FK constraint, Postgres does not allow an inconsistent state there in any case. – Erwin Brandstetter Jan 05 '17 at 16:55

score 3 · Answer 2 · answered Jan 02 '17 at 17:21

3

Postgres has the returning clause and CTEs to do what you want:

WITH t1 as (
      INSERT INTO table1 (id, value)
          VALUES (1, 'a')
          ON CONFLICT DO NOTHING
          RETURNING *
     ),
     t2 as (
      INSERT INTO table2 (table1_id, value) 
          SELECT id, value 
          FROM (SELECT 1 as id, 'a' as value) t
          WHERE NOT EXISTS (SELECT 1 FROM t1) 
   )
UPDATE table3
    set (table1_id, time) = (1, now())
    WHERE NOT EXISTS (SELECT 1 FROM t1);

The update looks strange because it updates all rows in table3.

answered Jan 02 '17 at 17:21

Gordon Linoff

1,242,037
58
646
786

On the first `INSERT`, could I do something like `RETURNING INTO` to put the inserted value into a variable in the function? And then in an `IF` statement check if that value exists, and if it does then run the next statements? – user779159 Jan 02 '17 at 17:26
1

Yes, that is the idea. However, instead of `if`, the statement uses `WHERE NOT EXISTS`. – Gordon Linoff Jan 02 '17 at 17:31
Thanks. Looks like there's another answer (by @MtwStark) that uses the `IF` option. As I'm new to CTEs and don't know much about them, is there any disadvantage to accomplishing it that way the way he suggested? Or does this case require CTEs to avoid some kind of problem for example a race condition? – user779159 Jan 02 '17 at 17:43
@user779159 . . . In either case, you can wrap the statements with an explicit transaction. When you are using the results from the `returning` (as opposed to checking if they are empty), this method is nice because you don't have to think about transactions and locking. – Gordon Linoff Jan 02 '17 at 18:29
@GordonLinoff: Actually, there is still a race condition and you still have to think about transactions and locking. CTEs cannot defend against the race condition. – Erwin Brandstetter Jan 03 '17 at 00:24
@ErwinBrandstetter . . . Thank you for the clarification. – Gordon Linoff Jan 03 '17 at 01:00

score 1 · Answer 3 · answered Jan 02 '17 at 17:39

Maybe you mean something like this?

INSERT INTO table1 (id, value) VALUES (1, 'a') ON CONFLICT DO NOTHING;
--- If the above statement didn't insert a new row
---   because id of 1 already existed, 
---   then run the following statements

affected_rows := SQL%ROWCOUNT;

IF affected_rows = 0 THEN
    INSERT INTO table2 (table1_id, value) VALUES (1, 'a');
    UPDATE table3 set (table1_id, time) = (1, now());
END IF

Abdel P. · Answer 4 · 2017-01-02T19:17:44.727

0

The easiest and reliable way is with the especial variable FOUND, this way:

INSERT INTO table1 (id, value) values (1, ‘a’) on conflict do nothing;

IF FOUND THEN

--success

ELSE

--failure

END IF;

Here is the documentation of diagnosing a statement https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html

edited Jan 02 '17 at 19:17

answered Jan 02 '17 at 18:52

Abdel P.

739
9
24

Run SQL statements in PL/pgSQL only if a row doesn't exist

4 Answers4

Race condition?

Linked