Обсуждение: Equivalent for AUTOINCREMENT?

Hello,

I have a table where I have a serialnumber which shuld be  increased  be
each INSERT.  I know I can use max() to get the highest number, but  how
can I use it in a INSERT statement?

There was a message for some month a message describing it on this  list
but I do not find the message anymore...

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)

On Fri, Oct 31, 2008 at 03:30:44AM +0100, Michelle Konzack wrote:
> I have a table where I have a serialnumber which shuld be  increased  be
> each INSERT.  I know I can use max() to get the highest number, but  how
> can I use it in a INSERT statement?

Just don't mention the column.  For example, say I had the following table:

  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
  );

And I wanted to insert a new user, I'd do:

  INSERT INTO users (name) VALUES ('Sam Mason');

If I wanted to refer back to the identifier I'd just used, there are a
couple of options depending on what you want to do.  If it's just your
code that wants to know, use the RETURNING clause:

  INSERT INTO users (name) VALUES ('Sam Mason')
    RETURNING (id);

If you want to use the value inside more SQL code and don't want more
round trips between your code and the database you can do:

  BEGIN;
  INSERT INTO users (name) VALUES ('Sam Mason');
  INSERT INTO sessions (userid, startdate)
    VALUES (currval('users_id_seq'), now());
  SELECT currval('users_id_seq');
  COMMIT;

The "users_id_seq" here identifies the sequence that was created when
the table was created; if you're not sure what this is you can use the
pg_get_serial_sequence() function[1].  The SELECT statement at the end
will cause the database to return the generated code back to you so
it can be used elsewhere.  The reason for having to name the sequence
explicitly is that because of triggers and other complications multiple
sequences can get involved, when you'd only expect one.  Because of
this if there was just a simple function like MySQL's LAST_INSERT_ID()
Postgres wouldn't know which sequence you were actually wanted to refer
to.


  Sam

 [1] http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

> I have a table where I have a serialnumber which shuld be  increased  be
> each INSERT.  I know I can use max() to get the highest number, but  how
> can I use it in a INSERT statement?

Have a look in the manual for the SERIAL data type.
For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.

Michelle Konzack wrote:
> I have a table where I have a serialnumber which shuld be  increased  be
> each INSERT.  I know I can use max() to get the highest number, but  how
> can I use it in a INSERT statement?
>
> There was a message for some month a message describing it on this  list
> but I do not find the message anymore...

You want to use 'SERIAL' or 'BIGSERIAL' data type.  Then, for your
insert statement, use the 'RETURNING' clause like this:

CREATE TABLE mytable (
    mypkey SERIAL NOT NULL,
    somecol VARCHAR(200)
);

INSERT INTO mytable (somecol)
VALUES ('this is a test)
RETURNING mypkey;

You'll get your insert statement to return the integer value generated
by the SERIAL sequence.

These features are way more powerful than AUTO_INCREMENT.

-- Dante


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


--
----------
D. Dante Lorenso
dante@lorenso.com

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

I use PostgreSQL since 1999 and since some time I have to install  mysql
and sqlite because programs are sucking. There is NO seperate definition
for the database stuff, so I have to walt trough the PHP scripts to  get
the stuff and try to put it into a seperated file...  which then can  be
adapted to any databases...

Note: Since I am hit by such problems, my own php script use external
      database definition which then can easily adapted and of course
      I try avoid database specific functions...

      MySQL has hit me more then once.

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)

Hello Nikolas,

Am 2008-10-31 09:44:50, schrieb Nikolas Everett:
> I think you want a sequence. Give the serial number the type bigserial or
> serial.  See
> http://www.postgresql.org/docs/current/static/functions-sequence.html for
> more.

OK, thats cool...  I have found an example in  "sql-createsequence.html"

CREATE SEQUENCE serial START 1;

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

But there is one thing not clear:

Du I need to create a SEQUENCE for each table or do I need only  ONE  of
if and can use it independant on differnt tables?

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)

Hello Michael,

Am 2008-10-31 11:15:54, schrieb Michael Hall:
> > I have a table where I have a serialnumber which shuld be  increased  be
> > each INSERT.  I know I can use max() to get the highest number, but  how
> > can I use it in a INSERT statement?
>
> Have a look in the manual for the SERIAL data type.
> For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.

OK, the SERIAL is now working, but there is ONE problem.

If the INSERT fails, the SERIAL is in any case increased...

So this was the reason, why I have tried to use max(serno)  and  add  +1
the get the next value for "serno".

Is there a solution for it?

Since I can use a transaction block if  required,  there  should  be  no
problem with concurence access.

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)

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

On Sat, Nov 01, 2008 at 02:24:37PM +0100, Michelle Konzack wrote:
> Du I need to create a SEQUENCE for each table or do I need only  ONE  of
> if and can use it independant on differnt tables?

If you just create a bunch of tables with SERIAL or BIGSERIAL columns,
it will create one sequence for each column. But you can make a set of
such columns use the same sequence if you want. SERIAL and BIGSERIAL are
really just "syntactic sugar" which create a sequence and set the
column's default value to the next value in the sequence, like this:

jtolley=# create table a (id serial);
NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for
serial column "a.id"
CREATE TABLE
jtolley=# \d a
                          Table "public.a"
 Column |  Type   |                   Modifiers
 --------+---------+------------------------------------------------
 id     | integer | not null default nextval('a_id_seq'::regclass)

If I need a new table or column using the same sequence, I just do this:

jtolley=# create table b (q integer not null default
nextval('a_id_seq'));
CREATE TABLE
jtolley=# \d b
                         Table "public.b"
 Column |  Type   |                   Modifiers
 --------+---------+------------------------------------------------
 q      | integer | not null default nextval('a_id_seq'::regclass)

- Josh / eggyknap

Michelle Konzack wrote:
> Hello Michael,
>
> Am 2008-10-31 11:15:54, schrieb Michael Hall:
>>> I have a table where I have a serialnumber which shuld be  increased  be
>>> each INSERT.  I know I can use max() to get the highest number, but  how
>>> can I use it in a INSERT statement?
>> Have a look in the manual for the SERIAL data type.
>> For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.
>
> OK, the SERIAL is now working, but there is ONE problem.
>
> If the INSERT fails, the SERIAL is in any case increased...

Yes, that's by design. If that didn't happen, then it would be
impossible for another transaction to obtain a value from the sequence
before the previous transaction had committed or rolled back, even if it
had to do lots of other unrelated work before it committed.

If you really, truly need gapless sequences, there are some options. I
posted about them recently on another thread. The archives will contain
that post and many others from many people on the same topic. Be aware,
though, that gapless sequences have some NASTY performance consequences.

> So this was the reason, why I have tried to use max(serno)  and  add  +1
> the get the next value for "serno".
>
> Is there a solution for it?

Design your application not to expect your primary keys to be gapless.
If it requires contiguous sequences for something, generate them at
query time instead of storing them as primary keys. If the contiguous
sequence numbers must also be stable over the life of the record, try to
redesign to avoid that requirement if at all possible.

> Since I can use a transaction block if  required,  there  should  be  no
> problem with concurence access.

If you do not mind your transactions being unable to concurrently access
the data, there are many options available for gapless sequences. If you
have to handle DELETEs then you'll need a trigger to renumber
everything, but assuming you don't, something like this should work:

CREATE TABLE id_counter ( last_used INTEGER NOT NULL );
INSERT INTO id_counter ( last_used ) VALUES ( -1 );


-- Now, within a transaction:
--
-- Update the id counter. This obtains a lock that'll cause other
-- transactions to block until this transaction commits or rolls back
-- if they also attempt to update the counter. Only one transaction
-- (that touches id_counter) at a time may be doing work from this
-- point on.
--
-- You could use an explicit LOCK TABLE, but it's kind of pointless.
--
UPDATE id_counter SET last_used = last_used + 1;

