Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made? - Mailing list pgsql-general

From Dave Cramer
Subject Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?
Date
Msg-id CADK3HH+Uvw3=_diiVMYbT83tGzH9V+cPTqPkqfm1Coxaz73rWw@mail.gmail.com
Whole thread Raw
In response to Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?  (electrotype <electrotype@gmail.com>)
List pgsql-general


On Wed, 9 Dec 2020 at 10:21, electrotype <electrotype@gmail.com> wrote:
So I'm curious. Why does order matter ?

Dave Cramer


When you have to save multiple new entities with subentities.

You first save all the parent entities in a single SQL batch insert, you get the generated ids, then insert all the subentities in another single SQL batch insert. To know which "parent id" to use for a given subentity of the second query, you need a way to associate a generated id with the correct parent entity. The order of the parents in their batch, and the order of the generated ids, is the only straighforward way.

I know all this could be made into a single SQL query, without having to associate the generated ids to the parents manually. But sometimes you have to fight really hard agains your framework or JDBC itself to write such more complex query, where two batch inserts are very natural.



Fair enough, however the spec does not say anything about the order. In fact it doesn't even say which keys will be returned. 

I don't think we can make any guarantees here.

Dave

pgsql-general by date:

Previous
From: Niels Jespersen
Date:
Subject: SV: Tools showing table partitions as tables in listings of tables
Next
From: "David G. Johnston"
Date:
Subject: Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?