Slashdot Mirror

Maybe PostgreSQL justs a name change and a new PR department. Many people don't even know how to pronounce PostgreSQL. Consider the name's awkward evolution: Ingres --> Postgres --> PostgreSQL. They've already got a decent logo (the blue elephant). Presumably, the elephant never forgets your data?

Looks like the PostgreSQL team is taking an active role to update their PR: A Call for PostgreSQL Case Study Participants

Maybe PostgreSQL justs a name change and a new PR department. Many people don't even know how to pronounce PostgreSQL. Consider the name's awkward evolution: Ingres --> Postgres --> PostgreSQL. They've already got a decent logo (the blue elephant). Presumably, the elephant never forgets your data?

Looks like the PostgreSQL team is taking an active role to update their PR: A Call for PostgreSQL Case Study Participants

http://www.postgresql.org

PostgreSQL has vast searchable "interactive documentation" as well.

Background vacuuming is on the TODO list. You're probably aware that as of version 7.2 vacuuming no longer requires table locking, so it seems quite feasable to vacuum in the background to me. There is also a 3rd party daemon available which will automatically vacuum tables based on a configurable activity threshold (i've never tried this though).

Background vacuuming is on the TODO list. You're probably aware that as of version 7.2 vacuuming no longer requires table locking, so it seems quite feasable to vacuum in the background to me. There is also a 3rd party daemon available which will automatically vacuum tables based on a configurable activity threshold (i've never tried this though).

reliable replication (argue away, only shareplex, NT SQL server & Sybase have it today)

Not arguing, just pointing out that PostgreSQL replicates reliably [PDF] too. (-:

reliable replication (argue away, only shareplex, NT SQL server & Sybase have it today)

Not arguing, just pointing out that PostgreSQL replicates reliably [PDF] too. (-:

[M]ySQL also works quite well on Solaris

So does PostgreSQL. When they polled their users, four percent claimed to run it on Solaris (4th most popular after Linux, Windows and *BSD).

[M]ySQL also works quite well on Solaris

So does PostgreSQL. When they polled their users, four percent claimed to run it on Solaris (4th most popular after Linux, Windows and *BSD).

SQLite claims to be twice as fast as both MySQL and PostgreSQL, and is more SQL92-compliant and ACID-compliant than MySQL.

Does that mean everyone should drop MySQL and PostgreSQL for SQLite? No. It means you have to evaluate your situation and choose the best tool for the job.

Personally, I've have very good luck using PostgreSQL, and probably won't ever consider using MySQL until it is truly ACID-compliant.

>wouldn't that just be storing a string?

Oh no. I mean, yes, you could just store XML as a string in a BLOB column, but that's no better than just storing as a file.

A custom XML datatype would not treat the XML as a blob, but actually parse the XML upon input into the table, storing an internal representation (probably as an associative array) which would allow custom operators to traverse the tree, visit nodes, etc...

But, it would also allow you to perform relational queries and place integrity constraints on your XML documents.

To explain further, I will use a specific example: PostgreSQL allows you to create custom datatypes, even importing C functions to handle the input and output of these types. Thus, theoretically, you should be able to create a custom datatype called "xmldoc", and use code from a standard C library which handles XML, such as libxml, or expat, which will parse the XML string into an internal data structure, and vice-versa upon output.

(I must stress, this is theoretical. I haven't had the time or need for such a thing, but according to the documentation, it should be possible)

Even as recently as last month, MSIE came in 6 out of 6 in a comparison of web browsers. Opera and Mozilla, among others, have it beat by a long shot in all categories (well, Opera costs, but I get my boss to pay). It's even documented in U.S. Federal Court records that MSIE acheived market share over Netscape by bundling MSIE with new copies of MS-Windows.

Quattro, Lotus 1-2-3 and other spread sheets were faster and more mature. It wasn't until MS-Excel v4 when Microsoft's alternativs started to come up to near the same grade as competitors.

Likewise with small desktop databases. Foxpro, dBase, FileMaker, Reflex, and others were still a length ahead of MS-Access. After all Microsoft is still playing catchup, though they did manage to buy out Foxpro. Oracle9i and IBM's DB2 by far offer the best performance and functionality for high end SQL servers. Postgresql and MySQL have the mid-range covered and would be what Microsoft's SQL server is trying hardest to compete with. The Microsoft SQL server is not up to snuff nor is it secure.

But almost-as-good won't displaced established tools. That's where leveraging and sales pitches comes in.

Early versions MS-Word were a unique exception among Microsoft's products in that they were actually competitive with contemporary products. However, whether MS-Word variants were actually better than WordPerfect, AMI and others is probably more an issue of taste than something objective. It and MS-Windows were used to shoehorn MS-Excel into sites.

My god man, you're totally wacko. Use a real database.

You might look a PostgreSQL....it beat out Oracle as the DB for the .org name servers....


Yeah. MySQL lacks transaction support, making it useless in certain enterprises. (e.g sourceforge.net stopped using it and started using PostgreSQL when it introduced foundries).
And a, a Recent PostgreSQL.org poll shows that 20% of postgres users formerly used SQL server. Just behind Sybase.

(Wasn't SQL server included in personal web server in NT 4.0 and 98/SE

Use the Database Migration Wizard from pgAdmin. I would recommend to do a migration of the schema first, then check the schema for problems (for instance, make sure the datatypes for fields FKs reference to are always the same, even INT4 vs. INT8 matters) and then import the data.

Well, the built process might be a bit tedious, but you can download the source for a native version of PostgreSQL 7.2.1 for Windows.

Mozilla team rejects libart (LGPL) and at the same time includes GTK (also LGPL). Strange, isn't it? But it doesn't look strange if you'll try to read some discussions in their bugzilla, like this one, where they buried out XFORMS b/c Why not just do it all in html and keep extending html. Basically, "we don't need any new standards, we need just some bugs to be fixed".

Look at other Mozilla projects having been promising and now dead. Look at their Roadmap - only version numbers, no info about any planned features (compare it with, for example PostgreSQL TODO list).

Gecko is (or was?) the most promising GUI technology I see today. It allows much more than HTML browsing - it allows to build real applications on the web. And several non-browser projects have been developed (i.e. mail). But tell me the name of any Gecko project newborn in last 6 months? Or old one riched its v1.0 quality in last 12 months? Did I miss something or nobody cares about Gecko anymore?

I think that for a long term Mozilla as project is slowing down and it may eventually die. At least with its current development team.

html format

downloadable inc. pdf & html

Only takes about a day to read the whole thing if you don't try and understand everything.

I was forced to do it in one sitting once when one of my colleagues got into difficulty and I was drafted in on a "this project was supposed to ship last week and Fred can't get to grips with Postgres, turns out his database skills were copy & paste" scenario and I've never looked back.

html format

downloadable inc. pdf & html

Only takes about a day to read the whole thing if you don't try and understand everything.

I was forced to do it in one sitting once when one of my colleagues got into difficulty and I was drafted in on a "this project was supposed to ship last week and Fred can't get to grips with Postgres, turns out his database skills were copy & paste" scenario and I've never looked back.

Well, now I have a really good reason to switch to postgres...
And the mandatory communist comment: in Soviet Russia, mysql finds holes in YOU!

So use PostGreSQL instead. You'll be better off both financially and technically.

1. No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).

2. Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.

3. The documentation is tough reading. Very formal. Obviously done by comp-sci academics.

No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic. For example, the simplest way I've found to calculate the number of days elapsed between today and a given date is:

ALTER TABLE products DROP COLUMN description;

You are linking to something that counts security advisories for Linux and compares them with security advisories for other OS'es. You seem to have misunderstood that security advisories for a certain OS are something entirely different from security advisories for a RDBMS. The 2 are completely unrelated.
In fact, if you want to you can run PostgreSQL on Windows if receiving less security advisories is more important than the time-to-fix. Or if you feel really paranoid, just run PostgreSQL on OpenBSD. That gives you an excellent safety record, with only 1 remotely exploitable security problem in nearly 6 year.

For a complete list of supported platforms see http://developer.postgresql.org/docs/postgres/supp orted-platforms.html

Um, OODBMS is not really
that innovative, although I will agree that it is cool. I prefer PostgreSQL myself, but that's because I don't have tens of thousands of dollars to spend on all the commercial databases. *shrugs*

I apologise in advance if Oracle has redefined OODBMS to mean something different than I'm used to it meaning, but at least as much as I know what it is, it's hardly innovative. It's been around a very long time.

PostgreSQL's got MVCC, which is miles ahead.

Subselects are currently in development for 4.1.

Check. PostgreSQL's got that one, too.

Stored procedures are planned for 5.0.

Check.

They are looking at triggers.

Duh, that one too.

And the speed is pretty much on par with MySQL. And it's so easy to setup, if you haven't tried it for some time, do so now, it's really stable, fast and loaded with goodies!

If you need to expand your SQL to include PostgreSQL, try:

PostgreSQL: Introduction and Concepts by Bruce Momjian

Practical PostgreSQL, by Command Prompt, Inc. written by John Worsley and Joshua Drake of Command Prompt, Inc.

Very practical definitions, examples, and procedures. I'm still scratching the surface of SQL, so I haven't found anything yet these sources can't handle.

I've also found the Usenet Posgres groups useful.

Personally I usually go with Postgres for any app with a lot of concurrent writes, like most of the web stuff I do (seems to handle it better than MySQL).

Btw, it's not always true that Oracle > MySQL; I remember doing a rather simple app where I had to do simple manipulations on several dozen million of simple records - MySQL beat the pants off Oracle performance-wise

I also often see MySQL being applied to situations where it seems to be overkill, where simple flat files would do just fine

When you want speed and flexibility and scalability and reliability and extendability and particularly developer productivity you will undoubtedly end up shooting yourself in the foot later if you avoid some form of DBMS up front.

Where you have a particularly well-defined, narrow functionality, and performance in a small footprint is a requirement, an RDBMS may not be such a good choice, but DB libraries like berkeley db can still be very useful.

And with PostgreSQL, Firebird , MySQL and so many other free, open-source projects out there covering such a broad spectrum of needs for a database, why would you not use that expert work?

How about an application's compatibility with the kernel however that doesnt just go over to another OS

Umm... can you please show me an example of a server-oriented application that only works on solaris and doesn't have a substitute on other systems?

I currently have to implement a DotLRN (.lrn) site which is based on OpenACS.

But anyway, OpenACS is the reworked version of ACS (which stands for 'Asomething' Community System) that was written for Oracle and AOLserver.

OpenACS is written in TCL, specifically for PostgreSQL and AOLserver.

I don't like TCL that much, but at least it works

[OT]

You sound like you should check out PL/PGSQL... just what you're after :-)

It's quite good, too...

This project needs a few people with lots of time, not many people with a little time.

My own opinion:

self-organized programmers are useful only to find the way or the direction when noone can see it. When the way is clearly seen and defined and the project is well managed and organized then the crowd will be alway beaten by the team. So, if Microsoft will decide to really address the really defined problem - they have their whole chances and open source should stay away and look for someting that Microsoft is either overlooked or ignored. Open source is effective to invent something conceptually new. Microsoft is professional to re-invent the wheel, by shaping it better and wrapping it nicier. No matter that it won't runoff-road, although :)

But on gborg itself you should have seen the Postgresql Replication project. Read this for a good technical summary of different types of replication and the basis for the new groundwork to implement robust, industrial strength replication for postgresql. This new project (for the 7.x series) is based on an academic implementation on the 6.x series, but looks like the 7.x core developers are working to forward port the implementation to the current series.

Mind you, there are already other replication solutions for postgresql, it's just that they aren't Good Enough(tm) for some db needs.

Heck, there's some serious technical discussion on what exactly to do on those pages, so it isn't on the Wishlist -- they're doing it already. (Is mysql's subselect being worked on or just on the Wishlist?)

But on gborg itself you should have seen the Postgresql Replication project. Read this for a good technical summary of different types of replication and the basis for the new groundwork to implement robust, industrial strength replication for postgresql. This new project (for the 7.x series) is based on an academic implementation on the 6.x series, but looks like the 7.x core developers are working to forward port the implementation to the current series.

Mind you, there are already other replication solutions for postgresql, it's just that they aren't Good Enough(tm) for some db needs.

Heck, there's some serious technical discussion on what exactly to do on those pages, so it isn't on the Wishlist -- they're doing it already. (Is mysql's subselect being worked on or just on the Wishlist?)

This inserts 1 million X's into a text field:

test=> insert into test (textcol) select repeat('x', 1000000);

This feature has been in PostgreSQL since April, 2001.

You are right, though,. PostgreSQL: 7 is really full-featured and powerful. However, I think that the drop column issue is a problem and so I do all my prototyping on MySQL.

It looks like one of the developers is currently working on that, starting with ALTER TABLE ALTER COLUMN SET/DROP NOT NULL, according to the To Do list.

I think most things you need to know are in:
PostgreSQL Interactive Documentation: Chapter 22. Procedural Languages

Make sure you install the procedural language first - even PL/pgSQL (covered in 22.2 and example 22-1 in that chapter).

There is a new beta period just been announced for the latest version here... It has a lot of feature improvements that the non-postgres fans moan about (i.e column drop)

> I thought Postgres would have too many limitations
> to be considered a healthy alternative to Oracle.
> eg 8k row sizes. Before people flame away. It has
> been a few years since I touched postgres, so this
> may be fixed by now.

Row sizes are now unlimited. See the Postgres
Limitations info page for more info.

I once felt the same way you did about Postgres, not enough features. I don't think it was until version 7.1 that postgres got outer joins (maybe only left/right outer joins, can't remember, but it was missing something like that). But recetly it has matured quite a bit and is a very nice database in my opinion. I use it for several applications, and I like it a lot.

