RE: PostgreSQL sequences revisited

From: Kevin Menard (kmenar..ervprise.com)
Date: Thu Aug 02 2007 - 09:23:35 EDT

  • Next message: antonio-petrelli..ibero\.it: "Re: newbie maven junit question"

    I'll have to give it a try. The serial type sets up the default value
    for the field, which basically calls nextval() on the sequence. I don't
    know if the JDBC driver deals with that or not, though. If it does, we
    could do it with the existing PK generator and not bother with serial.
    Serial is more a shorthand than anything else. Of course, this
    precludes Cayenne from caching PKs, too.

    I'll try to fire up a short example this afternoon and see what I find
    though.

    -- 
    Kevin
    

    > -----Original Message----- > From: Andrus Adamchik [mailto:andru..bjectstyle.org] > Sent: Thursday, August 02, 2007 8:20 AM > To: de..ayenne.apache.org > Subject: Re: PostgreSQL sequences revisited > > 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 > > > >



    This archive was generated by hypermail 2.0.0 : Thu Aug 02 2007 - 09:24:09 EDT