Re: Batch with keygen?

Thanks everyone for all the ideas. I am going the route of stored procedures. This lets me keep my DB generated keys, while also greatly reducing the number of SQL round trips, which will improve performance, all without denormalizing the schema.

I was hoping to avoid using stored procedures because we support multiple different DBs so it means writing them on various different platforms. But it looks like this is the only real option.

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Nelson Arape
> Sent: Tuesday, November 08, 2005 4:50 AM
> To: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Batch with keygen?
>
> Maybe I am a bit off, but the old friends curval() and
> nextval() don't do the
> trick? I mean
>
> con.setAutoCommit(false);
> Statement stmt = con.createStatement();
> stmt.addBatch("INSERT INTO TABLEA " +
> "VALUES(nextval('TABLEA_PK_SEQ'), 1, 2, 3)");
> stmt.addBatch("INSERT INTO TABLEB " +
> "VALUES(curval('TABLEA_PK_SEQ'), 4, 5, 6)");
> ...
> int [] updateCounts = stmt.executeBatch();
>
> Bye
> Nelson Arapé
> PS: sorry for my English
>
> El Lun 07 Nov 2005 20:01, Dave Cramer escribió:
> > Additionally you should be aware, that using this mechanism once a
> > connection gets a hundred values, they are consumed even if
> you don't
> > use them.
> >
> > Dave
> >
> > On 7-Nov-05, at 4:18 PM, Kris Jurka wrote:
> > > On Mon, 7 Nov 2005, Mike Clements wrote:
> > >> I've got a bunch of PreparedStatements doing inserts on
> tables with
> > >> primary keys generated by sequences. For example, insert
> on table A,
> > >> take generated primary key, insert on table B assigning
> foreign key
> > >> generated value for A's primary key.
> > >>
> > >> For performance reasons, I need to batch these commands. But I
> > >> don't see
> > >> how it would be possible to continue using this approach with a
> > >> batch of
> > >> commands, because I need the results of the first insert
> to make the
> > >> second insert. Is there some way to do this or am I
> going to have to
> > >> stop using keygen and instead have my application
> generate its own
> > >> keys?
> > >
> > > One option would be to tune your sequence generator to your batch
> > > size, consider: CREATE SEQUENCE myseq INCREMENT BY 100; Then if
> > > you fetch a nextval you know that you are also free to
> use the next
> > > 99 values as well in your batch statement without touching the
> > > sequence.
> > >
> > > Kris Jurka
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 6: explain analyze is your friend
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's
> datatypes do not
> > match
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>