On 6/1/06, Toby Thain <[EMAIL PROTECTED]> wrote:
> > OT from the original post, but pertinent to the discussion...
> >
> > On 5/27/06, Markus Hoenicka <[EMAIL PROTECTED]> wrote:
> > > Martin Kutschker writes:
> > > > Have the systems which use sequences some nameing schemes,
> > which build the sequence
> > name from the table name?
> > > >
> > >
> > > SQLite and MySQL don't use sequences, so I can speak only of
> > > PostgreSQL (don't know about Oracle, Sybase et al.). If you use the
> >
> > Oracle does use sequences, and they look quite a bit like Pg
> > sequences
> > but require a little more work to use AFAICT.
> >
> > Sybase (well, the MSSQL version) has a "magical" column property that
> > feels more like MySQL's autoincrement.
> >
> > http://troels.arvin.dk/db/rdbms/#mix-identity
>
> (An aside) Ingres uses sequence objects in the usual way. But there
> is a gotcha. The driver I have just implemented enables 'autocommit'
> mode by default, where every query is committed as a transaction (may
> be a necessary restriction until libdbi offers 'commit' and
> 'rollback' functions). This has an interesting implication for
> sequences. In autocommit mode, you cannot get the current value, a
> rule strictly enforced by the DBMS so that independent transactions
> using the same sequence cannot conflict. So, the way to track INSERT
> ids is to get the 'NEXT VALUE' for the sequence (in one transaction/
> query), then use that value in a later INSERT.
>
> >
> > > Serial type in PostgreSQL, a sequence is automatically created
> > with a
> > > predictable name (tablename_colname_seq). However, you can do this
> > > just as well:
> > >
> > > CREATE SEQUENCE whatever_seq;
> > > CREATE TABLE tablename (
> > > colname integer DEFAULT nextval('whatever_seq') NOT NULL
> > >
> > > A mechanism that relies on a particular naming scheme of sequences
> > > would fail in the second case.
> >
> > While it would take a little work in the Pg libdbi driver to detect
> > the version (if that's not already done), 8.1 has a function called
> > lastval() that returns the last value produced by the last sequence
> > used. This falls down if you use more than one sequence on a table
> > (say, as a default on a non-SERIAL column, as I do in some cases),
> > but
> > it works well for the common case. The driver could use the sequence
> > name if provided, otherwise, under 8.1, it would 'SELECT lastval()'
> > and return the expected value in either case.
>
> How does this interact with transactions, out of curiosity?
No time for more comments, but transactions and multiple connections
do not effect the atomicity of sequences in Pg. Each session
(connection) requests the next value from a sequence on demand, and
the value received by that connection is retained and returned by that
lastval() (and "select currval('seqname')") functions.
>
> >
> > Applications would have to take care to be coded to Pg 8.1+ in order
> > to use the "magic" sequence stuff, but it's more portably future
> > proof, IMHO. Of course, older apps that provide a sequence name
> > would
> > continue to function as they do now.
> >
> > >
> > > > And perhaps dbi couild handle the problem that Mysql doesn't
> > store the id by table or
> > sequence but by connection by automatically calling
> > mysql_last_insert_id() on every insert
> > and storing the ids in a dbi datastructure per table.
> > > >
> > >
> > > I don't see a real problem here. If an application makes sure to
> > store
> > > relevant id values after INSERT commands, you'll get a portable
> > code
> > > without causing dbi to interfere with tables behind the users back.
>
>
> The issue is already solved for MySQL; dbi_conn_sequence_last() calls
> mysql_last_insert_id(), which works as expected.
> dbi_conn_sequence_next() does nothing with MySQL (this "should be"
> discussed in the driver documentation).
>
> Furthermore, the association of last-insert-id with a connection gets
> around the transaction issue described above, though of course you
> can get 'holes' in your sequence numbering, a point discussed in the
> MySQL manual:
> - http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-
> column.html
> - http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
>
> The moral of this story is it's pretty difficult to get a *portable*
> usage pattern for incrementing INSERT ids, but each database tends to
> have a successful workaround.
>
> --Toby
>
> > >
> > > regards,
> > > Markus
> > >
> > > --
> > > Markus Hoenicka
> > > [EMAIL PROTECTED]
> > > (Spam-protected email: replace the quadrupeds with "mhoenicka")
> > > http://www.mhoenicka.de
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > All the advantages of Linux Managed Hosting--Without the Cost
> > and Risk!
> > > Fully trained technicians. The highest number of Red Hat
> > certifications in
> > > the hosting industry. Fanatical Support. Click to learn more
> > > http://sel.as-us.falkag.net/sel?
> > cmd=lnk&kid=107521&bid=248729&dat=121642
> > > _______________________________________________
> > > libdbi-users mailing list
> > > [EMAIL PROTECTED]
> > > https://lists.sourceforge.net/lists/listinfo/libdbi-users
> > >
> >
> >
> > --
> > Mike Rylander
> > [EMAIL PROTECTED]
> > GPLS -- PINES Development
> > Database Developer
> > http://open-ils.org
> >
>
>
> _______________________________________________
> libdbi-users mailing list
> libdbi-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/libdbi-users
>
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
