Databases
StackOverflow2013
Postsdbo
POIs this generic MERGE/UPSERT function for PostgreSQL safe?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs this generic MERGE/UPSERT function for PostgreSQL safe?
    primarykey
    data
    text
    <p>I have created a "merge" function which is supposed to execute either an UPDATE or an INSERT query, depending on existing data. Instead of writing an upsert-wrapper for each table (as in most of the available examples), this function takes entire SQL strings. Both of the SQL strings are automatically generated by our application.</p> <p>The plan is to call the function like this:</p> <pre><code>-- hypothetical "settings" table, with a primary key of (user_id, setting): SELECT merge( $$UPDATE settings SET value = 'x' WHERE user_id = 42 AND setting = 'foo'$$, $$INSERT INTO settings (user_id, setting, value) VALUES (42, 'foo', 'x')$$ ); </code></pre> <p>Here's the full code of the merge() function:</p> <pre><code>CREATE OR REPLACE FUNCTION merge (update_sql TEXT, insert_sql TEXT) RETURNS TEXT AS $func$ DECLARE max_iterations INTEGER := 10; i INTEGER := 0; num_updated INTEGER; BEGIN -- usually returns before re-entering the loop LOOP -- first try the update EXECUTE update_sql; GET DIAGNOSTICS num_updated = ROW_COUNT; IF num_updated &gt; 0 THEN RETURN 'UPDATE'; END IF; -- nothing was updated: try the insert, watching out for concurrent inserts BEGIN EXECUTE insert_sql; RETURN 'INSERT'; EXCEPTION WHEN unique_violation THEN -- nop; just loop and try again from the top END; -- emergency brake i := i + 1; IF i &gt;= max_iterations THEN RAISE EXCEPTION 'merge(): tried looping % times, giving up now.', i; EXIT; END IF; END LOOP; END; $func$ LANGUAGE plpgsql; </code></pre> <p>It appears to work well enough in my tests, but I'm not certain if I haven't missed anything crucial, especially regarding concurrent UPDATE/INSERT/DELETE queries, which may be issued without using this function. Did I overlook anything important?</p> <p>Among the resources I consulted for this function are:</p> <ul> <li><a href="http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING" rel="nofollow noreferrer">UPDATE/INSERT example 40.2 in the PostgreSQL manual</a></li> <li><a href="http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/" rel="nofollow noreferrer">Why is UPSERT so complicated?</a></li> <li><a href="https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql">SO: Insert, on duplicate update (postgresql)</a></li> </ul> <p>(Edit: one of the goals was to avoid locking the target table.)</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USZilk
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs this generic MERGE/UPSERT function for PostgreSQL safe?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs this generic MERGE/UPSERT function for PostgreSQL safe?
      1. USzero323
      1. VTFavorite
    3. VO
      singulars
      1. POIs this generic MERGE/UPSERT function for PostgreSQL safe?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload