Re: Equivalent for AUTOINCREMENT?

Hi again,

Am 2008-11-05 20:13:40, schrieb Craig Ringer:
> Michelle Konzack wrote:
> >OK, you hit me, I am trying to convert a mysql scheme to postgresql...

OK, in the same time I am trying to make programs like os-commerce
PostgreSQL usable

> >Realy, I hate programs which relay on ONE database only and can not
> >easyly adapt to use another one...

> 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.

My major problem is, that I am using PostgreSQL since I think 6.4 (it
was March 1999) and my databases are between 20 MBytes and several
TBytes and some of my hosting providers (I have 8 worldwide) use ONLY
MySQL and want install PostgreSQL.

And no, I do not realy use very High-Complex Database operation which
can not ported to anoter databases.

The ONLY real option would be, go to a HostingProvider which VERY good
and reliabel Internet connectivity and install there my WHOLE PostgreSQL
database and let my other websites access them over the internet...

This would solv all of my MySQL/PostgreSQL problems...

But I have not found a singel Hosting-Provider which is willing to host
a couple of Databases for which I need 6 TByte of diskspace...

Even a smaller one (used by my own website and onlinestore) which will
have arround 1-2 GByte give me already trouble. Also I have data from
over 140.000 worldwide customers in it.

A dedicated Root-Server with 300 GByte SAS in Raid-1 without Hotfix cost
alread 150 Euro per month. I have at home a SCSI dino of fiveteen
300 GB drives (->Raid-5 with 3600 GByte) and the most inexpensive Option
would be a 19"/42U closet in Nürnberg at Hetzner) which then cost
99€/month plus traffic and electricity.

Can you recommend to host the database on a seperated machine in the
Internet at another ISP?

> 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.

This why I have asked in another thread on <lists.php.net> how to make
an "API" for my programs which then can be adapted to the specific DB.

Since I have no need for complex operation I have created PHP includes
like

/usr/share/tdphp-vserver/01_database_pgsql.inc
/usr/share/tdphp-vserver/01_database_mysql.inc
/usr/share/tdphp-vserver/01_database_oracle.inc

which have the neccesary functions defined. However, not all functions
are universell used in my php5 scripts, which mean, that I have
sometimes 4 or 5 different UPDATE functions...

...and of course, have to comment it since otherwise in 4 weeks I would
not more know WHAT I have done and WHY...

However, this INCLUDES can then adapted to the database specific
functions, but right it is the hell...

> 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.

I know, WHY I use PostgreSQL for 9 1/2 years...

> 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.

My german hosting provider is using MySQL 5 and I have MANY problems to
adapt my own OnlineStore to it... With PostgreSQL 8.0 to 8.3 it was not
a problem... But since my hosting provider is a small one, maybe I can
convice him to do something. For some month he told me, for the
15 Euro/month month I have 2000 MByte of diskspace and if I do not
install a website Ican use the entired 2000 MByte for the database...

<grmpf>It is MySQL</grmpf>

I am thinking on a small dedicated database server with 4x SAS 147 GByte
and an AMD Opteron with 2 GByte of diskspace.

> 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.

Again: I know, WHY I use PostgreSQL for 9 1/2 years... ;-)

> So ... targeting a specific database isn't all bad, so long as you think

Right, but there are MANY application which are realy simple, where
database access can easyly ported but they HARDCODE inside the
applications the database functions instead of exporting them to an
INCLUDE for example and of course, without anny comments...

> carefully about it, understand the risks vs benefits, and don't use
> non-portable or database-specific features just because you can. It

This is, what I try to do in my applications...

> 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.

Yeah, peoples like you (and of course other) are responsable for this
great community here.

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)