Re: getGeneratedKeys method - Mailing list pgsql-jdbc

Ceki Gulcu wrote:
> 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.

Note that as it stands this isn't true for the postgresql driver,
executeBatch() actually just runs each query synchronously. Fixing this
is at the top of my todo list..

> 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?

It'd probably be possible to implement support for returning the OID of
inserted rows in a batch update. You can already get at that OID for
individual inserts via a postgresql-specific interface,
PGStatement.getLastOID(). However, the OID doesn't make a great key
anyway, and I think the general move is towards omitting OIDs from
tables altogether.

Anything else is going to be harder as the server does not provide much
in the way of metadata back from a query, and the JDBC driver does
little parsing of the SQL queries.

If you don't mind doing DB-specific implementations, and you have all of
the data for a particular event available at once, you might want to
look at using CREATE SEQUENCE for the event ID (I guess you're already
doing this if you want autogenerated keys) and use something like:

   INSERT INTO logging_event(nextval('id'), ...)
   INSERT INTO logging_event_property(currval('id'), "property 1", ...)
   INSERT INTO logging_event_property(currval('id'), "property 2", ...)
   INSERT INTO logging_event_exception(currval('id'), 'line 1", ...)
   INSERT INTO logging_event_exception(currval('id'), "line 2", ...)

You'd be able to batch those INSERTs so long as you keep them grouped
correctly.

This is safe to do even if multiple connections are using the sequence
concurrently -- see
http://www.postgresql.org/docs/7.4/static/functions-sequence.html.

-O