From: | Nelson Arape <narape(at)ica(dot)luz(dot)ve> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Batch with keygen? |
Date: | 2005-11-08 12:50:14 |
Message-ID: | 200511080850.14947.narape@ica.luz.ve |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2005-11-08 15:51:08 | Re: passing parameters to function |
Previous Message | Srivats | 2005-11-08 12:31:46 | passing parameters to function |