Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style.
The 'correct' way to do race free upsert is to take a table lock first -- you don't have to loop or open a subtransaction. A high concurrency version is nice but is more of a special case solution (it looks like concurrent MERGE might render the issue moot anyways). merlin
Index: doc/src/sgml/plpgsql.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.156 diff -c -6 -r1.156 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 29 Jul 2010 19:34:40 -0000 1.156 --- doc/src/sgml/plpgsql.sgml 5 Aug 2010 17:34:54 -0000 *************** *** 2332,2382 **** linkend="errcodes-table"> for a list of possible error codes). The <varname>SQLERRM</varname> variable contains the error message associated with the exception. These variables are undefined outside exception handlers. </para> - <example id="plpgsql-upsert-example"> - <title>Exceptions with <command>UPDATE</>/<command>INSERT</></title> - <para> - - This example uses exception handling to perform either - <command>UPDATE</> or <command>INSERT</>, as appropriate: - - <programlisting> - CREATE TABLE db (a INT PRIMARY KEY, b TEXT); - - CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS - $$ - BEGIN - LOOP - -- first try to update the key - UPDATE db SET b = data WHERE a = key; - IF found THEN - RETURN; - END IF; - -- not there, so try to insert the key - -- if someone else inserts the same key concurrently, - -- we could get a unique-key failure - BEGIN - INSERT INTO db(a,b) VALUES (key, data); - RETURN; - EXCEPTION WHEN unique_violation THEN - -- do nothing, and loop to try the UPDATE again - END; - END LOOP; - END; - $$ - LANGUAGE plpgsql; - - SELECT merge_db(1, 'david'); - SELECT merge_db(1, 'dennis'); - </programlisting> - - </para> - </example> </sect2> </sect1> <sect1 id="plpgsql-cursors"> <title>Cursors</title> --- 2332,2343 ----
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers