Thread: Re: [GENERAL] Re: Is PostgreSQL ready for mission critical applications?

>On the other hand, I have been using MS-SQL 7 for several months now, for
>another project, and am not at all happy with it -- it has crashed on me
>several times (because of some flaky OCXs), even though I was only doing
>database design and not doing production work, and I am frustrated by the
>lack of user-defined functions that I have taken for granted in
>PostgreSQL.
>

I think this is an excellant discussion that really needs to be posted in
the FAQ. I searches through the FAQ and the docs looking for a feature list
and or a comparison with other major databases and I saw verly little info.
Specifically there should be matrix of features with MS SQL7.0 Oracle,
Informix, Sybase, and Interbase.  If at all possible benchmarks should be
posted or at least some phrasing stating which are faster at what features.
It would be very helpful for a potential Postgreas user to know what they
are getting into.

Maybe it's not fair to compare Postgres to Oracle especially given the
costs involved but it could be useful in a cost benefit analysis (sure
Oracle has feature X but Do I really need it and how much do I want to pay
for it).

Another thing that is needed is some sort of a front end tool like access
to generate forms, reports, tables etc. I know that a programmer could whip
something up using TK/GTK or java but I am thinking about an end user.

Finally: Until I can replace Access I need to have postgress to play nice
with my windows desktops so I need really good ODBC/ADO support.
----------------------------------------------
             Tim Uckun
      Mobile Intelligence Unit.
----------------------------------------------
   "There are some who call me TIM?"
----------------------------------------------

I have been surprised by the response to this question.  I was hoping that the responses would be more consistent, after all when software is unreliable it is generally known by all users.

Although one would expect a subjective bias to the opinions, the answers provided in the thread are highly polarized.   Jochen Topf gave a frightening description of an unreliable database which gave unpredictable results.  For example:

The most frustrating thing is that most bugs are not repeatable or at least
not repeatable in a small test script that I could send in with a bug report.
Looking at the bug reports that come through the mailing list, there are a
lots of the type: X works here but not in this similar situation. This is
IMHO a symptom of a bad design. A recent upgrade (I think it was from 6.5
to 6.5.1 or something like that) helped a little bit but on the other hand
some query optimizations that worked before didn't work anymore.

This is pretty scary.

However, I then read another reply only to find that Brett McCoy is converting "hundreds of thousands of documents" with no PostgresSQL problems at all.  Brett indicates that:

So I think PostgreSQL is quite solid and reliable.  The only thing I think
that is sorely needed in PostgreSQL is referential integrity constraints
like foreign keys (although this can be emulated with triggers).

In fact, the lack of referential integrity constraints happens to be my biggest concern - assuming the database is reliable, something that is proving hard to determine.

Reading on, I see that "The Hermit Hacker" (love the name) also finds the database to be reliable:

Odd, I've been using PostgreSQL since v1.x for exactly this same reason,
and we haven't had any problems with the database crashing since v6.x was
released.  Then again, the radius server opens/closes its connections as
required, instead of relynig on one persistent connection, so maybe that
helps, but that's just "application programming" vs backend...

There is a subtle implication that perhaps Jochen's problems are self inflicted.  In a later email, Jochen responds and asks if he is the only one using "advanced features" and suggests that they may be the cause of his problems.  However, his list of "advanced features" is a little scary since that are the very features that makes PostgreSQL so attractive in  the first place - and I fully intend to use them!

So which is is guys, is this database dependable for commercial use - or is an academic oddity, worth watching but not using?

Any other success or failure stories would be really helpful....

Is PostgresSQL ready for prime time, or is it limpware?

Steve

-------------------------------------------------

PS This thread was started in pgsql-general, I cross posted to pgsql-novice as I am sure that some readers of that group would be interested in this topic.  If you want to comment, please reply to pgsql-general@postgreSQL.org, I don't want to fork the thread!
 

On Mon, 22 Nov 1999, Stephen Birch wrote:

> I have been surprised by the response to this question.  I was hoping that the
> responses would be more consistent, after all when software is unreliable it
> is generally known by all users.

