On Mon, Mar 31, 2014 at 03:28:14PM -0400, Ben Hoyt wrote: > , but I've just hit a case where two sessions each doing a > multiple insert don't use sequential IDs. For example, the range code above > for the first insert gave 2117552...2117829. And the second insert gave > 2117625...2117818. Which are obviously overlapping and is a nasty bug > waiting to happen. Thankfully it caused an IntegrityError further down in > my code so I didn't screw things up. Good thing you caught it. But yes, just from the description it looked like an obvious race to me. Concurrency is hard. > Second, what's the right thing to do here? The first thing I found was > PostgreSQL's "RETURNING" clause, but somewhat frustratingly for this use > case, even that's not guaranteed to return the results in the order you > specified. In SQL, _nothing_ is guaranteed to return in the order you specified. This isn't really a Postgres thing; unless you use ORDER BY, SQL's sets are not ordered. > I need the IDs in insertion order so I can do further processing. This sets off alarm bells for me. What further processing are you doing? Is it possible that you could move that into a single step in the database (maybe with a function or even a trigger) so that the result of your RETURNING really would provide you with what you need? > So currently I've changed my code to use RETURNING and then I'm ordering > the results based on a secondary column that I know the order of. This > works, but seems clunky, so I'm wondering if there's a nicer way. This is probably what I'd do, assuming that "further processing" isn't more data transformation. If it _is_, then I'd do the whole thing in a single step (in the database, once I inserted). A -- Andrew Sullivan ajs@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
