Re: Equivalent for AUTOINCREMENT?

Michelle Konzack wrote:
> Hello Grzegorz,
>
> Am 2008-10-31 13:39:46, schrieb Grzegorz Ja??kiewicz:
>> AUTOINCREMENT has so many problems, that soon you'll start to love sequences
>> so much, you'll start to hate mysql's childlish approach to problem solving
>> :)
>
> OK, you hit me, I am trying to convert a mysql scheme to postgresql...
>
> Realy, I hate programs which relay on ONE database only and can not
> easyly adapt to use another one...

There are times when this can be appropriate, mostly when your app is
complex enough that you have to worry about a lot more than SQL dialect
differences.

For example, if you're thinking about concurrency issues you'll find
differences between databases in transaction isolation, visibility,
locking, handling of deadlocks, etc. You'll potentially be able to use
the database much more efficiently, reliably, and smoothly if you design
to one particular RDBMS's concurrency features and behaviour, rather
than trying to make it universal. In fact, making it universal may end
up being the same thing as serializing absolutely everything. This isn't
generally something you can just hide behind a database abstraction
layer unless you're prepared for miserable performance and ugly, ugly
ways of doing things.

If you want to impose strong data intregrity checking as part of your
schema, and handle violations of those checks cleanly in your
application, then you'll probably be doing database specific things
there too.

Use of stored procedures / functions or updateable views to minimize
round trips, improve access control, provide a consistent and stable
public interface for the database, etc will usually force the use of
database specific features. At least in this case the app interface
should be similar enough that you can probably abstract it.

Sometimes there are also database features that're just so compelling
that you'll save yourself vast amounts of development time (and thus
produce a better app due to spending that time on other things) by using
them. I've made use of PostgreSQL's advisory locks to solve problems
that'd otherwise require inter-client communication by side channels or
the use of an application server, for example.

The main app I'm working on at present (not the awful Access based one
I'm having to do) would probably be portable to Oracle with a bit of
work. Porting it to MySQL, even new versions, or worse to SQLite would
be absurd to even try. The benefit of using powerful database features
and designing around MVCC has been significantly quicker development
than would've been possible had I been forced to attempt to be
database-agnostic, as well as very strong data integrity enforcement,
good error handling & recovery, etc.

So ... targeting a specific database isn't all bad, so long as you think
carefully about it, understand the risks vs benefits, and don't use
non-portable or database-specific features just because you can. It
helps if the database you've targeted is highly portable (to avoid
platform lock-in), open source (so there's little risk of vendor
collapse or massive price hikes), and has a number of support options
out there if you need them. That's a large part of why I decided to
target PostgreSQL specifically, though the fact that it's powerful,
stable, fast and has a great community also made a big difference.

--
Craig Ringer