Steve -  I tried to post an answer earlier (and it was verbose as is my
tendency!) but apparently it bounced.  I will cc the list, you and
scrappy on this note.

I converted my shop from FoxPro and PROGRESS (on UnixWare) to Postgres on
Linux/BSD this past year.  12 years worth of data.  My users are clinical
staff *not* computer ppl (in fact they are computer-phobic) and have made
myriad mistakes in entering and retrieving data via network and dialup
connections.  Our electricians have swapped feeder cables while my boxes
were online - ouch.  And I have made dubious errors in programming as I
learned perl, CGI and DBI.  But throughout it all, PG has suffered all of
our blunders and our predilection for the text data type.

Social Workers love to talk, even in their clinical progress notes.
But PG rolls with the punches and everyday I find new reasons to love this
database.  Performance is good (better on UnixWare and BSD than Linux)
and I am really happy that the Pg SQL is less idiosyncratic than Oracle.

Perl has become a 4GL of sorts for me (replacing PROGRESS) and my > 140
character apps are now being ported to CGI for a netscape interface.
They work well...

I can't say enuf about how much I like postgres and - how much my users
like it.  They suffered with PROGRESS (slooooow) and FoxPro (corruption).
I run Oracle on Linux and have tried Sybase and Informix too.  I'll stick
with Postgres.  MySQL and mSQL are too much like flat file db's (concurrency
control matters here) for me to even consider using them (I did try mSQL
and didn't much care for it...)

I use Pg ver 6.3.2 which is *rock* solid altho I'm told 6.5.x is faster.
I may upgrade at some point but I feel very comfortable with 6.3.2 as I
know it runs a wide array of mission critical apps with good speed and
and good reliability.  The tech support from the likes of Vadim and
Bruce (and volunteers like Herouth M and Brett) is also something I've
gotten used to...I often waffle between which unix I like best but I
know which database I prefer.

Cheers,
Tom

------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528
                                          Fax:   718-354-5056

/* Member: Computer Professionals For Social Responsibility */

Everyone has their own experiences, and difficulties...there are X
platforms out there that PostgreSQL supports, multiply that by however
many different hardware pieces that be thrown the standard box, and you'll
get that many different experiences...would i use it in a mission critical
box?  yes, I do on several.  have I ever had problems...to be honest,
yes...most of them at the application level.

take a look at:

http://www.pgsql.com/projects/projects.cgi?sort=name

There are ppl there working on projects such as:

Arctic and Antarctic Research Center
    - Scripps Institution of Oceanography / Frank Delahoyde

CFAR Molecular Biology Core
    - University of California San Diego / David J. Looney

Online Community Newspaper
    - Alex Wilson Coldstream Ltd / Anil Amarakoon

Camping-USA!
    - Camping-USA! / Vince Vielhaber

POS System for Retail Shop
    - PIPSE Information System Co. / Yewon Heo

Postgres Mail Database
    - National Center for Supercomputing Applications / Duane Moore

Software2Go Online Store
    - Software2Go, LLC / Eric Schnoebelen

Univ Texas @ Arlington - Engineering Distance Learning Site
    - Univ Texas @ Arlington / Charlie Lindahl

Utility Billing
    - City Of Lake Lotawana / A. Van Hook


And that is just a select listing of all the projects currently listed,
and doesn't include several hundred that I'm still enterign into the new
system...

Each one of those is mission critical to the person using it, and, in some
cases, I'd say to the ppl that they affect (Utility Billing and POS System
are the two that come to mind there)...

Any bugs/limitation of the current system can be worked around, and will
be improved in each release, as they have been to date.  Each release is
generally leaps ahead of previous releases...even the minor releases
contain changes that improve things...

Quite frankly, I think the fact that Jochen is still around *even though*
he has problems says alot about the quality of both the software and the
development processes that we've developed over the past year, and also
gives a good indication of where we are going...

