I don't think an advisory lock would remove the deadlock. On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett <r...@tara-lu.com> wrote:
> On 1/16/2015 2:41 AM, Jim Nasby wrote: > >> 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 >> > > And if the advisory lock is used? That presumably creates an exclusive > lock on the asset "hometown_name". [in most examples given "Portland, > OR".] Would not any other process that runs (this function) on the same > asset have to wait for this specific transaction to commit or roll back - > blocking the race condition? > > Roxanne > (sorry, I was out of town) > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
