Search Postgresql Archives

Re: Simple Atomic Relationship Insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco <robert.difalco@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux