>In table two you would not create a serial instead you would create an
>INTEGER because serial is a counter and the values in table 2 may not be
>in table 1.
>Use a transaction like as follows
>INSERT INTO TABLE1 VALUES (Whatever values);
>var = SELECT CURRVAL('sequence_name');
>INSERT INTO TABLE2 VALUES (var,whatever else);
But this is the race condition I am trying to avoid. Someone can
insert before I get the currval and it will beincremented and this
will result in invalid data. Right now, I'm doing exactly that but I
add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
>So you would start the transaction then insert into the first table.
>You would then get the current value of the sequence that the first table
>You would then insert this value into table2 along with anything else
>On Tue, 26 Mar 2002, power2themacs wrote:
>> I am using 7.2 and JDBC. I have a very simple situation where I
>> insert an item with a primary key which is a SERIAL. In the same
>> transaction I need to insert a reference in a separate table to this
>> item. Of course, race conditions could occur if I didn't use this
>> special isolation level. But is there no way in which I could use
>> row-level locking instead? Certainly, I am not updating the table I
>> just inserted to, so the FOR UPDATE would never unlock. Is the
>> isolation level my only option? I noticed that psql displays the oid
>> after an INSERT. That would be exactly what I need but JDBC doesn't
>> seem to offer this.
>> (Right now I set the isolation level and just get the SERIAL's
>> |---table1----------------| |---table2-----------------|
>> | id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |
>> Short version:
>> I just inserted into table1 and need these in the same transaction.
>> How can I get the id I just inserted into table2? Thanks folks.
>> Do You Yahoo!?
>> Get your free @yahoo.com address at http://mail.yahoo.com
>> ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
In response to
pgsql-general by date
|Next:||From: Doug McNaught||Date: 2002-03-26 19:58:47|
|Subject: Re: ISOLATION LEVEL SERIALIZABLE|
|Previous:||From: Sonia Sanchez Diaz||Date: 2002-03-26 19:38:22|
|Subject: Re: Performance in subconsult|