Re: Simple Atomic Relationship Insert

On 1/15/15 10:57 PM, Roxanne Reid-Bennett wrote:
>
> try this:  (if you still get deadlocks, uncomment the advisory lock [thanks Daniel] and try again)
> Logically I suppose it might run faster to do the select, then insert "if".  I almost always write these as insert
first- because it's the more restrictive lock. 
>
> CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS
> INTEGER AS
> $BODY$
> DECLARE
>      v_id integer;
> BEGIN
> --        perform pg_advisory_xact_lock(hashtext(hometown_name));
>        BEGIN
>          insert into hometowns (name)
>          select hometown_name where not exists (select id from hometowns where name = hometown_name)
>          returning id into v_id;

That has a race condition. The only safe way to do this (outside of SSI) is using the example code at
http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com