If you are still hestitant, write your application in such a way that if
things get to the point that you just can't stay with PostgreSQL, you can
switch.  Use perl/dbi, so that you can switch with a simple chagne to the
connect string, its what I do...except, in my case, its to make sure that
I can do all my development work in PostgreSQL, while keepign in mind that
the end user might not feel comfortable with that, and/or to keep options
open for them in the future...so far, I've been lucky, and all my clients
have been quite happy with PostgreSQL as well...

 On Mon, 22 Nov 1999, Stephen Birch wrote:

> I have been surprised by the response to this question.  I was hoping that the
> responses would be more consistent, after all when software is unreliable it
> is generally known by all users.
>
> Although one would expect a subjective bias to the opinions, the answers
> provided in the thread are highly polarized.   Jochen Topf gave a frightening
> description of an unreliable database which gave unpredictable results.  For
> example:
>
> > The most frustrating thing is that most bugs are not repeatable or at least
> > not repeatable in a small test script that I could send in with a bug report.
> > Looking at the bug reports that come through the mailing list, there are a
> > lots of the type: X works here but not in this similar situation. This is
> > IMHO a symptom of a bad design. A recent upgrade (I think it was from 6.5
> > to 6.5.1 or something like that) helped a little bit but on the other hand
> > some query optimizations that worked before didn't work anymore.
> >
>
> This is pretty scary.
>
> However, I then read another reply only to find that Brett McCoy is converting
> "hundreds of thousands of documents" with no PostgresSQL problems at all.
> Brett indicates that:
>
> > So I think PostgreSQL is quite solid and reliable.  The only thing I think
> > that is sorely needed in PostgreSQL is referential integrity constraints
> > like foreign keys (although this can be emulated with triggers).
> >
>
> In fact, the lack of referential integrity constraints happens to be my
> biggest concern - assuming the database is reliable, something that is proving
> hard to determine.
>
> Reading on, I see that "The Hermit Hacker" (love the name) also finds the
> database to be reliable:
>
> > Odd, I've been using PostgreSQL since v1.x for exactly this same reason,
> > and we haven't had any problems with the database crashing since v6.x was
> > released.  Then again, the radius server opens/closes its connections as
> > required, instead of relynig on one persistent connection, so maybe that
> > helps, but that's just "application programming" vs backend...
> >
>
> There is a subtle implication that perhaps Jochen's problems are self
> inflicted.  In a later email, Jochen responds and asks if he is the only one
> using "advanced features" and suggests that they may be the cause of his
> problems.  However, his list of "advanced features" is a little scary since
> that are the very features that makes PostgreSQL so attractive in  the first
> place - and I fully intend to use them!
>
> So which is is guys, is this database dependable for commercial use - or is an
> academic oddity, worth watching but not using?
>
> Any other success or failure stories would be really helpful....
>
> Is PostgresSQL ready for prime time, or is it limpware?
>
> Steve
>
> -------------------------------------------------
>
> PS This thread was started in pgsql-general, I cross posted to pgsql-novice as
> I am sure that some readers of that group would be interested in this topic.
> If you want to comment, please reply to pgsql-general@postgreSQL.org, I don't
> want to fork the thread!
>
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

The reason why opinions are so varied has alot to do with the expertise of
each person in relation to PostgreSQL and Linux.  Often problems that are
considered simple to resolve by some are very difficult for others.  And
sometimes problems are caused by actions that are done out of inexperince
with the system like cancelling certain operations in progress etc...
You probably would not be able to determine reliability from opinions.  The
thing is PostgreSQL is extremely reliable if u know what you are doing and
know how to handle/get around any bugs.

Lookig at some of the other posts about reliability...the number of records
in a database will mainly determine the ability of a database to maintain
performance at larger file/index sizes.  It does not really impact
stability.  Stability is mainly affected by the number of
reads/updates/inserts that are performed.  Usually u want to look at large
user loads, large transaction loads and large number of
updates/inserts/deletes to gauge reliability.   I havent seen anyone post
saying that they are running a system that does this...perhaps I just missed
the post.

can I ask what type of application u aer going to use PostgreSQL for?


