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@xxxxxxxxx> 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@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 > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
