Re: Equivalent for AUTOINCREMENT? - Mailing list pgsql-general

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)