----- Original Message -----
From: The Hermit Hacker <scrappy@hub.org>
To: Stephen Birch <sbirch@ironmountainsystems.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-novice@postgresql.org>
Sent: Monday, November 22, 1999 9:32 PM
Subject: Re: [GENERAL] Re: Is PostgreSQL ready for mission
criticalapplications?


>
> Everyone has their own experiences, and difficulties...there are X
> platforms out there that PostgreSQL supports, multiply that by however
> many different hardware pieces that be thrown the standard box, and you'll
> get that many different experiences...would i use it in a mission critical
> box?  yes, I do on several.  have I ever had problems...to be honest,
> yes...most of them at the application level.
>
>  On Mon, 22 Nov 1999, Stephen Birch wrote:
>
> > I have been surprised by the response to this question.  I was hoping
that the
> > responses would be more consistent, after all when software is
unreliable it
> > is generally known by all users.
> >
> > Although one would expect a subjective bias to the opinions, the answers
> > provided in the thread are highly polarized.   Jochen Topf gave a
frightening
> > description of an unreliable database which gave unpredictable results.
For
> > example:
> >
> > > The most frustrating thing is that most bugs are not repeatable or at
least
> > > not repeatable in a small test script that I could send in with a bug
report.
> > > Looking at the bug reports that come through the mailing list, there
are a
> > > lots of the type: X works here but not in this similar situation. This
is
> > > IMHO a symptom of a bad design. A recent upgrade (I think it was from
6.5
> > > to 6.5.1 or something like that) helped a little bit but on the other
hand
> > > some query optimizations that worked before didn't work anymore.
> > >
> >
> > This is pretty scary.
> >
> > However, I then read another reply only to find that Brett McCoy is
converting
> > "hundreds of thousands of documents" with no PostgresSQL problems at
all.
> > Brett indicates that:
> >
> > > So I think PostgreSQL is quite solid and reliable.  The only thing I
think
> > > that is sorely needed in PostgreSQL is referential integrity
constraints
> > > like foreign keys (although this can be emulated with triggers).
> > >
> >
> > In fact, the lack of referential integrity constraints happens to be my
> > biggest concern - assuming the database is reliable, something that is
proving
> > hard to determine.
> >
> > Reading on, I see that "The Hermit Hacker" (love the name) also finds
the
> > database to be reliable:
> >
> > > Odd, I've been using PostgreSQL since v1.x for exactly this same
reason,
> > > and we haven't had any problems with the database crashing since v6.x
was
> > > released.  Then again, the radius server opens/closes its connections
as
> > > required, instead of relynig on one persistent connection, so maybe
that
> > > helps, but that's just "application programming" vs backend...
> > >
> >
> > There is a subtle implication that perhaps Jochen's problems are self
> > inflicted.  In a later email, Jochen responds and asks if he is the only
one
> > using "advanced features" and suggests that they may be the cause of his
> > problems.  However, his list of "advanced features" is a little scary
since
> > that are the very features that makes PostgreSQL so attractive in  the
first
> > place - and I fully intend to use them!
> >
> > So which is is guys, is this database dependable for commercial use - or
is an
> > academic oddity, worth watching but not using?
> >
> > Any other success or failure stories would be really helpful....
> >
> > Is PostgresSQL ready for prime time, or is it limpware?
> >
> > Steve

On Mon, 22 Nov 1999, Kane Tao wrote:

> The reason why opinions are so varied has alot to do with the expertise of
> each person in relation to PostgreSQL and Linux.  Often problems that are

Ack, you know the discussion is going downhill when someone mentioned
Linux *sigh* *big, friendly, Daemon grin*


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

>
> So which is is guys, is this database dependable for commercial use - or is an
> academic oddity, worth watching but not using?
>

 For me it depends on what you use in PostreSQL. Some basic stuff is working
really well in PostgreSQL - other parts have problems.

 As I've written earlies in some postings. Our company is evaluating
PostgreSQL to get a solid database for a research project and perhaps
later for a product. We've used Adabas-D in our previous products.

 We've written a PostgreSQL->Smalltalk/X wrapper, now we are developing
