On 1/19/2015 4:58 PM, Robert DiFalco wrote:
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.
So you fixed it - good. In our situation we were never inserting more
than one hometown** record in the same transaction - so I see now that
my scenario was not the same as yours - but yours evolved to that.
(**using "hometown" for our record type)
On Mon, Jan 19, 2015 at 1:56 PM, Robert DiFalco
<robert.difa...@gmail.com <mailto:robert.difa...@gmail.com>> wrote:
I don't think an advisory lock would remove the deadlock.
For 2 or more hometown inserts per transaction - I agree - it won't fix it.
Glad to hear you have it fixed.
Roxanne
On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett
<r...@tara-lu.com <mailto: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
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
[At other schools] I think the most common fault in general is to teach
students how to pass exams instead of teaching them the science.
Donald Knuth