--
-- Use our newly obtained ID, which is guaranteed to be free and
-- unused by any concurrent transaction.
--
INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM
id_counter), 'blah');

-- You can now do your other work and commit. Do as little as
-- possible after this, though, because you're preventing any
-- other transactions that need to access id_counter from doing
-- anything.

--
Craig Ringer

Michelle Konzack wrote:
> > I think you want a sequence. Give the serial number the type
bigserial or
> > serial.  See
> >
http://www.postgresql.org/docs/current/static/functions-sequence.html
for
> > more.
>
> OK, thats cool...  I have found an example in
"sql-createsequence.html"
>
> CREATE SEQUENCE serial START 1;
>
> INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
>
> But there is one thing not clear:
>
> Du I need to create a SEQUENCE for each table or do I need only  ONE
of
> if and can use it independant on differnt tables?

You can use one sequence for many tables.

If you follow the advice by Nikolas Everett and use a "serial" column,
that would create one dedicated sequence per serial column.

Yours,
Laurenz Albe

Michelle Konzack wrote:
-- Start of PGP signed section.
> Hello Michael,
>
> Am 2008-10-31 11:15:54, schrieb Michael Hall:
> > > I have a table where I have a serialnumber which shuld be  increased  be
> > > each INSERT.  I know I can use max() to get the highest number, but  how
> > > can I use it in a INSERT statement?
> >
> > Have a look in the manual for the SERIAL data type.
> > For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.
>
> OK, the SERIAL is now working, but there is ONE problem.
>
> If the INSERT fails, the SERIAL is in any case increased...
>
> So this was the reason, why I have tried to use max(serno)  and  add  +1
> the get the next value for "serno".
>
> Is there a solution for it?

You should read our three FAQ entries about sequences:

    http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.1

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Sat, Nov 1, 2008 at 12:33 PM, Michelle Konzack
<linux4michelle@tamay-dogan.net> wrote:
> Hello Michael,
>
> Am 2008-10-31 11:15:54, schrieb Michael Hall:
>> > I have a table where I have a serialnumber which shuld be  increased  be
>> > each INSERT.  I know I can use max() to get the highest number, but  how
>> > can I use it in a INSERT statement?
>>
>> Have a look in the manual for the SERIAL data type.
>> For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.
>
> OK, the SERIAL is now working, but there is ONE problem.
>
> If the INSERT fails, the SERIAL is in any case increased...
>
> So this was the reason, why I have tried to use max(serno)  and  add  +1
> the get the next value for "serno".
>
> Is there a solution for it?

Note that this is not only how pgsql and oracle and most other big
databases work.  It's also how innodb tables in mysql work:

http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

and for good reason, that as mentioned elsewhere, gapless sequences
tend to scale poorly.


>
> Since I can use a transaction block if  required,  there  should  be  no
> problem with concurence access.
>
> 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)
>



--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

Halle Craig,

Am 2008-11-05 20:37:31, schrieb Craig Ringer:
> If you really, truly need gapless sequences, there are some options. I
> posted about them recently on another thread. The archives will contain
> that post and many others from many people on the same topic. Be aware,
> though, that gapless sequences have some NASTY performance consequences.

Since this "NASTY performance consequences" would only  hit  the  INSERT
statement and it is very unlikely that I  have  concurence  WRITE/INSERT
access, it is a minor problem.

> Design your application not to expect your primary keys to be gapless.
> If it requires contiguous sequences for something, generate them at
> query time instead of storing them as primary keys. If the contiguous
> sequence numbers must also be stable over the life of the record, try to
> redesign to avoid that requirement if at all possible.

Yes it is a requirement...  and this is, why I have  tried  to  get  the
highest value of the column "serno".

> CREATE TABLE id_counter ( last_used INTEGER NOT NULL );
> INSERT INTO id_counter ( last_used ) VALUES ( -1 );
> --
> UPDATE id_counter SET last_used = last_used + 1;

