Re: getGeneratedKeys method - Mailing list pgsql-jdbc

Ceki,

Yes, this is correct, there is no functionality in postgres to get the
generated keys.

This is because the keys are generated using a sequence which can be any
of the columns in the row, there is no apriori knowledge of the column
that is being generated.

There are three ways around this.

1) get the key before the insert. select nextval('sequence_name');

2) get the key after the insert leaving the key value out of the insert,
or inserting a null there. select currval('sequence_name');

3) do an insert and a select at the same time ie.
insert into foo ....; select curval('sequence name')

FYI, nextval and currval are valid in the context, so you don't have to
worry about other connections getting in the way. These functions do the
"right" thing.



On Wed, 2004-05-26 at 07:56, Ceki Gulcu wrote:
> Hello,
>
> We, the log4j developers, are working on an appender
> that will write
> log4j events to various RDBMS Various fields of an
> event object will
> go to 3 different tables, namely the logging_event
> table, the
> logging_event_property table and the
> logging_event_exception
> table. Each entry in the logging_event_property and
> the
> logging_event_exception tables uses a reference to
> event_id, where
> event_id is a database generated primary key in the
> logging_event
> table.
>
> For entries in the logging_event_property and
> logging_event_exception
> tables to be meaningful, we absolutely need the
> generated event_id
> each time we insert a new logging_event row. We are
> able to do this by
> following each logging_event insertion with a query to
> the database
> asking for the event_id of the last inserted event.
> This works for
> multiple database systems.
>
> However, we have discovered that batching multiple
> insertions gives a
> very significant boost in performance. Thus, we would
> like to insert
> say 50 logging_event rows, then multiple
> logging_event_property rows
> and then multiple logging_event_exception rows. We are
> using the
> JDBC getGeneratedKeys method to obtain the event_ids.
> Unfortunately, this
> function is not implemented in Postgresql.
>
>
> Looking at the archives it seems that this
> functionality requires
> support for the database back end which does not exist
> currently. Is
> that correct?
>
> Is there another way to insert into multiple tables in
> batches as
> described above without using the JDBC
> getGeneratedKeys  method?
>
> Your views on the matter would be highly appreciated.
> Thanking you
> in advance,
--
Dave Cramer
519 939 0336
ICQ # 14675561