A question - would serial type work as an auto-increment? I.e. is it
possible to run PreparedStatement like this (notice no PK in the
statement):
INSERT INTO ARTIST (NAME) VALUES (?)
And then get back DB-generated ARTIST_ID via
java.sql.Statement.getGeneratedKeys()? I suspect no, but if it is, it
would be a really nice improvement to PG adapter.
Andrus
On Aug 2, 2007, at 2:08 PM, Kevin Menard wrote:
I'd like to solicit feedback on PostgreSQL sequences, especially from
Tore. When I added the code to generate PKs from sequences, I think I
did so rather naively. I assumed sequences were sequences and
essentially lifted the code from the Oracle PK generator. This was
simple and easy to digest because it was so similar to Oracle.
Fast forward a couple years and now I've seen the use of the "serial"
type to create an int field, a sequence, and set up the default value.
There's nothing really magical about "serial" and it is very
similar to
what the PostgreSQL PK generator does now. What is different is the
name of the sequence. Serial types created sequences named in a
particular format, just as Cayenne does, but neither use the same
format. This turns out not to be merely cosmetic. The dynamic
language
frameworks with their built-in ORMs can only integrate with a
legacy DB
if the sequences are named appropriately (i.e., I've yet to see one
that
will allow overriding the sequence name as Cayenne does).
So, is it worth re-visiting the generator to create sequences named
the
same way that "serial" would? Should we simply modify the creation
DDL
to use the serial type (similar to MySQL)? Or should we consider it
someone else's problem and continue with a "non-standard", but
sound, PK
creation strategy?
--
Kevin Menard
Servprise International, Inc.
800.832.3823 x308