There are still a couple of things that are a bit clumsy with PostGres (deleting a column, for instance), but I believe it has most major features you'd expect from an rdbms. At one of my jobs we use MSSQL7 for everything, and I've worked with it a lot. In my workings with recent versions of Postgres I have not stumbled across anything that I expected to be there from my experience with other DBs that was not in Postgres.

I'd give it another look if I were you. I trust Postgres a lot more now and I'm very pleased with it. It is working really well for me.

> I thought Postgres would have too many limitations
> to be considered a healthy alternative to Oracle.
> eg 8k row sizes. Before people flame away. It has
> been a few years since I touched postgres, so this
> may be fixed by now.

Row sizes are now unlimited. See the Postgres
Limitations info page for more info.

I once felt the same way you did about Postgres, not enough features. I don't think it was until version 7.1 that postgres got outer joins (maybe only left/right outer joins, can't remember, but it was missing something like that). But recetly it has matured quite a bit and is a very nice database in my opinion. I use it for several applications, and I like it a lot.

There are still a couple of things that are a bit clumsy with PostGres (deleting a column, for instance), but I believe it has most major features you'd expect from an rdbms. At one of my jobs we use MSSQL7 for everything, and I've worked with it a lot. In my workings with recent versions of Postgres I have not stumbled across anything that I expected to be there from my experience with other DBs that was not in Postgres.

I'd give it another look if I were you. I trust Postgres a lot more now and I'm very pleased with it. It is working really well for me.

If you do a Google search for "MySQL vs. PostgreSQL, you'll get a lot of hits. Here are a few that seem to be pretty informative (if not slightly dated):

here
here
here
here
here
here (not really a comparison, but read this article and the linked Postgres article for more info)

In my personal experience, Postgres has historically been the database more prepared for larger, more multi-threaded applications.

Obviously, there have been debates about which are faster in various different applications. To be honest, I have no hard data, nor have I stretched them either to their capacity, but as a user and casual developer, they are both fast enough for me not to notice.

What's inarguable exciting can be directly quoted from MySQL's own comparison of the two (listed above):

[B]oth products are continually evolving. We at MySQL AB and the PostgreSQL developers are both working on making our respective databases as good as possible, so we are both a serious alternative to any commercial database.

I am so furious with Postgres at this point.

I just had a client that needed a database larger than MySQL could capably handle (3 million records.) Since their budget was tight, I went ahead and recommended Postgres.

Oh! Whoops! Postgres doesn't run natively on Windows. This is COMPLETELY unacceptable. Their development environment and about half their servers, including the one allocated for this project, run on Windows. They went with Microsoft SQL Server, which was acceptable, but which ate almost a third of the budget for the project.

