Re: Simple Atomic Relationship Insert - Mailing list pgsql-general

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 out of the app and also to increase
performance by reducing the back-and-forth to the db.
http://omniti.com/seeds/writable-ctes-improve-performance



On Tue, Jan 13, 2015 at 4:21 PM, Robert DiFalco
<robert.difalco@gmail.com> wrote:
> 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 <john.archie.mckown@gmail.com>
> wrote:
>>
>> On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco
>> <robert.difalco@gmail.com> 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 (
>>>   INSERT INTO hometowns(name)
>>>     SELECT 'Portland'
>>>     WHERE NOT EXISTS (SELECT 1 FROM sel)
>>>   RETURNING id
>>> )
>>> INSERT INTO users(name, hometown_id)
>>>     VALUES ('Robert', SELECT id FROM ins UNION ALL SELECT id FROM sel);
>>>
>>>
>>
>> Oh, that is very clever. I've not see such a thing before. Thanks.
>>
>> I've added it to my stable of "tricks". Which aren't really tricks, just
>> really nice new methods to do something.
>>
>> The main advantage that I can see is that it is a single SQL statement to
>> send to the server. That makes it "self contained" so that it would be more
>> difficult for someone to accidentally mess it up. On the other hand, CTEs
>> are still a bit new (at least to me) and so the "why it works" might not be
>> very obvious to other programmers who might need to maintain the
>> application. To many this "lack of obviousness" is a detriment. To me, it
>> means "update your knowledge". But then, I am sometimes a arrogant BOFH. Add
>> that to my being an surly old curmudgeon, and you can end up with some bad
>> advice when in a "corporate" environment. The minus, at present, is that it
>> is "clever" and so may violate corporate coding standards due to
>> "complexity". Or maybe I just work for a staid company.
>>
>> --
>> While a transcendent vocabulary is laudable, one must be eternally careful
>> so that the calculated objective of communication does not become ensconced
>> in obscurity.  In other words, eschew obfuscation.
>>
>> 111,111,111 x 111,111,111 = 12,345,678,987,654,321
>>
>> Maranatha! <><
>> John McKown
>
>