Thread: JDBC problem
Hello I'm using the Postgresql 6.5 and i try to perform statement INSERT into KEY_ITEM (key_item_id,keyName) values(((SELECT MAX(key_item_id) FROM KEY_ITEM)+1),?) and I get the message : Error: ERROR: parser: parse error at or near "select" I'm not sure but part "(SELECT MAX(key_item_id) FROM KEY_ITEM)+1)" of statement does't work thanks for all informations Bogdan Kromski ps. on Oracle and Sybase it is working correct
The syntax that I've seen is: insert into KEY_ITEM (key_item_id,keyname) values (select max(key_item_id)+1,'?' FROM KEY_ITEM); Just out of curiousity, why not make key_item_id a SERIAL field? That way, if you don't specify a value, it gets the next value from the associated sequence. On Tue, 22 Aug 2000, BogdanKRomski wrote: > Hello > > I'm using the Postgresql 6.5 and i try to perform statement > > INSERT into KEY_ITEM (key_item_id,keyName) values(((SELECT > MAX(key_item_id) FROM KEY_ITEM)+1),?) > > and I get the message : > > Error: ERROR: parser: parse error at or near "select" > > I'm not sure but part "(SELECT MAX(key_item_id) FROM KEY_ITEM)+1)" of > statement does't work > > thanks for all informations > > Bogdan Kromski > > ps. on Oracle and Sybase it is working correct >
The parser error is from the backend not JDBC (as it doesnt parse anything other than PreparedStatement's). I think it's the sub-select that's causing the problem, as it's not supported in 6.5. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@it.maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: BogdanKRomski [mailto:b.kromski@gaschka.pl] Sent: Tuesday, August 22, 2000 12:47 PM To: pgsql-interfaces@postgresql.org Subject: [INTERFACES] JDBC problem Hello I'm using the Postgresql 6.5 and i try to perform statement INSERT into KEY_ITEM (key_item_id,keyName) values(((SELECT MAX(key_item_id) FROM KEY_ITEM)+1),?) and I get the message : Error: ERROR: parser: parse error at or near "select" I'm not sure but part "(SELECT MAX(key_item_id) FROM KEY_ITEM)+1)" of statement does't work thanks for all informations Bogdan Kromski ps. on Oracle and Sybase it is working correct
On Thu, 24 Aug 2000, John McKown wrote: > The syntax that I've seen is: > > insert into KEY_ITEM (key_item_id,keyname) values > (select max(key_item_id)+1,'?' FROM KEY_ITEM); > > Just out of curiousity, why not make key_item_id a SERIAL field? That way, > if you don't specify a value, it gets the next value from the associated > sequence. I was going to ask you to explain SERIAL but the documentation makes it fairly clear.. basically just the next value, yes? Simply setting the field type to SERIAL seems to be the same as the MS Access 'autonumber' field. Is this correct? One detail that seems to have an advantage over the MAX+1 method is that in the case of adding a new record, deleting it, then adding another new record the ID will not be reused (correct?). Question: What happens when transactions enter the picture? Will you get duplicate values, or holes, or does it work just fine? -Cedar
On Fri, Aug 25, 2000 at 11:49:32AM +0300, Cedar Cox wrote:
>
>
> One detail that seems to have an advantage over the MAX+1 method is that
> in the case of adding a new record, deleting it, then adding another new
> record the ID will not be reused (correct?).
>
> Question: What happens when transactions enter the picture? Will you get
> duplicate values, or holes, or does it work just fine?
Well, in my opinion it works just fine, but that does mean you get holes.
This has been discussed do death over on GENERAL a number of times. In
order to avoid locking on the serial seequence generator, multiple
transacations each get to increment the counter, but do not roll it
back on transaction failure. And each connection maintains it's own
state of the underlying sequence, so that currval('table_field_seq')
is multiuser safe.
And as you mention above, not reusing numbers also leads to holes, so
your app needs to be able to handle that, anyway.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
On Fri, 25 Aug 2000, Cedar Cox wrote: > > > On Thu, 24 Aug 2000, John McKown wrote: > > > The syntax that I've seen is: > > > > insert into KEY_ITEM (key_item_id,keyname) values > > (select max(key_item_id)+1,'?' FROM KEY_ITEM); > > > > Just out of curiousity, why not make key_item_id a SERIAL field? That way, > > if you don't specify a value, it gets the next value from the associated > > sequence. > > I was going to ask you to explain SERIAL but the documentation makes it > fairly clear.. basically just the next value, yes? Simply setting the > field type to SERIAL seems to be the same as the MS Access 'autonumber' > field. Is this correct? I'm not familiar with MS Access, but I'll bet that it is very similiar to 'autonumber'. However, a sequence can be shared by multple tables / fields. This means that a single table/field could have "holes" in the sequence number. > > One detail that seems to have an advantage over the MAX+1 method is that > in the case of adding a new record, deleting it, then adding another new > record the ID will not be reused (correct?). Correct. The sequence is independant of the table and the value only increases. > > Question: What happens when transactions enter the picture? Will you get > duplicate values, or holes, or does it work just fine? A good question to which I don't know the answer. However, after thinking about it a bit, I would guess that the sequence is "locked" for the duration of the transaction. This would imply that all other transactions which needed to access the sequence would be stalled until this transaction ended. When the transaction ended, then sequence would either have the highest number assigned so far, or would be rolled back to the same value it had when the transaction started. The above is just my guess. If the sequence is not "locked", then I would guess that an aborted transaction would cause the sequence to end up with holes. Actually, multiple tables and fields can share a sequence (why would anybody do this?). This means that a single table could have "holes" in the sequence. Under no circumstances would I expect duplicates. > > -Cedar > >
On Fri, 25 Aug 2000, John McKown wrote: [talking about sequences] > > Question: What happens when transactions enter the picture? Will you get > > duplicate values, or holes, or does it work just fine? > > A good question to which I don't know the answer. > > However, after thinking about it a bit, I would guess that the sequence is > "locked" for the duration of the transaction. This would imply that all > other transactions which needed to access the sequence would be stalled > until this transaction ended. When the transaction ended, then sequence > would either have the highest number assigned so far, or would be rolled > back to the same value it had when the transaction started. > > The above is just my guess. If the sequence is not "locked", then I would > guess that an aborted transaction would cause the sequence to end up with > holes. Actually, multiple tables and fields can share a sequence (why > would anybody do this?). I'm not sure either, but I'm sure it could be used for something interesting.. > This means that a single table could have "holes" in the sequence. > > Under no circumstances would I expect duplicates. This 'locking' thing was what I was wondering about. If 'sequence locking' does actually take place, I don't like the idea of other transactions having to wait but it seems necessary. On the other hand, holes in a sequence shouldn't be an issue for us so 'no locking' would be just fine. Can someone confirm? I just want to make sure I have a correct understanding of how it works.. Thanks -Cedar
Today, in a message to John McKown, Cedar Cox wrote: > > On Fri, 25 Aug 2000, John McKown wrote: > > > > However, after thinking about it a bit, I would guess that the sequence is > > "locked" for the duration of the transaction. This would imply that all > > other transactions which needed to access the sequence would be stalled > > until this transaction ended. No, the sequence does not remain locked for the duration of the transaction. If that was the case sequences would be nearly useless for most applications. > > The above is just my guess. It is always dangerous to guess with these things. :-) > This 'locking' thing was what I was wondering about. If 'sequence > locking' does actually take place, It doesn't. > On the other hand, holes in a sequence shouldn't be an issue for us so > 'no locking' would be just fine. The purpose of these sequences is to generate unique keys, therefore, holes don't matter. Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)
On Sun, 27 Aug 2000, Joachim Achtzehnter wrote: > > On the other hand, holes in a sequence shouldn't be an issue for us so > > 'no locking' would be just fine. > > The purpose of these sequences is to generate unique keys, therefore, > holes don't matter. Exactly. Thanks for the input.. -Cedar
Serial actually allocates numbers in blocks (default=1), so you won't get a duplicate. The plus side of this is that you can set the block size >1, so if you are loading lots of data, you can set it to 10, then it only allocates a new block once the last one is used up. However, if your connection is closed before the block is used, the unused numbers are lost. Peter PS: Major email problems here at mbc, so I may not be able to reply to any directly written replies for a while (I accidentally erased all the addresses, whoops)... -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@it.maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Cedar Cox [mailto:cedarc@visionforisrael.com] Sent: Friday, August 25, 2000 9:50 AM To: John McKown Cc: BogdanKRomski; pgsql-interfaces@postgresql.org Subject: Re: [INTERFACES] JDBC problem On Thu, 24 Aug 2000, John McKown wrote: > The syntax that I've seen is: > > insert into KEY_ITEM (key_item_id,keyname) values > (select max(key_item_id)+1,'?' FROM KEY_ITEM); > > Just out of curiousity, why not make key_item_id a SERIAL field? That way, > if you don't specify a value, it gets the next value from the associated > sequence. I was going to ask you to explain SERIAL but the documentation makes it fairly clear.. basically just the next value, yes? Simply setting the field type to SERIAL seems to be the same as the MS Access 'autonumber' field. Is this correct? One detail that seems to have an advantage over the MAX+1 method is that in the case of adding a new record, deleting it, then adding another new record the ID will not be reused (correct?). Question: What happens when transactions enter the picture? Will you get duplicate values, or holes, or does it work just fine? -Cedar