Many companies use a Windows development environment and/or Windows servers. It's stupid to ignore or shrug off the platform that runs at least 25% of the world's web servers and many more file, print, and email servers at businesses -- not to mention 95% of the clients out there, at least some of which are being used for local database development.

I cannot seriously recommend a product that ignores these numbers. Yeah, maybe they aren't out to make money, and so they don't care about losing "business" per se. But they lost my recommendation for projects that MySQL just can't handle. For me, now, it's either MySQL or a commercial database, and frankly, I am disgusted and disappointed to not have a Free alternative in between those two.

You wil be going through referential-integrity and stored-procedure withdrawl if you move from Oracle/SQL Server to MySQL. Try PostgreSQL instead.

Right now postges sends a message to the application saying, this is locked retry transaction, this is just not up to real enterprise levels.

This can happen if you use the serializable transaction isolation level. It is not the default - you have to specify that isolation level for your transaction. And as far as I know, it is the only way to implement that isolation level while still allowing any kind of concurrency. I have never had to re-run a transaction with PostgreSQL, because I use the default transaction isolation level. You might want to read the relevant documentation.

I have seen the "re-run your transaction" message - using MS SQL. Yes, "real" databases do do this. Again, it depends on the transaction isolation level.

Most of your other points don't even apply to PostgreSQL. PostgreSQL has log-based transactions. It does not use one file per table (I can't remember how many files there were in my last PostgreSQL DB, but it wasn't many and certainly wasn't one-per-table). Others have commented on your other points (except XML) so I won't repeat them.

You might want to take another look at PostgreSQL. Don't assume that it is the same as MySQL (if that is where your criticisms are coming from).

I haven't tried MySQL so I really can't comment on it.

I wondered the same thing! I love PostgreSQL and there's no reason it shouldn't be kicking the crap out of mySQL. But then it hit me.

I believe what made mySQL take off was when mySQL AB released easy to install packages for windows about the same time that PHP and Apache were becoming accessible to the Windows user. That way freelance web developers and new hackers were trying the stuff out at home and Windows and *nix users were programming on the same platform (PHP/mySQL on Apache).

Today, if you want to download PostgreSQL, you have two choices on their website. Tarball or RPM. Joe Clickey-Click doesn't want or need the trouble. All he wants to do is make a guestbook for his website.

Look at what Joe Clickey-Click sees when he would like to try out either
PostgreSQL or
mySQL.

MySQL is not yet an acceptable replacement for a well done installation of Oracle ... I want something others of used

You want this or this.

As of Postgres v6.2, time travel is no longer supported.

http://techdocs.postgresql.org/techdocs/settingupr serv.php