a oo->rdmbs framework on top of it and we've noticed the following
problems with PostgreSQL 6.5.1:

 a) Due to the database layouts we are in need of doing all these nice
    sql-statements like "group by" and "having" ... and as posted earlier
    in this group: they're limited in PostgreSQL.

    Now if you need these aggregations urgently you get many, many
    problems and you have to produce work-arounds ...

    And this is one reason for all problems running around: as with
    all programming languages all these guys come with special SQL
    knowledge (e.g. I use these statements very much ...) and now
    you come to POstgreSQL and find out, that these statements are
    special.

    Our application relies on "groub by" and "having" due to the fact,
    that we store our attributes for objects not column-wide but
    row-wide. Therefore you've the need for much more complicated
    SQL commands to retrieve the attribute values for one object - if
    they do not work - you have really problems.

    Now working two months with PostgreSQL I've to admit, that the
    database works, but due to the sql limitations we consider to
    drop it.

 b) We had problems with vacuumdb here and there. Some times it cored.
    We've deleted a 300 MB database under psql and the backend cored ...

 In general it is no wonder, that some persons tell us: "we use it
with success in our multi-gigabyte database" and others have a totally
different opinion.

 When considering the fact, that PostgreSQl is a free database it is
worty. Some persons are developing the database and if I
could have a wish: please, please fix all these limitations of
"groub by" and "having" statements and get closer to the sql standard.

 And to mention, how different the expectations are: some persons out
there mentioned, that referential integrity would be a very urgent need
for them - I've the totally different opinion about this:

 When doing procedural queries to the database, this need is ok. If you
put a full oo->rdbms wrapper on top of this database and do your
programming in some oo-languages this need vanishes - because referential
integrity does so much in the background, that your object-model in
your application simply becomes wrong - therefore I throw away
referential integrity. It makes the administration for the databases
also much more simplier.

 Just my opinion .. not to be misinterpreted. I encourage every work
the people push into PostgreSQL because I want to have a free
database.


 Marten

hi,

I'm wondering if Pg has anything like an interbase generator?  What I'd like to do
is create a trigger that auto numbers a column.

Thanks,
Jason.

--
.............
......... Jason C. Leach
...... University College of the Cariboo
... jcl@mail.ocis.net.
.. http://www.ocis.net/~jcl
.

The Search for Extraterrestrial Intelligence from Home:
http://setiathome.ssl.berkeley.edu

                                                                LINUX!

On Tue, 23 Nov 1999 marten@feki.toppoint.de wrote:

>  a) Due to the database layouts we are in need of doing all these nice
>     sql-statements like "group by" and "having" ... and as posted earlier
>     in this group: they're limited in PostgreSQL.

Since I use 'Group By' quite a bit...Having not so much...can you be more
specific on the problems?

>  b) We had problems with vacuumdb here and there. Some times it cored.
>     We've deleted a 300 MB database under psql and the backend cored ...

What version of PostgreSQL?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

On Tue, 23 Nov 1999 marten@feki.toppoint.de wrote:

> [SNIP]
>  We've written a PostgreSQL->Smalltalk/X wrapper, now we are developing
> a oo->rdmbs framework on top of it and we've noticed the following
> problems with PostgreSQL 6.5.1:

wow, people still use Smalltalk ?  i had figured that most moved to
Objective-C or Java.

>  a) Due to the database layouts we are in need of doing all these nice
>     sql-statements like "group by" and "having" ... and as posted earlier
>     in this group: they're limited in PostgreSQL.
>
>     Now if you need these aggregations urgently you get many, many
>     problems and you have to produce work-arounds ...

why wouldnt you have 'group by' functionality in the framework layer like
NeXT's Enterprise Objects Framework?  isnt the whole point to eliminate (
or nearly eliminate ) the need to key in SQL ?

EOF gives you methods like -[NSArray computeAvgForKey:], -[NSArray
computeCountForKey:], -[NSArray computeMaxForKey:], -[NSArray
computeMinForKey:], and -[NSArray computeSumForKey:].  using, say, 2
methods, one can more than likely get the 'group by' functionality you're
talking about in an OO and RDBMS-independent way.  for instance:

