Hometowns get selected and possibly inserted in unpredictable ways even from multiple concurrent sessions. The only way I could figure out how to solve it was to force each INSERT hometowns to be in its own transaction.
On Mon, Jan 19, 2015 at 1:56 PM, Robert DiFalco <robert.difa...@gmail.com> wrote: > 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 >> > >
