Re: getGeneratedKeys method

A slight modification of (1) that I've used is "select
nextval('sequence_name'), nextval('sequence_name'),
nextval('sequence_name'), ...", to get the next n keys, then follow that
with a bunch of inserts that use those keys. This lets (1) play a
little more nicely with batch inserts (although (3) may be more useful).

mike

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
Sent: Wednesday, May 26, 2004 7:12 AM
To: Ceki Gulcu
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] getGeneratedKeys method

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match