- (NSDecimalNumber *)amountBackOrdered
{
   EOQualifier *backOrderedQualif =
      [[EOKeyValueQualifier alloc] initWithKey:@"isBackOrdered"
         operatorSelector:EOQualifierOperatorEqual
                    value:[NSNumber numberWithBool:YES]];
   NSArray *backorders;

   backorders =
     [[self orders] filteredArrayUsingQualifier:backOrderedQualif];

   return [backorders computeSumForKey:@"amount"];
}

( somewhat familiar syntax, eh ? :) )

lookie, no SQL!  and, oh, btw, this works if your datastore is a flatfile,
postgresql, oracle, sybase, informix, or that gdbm-ish database.

>     And this is one reason for all problems running around: as with
>     all programming languages all these guys come with special SQL
>     knowledge (e.g. I use these statements very much ...) and now
>     you come to POstgreSQL and find out, that these statements are
>     special.

again, isnt the point of OO frameworks on top of non-OO RDBMSs to
eliminate the need to learn 20 vendors' implementations of SQL by
providing an OO 'alternative' ( for lack of a better word ) ?

> [SNIP]
>  b) We had problems with vacuumdb here and there. Some times it cored.
>     We've deleted a 300 MB database under psql and the backend cored ...

the largest table i have is ~71m and growing somewhat quickly:
-rw-------   1 postgres postgres 71753728 Nov 24 02:06 logins
-rw-------   1 postgres postgres 71761920 Nov 24 02:10 logins

... and ive had absolutely no problems vacuuming it under 6.5.x.

> [SNIP]
>  When considering the fact, that PostgreSQl is a free database it is
> worty. Some persons are developing the database and if I
> could have a wish: please, please fix all these limitations of
> "groub by" and "having" statements and get closer to the sql standard.

www.pgsql.com, make a donation.  :)

>  And to mention, how different the expectations are: some persons out
> there mentioned, that referential integrity would be a very urgent need
> for them - I've the totally different opinion about this:
>
>  When doing procedural queries to the database, this need is ok. If you
> put a full oo->rdbms wrapper on top of this database and do your
> programming in some oo-languages this need vanishes - because referential
> integrity does so much in the background, that your object-model in
> your application simply becomes wrong - therefore I throw away
> referential integrity. It makes the administration for the databases
> also much more simplier.

:)  funny you should mention this -- there was a LOT of talk on the
webobjects mailing list about this recently.  EOF uses two methods to add
objects to both sides of a relationship ( for back pointers ).  these
methods end up calling two other methods in your custom classes.  the
discussion was how to add two OTHER methods to front for
-addObject:toBothSidesOfRelationshipWithKey: so one wouldnt have to keep
typing that method ( and getting carpal tunnel syndrome in the process ).

but anyway... throwing RI away isnt all that wise, however.  every db with
2+ tables would most likely benefit from keeping the two in sync, be it in
your object model or whatnot.  of course, if your RI is in your object
model and object model ONLY, this would most likely present problems for
applications that dont use your framework; there's nothing preventing them
from mucking up your foreign keys.  as long as you order your
inserts/deletes/updates properly, one can safely keep RI in the rdbms and
the object model... it get a little tricky when there's a table that
refers back to itself, though.

>  Just my opinion .. not to be misinterpreted. I encourage every work
> the people push into PostgreSQL because I want to have a free
> database.

you know, in all honesty, i could care less if the product was free.  if
it works, and works well, then ill use it.  so far, pgsql has worked
_VERY_ nicely -- so nicely, in fact, that i havent felt the need to move
off to Oracle for my internal dbs.

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
"Tell a man that there are 400 billion stars and he'll believe you.
 Tell him a bench has wet paint and he has to touch it."

You for everybodies information. I'm a relative new user of PostgreSQL.
I've only used the 6.5.3 version, and I've only been using it for about
2 weeks. In that time I have converted all of our legacy data (60+ GB)
divided over 8 databases and several hundred tables to PostgreSQL.

