Postgresql function not handling duplicate records

Question

Update: 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, not calling the function was the error. The code below is fine (with the caveat added by Daniel).

I have the following plpgsql function:

-- 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;

The intent is to do an update if the record exists, or an insert if it does not. The intended logic is:

// 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

The person_site table has a person_site_pkey on the person_id and site_id fields. However, many times when this function is run, I get an exception stating duplicate key value violates unique constraint "person_site_pkey" at ....

Can someone help me understand what I'm missing? I thought the EXCEPTION WHEN OTHERS block would trap that.

This is Postgresql 8.4.13 running on Debian Squeezebox. The application code is Perl and uses the DBD::Pg module.

TLP: The error is at the Postgres level. I mentioned in the last sentence that I'm using the Perl DBD::Pg module on the unlikely chance that the error is there. — Ovid, Jul 22 '13 at 10:38

score 1 · Accepted Answer · answered Jul 22 '13 at 10:54

1

When curr_job_title is NULL, the code falls into the INSERT whether the person is already in the table or not.

Based on this comment:

-- Also, note that we refuse to update with a NULL job title because an
-- import may simply fail to specify one.

the test should better be implemented as:

IF curr_job_title IS NULL THEN
  return;
END IF;

at the beginning of the function before attempting the first UPDATE.

answered Jul 22 '13 at 10:54

Daniel Vérité

58,074
15
129
156

Thank you Daniel, that makes sense. But do you any idea why the BEGIN/EXCEPTION block isn't trapping the error? We're actually going to be doing away with the "NOT NULL" requirement and I'm concerned why the rest of the function appears to misbehave. – Ovid Jul 22 '13 at 11:06
No I can't see how the exception block would be skipped. But I can see other ways it could misbehave, such as the UPDATE missing existing rows due to the isolation mode. You may read http://stackoverflow.com/questions/17267417 as a recent revisit of upsert. Also does it fail on high concurrency or does it happen when run sequentially? – Daniel Vérité Jul 22 '13 at 11:40
See my update above. I gave you "best answer" anyway, because you were helpful. It turns out that a misleading stack trace led me astray. – Ovid Jul 22 '13 at 11:51

Postgresql function not handling duplicate records

1 Answers1