> --
> INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM
> id_counter), 'blah');

Thank you for the example....
I will try it out now.

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)

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)

Michelle Konzack wrote:

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

... making them subject to problems with transit between the host
handing PostgreSQL and the host handling the rest of the work. You need
every hop of the main routes between the Pg host and all your other
providers to work reliably, with stable and reasonably low latency, all
the time. This isn't very likely.

You'll also have big latency problems, and if your design doesn't use
any more powerful database features you probably do LOTS of round trips,
lots of read-modify-writes, etc, and you'll suffer a horrible impact on
performance.

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

You would probably need dedicated co-located hosting where you provide
the machine and they provide rack space, stable power, an Ethernet port
or two, access to a console server with remote power cycle capability,
and hopefully access to some backup storage.

You'd have a hard time fitting 6TB of fast, reliable storage even in a
5RU enclosure (say 24 300GB SAS disks in a high-density storage server
chassis) so you'd probably need an external FC or SAS storage enclosure,
probably as well as internal storage.

$LOTS.

On the other hand, you expect $LOTS if you need to store 6TB of data
with fast, reliable access to it.

At least with SAS becoming so standard you don't need to worry about
fibre channel anymore.

If you don't need all of your data to be accessible particularly
quickly, you can save megabucks by using a set of 1TB SATA disks for
your bigger, more rarely used stuff. If you don't have too many big
indexes you might be able to keep them on the SAS disks for faster access.

> A dedicated Root-Server with 300 GByte SAS in Raid-1 without Hotfix cost
> alread 150 Euro per month.

Where is it hosted? At that price, I want one.

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

Personally, I wouldn't do it, because of the aforementioned problems
with latency and with unstable transit. I guess it depends on your
price/performance/reliability tradeoff requirements.

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

If you host your database somewhere far enough away from the application
that you're seeing several-hundred-millisecond latencies then you WILL
need complex operations. You will need to do as much work as possible on
the database end, avoid read-modify-writes (but you should do that
anyway), use SQL or PL/PgSQL functions to batch operations together, etc.

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

Yeah, I agree that's pretty objectionable. Whether it's bad because the
database access is simple (ie they're not using the database's ability
to do lots of the hard work for them) or because they've mixed it in
with the rest of the code I don't know.

I find I'll look at some application and see an "abstraction layer" that
results in the application doing things like:

x = get_value('field', 'table');
x ++;
set_value('field', 'table', x);

ie

SELECT field FROM table;
-- returns `42'
UPDATE table SET field = 43;

... which just makes me want to cry. Concurrency issues? Latency? Argh.
And that's a simple one; it gets WAY better once they come up with their
own procedural methods of doing what could be done with an UPDATE ...
FROM ... WHERE or the like.

That said, I'll confess to using Hibernate (a Java ORM) in large parts
of one of the apps I'm working on at present. It does a pretty good job,
and it's sane enough that you can bypass and use SQL (via the JDBC APIs)
  when you have work to do that it's not well suited for, such as bulk
updates or reporting queries.

--
Craig Ringer

Michelle Konzack wrote:
> Halle Craig,
>
> Am 2008-11-05 20:37:31, schrieb Craig Ringer:
>> If you really, truly need gapless sequences, there are some options. I
>> posted about them recently on another thread. The archives will contain
>> that post and many others from many people on the same topic. Be aware,
>> though, that gapless sequences have some NASTY performance consequences.
>
> Since this "NASTY performance consequences" would only  hit  the  INSERT
> statement and it is very unlikely that I  have  concurence  WRITE/INSERT
> access, it is a minor problem.

And DELETE.

And anything that happens in the same transaction after the INSERT or
DELETE that touches the table with the gapless sequence.

It'll probably be OK if you keep the transactions that modify the table
with the gapless sequences as short as possible, preferably doing
nothing except the modification in question.

--
Craig Ringer