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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
