StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgresql function not handling duplicate records
text
Body
copied!<p><strong>Update</strong>: After extensive instrumenting of my code, I found a place where another developer inserts into this table without using the 'upsert' function. However, due to how the methods were grouped and the exception captured, the stack trace suggested that this function was in error, when, in fact, <em>not</em> calling the function was the error. The code below is fine (with the caveat added by Daniel).</p> <p>I have the following plpgsql function:</p> <pre><code>-- http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ CREATE OR REPLACE FUNCTION upsert_person_site( curr_site_id INTEGER, curr_person_id INTEGER, curr_job_title CHARACTER VARYING(128) ) RETURNS void as $$ BEGIN -- strictly speaking, running the update first is not needed and -- duplicate code, but exceptions are relatively expensive. -- Also, note that we refuse to update with a NULL job title because an -- import may simply fail to specify one. UPDATE person_site SET job_title = curr_job_title WHERE site_id = curr_site_id AND person_id = curr_person_id AND curr_job_title IS NOT NULL; IF FOUND THEN RETURN; END IF; BEGIN INSERT INTO person_site ( site_id, person_id, job_title ) VALUES ( curr_site_id, curr_person_id, curr_job_title ); EXCEPTION WHEN OTHERS THEN UPDATE person_site SET job_title = curr_job_title WHERE site_id = curr_site_id AND person_id = curr_person_id AND curr_job_title IS NOT NULL; END; RETURN; END; $$ language plpgsql; </code></pre> <p>The intent is to do an update if the record exists, or an insert if it does not. The intended logic is:</p> <pre><code>// duplicating the update to avoid trapping an expensive exception try to update the record if successful return try // I believe it's failing here insert a new record catch update an existing record </code></pre> <p>The <code>person_site</code> table has a <code>person_site_pkey</code> on the <code>person_id</code> and <code>site_id</code> fields. However, many times when this function is run, I get an exception stating <code>duplicate key value violates unique constraint "person_site_pkey" at ...</code>.</p> <p>Can someone help me understand what I'm missing? I thought the <code>EXCEPTION WHEN OTHERS</code> block would trap that. </p> <p>This is Postgresql 8.4.13 running on Debian Squeezebox. The application code is Perl and uses the <a href="http://search.cpan.org/dist/DBD-Pg/" rel="nofollow">DBD::Pg</a> module.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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