Re: Synthesize support for Statement.getGeneratedKeys()?
От | Dave Cramer |
---|---|
Тема | Re: Synthesize support for Statement.getGeneratedKeys()? |
Дата | |
Msg-id | EFC27EC0-3877-4F9C-AEDC-FDAD1D6C306F@fastcrypt.com обсуждение исходный текст |
Ответ на | Re: Synthesize support for Statement.getGeneratedKeys()? (Michael Paesold <mpaesold@gmx.at>) |
Ответы |
Re: Synthesize support for Statement.getGeneratedKeys()?
Re: Synthesize support for Statement.getGeneratedKeys()? |
Список | pgsql-jdbc |
On 22-Jan-07, at 4:16 AM, Michael Paesold wrote: > Ken Johanson wrote: >>>> As an aside, how do PG jdbc users get the server generated keys? >>>> Or does everyone use some kind of UUID system (which I think is >>>> generally regarded as detrimental to indexes/memory under high >>>> load and large DB sizes - compared to int/bigint)? Or do PG >>>> users using some standard or server-specific (RETURNING) SQL >>>> clause? >>> >>> either create the key ahead of time select nextval('sequence') >>> and insert it explicitly, or insert the row and then select >>> currval('sequence') >>> >> That makes sense; the sequence is retrieved and it internally >> increments - regardless of whether the key was actually inserted >> or not. I'm personally not used to this though, it allows for >> actual keys in the database to possibly have gaps (if the key want >> actually used / rollback etc). Thats trivial / innocuous I guess, >> but I'm just used to having sequential keys tables. Would this >> require two trips to the server, or can we handle in one >> excecuteUpdate? >> My real question is, what about the case where multiple VALUES are >> inserted; if I have 3 values should I call the sequence 3 times? >> What is the most efficient was to do that? (Can I do it in a >> single query?) > > I don't think you should use "currval" or "nextval" at all. A > general solution in the JDBC driver should even work in the case of > triggers that interfere with the value of a sequence. Or which > might change the value actually inserted into the table. Just think > of an insert trigger that uses a sequence for a second time. > > There is only one way to reliably get the database generated > values: the RETURNING clause. > > So my basic suggestion was to rewrite a query written as: > "INSERT INTO tab VALUES (...)" > into > "INSERT INTO tab VALUES (...) RETURNING x" > > With x being either (a) what the user specified using the Java API > (i.e. any column names) or (b) the primary key column(s) (or other > columns having a "DEFAULT currval(...)"). > The second case (b) I would leave for later, since it requires > parsing the query and finding the table which will be inserted > into. And you would have to use database meta data to find the > columns to return. > Yes, agreed, Ken was just curious how it is being done now. > Of course, there should be a minimum amount of parsing to detect if > the query is a valid INSERT query and does not already have a > different RETURNING clause. > > Another option would be to convince backend developers to add a way > to specify a "RETURNING clause" on the protocol level, i.e. without > having to change the query string. Yes, this would be the best solution. > Best Regards > Michael Paesold > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Dave CramerДата:
Сообщение: Re: Synthesize support for Statement.getGeneratedKeys()?
Следующее
От: Michael PaesoldДата:
Сообщение: Re: Synthesize support for Statement.getGeneratedKeys()?