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 ou
I don't think an advisory lock would remove the deadlock.
On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett
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
>>
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
wrote:
> I don't think an ad
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 a
Is there a way to force a new private transaction in a FUNCTION? That seems
like it would be a good solution here because I could simply do the insert
in the RAISE within its own private transaction. Then on the next iteration
of the loop (as long as I don't have repeatable reads) it should be pick
Robert DiFalco wrote:
> I must be doing something wrong because both of these approaches are giving
> me deadlock exceptions.
Deadlocks are to be expected if the INSERTs are batched within a single
transaction and there are several sessions doing this in parallel.
Given that there's an u
The code shown in the Doc (I think) will still give you deadlock in the
case where you have two sessions concurrently trying to insert the same
'hometown'. For example:
INSERT INTO users VALUES('Tom', select_hometown_id('Portland, OR'));
INSERT INTO users VALUES(''Waits', select_hometown_id('P
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 r
On 1/15/2015 6:12 PM, Robert DiFalco wrote:
FWIW I was using the select_hometown_id FUNCTION like this:
INSERT INTO users(...) values(..., select_hometown_id('Portland, OR'));
try this: (if you still get deadlocks, uncomment the advisory lock
[thanks Daniel] and try again)
Logically I suppos
On 1/14/15 8:28 AM, Daniel Verite wrote:
Roxanne Reid-Bennett wrote:
>When you have a sequence of steps that need to be serialized across
>processes, choose or even create a table to use for locking
This can also be done with an advisory lock, presumably faster:
http://www.postgresql.o
FWIW I was using the select_hometown_id FUNCTION like this:
INSERT INTO users(...) values(..., select_hometown_id('Portland, OR'));
On Tue, Jan 13, 2015 at 2:21 PM, Brian Dunavant wrote:
> The loop to run it twice handles that yes. I don't think that buys
> you anything over a more tradition
I must be doing something wrong because both of these approaches are giving
me deadlock exceptions.
On Tue, Jan 13, 2015 at 2:21 PM, Brian Dunavant wrote:
> The loop to run it twice handles that yes. I don't think that buys
> you anything over a more traditional non-cte method though. I'd run
Roxanne Reid-Bennett wrote:
> When you have a sequence of steps that need to be serialized across
> processes, choose or even create a table to use for locking
This can also be done with an advisory lock, presumably faster:
http://www.postgresql.org/docs/current/static/explicit-locking.ht
I don't like loops to catch "failure" condition... can you possibly fail
to stop?
In a stored procedure (or with auto-commit turned off in any
transaction)... You can avoid any race condition by using a semaphore
(e.g. you lock "something" for the duration of the critical part of your
process
John McKown wrote:
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
mailto:robert.difa...@gmail.com>>wrote:
Let's say I have two tables like this (I'm leaving stuff out for
simplicity):
CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
id INTE
Good points. I guess my feeling is that if there can be a race condition on
INSERT then the CTE version is not truly atomic, hence the LOOP.
On Tue, Jan 13, 2015 at 3:11 PM, Brian Dunavant wrote:
> A very good point, but it does not apply as here (and in my article)
> we are not using updates, o
A very good point, but it does not apply as here (and in my article)
we are not using updates, only insert and select.
On Tue, Jan 13, 2015 at 6:03 PM, Thomas Kellerer wrote:
> Brian Dunavant wrote on 13.01.2015 22:33:
>>
>> What issue are you having? I'd imagine you have a race condition on
>
Brian Dunavant wrote on 13.01.2015 22:33:
What issue are you having? I'd imagine you have a race condition on
the insert into hometowns, but you'd have that same race condition in
your app code using a more traditional 3 query version as well.
I often use CTEs like this to make things atomic.
The loop to run it twice handles that yes. I don't think that buys
you anything over a more traditional non-cte method though. I'd run
them a few thousand times to see if there's any difference in runtimes
but my guess is the CTE version would be slightly slower here.
v_id integer;
BEGIN;
sele
This seems to get rid of the INSERT race condition.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR)
RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN
LOOP
BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
With the single CTE I don't believe you can do a full upsert loop. If
you're doing this inside of a postgres function, your changes are
already atomic, so I don't believe by switching you are buying
yourself much (if anything) by using a CTE query instead of something
more traditional here.
The a
Well, traditionally I would create a LOOP where I tried the SELECT, if
there was nothing I did the INSERT, if that raised an exception I would
repeat the LOOP.
What's the best way to do it with the CTE? Currently I have the following
which gives me Duplicate Key Exceptions when two sessions try to
What issue are you having? I'd imagine you have a race condition on
the insert into hometowns, but you'd have that same race condition in
your app code using a more traditional 3 query version as well.
I often use CTEs like this to make things atomic. It allows me to
remove transactional code ou
This CTE approach doesn't appear to play well with multiple concurrent
transactions/connections.
On Tue, Jan 13, 2015 at 10:05 AM, John McKown
wrote:
> On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco > wrote:
>
>> Thanks John. I've been seeing a lot of examples like this lately. Does
>> the fo
On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco
wrote:
> Thanks John. I've been seeing a lot of examples like this lately. Does the
> following approach have any advantages over traditional approaches?
>
>
> WITH sel AS (
> SELECT id FROM hometowns WHERE name = 'Portland'
> ), ins AS (
>
Thanks John. I've been seeing a lot of examples like this lately. Does the
following approach have any advantages over traditional approaches?
WITH sel AS (
SELECT id FROM hometowns WHERE name = 'Portland'
), ins AS (
INSERT INTO hometowns(name)
SELECT 'Portland'
WHERE NOT EXISTS (SE
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
wrote:
> Let's say I have two tables like this (I'm leaving stuff out for
> simplicity):
>
> CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE hometowns (
> id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
> name VARCHAR,
>
27 matches
Mail list logo