Our users have been using the migrated data for a week now, and I
must say I'm very happy. PostgreSQL has consistantly processed
1,200,000 hits /day. We vacume the databases every night, and
PostgreSQL has yet to fail on me once.

Queries range from simple selects, to more complex queries with group,
having, and sub selects.

I am did have some problems with the more complex datatypes, and
I did have to program around a bug in the Operating System. But that
is due to the fact that I'm bound to SCO Open Server 5.0.5.

    Wim

The Hermit Hacker wrote:

> On Tue, 23 Nov 1999 marten@feki.toppoint.de wrote:
>
> >  a) Due to the database layouts we are in need of doing all these nice
> >     sql-statements like "group by" and "having" ... and as posted earlier
> >     in this group: they're limited in PostgreSQL.
>
> Since I use 'Group By' quite a bit...Having not so much...can you be more
> specific on the problems?
>
> >  b) We had problems with vacuumdb here and there. Some times it cored.
> >     We've deleted a 300 MB database under psql and the backend cored ...
>
> What version of PostgreSQL?
>
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
>
> ************

> On Tue, 23 Nov 1999 marten@feki.toppoint.de wrote:
>
> >  a) Due to the database layouts we are in need of doing all these nice
> >     sql-statements like "group by" and "having" ... and as posted earlier
> >     in this group: they're limited in PostgreSQL.
>
> Since I use 'Group By' quite a bit...Having not so much...can you be more
> specific on the problems?

 This has been discussed on the e-mail lists (sql) this month several times.
Tom also mentioned the reason for that. If I remember correctly:

 The having construct in sub-selects are not interpreted correctly and may
not return the result one hope should be the result.

 select * from TABLE-A
   where AO IN
     (select AO from TABLE-B where ... group by AO having 2<count(*))

 Statements like these do NOT work.

 They mean: return all rows from table-a if you have at least two rows
on table-b having the identical AO value.


> >  b) We had problems with vacuumdb here and there. Some times it cored.
> >     We've deleted a 300 MB database under psql and the backend cored ...
>
> What version of PostgreSQL?
>

 6.5.1


 Marten

Hello,

You may be interested by a script which drops a column as this
feature isn't supported by Postgresql. I guess it could be easier
and nice in Perl or something similar but I'm using what I know.

The parameters are in that order :

the name of the database
the table
the column to drop

Alain

#!/bin/sh

psql -d $1 -c "\d $2" | awk 'BEGIN { keep=1 } /+-/ { keep=1-keep } { if
(keep) { print } }' | grep -v "\-\-" | grep -v "Table *=" | grep -v " $3 " |
sed "s/| \([^ ]*\).*/\1/" | tr -s \\012 "," | sed "s/,$//" | sed
"s/\(.*\)/select \1 into temp tmp_drop_column from $2 ; drop table $2 ;
select * into $2 from tmp_drop_column;/" > tmp_sql_drop_column
psql -d $1 -f tmp_sql_drop_column
rm tmp_sql_drop_column

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hello,
>
> You may be interested by a script which drops a column as this
> feature isn't supported by Postgresql. I guess it could be easier
> and nice in Perl or something similar but I'm using what I know.
>
> The parameters are in that order :
>
> the name of the database
> the table
> the column to drop
>
> Alain
>
> #!/bin/sh
>
> psql -d $1 -c "\d $2" | awk 'BEGIN { keep=1 } /+-/ { keep=1-keep } { if
> (keep) { print } }' | grep -v "\-\-" | grep -v "Table *=" | grep -v " $3 " |
> sed "s/| \([^ ]*\).*/\1/" | tr -s \\012 "," | sed "s/,$//" | sed
> "s/\(.*\)/select \1 into temp tmp_drop_column from $2 ; drop table $2 ;
> select * into $2 from tmp_drop_column;/" > tmp_sql_drop_column
> psql -d $1 -f tmp_sql_drop_column
> rm tmp_sql_drop_column

The fact is that internally this is exactly what we would have to do to
drop a column.  Now that we have temp tables, maybe someone could code
up some C to do this, or just an pg_exec_query_dest() call to do the
job.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026