PostgreSQL 8.0 Released

Christopher Cashell writes "The PostgreSQL project has released version 8.0 of their well known Object-Relational Database. New features include: Win32 Native Server, Savepoints, Point-In-Time Recovery, Tablespaces, and lots more. Downloads are available via bittorrent for Unix/Linux, and the much anticipated Win32 version, or via ftp (use a mirror!)." (Here's the official announcement.)

Comments

Finally

[eugenelim]

An adequate replacement for MySQL on Windows. Can anyone say WAPP instead of LAMP?

Re:Finally

[redtux1]

nah - LAPP sounds a lot better

Re:Finally

[Anonymous Coward]

Even more important than being a replacement for MySQL, in many situations it can be a replacement for MSSQL!

Re:Finally

[vigilology]

Can anyone say WAPP instead of LAMP?

No.

Re:Finally

[Anonymous Coward]

I call it: Good Free software meets crappy closed source OS.

I'll stick with Linux, thank you very much. :-P

Also you'll probably find for 90% of the tasks (and probably 100% of the tasks you, I and most people do) the advantage of Postgresql over MySQL is... well...

nothing.

Not that there is nothing wrong with PostgreSQL, mind you, it just seems that lots of slashdotters have irrational belief that mysql is just fundamentally inferior in some strange way. That's something I don't understand.

I figure it's similar to how people beleive that OS X is the best thing to ever happen to modern computing since Windows 95, even though a vast majority of OS X lovers never used it for anything other then a desktop or a toy for any length of time.

But whatever. To each their own.

Re:Finally

[DrSkwid]

it just seems that lots of slashdotters have irrational belief that mysql is just fundamentally inferior in some strange way. That's something I don't understand.

You have evaluated them both in production environments where data integrity is paramount and developer time is expensive ?

Re:Finally

[Anonymous Coward]

If you don't get any advantage by using pgsql over mysql you might be better off using sqlite. I am serious about that, for simple operations sqlite is usually even faster than mysql.

Re:Finally

[GuyWithLag]

In my last project I was forced to use MySQL due to hosting issues. I still believe that MySQL is the Access of the OSS dtabases (in a non-GUI way).

Re:Finally

[the eric conspiracy]

I still believe that MySQL is the Access of the OSS dtabases

Perhaps, except that it is n* times better. I've run some pretty intense, badly-written db-abusive e-commerce sites (we are talking $5 million per year in cash flow) using MySQL without problems.

* Footnote: n = number of protons in the universe, around 10^40.

Re:Finally

[pebs]

Saying MySQL is better than Access is not saying much. MySQL may be excellent when it meets the requirements, but there are a lot of cases where it simply does not.

Re:Finally

[moderators_are_w*nke]

I figure it's similar to how people beleive that OS X is the best thing to ever happen to modern computing since Windows 95, even though a vast majority of OS X lovers never used it for anything other then a desktop or a toy for any length of time.

Well, OS X is great. Its a UNIX OS with a decent GUI. Not often seen

Feel free to mod offtopic

Re:Finally

[Anonymous Coward]

Maybe someday you will learn about data integrity. And what features real relational databases have, and how to use them. Then you will understand.

It just seems that lots of slashdotters have an irrational belief that mysql is just as good as any other database.

Re:Finally

[dsginter]

Can anyone say WAPP instead of LAMP?

How about just saying "thanks"? I looked for a "donations" link on the Postgres page and they don't even have one.

Kudos to the Postgres team. Thank you.

Re:Finally

[fimbulvetr]

Seems that finance.yahoo.com has.

Re:Finally

[snorklewacker]

> I looked for a "donations" link on the Postgres page and they don't even have one.

It's there, under "support us", middle column at the bottom. http://www.postgresql.org/about/donate

Re:Finally

[HeadDown]

An adequate replacement for MySQL on Windows has been available for some time now: Ingres.

Re:Finally

[kpharmer]

>> I still believe that MySQL is the Access of the OSS dtabases

> Perhaps, except that it is n* times better. I've run some pretty intense, badly-written db-abusive
> e-commerce sites (we are talking $5 million per year in cash flow) using MySQL without problems.

Hmmm, but wouldn't it be better to use a database that doesn't silently truncate numbers so that you could be a $50 million per year company instead?

Re:Finally

[DrSkwid]

Prior to its switch to MySQL in 2000, Yahoo! Finance managed its database needs through homegrown flat files and Berkeley DB databases. But those solutions proved to be inflexible and not scalable enough for their needs.

from this one can imagine that the needs of finance.yahoo is returning rows based on a few joins

fair play to mysql, it does return rows quickly

Re:Finally

[davegaramond]

Unfortunately, many (quite a large percentage) of applications running MySQL *do* need MySQL features that don't exist in SQLite. Full text indexing, for one, which is utilized by many Forum web app.

Re:Finally

[davegaramond]

I also suspect the master data is stored in a different place (either in another MySQL machine or other DB). So the MySQL machines that serve finance.yahoo.com are basically for read-only simple queries (but involve lots of rows). MySQL is perfect for this.

And the data is not really mission-critical. It's just a bunch of numbers (exchange rates, stock prices) aggregated from various places. Database crash? Just repopulate by querying the various data sources.

Re:Finally

[sedna]

Interesting enough it seems like every single person on slashdot is running such environments. How many Extremely Important Mission Critical Database Systems are there out there? I am working with data-management at a department in a Large university and we don't have many. Perhaps the economy student system. The rest is run on different DBs without any problems. Heck, our Course database ran on Access until recently. Not well though.

Where real-world database applications is used around me, it is way more important to have good support than the extreme data integrity PG/Oracle/MSsql etc have. If someone here needs to set up a database for the museum collections, 3d-model output, Student statistics, web CMS, lab-results, instrument output etc. etc. etc. they often need a company to talk to. Mysql i perfect in that you get a system which handles these tasks good enough for free, but with an option to by support from the company. For a vast majority of systems out there and every single one I been in contact with, backup solutions are good enough for keeping the data integrity intact.

Re:Finally

[llefler]

Even more important than being a replacement for MySQL, in many situations it can be a replacement for MSSQL!

Case sensitivity is one of the big factors why Postgres won't be replacing MSSQL any time soon. That little annoyance requires restructuring tables or changing all your queries. Take the sample Northwind database as an example:

Tablename: Orders

Fields: OrderID, CustomerID, EmployeID, etc.

Now, a MSSQL SQL statement of:

Select OrderID From Orders
Where CustomerID = 'xxxxx'

Works fine under MSSQL. Under Postgres you either have to change it to:

Select "OrderID" From "Orders"
Where "CustomerID" = 'xxxxx'

or change the table structure

Tablename: orders

Fields: orderid, customerid, employeid, etc.

Once you've done that and recertified all of your apps, any savings from Postgres are long gone.

Firebird is a much more reasonable OS alternative to MSSQL.

Re:Finally

[sedna]

To late in the evening. Please bare with all spelling errors. Shit, I even used preview...

I meant the main student grade handling and economy systems at the university in paragraph one.

Mysql is perfect
An option to buy support in the second

Re:Finally

[fimbulvetr]

I also suspect the master data is stored in a different place
Really, I don't mean to be mean, but of course it is. That's one of the purposes of replication.

MySQL is perfect for this. I agree, I've had a tremendous amount of success with this type of scenario.

And the data is not really mission-critical
So it should be no problem for Jeremy Zawodny to convince Senior Management of this newfound piece of information? I'd have to say he or anyone else would have a really friggen hard time convincing anyone at yahoo (and their finance/my yahoo customers) that the availability of their data is _not_ mission critical.

Database crash? Just repopulate by querying the various data sources.
Though that is entirely possible, I highly doubt that is their disaster recovery plan. Besides, even if they were using Oracle or MSSQL, this plan would still work.

Re:Finally

[Anonymous Coward]

Not true:
Table "public.news"
Column
-----------
news_id
news_date
news_pic

SELECT * FROM NEWS WHERE NEWS_ID < 10;
news_id | news_date | news_pic
2 | 2005-01-09 17:36:07 |

Re:Finally

[DrSkwid]

When one of my customer buys something I need to know that the record of that purchase has been written to disk.

It's not fun fielding calls from our customers wanting to know where there advertising credits have gone.

WAL (Write Ahead Logging) is really a pre-requisite for anything where cash is involved and is part of Postgresql.

WAL is now available in the mysql InnoDB table type which has only been included by default in binaries since 4.0 and has to be enabled manually if you build from source.

Re:Finally

[kevpie]

How about MSSQL Select [OrderID] FROM [Orders] WHERE [CustomerID] = 'xxxxx' PGSQL Selct "OrderID" FROM "Orders" WHERE "CustomerID" = 'xxxxx' If you could tell MSSQL to quote all entities using their []'s you could more easily transform it. Actually isn't the case sensitivity in MSSQL irrelivant anyways?

Re:Finally

[Frank T. Lofaro Jr.]

Linux + KDE is better.

Plus Linux is the native platform for PostgreSQL. I think it may be the native platform for Apache also.

Re:Finally

[Anonymous Coward]

I had absolutely no problem with this example, with or without quotes. What bizarre configuration setting did you change in your setup?

Re:Finally

[kevpie]

How about MSSQL
Select [OrderID] FROM [Orders] WHERE [CustomerID] = 'xxxxx'

PGSQL
Select "OrderID" FROM "Orders" WHERE "CustomerID" = 'xxxxx'

If you could tell MSSQL to quote all entities using their []'s you could more easily transform it.

Actually isn't the case sensitivity in MSSQL irrelivant anyways?

Re:Finally

[llefler]

MSSQL is truly case insensitive. Postgres converts the SQL statement to lowercase and then does a case-sensitive compare to the tables/fields, and possibly data.

Changing the SQL statements on the MSSQL side isn't an option, unless you are planning on migratine FROM Postgres. The problem is that you have to modify current production SQL/entities to move from MSSQL to Postgres. And the required regression testing will remove any cost advantage for existing MSSQL customers after about a week.

It's very hard to argue OS semantics with management. Once you take the cost advantage out of the equation, you've pretty much lost the battle.

Re:Finally

[elemental23]

Linux + KDE is better.

I disagree.[1] Isn't it funny how opinions work?

Plus Linux is the native platform for PostgreSQL. I think it may be the native platform for Apache also.

Sure, if by "Linux" you mean "Unix".

Of course you also have to keep in mind that OS X is just as much Unix as Linux is (ie, not officially, but for all intents and purposes). Anyway, I'm not sure what your point is, as no one mentioned native platforms, running PostgreSQL on OS X, or Apache at all.

[1] Assuming we're talking about a desktop machine. For servers, I still disagree, but only because the Linux servers I admin don't have X installed, much less KDE.

Re:Finally

[einhverfr]

Also you'll probably find for 90% of the tasks (and probably 100% of the tasks you, I and most people do) the advantage of Postgresql over MySQL is... well...

Except accounting, ecommerce, etc. where the data integrity is extremely important.

MySQL is good for mayn things-- but it will not ever likely be as robust or capable of maintaining meaningful and accurage information as PostgreSQL.

Re:Finally

[Anonymous Coward]

YOu can still say LAMP - Linux Apache Middleware PostgreSQL. Building a brighter lamp!

Re:Finally

[moderators_are_w*nke]

I also disagree, and I'm using KDE right now (on FreeBSD as opposed to Linux, but lets not get into that). KDE is pretty, I'll give it that, but its slow compared to MacOS 10.3. Its bundled applications are not on a level with the Mac either. Kmail doesn't feel as good to use as Mac mail, and nothing can touch iCal or iTunes IMHO. Also, in being a unix system I can compile and use most Linux apps anyway should I want to.

Re:Finally

[Doomdark]

Full text indexing, for one, which is utilized by many Forum web app.

.... which is pretty unfortunate, since I feel strongly that relational DBs are the wrong place to put full text indexing functionality in. There are very good text indexing packages (like Lucene) that do very good job of indexing any and all textual data (and with tweaking many semi-textual ones); I prefer to have modular approach instead of creating a dependency to a specific RDBMS. Although I do understand that it may be convenient to just use whatever DB provides, there is still some value for using the right tool for the job. And doing so reduces dependencies to your specific DB engine.

Re:Finally

[phats garage]

My current operating theory is that "there is always a race condition."

This was born out by a recent experience at an atm machine, I entered my account information and withdrawal request, the atm did everything required of it except dispense the money. Of course my account was decreased by the amount of my withdrawal request. The last act of the atm in that transaction was to crash.

Sure, whether its an actual race condition is up to the computer science weenies, but no manner of before or after image journaling can protect integrity of the last transaction before a crash.

Its much like the joke, two guys were in a store and a third walks in with a gun drawn to rob the store and the customers, the first guy says to the second guy "heres that $20 I owe you."

Re:Finally

[Anonymous Coward]

Under Postgres you either have to change it to:
Select "OrderID" From "Orders" Where "CustomerID" = 'xxxxx'

You don't have to.

Select OrderID From Orders Where CustomerID = 'xxxxx';

or

select orderid from orders where customerid = 'xxxxx';

or

SELECT ORDERID FROM ORDERS WHERE CUSTOMERID = 'xxxxx';

all works fine under PostgreSQL.

Re:Finally

[Anonymous Coward]

You can buy support for PostgreSQL as well.

There are many companies selling PostgreSQL support, even for 8.0 already.
To name one company which sells the support... My company.

Re:Finally

[jadavis]

I haven't heard that before. Could you point me to some resources so I can learn more?

Re:Finally

[ckaminski]

Who the hell actually uses such syntax? The only place I've seen that crap is Access queries.

Select orderid from orders where customerid = 'xxxxx'.

WTF?

Re:Finally

[ckaminski]

I'm sorry, all Yahoo! Finance data is delayed at least 15 minutes. Anyone who depends on this data to make money is going to get a real-time feed from Schwab, Ameritrade or E*Trade, and NOT rely on Yahoo!.

There's data you can't get on Yahoo Finance.

Re:Finally

[mabinogi]

which syntax are you referring to?

The [ ] quoting? or the select query itself?

It looks like you're asking if anyone uses SQL.
That's a bit like asking if anyone uses English. Although, on Slashdot that's probably a valid question.

Re:Finally

[bdbafh]

use bind variables, you incompetent clod!

Re:Finally

[ckaminski]

The bracket quoting. [] I've used many a third party SQL based tool, and done much SQL myself, I've only seen the quoting in Access databases.

Re:Finally

[fimbulvetr]

I'm not trying to argue whether or not they can make money off the data, or even if they need the data in real time.
Though I'm a my yahoo user (flights, etc), I don't use the finance area. I don't know what those people (people who do use finance.yahoo.com) expect, but I can tell you one thing:

There are people using finance.yahoo.com, and those people would be upset if their service/data was not there, corrupt, wrong or shutdown.

Re:Finally

[Doomdark]

One good place to start might be Lucene's home page:
http://jakarta.apache.org/lucene/docs/index.html

Although Lucene started as a Java search (or indexing) engine there are ports to other languages; and design seems quite clean. As the engine it's pretty amazing (as well as free+open). It does not include spidering component, but there are projects that add those on top of Lucene engine. Plus, when using DBs one often does not need a spider: you can directly index content that is primarily stored in DB and just refer to that from the search index.

I implemented a simple (but powerful enough) search functionality for an enterprise-wide CMS using Lucene; doing that integration was probably the simplest part of the whole project. And users seem quite happy with the results (especially compared to the old system that was replaced).

Re:Finally

[jadavis]

Thanks.

What would you say is the primary reason to not put the full text search in an RDBMS?

Great

[Anonymous Coward]

Great, but why should I use PostgreSQL when I already have a database, you might ask? Here's why.

Re:Great

[Anonymous Coward]

More than that... It keeps getting better and better - Mysql became fast first and features later, Postgres took the other path.. I started using pgsql, because I picked the first "sql" thingy in the list (I was young and foolish , but fortune favours that variety of fools).

Just for once - I wish Postgresql had an "enterprise" edition, I could buy and bypass IT regulations.

Re:Great

[Anonymous Coward]

6
6.1
5
5.1
5.2
4.3
3.1
3.2
3.6
2.4
1.4
1.5
1.6
1.7
1.8
1.10
1.12

That is a list of all the "gotchas" that are purely opinion of the author or have been fixed in dated releases. This is *not* a bug list. It is a whinny list about compliance with standards that MySQL has no duty to comply with.

Re:Great

[McFarlane]

does noone use mysql with InnoDB tables and sql_mode='TRADITIONAL'

??

http://dev.mysql.com/doc/mysql/en/Server_SQL_mod e. html

Re:Great

[pivo]

Not to mention, MySQL is not Open Source.

Or, even if it is technically open source, if you use it in any commercial application you have to pay. You're even supposed to pay for each copy of MySQL used by developers of commercial applications on their development machines. That's not the kind of Open Source software I'm interested in.

Re:Great

[Anonymous Coward]

It is a whinny list about compliance with standards that MySQL has no duty to comply with.

You mean like the SQL standard, the one MySQL takes its name from? Nice blinders ya got there.

Re:Great

[jaseuk]

MySQL is GPLed, GPL rules apply. So if you wanted to include MySQL as part of your product you can, as long as you provide source / modifications as usual.

If you wish to alter or integrate mysql and not use the GPL terms then you can then purchase a license to do this.

Re:Great

[ultranova]

Not to mention, MySQL is not Open Source.

From http://www.mysql.com/company/legal/licensing/opens ource-license.html

Our software is 100% GPL (General Public License); if yours is 100% GPL compliant, then you have no obligation to pay us for the licenses.

As for the link you provided, it seems to be a complaint about the habit of people to do business with proven, rather than unknown, vendors. It does mention MySQL, and says that it's license pages (not actual licenses, but pages talking about licensing) "suggest" that you should get a commercial license for in-house software involving MySQL. It also implies that the GPL is vulnerable to "radical interpretation", whatever that means, and hints of ominous things that might happen if open source companies are acquired by HP, IBM or Oracle - not to mention the secret desire of RedHat: "To gently push me into a network of interlocking software and hardware components all under the same brand name" - does RedHat even make hardware ?

In short, genral FUD piece.

Or, even if it is technically open source, if you use it in any commercial application you have to pay.

It is open source, on the grounds that the source code is available. It is free open source, on the grounds that the source code is available under an open source license (GPL). As usual, you cannot join GPL'd and proprietary code, unless you manage to get a license that allows this from the copyright holder of the GPL'd code.

Please explain this technicality you speak of.

You're even supposed to pay for each copy of MySQL used by developers of commercial applications on their development machines.

If you redistribute MySQL in-house in a way that violates the GPL, you will need to get a commercial license, since you cannot use the GPL since you just violated it. How is this different from any other product ?

That's not the kind of Open Source software I'm interested in.

Fine, don't use it then. But please don't spread FUD about it.

Re:Great

[Anonymous Coward]

I hate to break it to you, but SQL is not a standard; SQL-92 is. If it were MySQL-92, you'd have a point. But the people behind MySQL design it specifically to NOT fully follow SQL-92. Do some research.

Re:Great

[ckaminski]

I'll sell you Enterprise PostgreSQL Support. :-)

plperl

[michaelhood]

One of the most exciting features of 8.0 is plperl, their Perl-based server side language, allowing for triggers and persistent storage. On another note, I wish MySQL would catch up to PgSQL. Even if you don't like MySQL, the competition keeps them innovating. If PgSQL is light years ahead, what's pushing them?

Re:plperl

[mukund]

What's pushing PostgreSQL to keep innovating? The competition obviously. Oracle.

Re:plperl

[Nohea]

Oracle and MS-SQL are pushing them...

That's what the point-in-time recovery and tablespaces are about.

Re:plperl

[Anonymous Coward]

PostgreSQL isn't competing against MySql. They are competing against Oracle.

Re:plperl

[Anonymous Coward]

PostgreSQL isn't competing against MySql. They are competing against Oracle.

Who do you mean by "they"? PostgreSQL is competing against Oracle indeed, but MySQL is competing against SQLite and Berkley DB.

Re:plperl

[StrawberryFrog]

One of the most exciting features of 8.0 is plperl, their Perl-based server side language

I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or MS SQL.

Re:plperl

[arkanes]

If the SQL standard weren't woefully inadequate, inconsistent, unclear, and primitive, then maybe we wouldn't have people fixing it. Open standards are great, but when the standard sucks it's not real usefull. It's not a big step away from database independence because there's never been any to begin with.

Re:plperl

[Anonymous Coward]

Er, so don't use plperl!

Re:plperl

[AhBeeDoi]

One of the most exciting features of 8.0 is plperl, their Perl-based server side language

I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or MS SQL.

Hardly.

First of all, SQL is supposed to be a sub-language used by other languages to access an RDMS. PL/Perl is a server side procedural language which allows you to write PgSQL functions in Perl. How standard is PL/SQL for Oracle? It's proprietary. In fact, Oracle and MS SQL are not entirely compliant with SQL 92 as they have their own extensions.

Re:plperl

[Anonymous Coward]

Even more exciting for some of us is the availability of Ruby and Python as the stored-procedure language.

I love perl for quick-hits and one-liners but for all other scripts, I prefer Ruby.

The combination of Ruby 1.8.2 + ActiveRecord + Postgresql is really hard to beat in terms of programmer productivity. And this is coming from someone who coded professionally in assembly (motorola chips), c, c++ (gcc & msvc), cobol, dbase, delphi, foxpro, java, perl, etc.

RubyOnRails is receiving a lot of hype and attention but I think ActiveRecord (a component of RubyOnRails) is what truly shines. It would be nice to see ActiveRecord ported to other languages but I doubt it'll happen anytime soon.

Once you try Ruby + ActiveRecord to access databases, anything else will seem like coding in c++ without the benefit of smartpointers.

Nothing is as easy as ActiveRecord in the world of object-relational mapping.

Re:plperl

[justins]

I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that.

I see what you're trying to say, but you're missing what's potentially good about this. It's all open-source, mysql or maxdb or whatever are free to implement it and the code is already there, unlike PL/SQL or Transact-SQL.

As a practical matter PosgreSQL's feature set is far enough ahead of even max db that it couldn't work the same way and be truly compatible, but hopefully you see what I mean.

Re:plperl

[Anonymous Coward]

What do you mean?

Re:plperl

[Anonymous Coward]

He means it's pretty damn obvious that the 'they' was Postgres, not MySQL.

Re:plperl

[Anonymous Coward]

Being able to move from one database to another has relevance primarily in the world of proprietary software, where you really want to avoid vendor lock-in. PostgreSQL is free software, so it's hardly like they've got you over a barrel. PostgreSQL specific programming will do wonders for the performance, correctness, and long term maintainability of your program. Why should every application side program redundantly implement functions that can be embedded in the database?

Re:plperl

[hey!]

Hah! Try writing stored procedure that ports from Oracle to Microsoft SQL Server. The procedural extensions to SQL are extremely non-standard.

Oracle's PL/SQL is not bad; those who say otherwise have never used Microsoft's SQL-Server implementation of T-SQL. I could swallow a handful of punch card dots and shit a better parser. But a standard language like Perl would be a godsend.

If I recall, you can use Java as a stored procedure language in Postgres. This is terrific, because you can also do so in Oracle. Not only does this provide the ability to create portable stored procedures and triggers between the platforms, Java is going to perform much better than native trigger languages on anything complex. I've used in on Oracle projects to do complicated mathematical processing that would have been extremely painful

Re:plperl

[kpharmer]

> I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another
> with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or
> MS SQL.

Well, every other mature dbms vendor is convinced, as are most major application vendors: the ability to write simple procedures can be of enormous value to some applications. And as long as you don't go nuts, it's typically is easy to port as well.

Using a stored procedure language you can:
1. only expose views (rather than tables), even allowing writes to views with joins via an 'instead of' trigger + stored procedure.

2. completely encapsulate tables & views behind stored procedures. This provides enormous benefits to rapidly-evolving applications, since the dba can make whatever changes are needed on the backend (for performance, security, functionality, etc) in parallel to application developers on the application layer.

3. automatically maintain multiple copies of data: when using recursive data structures you often need to keep a second copy for massive scans. The stored procedure + trigger can easily manage this for you. You can likewise use this method to automatically maintain a transaction history table. This can be much more reliably & productively than you'd ever do in an application.

4. encapsulate logic for use within queries. Need to convert ip addresses between strings for presentation & integers for storage? A simple stored procedure function can do that job for you. And the value of doing it in the database, is that you won't have to write application logic around simple adhoc queries.

etc, etc, etc, etc.

Re:plperl

[danharan]

Quite right, it's supposed to be a standard. Programs with stored procedures tend to be less maintainable despite what its advocates say.

They have their place though. For certain operations where performance is critical, it makes sense to use a SP. Problem is we often assume certain parts will be performance hazards before actually benchmarking.

Re:plperl

[StrawberryFrog]

In fact, Oracle and MS SQL are not entirely compliant with SQL 92 as they have their own extensions.

That's what I said, as in "This is a big step away from that. Much like the features you'd find in Oracle or MS SQL."

Perhaps I should have drool-proofed it and written "This is a big step away from standardisation. It is reminicent of the features you'd find in Oracle or MS SQL that lead one down the proprietary path."

Re:plperl

[Anonymous Coward]

Then he/she should have written "It", not "They". "They" reads like both PostgreSQL and MySQL are competing against Oracle. The other AC read it that way, and so did I. It wasn't fucking "obvious" at all that "They" meant only PostgreSQL. Since English obviously isn't your first language, let me parse the original expression for you:

PostgreSQL isn't competing against MySql. They are competing against Oracle.

Note the phrase "PostgreSQL isn't". The "isn't" means that "PostgreSQL" is being referred to in the singular. Now, note the "They are". That is plural. That means PostgreSQL and something else, since PostgreSQL is being referred to in the singular. The only other thing around is MySql. Therefore, the second sentence means "PostgrSQL and MySQL are competing against Oracle." Your interpretation that "They" meant only PostgreSQL, and not both SQLs, is wrong. You are the one with the problem. It's people like you who are causing the English language to go to shit.

Re:plperl

[DarkTempes]

nah, everyone else got your meaning ;) someone apparently just doesn't know how to read =)

Re:plperl

[alannon]

There actually is a Standard SQL server-side, procedural language. It's called PSM (Persistant Stored Modules). It looks fairly similar to Oracle's PL/SQL, but seems to be more deliberately designed. It's an addendum to SQL-92 and standard in SLQ-3. Some information here. Unfortunately, the only database I've personally used that implements it is Frontbase. Frontbase is an interesting creature, since it offers no syntax or language manual itself. According to them, the appropriate manual is simply the "Guide to SQL Standard, 4th edition", available here, for example. They document a few differences between the standard and their database on their web site, and anything else that doesn't match is considered a bug. I find something very appealing about that design philosophy.

Re:plperl

[HumanTorch]

If PgSQL is light years ahead, what's pushing them?

Oracle?

Re:plperl

[einhverfr]

First of all, SQL is supposed to be a sub-language used by other languages to access an RDMS. PL/Perl is a server side procedural language which allows you to write PgSQL functions in Perl. How standard is PL/SQL for Oracle? It's proprietary. In fact, Oracle and MS SQL are not entirely compliant with SQL 92 as they have their own extensions.

Way too many people go coding stored procedures without asking themselves what they are going to do if they need to move to another database manager. Part of the problem is that there is no standard way to call a stored procedure, so changing databases often involved porting the application as well.

I use stored procedures a lot in my databases, but I never allow the applications to call them directly. This ensures the portability of the application. Instead, the stored procedures are wrapped in views, which allow the application not to know anything about the stored procedure in question. This allows the RDBMS to do what it needs to do: Store, manage, and present the information as needed.

Pl/Perl is a great benefit.for this sort of work, but it can also be badly misused. While I look forward to it, it will be interesting to see how it is abused.

Re:plperl

[MarkLewis]

No offense to the MySQL developers, but MySQL isn't really pushing PostgreSQL internals development these days. Oracle is.

What do you think features such as tablespaces, point-in-time recovery, transaction savepoints and the like are aimed at? It's aimed at enterprise customers who would otherwise be considering Oracle/DB2 (or maybe SQL Server, although I hate to include it in the list because it's still just a very well-marketed toy).

Re:plperl

[Anonymous Coward]

I am the AC who asked "Who do you mean by 'they'?" and I'm glad that there are still literate people on Slashdot. You have perfectly explained my reasoning.

Re:plperl

[Anonymous Coward]

Every stored procedure language in a SQL database is a step away from SQL.

SQL is really just "standardized" for queries.

Basically, you have brought up a red herring argument, that is really more along the lines of:

"Should business logic be in the database (stored procs, triggers, referential integrity, db security), or done purely at the application level?"

I'm of the "put the data integrity stuff in the database", to minimize the potential for an inadvertent dba (or user) doing a "DELETE FROM MYTABLE" from causing massive amounts of havoc and disaster in your database.

Re:plperl

[Doctor Memory]

Way too many people go coding stored procedures without asking themselves what they are going to do if they need to move to another database manager. Part of the problem is that there is no standard way to call a stored procedure, so changing databases often involved porting the application as well.

That's a shame, since writing wrapper code or an isolation layer or interface library has been a Best Practice since the late 70s. You shouldn't call a stored procedure directly, you should always call a library routine that calls the stored procedure for you.

Unfortunately, now the pendulum's swung the other way and we find ourselves with staggering persistence layers like EJB that wind up making it almost impossible to use a database's native features. And we have developers who insist on coding applications so the the back-end database can be changed with no more work than modifying a configuration file. Sounds nice until you realize you have to give up a lot of performance enhancements (since they're almost always SQL extensions of some sort) for this dubious ability. And what do you think is more likely to happen: a user's going to complain about performance, or a sudden shift in the company's fortunes is going to migrate a move to/from Oracle?

Re:plperl

[cpeterso]

writing wrapper code or an isolation layer or interface library has been a Best Practice since the late 70s. You shouldn't call a stored procedure directly, you should always call a library routine that calls the stored procedure for you.

Writing wrapper code to abstract away stored procedure calls is only half the problem. The stored procedures trap your "business logic" in non-portable code!

Re:plperl

[einhverfr]

Writing wrapper code to abstract away stored procedure calls is only half the problem. The stored procedures trap your "business logic" in non-portable code!

True. but there is another aspect to this as well. That is that if you include everything and the kitchen sink in your database manager, then you add load and latency to something which may be a bottle neck for your app.

In general, the best approach, IMO, is to divide up data logic and application logic along lines based on what is helpful in terms of widely understanding (presentation) or properly managing the data. Then the applications do their specific logic without burdening the database.

This is somewhere in the middle. If you want to change backends, you have some work to do (it is not a simple drop-in process) but it can be done and is not as bad as if all the business logic was stored there.

Additionally, you get the best balance of speed and flexibility.

Re:plperl

[dbullock]

They don't need MySQL to compete with them.

They have Oracle and to some degree MS-SQL as competition.

Re:plperl

[jadavis]

That's true, and I agree, but the larger issue is this:

It's a feature. The selection of standard procedural languages is inadequate (or perhaps absent is a better word). But procedural languages happen to be useful, so PostgreSQL provides as many as it can (I think they even have plphp now...).

When there's no standard, that doesn't mean you do nothing. PostgreSQL is doing more than a lot of other systems! They have an extensible procedural language system allowing maximum flexibility. That way, when something does become a reasonable standard, they will be ahead of the pack.

Magical upgrade needed

[inflex]

Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!

Converting 200Gb of data is not something you want to try do without downtime.

All that said, it's good to see PostgreSQL always improving. Good one guys.

Re:Magical upgrade needed

[Dom2]

You need to look at setting up a second system and running slony-I.

-Dom

Re:Magical upgrade needed

[CaptainZapp]

Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!

Even though I can feel your pain I' d like to ask a question: Do you know of any database (free or commercial) that supports such a feature (auto)magically?

See, I thought not.

Migrating a data server to a new major release is a pain by definiton and while you might be able to trick the users into believing that it never goes down you ultimately will have to apply some more or less vicious hack (i.e. warm standby, while you're upgrading the primary db) to achieve that.

In a replicated environment the DBAs pains and hassles grow exponentially if no or very minimal downtime is permissable.

Re:Magical upgrade needed

[mborland]

For smaller databases (the one I'm working with is in the 200MB range, so much smaller than what you're talking about), I haven't noticed any bumps when testing conversion to 8.0 (at least the betas). Data/schema, queries, etc. all were loaded in just fine, and apps so far have behaved as though nothing has changed. That all said, I won't begin to roll this into production until Summer due to app testing.

I am curious about the query optimizer and understanding what sort of improvements may have been made on it.

Re:Magical upgrade needed

[Anonymous Coward]

I dunno, I've never migrated anything with MySQL, you just install the new RPM's. I've gone from 3.x to 4.0 to 4.1 just by "rpm -Uvh mysql*". There's also no need to do regular maintenance with MySQL, which is a bit of a culture shock when you move to the much more finicky Postgres.

Re:Magical upgrade needed

[TargetBoy]

MS SQL Server 7.0 => 8.0 (aka 2000) did this just fine.

Re:Magical upgrade needed

[DrSkwid]

Do you know of any database (free or commercial) that supports such a feature (auto)magically?

yes : this one

see http://slony.info/ as the other poster mentioned

Re:Magical upgrade needed

[pizza_milkshake]

yes, and task scheduling used to be done manually, but now the kernel does a much better job than any human ever could. "it's supposed to suck" is not a good argument.

Re:Magical upgrade needed

[CaptainZapp]

I said it's complex. I never said it's supposed to suck. I thank you for not putting words into my mouth, which I never said.

In addition: IMO Microsofts approach to mask potentially incredible complex systems - like networks or databases - by nifty point and click interfaces and imbecile paperclips dîd far more damage to enterprise computing then any archaic scripting interface.

Re:Magical upgrade needed

[davegaramond]

Postgres has the auto vacuum daemon that does this manually. It's not integrated/activated yet by default, due to beta status. It'll probably be, in 8.1. I agree it's a bit late because most other DBs (InnoDB, Firebird) do this for you in the background.

Re:Magical upgrade needed

[Anonymous Coward]

True, but there's 3 orders of magnitude difference in size between your database and his. How long was your downtime to dump and re-load? Multiply by 1000 and how long do you get?

Re:Magical upgrade needed

[Anonymous Coward]

Converting 200Gb of data is not something you want to try do without downtime.

I'm doing this now with 10X more data than that.

The idea is

1. First remove half your front-end machines from the load balancing pool.
2. Begin logging all transactions in the old-database so you know what to replay to the new one.
3. Upgrade our hot-standby database to 8.X
4. Upgrade the client software on the half the front-end machines you removed from the load balancing pool, and point them at the hot-standby database.
5. Remove the old front-end machines from the load balancing pool and re-add the upgraded ones pointing at the backup database.
6. Replay the transactions you logged since step 2, watching carefully for any constraints that may be violated because of changes that may have happened between those times (or, if you can, simply disable updates between steps 2 and here).
7. Now upgrade the other half of the machines and upgrade the original database server. Now the original database server is the hot-standby.

You do have enough hardware & redundancy in your design to handle failover, don't you?

If so, a database upgrade is no different than a power-supply catching fire. Your processes should handle it.

Re:Magical upgrade needed

[dodobh]

Run both postmasters in parallel, and have slony feed from 7.x to 8.0 (you need to upgrade anyway. 7.2.x has data corruption issues).

Slony is your friend.

Re:Magical upgrade needed

[Sxooter]

Unfortunately, slony is only compatible with versions as far back as 7.3 (because slony relies on schemas, a feature first added in tahat version.)

Re:Magical upgrade needed

[kashani]

According to slony's website, they only support Postgres 7.3.x and up. The original poster is using 7.2.4.

kashani

Re:Magical upgrade needed

[Anonymous Coward]

Yep. But there are some huge reasons for this. One of the biggest are that that there weren't that many new features in the core of the system. Almost only in the peripheral parts(compare migration from 6.x -7.0 whitch was a b.i.t.c.h.). MS SQL is, IMHO, far behind postgres concerning core database features that is actually usable. Yes, all this functionality has been coming with a quite considerable performance penalty. But in later years, things has been speeding up considerably, making it a viable option. It is actually not that difficult to migrate postgres databases...It's just different. Ok. Maybe a wee bit more difficult. And, yes, i almost forgot, it's free.

Re:Magical upgrade needed

[einhverfr]

Yes. and I need a magical upgrade utility to upgrade my MS SQL 6.5 server to MS SQL 2005 without missing a single beat of downtime too....

Or Oracle 8i to Oracle 10i

Or MySQL 2.5 to 4.1

With the development of Slony, you will be able to do most of this as far back as 7.3. But 7.2 is old and had a number of diferences in system catalogs (why most replication solutions don't support it). You could probably even do this on the same system by using a different port, but I don't recommend it.

Re:Magical upgrade needed

[jadavis]

I'll second this. Try out Slony-I. That's one of the main things that Slony was designed for.

However, I encourage you to verify that your application properly works on 8.0 first; 7.2 was 3 releases ago.

Re:Magical upgrade needed

[Anonymous Coward]

Lets get this straight :

you want to convert 200Gb of data *without testing*,
and do it over the top of the existing data?

If not how much worse is (current):
dump 7.4, import to 8.0
than (proposed):
copy 7.4, convert copy to 8.0

Re:Magical upgrade needed

[bdbafh]

Oracle. read the fine readme. download patch uncompress shutdown apply patchset rebuild dictionary $sqlplus /nolog SQL> spool catpatch.lst SQL> connect / as sysdba SQL> shutdown immediate SQL> startup migrate SQL> @?/rdbms/admin/catpatch.sql; SQL> shutdown immediate SQL> startup restrict SQL> @?/rdbms/admin/utlrp; SQL> alter system disable restricted session; SQL> spool off SQL> quit done. profit.

Re:Magical upgrade needed

[DrSkwid]

It might not be that important to you but you forgot

step 1.

send oracle $3000 per 5 users per database

Re:Magical upgrade needed

[rtaylor]

I've been there. Around 160GB upgraded from 7.2.4 through to 7.4, then a hardware upgrade from 7.4 to 7.4 with about 20 minutes downtime in total (for both migrations).

Check out eRServer and Slony replication methods. They have pretty heavy requirements (double to triple diskspace as normal, lots of spare CPU cycles) but can get the job done.

I didn't read the article...

[Max Romantschuk]

.. but the key issue on Windows is:

Does is have a native Microsoft SQL Server import feature?

The thing is, getting a plain SQL dump out of MS SQL Server isn't possible with version 2000, haven't checked since then though. I'm sure this is a deliberate thing tough...

Re:I didn't read the article...

[Anonymous Coward]

Yes it is - just create new tables, views etc and native stored procedures and DTS the data in using odbc.

Re:I didn't read the article...

[Mark Imbriaco]

PostgreSQL has had an ODBC driver for quite some time. You could use that in conjunction with SQL Servers DTS tools to copy data from a SQL Server DB to Pg. There might be some pain involved, particular with indices and constraints, but it shouldn't be too awful.

Re:I didn't read the article...

[bloxnet]

Intelligent Converters makes conversion tools to dump a variety of databases to other formats.

Off the top of my head snagging their MS SQL to MySQL converter might do the trick for you?

Hope that helps a little...

Re:I didn't read the article...

[Galaxie]

You could try scripting all your objects and then using bcp or something to dump the data into something delimited.

It's pretty manual, but should do the trick.

Re:I didn't read the article...

[SiggyRadiation]

If you want to migrate away from SQL-Server then you have MS DTS (at least for the time being).

DTS can pump your MS-SQL-database into postgresql with little problem i'd expect. Now getting the logic (triggers, functions) transferred is a whole other question.

Siggy.

Re:I didn't read the article...

[laptop006]

Huh?
Have you never used enterprise manager?
I know that at least for v7 and 2000 you can get an SQL dump from it with little hassels.

How useful that is due to all the MS proprietery stuff (datatypes etc) is a different matter, and one that could certainly be handled by an upgrade script.

However this is obviously not the way to handle anything more then a few hundred MB of data, any more and you start having serious issues (and at that size writing a small program to do the migration live would be beneficial anyway.

Re:I didn't read the article...

[TheNetAvenger]

The thing is, getting a plain SQL dump out of MS SQL Server isn't possible with version 2000, haven't checked since then though. I'm sure this is a deliberate thing tough...

It can be done, just isn't as obvious as people would like.

Secondly Version 2000 is the latest version of MS SQL Server.

Re:I didn't read the article...

[Anonymous Coward]

Have you ever used enterprise manager? Those dumps are db backus, a binary formatted file suitable for restoring to SQL server, and nothing else. It's not like a mysql dump, which provides ddl at the top, and then row data, which is what the guy was talking about.

Re:I didn't read the article...

[Max Romantschuk]

Have you never used enterprise manager?
I know that at least for v7 and 2000 you can get an SQL dump from it with little hassels.

You can dump the table structure as an SQL dump, but not the data.

Re:I didn't read the article...

[Anonymous Coward]

Secondly Version 2000 is the latest version of MS SQL Server.

True, but they've had 2005 out as a beta for some time now - he might have meant that. 2005 Express beta; full version from MSDN subscriber downloads.

Re:I didn't read the article...

[TheNetAvenger]

True, but they've had 2005 out as a beta for some time now - he might have meant that. 2005 Express

Ya, 2005 has been in beta for a while now, but because he didn't know how to do a SQL dump and his wording was in reference to 2000 being the last he used, it seemed he wasn't up on the MS SQL available. Besides didn't want others to think 2000 was as ancient as it sounds. It has several major revisions even though it is still called 2000.

Take Care.

Erm?

[cca93014]

Can anyone explain to me how it is "Object Relational" as opposed to just straight "Relational" or is this just another case of /. bullshit?

Re:Erm?

[Lemming Mark]

It's true it's an Object Releational DB - PostgreSQL is a hybrid of Relational and Object database features.

I've not looked in detail into the Object database features. I believe they are more closely aligned with object orientation-type concepts, in contrast to the relational model's set-theoretic background.

How good having objects in a database actually is would probably be hotly contested by some but PostgreSQL's relational features is very good in itself (you don't have to think about objects if it doesn't suit you).

Re:Erm?

[wackysootroom]

It's not Object Relational. It's a pure RDBMS. The author may have his terms confused sine postgresql has some object oriented features such as table inheritance.

You can still do all of the ORM stuff at the client with mappers such as Hibernate and SQLObject.

Re:Erm?

[Anonymous Coward]

It's object-relation in the sense that the (relational) tables can inheret from each other; in other words, the tables themselves are treated in an OO manner.

This may, but doesn't necessarily, aid in implementing persistent objects in a pgsql database.

Re:Erm?

[Erik Hensema]

In PostgresQL you can make a table inherit parts of another table. This is what's meant by object relational. Example (from the docs):

CREATE TABLE cities (
name text,
population float,
altitude int -- (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

Re:Erm?

[someguyintoronto]

From what I understand, internally PostgreSQL datatypes are represented as objects/structs. The way it supports User Types suggests this. Is it really an Object Database as in the imfamous ODBMS? No, but the name seems apt.

Re:Erm?

[Delirium Tremens]

From http://www.postgresql.org/docs/7.3/interactive/use r-preface.html:

PostgreSQL offers substantial additional power by incorporating the following additional concepts in such a way that users can easily extend the system:

• inheritance
• data types
• functions

Other features provide additional power and flexibility:

• constraints
• triggers
• rules
• transactional integrity

These features put PostgreSQL into the category of databases referred to as object-relational. Note that this is distinct from those referred to as object-oriented, which in general are not as well suited to supporting traditional relational database languages. So, although PostgreSQL has some object-oriented features, it is firmly in the relational database world.

Re:Erm?

[quigonn]

The difference is explained in this wonderful Wikipedia article on object-relational databases.

Re:Erm?

[afidel]

That's not really all that object relational, it's kind of like early iterations of c++ which were just preprocessor macro's. To see a true object oriented DB that still allows standard SQL access see Intersystems CACHÉ. Everything is an object and can be manipulated as such, it's a truely different way of looking at SQL.

Re:Erm?

[indifferent children]

For one thing, Postgres supports inheritence between tables. Sort of like: CREATE TABLE x INHERITS TABLE y (...) (x will have all of the columns of table y plus whatever other columns you specify). That syntax is probably wrong, because I have never wanted to use that feature. Postgres is a great relational database, however, and I have used it in many mission-critical projects (not life-threatening, just critical to the continued existence of our company) since 1997.

Re:Erm?

[Anonymous Coward]

Read the fucking webpage: PostgreSQL is a highly-scalable, SQL compliant, open source object-relational database management system.

Re:Erm?

[realnowhereman]

Object relational databases allow you to create systesm with table inheritance. For example:

People
/ \
Employees Customers
|
Managers

Now, every record in manager is automatically an employee and every employee is automatically a person. References from other tables to the People table will include all employees and customers.

Additionally, the Employees table might have extra fields - salary and employee number and when viewed as an employee, these fields would show.

Having said all that; I've never been able to find a really good use for an Object Relational database...

Re:Erm?

[wackysootroom]

I stand corrected then.

Re:Erm?

[Anonymous Coward]

macro's = belonging to macro
macros = the plural of macro (which is what you intended)

For the love of god, won't somebody please think of the apostrophes? (Note the lack of an apostrophe in "apostrophes.)

Re:Erm?

[plopez]

O/R is a basically a marketing buzzword. It supposed to mean that the database is relational with "Object Oriented" features to support OOP. Since there is a formal definition of the relational model you can say that PG, and most other widely available database engines, is not 100% compliant with the model. So they are not, per se, truly relational. Since there is no formal definition of OOP, then whether it is compliant with OOP is a matter of opinion.

What does this mean? It means that there are features which an OOP programmer is used to and make them more comfortable using the database engine (or DBMS if you prefer). One of my rants is about programmers who can't shift between different languages, syntax and paradigms when neccessary. Don't get me started.

So, IMO, it is just a set of buzzwords. I personnaly just like to say PG has some really nice features that support 'soft' coding practices. Not an Oracle killer yet, but it could easily be a MS Sql Server killer.

Re:Erm?

[Qzukk]

Having read their site talking about the power of leveraging their synergistic object oriented database paradigm in the enterprise front end situational operation, I can feel my brains melting, and I still don't know what the hell "real" object oriented databases are and why they're so different from the table->row->field used by relational databases. I'm glad they can draw pretty 3D pictures to represent my data (does it really? its hard to tell what they're talking about).

Re:Erm?

[Tablizer]

Incidently, in practice such is bad design. That can create duplication if a capital city is both in the "cities" table and in the "capitals" table. It is simpler just to put an "isCapital" or "capitalState" column in the "cities" table. Or perhaps put a foriegn key to the capital in the "states" table, if there is one.

Re:Erm?

[afidel]

check out this page which does a far better job of describing how the object model works. Basically a class can contain other classes as well as simple data classes, which can be very usefull for mapping your object oriented programming model onto the database.

Re:Erm?

[killjoe]

It doesn't work that way. It's only stored in one table. You can think of the parent table as a view.

Re:Erm?

[killjoe]

Not to mention it's more expensive then all the other databases (even oracle and SQL server).

Re:Erm?

[johnnyb]

"That's not really all that object relational"

"To see a true object oriented DB..."

That's the point you're missing. "object relational" DOES NOT mean "object oriented". They are different. PostgreSQL is object relational while other systems may be object oriented. SQL access does not make a system relational, either, although it does in many people's minds.

Re:Erm?

[jadavis]

Relational manipulations using relational algebra and relational calculus have been around for 30+ years. I'm a little hesitant to buy into the idea that someone reinvented that math in a better way.

I don't want a 1-to-1 mapping of application objects to database objects. To me, that means it's just a persistent storage engine for objects, which is a regression from an RDBMS.

Re:Erm?

[Chatterton]

Is there something like table inheritnce in Oracle ? I have just discovered this capability :)

Re:Erm?

[Anonymous Coward]

It's BS. Ignore it.

An "object relational" database is just a well-designed relational database (note: no existing SQL system is relational, because SQL is non-relational).

I would strongly suggest that anybody who is interested in the reasoning for that statement please learn about the relational model and why databases today aren't implementing it correctly.

And then PLEASE demand *truly relational* databases instead of making do with stuff like PostgreSQL. And I don't even consider MySQL and SQLite worth talking about at all.. at least I can do a good subset of what I want with Postgresql.

PostgreSQL's "object" stuff is broken anyway, because it doesn't enforce constraints correctly (like uniqueness!). So it ends up being completely useless!

Re:Erm?

[Anonymous Coward]

inheritance

This is broken in PostgreSQL, because it doesn't enforce constraints correctly. So ignore it.

data types

Part of SQL, part of the relational model.

functions

Part of SQL, part of the relational model.

constraints

Ditto. Listing these features as "additional" just demonstrates how shameful most SQL databases are. How can something be a database that *doesn't* enforce constraints!

triggers

Part of SQL, but NOT part of the relational model. Relational model works best with *declarative* code, not *procedural*. Ignore this unless you need data integrity constraints you can't handle with the usual SQL syntax.

rules

This is the same as constraints.

transactional integrity

SQL doesn't guarantee this, so neither does PostgreSQL. Think about this: you have a constraint that table X must contain zero rows, or more than 5 rows. It's impossible to insert 5 rows all at once with standard SQL. So at some point in your transaction, your database is inconsistent in a place where SELECT's can happen. This violates integrity. Bad!

These features put PostgreSQL into the category of databases referred to as object-relational. Note that this is distinct from those referred to as object-oriented, which in general are not as well suited to supporting traditional relational database languages. So, although PostgreSQL has some object-oriented features, it is firmly in the relational database world.

Besides the broken "inheritance" feature, this is all standard SQL and mostly standard relational requirements (even though SQL is non-relational). So I would just call it an "SQL database" and leave off the buzzwords.

A correctly implemented relational database (as opposed to SQL database) would be able to handle any type of data including objects, XML, or anything else so there's no need to "overload" the name with the word "object".

Re:Erm?

[Anonymous Coward]

Until you want to enforce the constraint that all EMPLOYEE_ID's are unique across the hierarchy. Oops. Can't do that. So what good is this "feature"?

Re:Erm?

[Tablizer]

It doesn't work that way. It's only stored in one table. You can think of the parent table as a view.

Then why not just make a view?

Congratulations to all involved

[alyosha1]

Thanks to the postgresql team for providing this great product. I've been using postgresql for a couple of years now, and the release candidates of version 8 for the last month or so. (It's good to finally have a windows port!)

Apart from the initial learning curve, I have exactly zero complaints with postgres - it always does exactly what it says it will. I have no qualms trusting vital data to it. Combined with the excellent pqxx c++ bindings library you can build robust applications with very tightly controlled data integrity - for example (shameless plug) my medical imaging server

Anyways, what are you waiting for? Go download it - you won't be disappointed.

Web Server Difficulties

[Reality_X]

Hi Folks,

Please take it easy on 'wwwmaster'.

'www' fell over a couple of hours ago, and a couple of mirrors are coming online to round-robin the address.

Can someone please change the the first link ("PostgreSQL project") in the story to point to 'www'?

Thanks.

I've been waiting for this

[wackysootroom]

Goodbye Oracle, hello PostgreSQL. Now I can have a mostly SQL92 compliant database with ACID, transactions and now PITR and tablespaces that I can use on the server and on a win32 desktop.

For those of you wanting a great frontend, try PGAdmin3. It works on Win32 and Linux.

Re:I've been waiting for this

[Anonymous Coward]

... and now PITR ... on a win32 desktop.

I think you meant PITA on a win32 desktop, but that's kind of redundant.

and has nothing to do with PostgreSQL.

Re:I've been waiting for this

[afabbro]

For those of you wanting a great frontend,

A great admin frontend. I'm still waiting for a great, easy-to-use frontend builder like Microsoft Access.

Re:I've been waiting for this

[Barromind]

And if you're interested in migrating from MSAccess, maybe you'll be interested in PGAdminII, which has a plugin for that. I haven't found any other easy way to do that migration.

Why this plugin hasn't made it to PGAminIII, I don't know it.

Re:I've been waiting for this

[ZonaldRumzfeld]

They do, but it's usually a commercial product.

Re:I've been waiting for this

[sootman]

And for phpMyAdmin fans there's phpPgAdmin.

Re:I've been waiting for this

[killjoe]

Well it's not quite oracle but then again 99% of the businesses and people on this planet don't need oracle (or sql server or db/2).

Unless you are running an enterprise postgres can do anything you need and more.

Re:I've been waiting for this

[p80]

http://www.kexi-project.org/screenshots.html/ Kexi is free and it runs on both linux and windows. It also works great with sqlite and mysql. There's also something called rekall but I'm not sure it's free on windows and isn't as actively developed as Kexi is.

Re:I've been waiting for this

[einhverfr]

There are a few:

* Gnu Enterprise (never used it)
* Rekall (Python based)
* pgaccess (TCL/Tk based)

Re:I've been waiting for this

[einhverfr]

I think that PostgreSQL can handle the needs of most enterprises quite well. A few things which are missing though (to date):

* Two-phase commit (expected with 8.1)
* Multimaster asynchronous replication for independent database servers in a disconnected world... (think of the ability for a salesman to have a portion of the database on his or her hard drive and replicate updates back to the master data store)
* Load ballanced clustering (synchronous replication-- depends on TPC for accuracy, maybe pgpool will do this once TCP is implimented and stable).

THere are also a few developer features I would like to see:
1) Tuples within tuples-- i.e. the ability to use complex datatypes as fields for a table (this allows for custom data types to be created using PL's and SQL without having to code in C).

That being said, PostgreSQL will currently work in nearly every environment required at the moment. There is, however, always room for improvement.

Re:I've been waiting for this

[afabbro]

99% of the businesses and people on this planet don't need oracle (or sql server or db/2)

That's almost true. The problem is you buy application X and it says it runs on SQL Server, Oracle, or DB/2 as the backend. So to run this application which your organization needs, now you need SQL Server, Oracle, or DB/2. This is really common in large enterprises.

You're right, of course, that many of those ISVs are not using features in Oracle, etc. that are not present in PostGreSQL.

Re:I've been waiting for this

[Anonymous Coward]

Dude, phppgadmin is way better:
http://phppgadmin.sourceforge.net/

Re:I've been waiting for this

[LWATCDR]

Simple use Access. No really use ODBC to use Postgres as the backend and Access as the frontend.
Not my favorite way to do things but it will work.
Of course you also have Excel using ODBC and you can setup Apache PHP or Python or Perl or Ruby and do it web based.
Or you could use Visual basic or .net

Re:I've been waiting for this

[killjoe]

Have you seen this.

Its an oracle mode firebird. They took the firebird open source database and made it look like oracle. Same table types, same column types, same SP language.

I wonder if the same thing could be done with postgres. If I recall sapdb was also compatible with oracle 7.3 or something like that.

Gotchas

[michaelhood]

MySQL
PostgreSQL

Re:Gotchas

[DrSkwid]

some of those mysql look really nasty

the postgres ones look fairly innocuous, esp. for recent versions

What's New in 8.0

[Pan T. Hose]

An adequate replacement for MySQL on Windows. Can anyone say WAPP instead of LAMP?

Not only that. Here's the most important link: What's New in 8.0. (To editors: why there are links to torrents, but no link to features?)

Re:What's New in 8.0

[Anonymous Coward]

Everyone who reads Slashdot knows the features well.

Re:What's New in 8.0

[Anonymous Coward]

Everyone who reads Slashdot knows the features well.

If everyone who reads Slashdot knows the new features of PostgreSQL 8.0 then everyone must also know about the new version in the first place, right? Why post this story then?

Queue the comments

[Anonymous Coward]

About how much better PostgreSQL is than MySQL, then the MySQL suckers getting all offended and making up sob stories about "using the best tool for the job", and "web pages don't need to be robust".

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:Win32?

[Quill_28]

I didn't know the goal of Postgresql was to rid the world of windows.

Vital Data

[_Hellfire_]

I wrote a PHP program that tracks my gf's and I's finances with a Postgresql database backend.

It's up to about 600 records now and although I wouldn't say it's mission critical it certainly is vital.

I have to say I'm extremely impressed with pgsql. It's easy to use and consistent in what it does. I have no complaints whatsoever. What I like most (although I'm not sure this is exclusive to pgsql) is the fact that I can at any time get a plain text dump of everything in the database in a format that makes sense. If the worst comes to the absolute worst, I can always mess around with awk and write a script which can convert a pg dump to another format. It gives me piece of mind that my data can always be read.

I've heard that ms sql users are not so fortunate - ie no plain text dumps. Correct me if I'm wrong.

Kudos to the pgsql team for such a fine product and keep up the good work!

Re:Vital Data

[Anonymous Coward]

I wrote a PHP program that tracks my gf's and I's finances

Cool. Now write one that teaches you the difference between "me" and "I". Would you really write :

I wrote a PHP program that tracks I's finances

Re:Vital Data

[Anonymous Coward]

I wrote a PHP program that tracks my gf's and I's finances

Be very careful with PHP there. Be sure you're using transactions to make sure your page finishes loading, or an error on the page might make half your database statements execute before PHP says "oh wait, this is wrong" and dies. There are other cases too... if your browser drops the connection theres a php configuration flag you have to set to make sure the script completely executes instead of terminating immediately, and other gotchas inherent in this.

So to recap: "pgsql has transactions, use them."

Re:Vital Data

[LurkerXXX]

This is why end users (web pages) should only talk to stored procedures and not directly to tables. This is where many MySQL users get bad data silently going into their database. They often write directly to the tables.

The other thing is most of them don't seem to use InnoDB tables which offer transactions, instead they use MyISAM tables because they are 'faster'(who cares about data integrity?)or don't realize they are using MyISAM (You can tell MySQL to make an InnoDB table, but if it decides it can't it will make a MyISAM table instead and won't throw up a warning error when that happens). So sometimes errors will creep in when only part of a change goes into the system. Transactions are good. Use them.

Re:Vital Data

[StormReaver]

"This is why end users (web pages) should only talk to stored procedures and not directly to tables."

I don't see how this would matter. If the stored procedure doesn't wrap complex operations in a transaction, then you run the same risks as writing directly to the tables from PHP (or any other language).

I wholeheartedly agree with using transactions. I can't overstate their importance.

Re:Vital Data

[LurkerXXX]

The stored procedure definitely should wrap up the whole operation in a transaction. You can then also set security on the tables so that only the transaction can be run on them, and no user (including the web user) can directly write to them. That forces all changes to come through the sane transaction you have set up in the stored procedure, and prevents half-assed changes being made to your tables.

Re:Vital Data

[adiposity]

Would he write "I wrote a PHP program that tracks me's finances"?

Perhaps you mean the difference between I and "my". :)

Re:Vital Data

[Anonymous Coward]

However "I wrote a PHP program that tracks I and I's finances" is perfectly valid

Re:Vital Data

[Anonymous Coward]

Why not use a spreadsheet?

Seriously. Why write a custom application to handle something that you can do in a spreadsheet? Especially if you are only storing 600 entries.

I use a spreadsheet to store my own record of credit card purchases and the like. It even tells me what my minimum payment will be next month, and so on. It took around 20 minutes to create and set up.

You've used a sledgehammer to crack a nut. Unless you had an overwhelming desire to learn how to swing the sledgehammer, why oh why use it to crack a nut?

Re:Vital Data

[Anonymous Coward]

Yes, but only if there's a nice chunky Bob Marley groove playing in the background...

Re:Vital Data

[mrroach]

> I've heard that ms sql users are not so fortunate
> - ie no plain text dumps. Correct me if I'm wrong.

It does have plain text dumps. There's a command line tool to do so. I migrated a couple million records from an MSSQL 6.5 server to postgres that way. I had to use sed to change some of the types to postgres ones (smallint and record to int and text IIRC) and do some escaping of characters, but it wasn't too hard a job.

-Mark

Re:Vital Data

[Anonymous Coward]

Hence the funny mod..

Re:Their database server is down

[devrim.gunduz]

It seems to be a temporary message...

How does it compare to Oracle?

[Xpilot]

Everyone keeps saying PostgreSQL is better than mySQL, but where does it stand in comparison to Oracle? Anyone know?

Re:How does it compare to Oracle?

[Anonymous Coward]

PostgreSQL is only 20 slower on a bulk INSERT, but it's getting there. In time, big database vendors will feel the pressure from the little guys. Basically, same story as with Window/Unixes vs. Linux.

Re:How does it compare to Oracle?

[nemesisj]

It's got a long way to go as far as enterprise features.

There is no clustering support in PostgreSQL (and I mean real clustering, not some Java hack where transactions are shipped off to two separate DB servers, both of which don't know they're part of a cluster). This is pretty much a show stopper as far as using PostgreSQL in the company I work for, as high availability is a large concern, and any downtime would be serious.

In previous versions of PostgreSQL, the pg_dump and pg_restore tools were not very good - dumps that included tables or views often would fail on reimport because PostgreSQL wouldn't know the order in which to import everything. You also had to pass in a number of options on the command line just to get a dump that made sense, and large object support was kind of clunky.

That said, I still use PostgreSQL for many many projects and have used PostgreSQL for many years. It's a great product, but it isn't near Oracle in terms of enterprise level features.

Re:How does it compare to Oracle?

[HeelToe]

I'll preface this with stating that I generally stick to ANSI SQL 92 and limit my use of database features to that.

From that perspective, PostgreSQL performs within 5% either way of Oracle for me in pretty much all cases, save one:

PostgreSQL performs much worse if I didn't create an index I needed. Oracle will automatically index based on queries that are run - this can yield HUGELY better performance if you screw up and don't create your physical model to support how you use the database in your applications.

Re:How does it compare to Oracle?

[orasio]

Oracle is bigger, they have lots of enterprise features and apps, and trained people to support you, plus a big company behind.
It must be the sane choice for big corps. In my workplace, we develop for oracle, and postgres, and we are very pleased with both performances.
Programming for postgres is nicer, because they are more conformant to standars (the version of ORacle we are using doesn't have "LIMIT" and "OFFSET", for example). Administering it is easier, backups/restores are easier, mirroring is easier. I have all kinds of problems understanding the way Oracle does things. It must be that Oracle requires enterprise size, and a DBA that understands it, plus it provides lots of enterprise stuff that _I_ don't make use of, but for small-medium size apps, I believe the best choice is Postgres. For enterprise applications, they have a lot of infrastructure that might be useful.

Re:How does it compare to Oracle?

[Anonymous Coward]

> I'll preface this with stating that I generally stick to ANSI SQL 92 and limit my use of database features to that.

Well, good luck. If you pay wad of cash for DB - what is the fucking point of not using its full potential? SQL 92 is too generic, you know.

> PostgreSQL performs much worse if I didn't create an index I needed. Oracle will automatically index based on queries that are run

ABSOLUTE BULLSHIT. Oracle will do table scans (more IO, slower) if there are no indexes for query. It will not create anything by itself.

It looks like you've never built anything DB-backed bigger than a guestbook.

Re:How does it compare to Oracle?

[HeelToe]

I don't feel a need to go into details about the systems I've built, but if you build systems intelligently, many times you do not need more than ANSI SQL 92. Sure, there are times to use other features, and I have done that as well, but many times portability is more important on my projects than raw throughput on a specific database. Your assumptions that 1) eeking every bit of performance is more important than portability and 2) that I actually paid a wad of cash for Oracle are both wrong. They may hold true of your situation, but not mine.

With respect to Oracle auto-indexing. If it's not auto-indexing, I don't know what it's doing - storing column tuples in memory for future queries? I can perform queries against PostgreSQL using the same schema and indeces, and return results in 15 minutes or so. Oracle is subsecond. Adding a well-placed index for the query will reduce PostgreSQL's query time to subsecond as well. Oracle's first query runs in the 10 minute range, but magically, thereafter, it's subsecond. Automatically indexing may not be the right term, but it is doing something to improve performance where there's a physical data model problem that otherwise prevents it.

Re:How does it compare to Oracle?

[mborland]

I agree that PostgreSQL sometimes takes itself out for a spin--the optimizer seems to get more confused by some queries in PostgreSQL than in other databases. Generally the problems are solved with indexes in logical places.

The optimizer in PostgreSQL (in my experience) gets somewhat confused by how to best handle certain types of views. Most of the time it's a query structure problem, but other times it just seems to make 'dumber' mistakes than other databases. Granted, you can tweak the settings for the optimizer and sometimes this helps, but I'd like to see some improvements in the optimizer esp. with regards to the treatment of views.

Re:How does it compare to Oracle?

[voselus]

No, sorry, that is one of the nuggets of all wisdom that has so far eluded the minds of mortals.

Re:How does it compare to Oracle?

[Anonymous Coward]

Some of the most common of these problems dealing with different datatypes (which is a unique problem to postgresql since no other database is as extensible) have been fixed in 8.0.

Re:How does it compare to Oracle?

[gUmbi]

Everyone keeps saying PostgreSQL is better than mySQL, but where does it stand in comparison to Oracle? Anyone know?


The main difference: PostgreSQL is anywhere from $5,000 to $1,000,000 less expensive than Oracle.

Re:How does it compare to Oracle?

[Anonymous Coward]

Something I can't stand about Oracle is the need for X11 and Java just to do the install.

I don't want to run either on my servers. Postgresql on the other hand is fully available via the command line.

Re:How does it compare to Oracle?

[Anonymous Coward]

Wasn't it only about 2 years ago when Oracle got serious clustering capabilities ?

In fact those capabilities are the bullet proof clustering technology from VAX/VMS.

Re:How does it compare to Oracle?

[kpharmer]

Postgresql is ready to take on the smaller, non-critical databases that oracle used to get. This is significant proportion of the databases out there, and will take revenue away from oracle. (Mysql will actually probably take more revenue away, but it has too many quality problems and functionality gaps to really deserve to.)

But there are many other, more demanding databases that postgresql isn't yet ready for. Oracle, DB2, and even SQL Server 2005 all have very mature & solid: optimizers, replication, partitioning solutions, parallelism, failover/clustering support, etc.

Here are two examples:

Using db2 for example, you can create a view which is automatically populated by the database like a table (MQT). Then any queries against the base tables that could be sped up by hitting this view will be rewritten by the engine to hit the view. Now, this might seem like needless fluff if you're just writing a hobby php app. But if you need to implement a commercial app like SAP with its 6,000 tables - and you have performance issues - you can make adjustments in the database layer this way. Also, if you're provoding adhoc reporting for hundreds of users on a terrabyte of data - this technique can provide *dramatic* performance benefits.

Another example is partitioning. Back to db2 (which I work with the most): you can spread a database across a dozen separate servers using a hashkey. Now, every query will have all dozen servers working independently on its own fraction of the data. On each of those servers, you can then partition again, this time using ranges or values (MDC) - so that data that doesn't apply to a query will be skipped in tablescans of that table. Using these techniques you can get sub-second response to *adhoc* queries against a terrabyte of data - without indexes (notoriously unreliable here).

Lots more examples where the above came from. Sure, you will pay real money for licensing, hardware, and labor to implement these. Then again, the two above features actually save you in hardware costs. Additionally, some problems are big enough that they can easily justify the cost of licensing a product like this. I've seen these techniques used to save companies million, even hundreds of millions of dollars.

Re:How does it compare to Oracle?

[LurkerXXX]

I take it you've never run Oracle in a large enterprise. The extra money is well worth it for the enterprise features. Those are the main difference.

Re:How does it compare to Oracle?

[chriskl]

Try Slony for full, production quality replication on PostgreSQL:

http://www.slony.info/

Re:How does it compare to Oracle?

[eison]

Oracle 8.1.6 could not successfully reimport views from dumps because it wouldn't know the order in which to import everything. Would you say it also had a long way to go as far as enterprise features?

Re:How does it compare to Oracle?

[mborland]

I'm looking forward to exploring the new parts of 8.0. If they have cleared up some of the 'issues' I mentioned (not so terribly important in the big picture), then I'm a very happy man.

Re:How does it compare to Oracle?

[ahodgson]

That's fine, if you need Oracle, then you need Oracle. Most people don't.

Re:How does it compare to Oracle?

[killjoe]

SQL server enterprise edition costs 16,000 per processor last time I checked (and yes you need that version if you want all those features).

This is about the same as oracle although oracle has a few more features and better replication.

For the vast majority of the businesses on the planet postgres will do just fine. Worry about the other stuff when you have 30+ K to spend on a database server.

Re:How does it compare to Oracle?

[johnnyb]

" I agree that PostgreSQL sometimes takes itself out for a spin--the optimizer seems to get more confused by some queries in PostgreSQL than in other databases."

Sometimes this can happen if your queries do not include enough type information. 8.0 fixes this, though.

For example, if I have int8 columns, you have to cast any direct numbers into int8's or the optimizer won't find the indexes. Why? Because numbers by default are int's, and therefore can't use int8 indexes. PG8 is smarter about this, but you could work around it by doing 23424243::int8. Note that you don't have to do this with columns comparisons, since the type information for both columns is already there -- only when you are comparing a column of type int8 to a constant value, because they are interpretted as being int4's.

Re:How does it compare to Oracle?

[johnnyb]

"In previous versions of PostgreSQL, the pg_dump and pg_restore tools were not very good - dumps that included tables or views often would fail on reimport because PostgreSQL wouldn't know the order in which to import everything."

I haven't had that problem w/ 7.4

"You also had to pass in a number of options on the command line just to get a dump that made sense"

Really? pg_dump and pg_dumpall work just fine for me. As long as you use bytea's instead of the other large object types, that is.

"and large object support was kind of clunky."

Depends. If you use bytea columns (up to 2GB I think) then its super-simple. They have large objects themselves, and they _are_ terrible, but you can generally just use bytea's instead.

Re:How does it compare to Oracle?

[luisdom]

"a view which is automatically populated by the database like a table".

I suppose you are referring to materialized views, which are also present on Oracle. It ~is the base of ROLAP a "type" of OLAP (for you acronym lovers). I'm afraid Postgres doesn't have real support for this, yet. There is a project which aims to add it, but it is in a very early state, and it hasn't been updated for a year.

Re:How does it compare to Oracle?

[jadavis]

Not only that but the pg_dump dependency issues were fixed in pg 8.0

Re:How does it compare to Oracle?

[jadavis]

Materialized views can be implemented in PostgreSQL with triggers, and often are.

It's the same thing as far as I can tell, but it is missing the ability for the planner to automatically select the view instead of the table for a certain query.

Re:How does it compare to Oracle?

[Anonymous Coward]

indices

Re:How does it compare to Oracle?

[kpharmer]

> Materialized views can be implemented in PostgreSQL with triggers, and often are.

Right, that's a fine workaround. Of course, you'll also want user-maintainable ones as well (in order to coordinate around loads, etc) but you can drive that at the application level also.

> It's the same thing as far as I can tell, but it is missing the ability for the planner to
> automatically select the view instead of the table for a certain query.

Right, and this is both the critical & difficult to implement aspect of this functionality. Note also that there's no direct relationship to rolap here - other than rolap is one type of application that often heavily leverages this functionality.

And as far as rolap is concerned, I almost always implement summary tables for analytical apps. When we write these apps from scratch, then I typically have them read directly from user-maintained summary tables. When using canned applications (Cognos, Business Objects, Microstrategy) then the only choice is to go with materialized views (Materialized Query Tables in db2) & query rewrite.

Re:How does it compare to Oracle?

[Anonymous Coward]

But if you need to implement a commercial app like SAP with its 6,000 tables

You, my friend, are running an older version of SAP. R/3 flew by 7,000 tables in the 4.0 days and has hit the 12,000 table mark.

Re:How does it compare to Oracle?

[Anonymous Coward]

Uh, You're saying This isn't a big company!?! At 156,000 employees and $4.7 Trillion Yen (over 40 billion USD) Revenue, it's quite big IMHO. If my math is right, that's way bigger than Oracle. And yes, Fujitsu does support Postgresql

Re:How does it compare to Oracle?

[Anonymous Coward]

The extra money is very nice for DBAs, Oracle Consultants, and the gifts Oracle Sales Guys Bring :) Until the postgresql dev team can buy our CFO as nice a wine at lunch as Oracle, they have no chance getting our business.

Re:How does it compare to Oracle?

[bdbafh]

so create the views FORCE and recompile afterwards. yes, 8.1.6 lacked a good dependency-driven utility such as utlrp, so you had to recompile things by hand at times. you upgraded to 9.2 and got over it. 10g Release 2 is soon upon us.

Re:How does it compare to Oracle?

[bdbafh]

so pickup a cygwin CD and run the xserver on your win32 laptop. I've installed 9.2 and 10.1 on RHEL 3.0 ES via the Cygwin Xserver on the WinXP boot on me ole laptop. Its covered at sites like: http://www.puschitz.com/

Re:How does it compare to Oracle?

[orasio]

Well, I said that oracle is bigger than postgres.
I believe so.
I was also stating my experience, that in my field, that is small to middle-size databases, Postgres is much better. Easier to program for, easier to administer.
I was just making a conjecture that maybe for big size companies, it's easier to deal with the biggest db provider.
In many companies aroud here, it's easier to just let them buy whatever Oracle database they want to buy, if they feel safer that way.

Re:How does it compare to Oracle?

[Jaeger-]

I've seen these techniques used to save companies million, even hundreds of millions of dollars.

I will give you the the "million" number without question/issue...

But I am going to have to call your bluff on "hundreds of millions of dollars". I just don't think thats a reasonable estimate at all.

Re:How does it compare to Oracle?

[kpharmer]

>> I've seen these techniques used to save companies million, even hundreds of millions of dollars.

> I will give you the the "million" number without question/issue...

> But I am going to have to call your bluff on "hundreds of millions of dollars". I just don't
> think thats a reasonable estimate at all.

ha, yeah I thought that later - it didn't sound credible. However, back in 1998 I did save a company $150 million using a warehouse that used these techniques. The savings came from comparing all financial data for a fortune 10 company to all inventory data - and finding lost assets.

The partitioning & parallelism methods I mentioned earlier weren't directly responsible for the savings. But this system was originally built in 1995 - supporting a terrabyte of data on 4.5 gbyte 7200 rpm drives and on 66 mhz cpus with 256 mbyte of memory- and gave 30 second response time to adhoc queries that had to scan 300+ gbytes of data. Without these database features it wouldn't have been practical to compare such large volumes of data - and the application just wouldn't have been built. And the savings wouldn't have been made.

Since then I've been on projects to automate reports that drove the workload of 6,000 engineers at quest - with a warehouse that loaded 50 million rows a day, and produced reports in 6 minutes on a 4-way using oracle that replaced a 12-way e6500 running sas that took 14 hours a day to run.

I've built another warehouse that improved service managability so much that it ended up becoming the #1 selling point for one (huge) managed service provider.

etc, etc. But the $150 mil is really the biggest tangible benefit I can point to I guess. Not quite the same as saving hundreds of millions in database licensing costs. But an indirect savings anyway.

ken

Re:Win32?

[Zaiff Urgulbunger]

It may, but it really helps for folks who use Windows and want to run a local copy of their database for development purposes.

Myself, I'm absolutely thrilled wiht this new release (and indeed, their new website -- the old one did look a bit naff). I expect PostGreSQL's popularity to increase a fair bit this year, and good luck to them!

Sorry, don't want to start a war but...

[bigfatwill]

Could anyone give an example of where you might want to use postgresql over mysql and vice versa?

Re:Sorry, don't want to start a war but...

[Anonymous Coward]

Could anyone give an example of where you might want to use postgresql over mysql and vice versa?

Here it is.

Re:Sorry, don't want to start a war but...

[mark-t]

A big one... for a lot of people, is that Postgresql does not require that a person who copies it to use it as part of their own project also place their code under the GPL when they release it.

This makes Postgresql a more viable option for large corporate projects under development that may want to keep their options open about whether or not they will release their source code.

Re:Sorry, don't want to start a war but...

[mborland]

GPL licenses apply to the distribution of derivatives, not products that happen to use a GPL'd service. Were PostgreSQL GPL, then when you modify *PostgreSQL* and want to distribute it, yes, you'd need to distribute the source code. However, if you have a product that happens to talk to a GPL'd dataserver, by no means would you have to release the source of your application on your app's distribution.

Re:Sorry, don't want to start a war but...

[matchboy]

If you want referencial integrity of your data...

Re:Sorry, don't want to start a war but...

[Anonymous Coward]

The first post about the GPL is pretty ignorant.

If you take MySQL's GPL'd version a corporation can modify it and use it for their own software usage and never ever have to worry about redistributing the code or not.

It's only a problem when you want to distribute programs that use MySQL code in it without distributing the code for the entire program.

For internal use you can keep it as closed as you feel like. As long as you dont' use MySQL code in your own product that you plan on selling to people the GPL'd-ness of it is a non-issue.

Even then, shipping MySQL as a database backend for a project won't have any impact on the license for the rest of the product. It's only when you use code from MySQL is when you'd run into issues.

Re:Sorry, don't want to start a war but...

[bovinewasteproduct]

GPL licenses apply to the distribution of derivatives, not products that happen to use a GPL'd service.

The problem is always, is the access library GPL or LGPL? If your access library is GPL, you can't write a C app to access the DB without putting it under the GPL.

BWP

Re:Sorry, don't want to start a war but...

[mborland]

I believe you are incorrect. Under your interpretation any system that accesses any GPL system or *service* must be GPL'd. There is a difference between referring directly to a GPL's product's libraries , and making use of a GPL'd resource. Under your interpretation, a browser accessing a GPL'd web server would have to be GPL'd. This is simply not the case.

Re:Sorry, don't want to start a war but...

[Just Some Guy]

I think you missed his point. The MySQL client libraries are GPL, so you can't distribute an application that links to them without using a GPL-compatible license. You're free, of course, to write your own non-GPL version of mysqlclient.so but I doubt that's likely to happen.

Re:Sorry, don't want to start a war but...

[Just Some Guy]

As long as you dont' use MySQL code in your own product

There's the problem, though: how do you access MySQL without using their client library (short of some lame shell-script hack)? I guess you could write some loosely-coupled adapter, such as a GPLed stub that links to libmysqlclient.so and that communicates with the main non-GPL application via SOAP or something, and then pray that you're covering yourself legally and that the overhead in the interconnect doesn't kill performance.

Alternatively, you can take the unambiguously clear path of using PostgreSQL's BSD-licensed client and server and avoid the issue altogether.

Re:Sorry, don't want to start a war but...

[Zaiff Urgulbunger]

There's the problem, though: how do you access MySQL without using their client library (short of some lame shell-script hack)?

I am sooo not a lawyer, but I'd guess that here, although you are using their code in that you're compiling it into your own software, you are not extending it... you're just building on top of their API?

But yeah, the distinction is not massively clear so I don't know if that leaves you open to problems on a legal front.

Re:Sorry, don't want to start a war but...

[Just Some Guy]

I am sooo not a lawyer, but I'd guess that here, although you are using their code in that you're compiling it into your own software, you are not extending it... you're just building on top of their API?

You don't have to extend it to invoke the GPL's protections - you just have to link to it.

Don't get me wrong: the GPL is a good thing and I wholeheartedly support its goals and intentions, but this is a horrible place to use it. As far as I know, you can even use the Oracle and MS SQL client libraries without such restrictions.

Re:Sorry, don't want to start a war but...

[jadavis]

There are 3 types of installations:

(1) Installations which are forced to use PostgreSQL because of performance or features that MySQL cannot provide.
(2) Installations which are forced to use MySQL because of performance that PostgreSQL cannot provide.
(3) Installations which could use either

For #1 and #2, you just have to try your data and see which database is up to the task and which isn't.

For #3, I think you'll like PostgreSQL, and I think it will reduce developer time.

Re:Sorry, don't want to start a war but...

[Anonymous Coward]

Question: are you storing data?

If the answer is yes, use PostgreSQL!

Seriously, it has a superset of MySQL's functionality, and is freely available just like MySQL, so there's no reason I can see to use MySQL.

Unless you are stock with a hosting service that only offers MySQL. Then you better not learn PostgreSQL because you will just get depressed.

Re:Sorry, don't want to start a war but...

[mark-t]

It's unlikely to happen because it would be practically impossible to adequately defend the premise that your own non-GPL version of the client libraries that didn't borrow from theirs to the extent that copyright would consider it a derivative work.

It is much more likely that you would write your own entire relational database from scratch.

Re:Win32?

[wackysootroom]

IMHO, The win32 version is to give people who dabble with MySQL in windows and alternative database.

congrats

[wikinerd]

Congratulations to all developers and beta testers who made PostgreSQL 8.0 possible! Those who download via BitTorrent please keep your downloaders open for as long as you can, so that they can seed the torrent to others.

What about Oracle gotchas?

[Anonymous Coward]

Does oracle have similar problems?

Not ./ BS

[Anonymous Coward]

PostgreSQL is object oriented, in that you can store objects rather than your normal flat table/row data layout scheme used by most databases.

This is covered in most introductory books on postgresql, I suggest you try it out! :)

James Carr

Mac OS X support?

[Archibald Buttle]

I have heard great things about PostgreSQL, and a buddy of mine recently told me that this release was pending.

One thing I'm not keen on though is tarball distributions. I don't want to have to compile the thing, I just want to grab a package for my platform and install it (just like I do with MySQL).

My favoured platform though is Mac OS X. There's a whole list of platforms in the FAQ, but Mac isn't amongst them.

So my questions are is Mac OS X supported, and if so are there any plans to make a Mac OS X .pkg installation file?

Re:Mac OS X support?

[Archibald Buttle]

To answer the first part of my question, I just found the Supported Platforms part of the manual, and sure enough Mac OS X is there.

The question remains though - are there plans for a Mac OS X installer package?

Re:Mac OS X support?

[DrZZ]

This guy usually isn't too far behind in creating .pkg for the stable PostgreSQL releases. I have run it on OS X for a number of years and I have been very happy with it.

Re:Mac OS X support?

[larkost]

The standard way of finding MacOS software would have answered this question in a heartbeat: VersionTracker or MacUpdate, both of which list installers.

Re:Mac OS X support?

[HeelToe]

PostgreSQL 7.4 for the mostpart* works fine on Mac OS X. In terms of installation, I do it with fink. I'm not sure when a PostgreSQL 8.0 source package will be available for fink, but hopefully not too long.

*If you want to support more connections or more shared memory per connection, you need to set your sysv shm settings in /etc/rc something similar to FreeBSD 5's defaults and then reboot. They are set once on boot and cannot be changed with sysctl after they are first set.

Here's what I'm currently using:

sysctl -w kern.sysv.shmmax=33554432
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=192
sysctl -w kern.sysv.shmseg=128
sysctl -w kern.sysv.shmall=8192

Re:Mac OS X support?

[SuperBanana]

My favoured platform though is Mac OS X. There's a whole list of platforms in the FAQ, but Mac isn't amongst them. So my questions are is Mac OS X supported, and if so are there any plans to make a Mac OS X .pkg installation file?

7.x compiles right out of the box- in fact, Apple's Remote Desktop system actually installs and uses PostgreSQL for all its data storage (client system data and whatnot; ARD can collect a lot of per-system data). Very slick.

Re:Mac OS X support?

[Anonymous Coward]

its in the unstable tree in fink

Re:Mac OS X support?

[leandrod]

> Apple's Remote Desktop system actually installs and uses PostgreSQL for all its data storage

Does it do that in a standard way so that other users can take advantage of that same PostgreSQL installation, or if one wants a general-purpose database one still needs to do a new installation?

Anyway, a PostgreSQL installation is so light that it hardly makes any difference...

Re:Mac OS X support?

[realkiwi]

My development server is Mac OS X with Postgresql 7.4.x. A pkg will be made RSN you can count on it.

There are packages and packages though. Mine come from a guy in Switzerland http://entropy.ch/ who does it really well.

Re:Mac OS X support?

[realkiwi]

www.entropy.ch

Sorry!

Re:Mac OS X support?

[pHDNgell]

My favoured platform though is Mac OS X. There's a whole list of platforms in the FAQ, but Mac isn't amongst them.

My main DB server at home has been a G4 cube for a long time now.

Re:Mac OS X support?

[jmelloy]

Though it compiles just fine, there is a nice package available from http://www.entropy.de

Java Stored Procedures?

[SQLz]

What happened to the big news about the Java stored prodcedures? Wasn't there some news not to long ago about adding support for this in Postgres? To me that would been a more welcome feature than the rest of the stuff.

Re:Java Stored Procedures?

[JohnnyCannuk]

Here ya go

You may want to wait a week or so until they get caught up to Postres...

Re:Java Stored Procedures?

[thhal]

Take a look at PLJava. http://gborg.postgresql.org/project/pljava

Comment removed

[account_deleted]

Comment removed based on user account deletion

Typical

[Anonymous Coward]

I hate it when MySQL fanboys jump into threads like this only to show their ignorance of relational algebra and predicate calculus saying that no one should ever bother with PostgreSQL and ACID-compliance, because MySQL is somehow a "better tool for the job" in the "real world". We already have comments saying that, so people, please read this first: [1] [2] [3] [4] [5] [6] [7] before you post yet another misleading plug for your favorite toy. Thank you. A real relational database is more than just a data store with SQL frontend.

Re:Typical

[Anonymous Coward]

Hey um, while I'm no fan of MySQL or its fanboys, most DBAs don't know anything about relational algebra or predicate calculus, and most are going to fail to be impressed by your own knowledge of the subjects. It's not like you need to know about aerodynamics, thermodynamics, and metallurgy to be a race car driver.

Re:Typical

[Anonymous Coward]

but only because that race car driver has an army of techs behind the scene who do know about all these issues..

in the case of a DBA, either he, or the database vendor should know about that - looking at mysql, neither can be assumed.

Re:Typical

[Anonymous Coward]

Ahh, but PostgreSQL is not a real relational database either..

but yes, if you design your database with the relational model in mind, and then implement it in either MySQL or PostgreSQL, you will have a much easier time in PostgreSQL, because you can "simulate" updateable views, arbitrary constraints, etc., using PostgreSQL features, while in MySQL you just can't do much of that at all.

For instance I need to make sure that each row in table X refers to a row either in table Y or table Z, but not both. How do you enforce this in MySQL? You don't. In Postgresql you can write a trigger that checks it, which isn't quite as good as full declarative constrains but it works.

Replication and vacuum?

[Purpendicular]

Does it have replication through some mechanism that is free or close to. (Please don't answer with obscure link to work in progress).
Does it need vacuum still? Can I run it 24/24 with load 365/365 without ever having to block access to a single table?

Re:Replication and vacuum?

[wackysootroom]

Slony is what you're looking for.

Yes, you can vacuum your tables without locking them.

Re:Replication and vacuum?

[IsleOfView]

Yes and Yes.

Master-Slave replication is available through Slony1. (This is currently used by Afilias on the .info domain) Slony2 is in progress and will provide multi-master replication.

Vacuuming is still necessary, but it no longer locks tables. The distribution includes a utility called pg_autovacuum which can take care of all the vacuuming tasks on an automated basis if you desire. (The gentoo release automatically installs this with a nice init script :) )

Re:Replication and vacuum?

[Anonymous Coward]

Can I run it 24/24 with load 365/365 without ever having to block access to a single table?

You can't even do that using MySQL unless you never do any inserts.

PGSQL has its own gotchas

[jdoeii]

For example, exceptionally poor performance of aggregates like COUNT(*), relatively weak optimizer.

See Wiki entry for more.

Re:PGSQL has its own gotchas

[Anonymous Coward]

Yeah, because doing a fast count(*) is so crucial right? /rolls eyes

On those rare occasions apps need a count(*), isn't it better to maintain a the required count/s via triggers, instead of burdening every single index with MVCC information?

Re:PGSQL has its own gotchas

[JohanV]

I would consider the PostgreSQL gotchas to be of an entirely different category as the MySQL gotchas.
In PostgreSQL the gotchas are all about performance. And while it may be bad that the database crawls if I didn't schedule a job with vacuum and analyze, that does not have any long term effects. I just run the maintenance tasks manually, add them to a cronjob and I am good to go.
In MySQL however, the gotchas are mostly about data integrity. And that means that they can be disasterous because they can lead to dataloss. Unless you can explain to me how to get back whatever was silently truncated by MySQL if I discover the problems a few days later.

I do very much prefer PostgreSQLs focus on data integrity. But of course I actually read the manual so I won't be bitten by them in either database.

Re:PGSQL has its own gotchas

[StormReaver]

I have several tables with over 3 million rows, and most aggregates return immediately (or nearly immediately). count(*) is the only dog since it reads every row to get the count, but it's acceptable since I rarely count all rows in the table. I've often wondered why the developers couldn't keep an internal count of all active rows, so count(*) would return immediately, but I'm sure they have their reasons.

You've got to be joking about PostgreSQL having a weak optimizer. If it's weak, only the computer can tell.

Your Wiki link spent most of its space praising PostgreSQL for its advanced features, while your intent is clearly to denigrate it. If that represents PosgreSQL's worst facets, then I am very, very happy.

Re:PGSQL has its own gotchas

[Anonymous Coward]

No, and I will fill you in. count(*) is easier for me.

Do you get it now?

Re:PGSQL has its own gotchas

[Anonymous Coward]

Because most applications do not need to count all rows in a table. It would be a waste of time to keep updating such a count. In most cases where you count rows in a database you have a WHERE clause, counting only some rows (and here an index will help postgresql to deliver it fast).

If you still want a count of all rows it's very easy to set up some triggers that update a count.

Re:PGSQL has its own gotchas

[Hammer]

Another garage product also fares badly on COUNT(*) however if you do COUNT() they both do real well. It has to do with the fact that when you do COUNT(*) you have to count every row and that takes a fair bit of time. When you do COUNT(key) you count rows in the index file and that is real quick :-)

Re:PGSQL has its own gotchas

[jedidiah]

If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

Re:PGSQL has its own gotchas

[leroybrown]

if you do a count(1) instead of a count(*), it works faster since it doesn't have to return any actual fields.

Re:PGSQL has its own gotchas

[Cajal]

In MySQL (with MyISAM tables), the reason things like count(*) are fast is that MyISAM pre-computes those values. It can do this because it locks the table on insert and update. PostgreSQL doesn't lock the table on modifications -- it allows concurrent access via Multi-Version Concurrency Control (MVCC). Basically, each row in the DB has additional information (used internally by PostgreSQL), which stores which transaction created and last modified the row. PG uses this to determine if any given row should be "visible" to the current transaction. Because this informaiton is constantly changing (and varies from transaction to transaction), you can't precompute things like count(*) and sum(*). See http://developer.postgresql.org/pdf/internalpics.p df for more info (start around page 56).

Re:PGSQL has its own gotchas

[Anonymous Coward]

Either that, or your understanding of how PostgreSQL works is broken.

Re:PGSQL has its own gotchas

[jsight]

This does not help with Postgres (weirdly formated thanks to the lameness helper):

xmd=> explain analyze select count(*) from message;

QUERY PLAN
Aggregate (cost=42781.57..42781.57 rows=1 width=0) (actual time=1353.191..1353.192 rows=1 loops=1)
-> Seq Scan on message (cost=0.00..41808.05 rows=389405 width=0) (actual time=0.043..924.408 rows=390984 loops=1)
Total runtime: 1353.282 ms
(3 rows)

xmd=> explain analyze select count(primarykey) from message;
QUERY PLAN
Aggregate (cost=42781.57..42781.57 rows=1 width=38) (actual time=1356.534..1356.534 rows=1 loops=1)
-> Seq Scan on message (cost=0.00..41808.05 rows=389405 width=38) (actual time=0.041..918.971 rows=390984 loops=1)
Total runtime: 1356.621 ms
(3 rows)

Re:PGSQL has its own gotchas

[Anonymous Coward]

Huh? Full scan? Do You have primary keys?

Re:PGSQL has its own gotchas

[davegaramond]

COUNT(*)/SUM()/etc performance: Well, try doing that in MySQL's InnoDB. It's the consequence of MVCC, not Postgres' fault.

Re:PGSQL has its own gotchas

[amorsen]

If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

Postgres knows how many rows are in the table, but it does not know how many of those you can see. Some of them may be inserted speculatively by another transaction. Postgres needs to go through each row to determine whether or not that row is actually visible to you. It is possible to turn this into an O(1) operation if you're willing to do sufficient work on inserts and deletes. Whether this is a good tradeoff depends on how often you do count(*) compared to how often you do inserts and deletes.

Re:PGSQL has its own gotchas

[Anonymous Coward]

My favorite gotcha not mentioned is how MySQL's own system tables are all MyISAM. That's right, they're not transaction-safe, so if you roll your own admin scripts, be very very very careful.

Re:PGSQL has its own gotchas

[snorklewacker]

Unless you locked the whole table, Oracle is also pretty craptastic about COUNT(*), even if you have an index. Learned this the hard way doing it on a 20 million row table...

Re:PGSQL has its own gotchas

[Dan Ost]

Have you used PostgreSQL recently?

Performance hasn't been an issue since at least version 7.

Re:PGSQL has its own gotchas

[michaelggreer]

Take a look at the auto-vacuum daemon in contrib. It looks at the statistics and vacuums tables when sufficient change has occurred to them. Runs more optimally than a cron job. Works great.

Re:PGSQL has its own gotchas

[leandrod]

> If your RDBMS is doing a full table scan just to do a count(*)

COUNT (*) is an SQL construct, and SQL isn't relational, therefore an RDBMS can't do COUNT (*) unless it implements a quasi-SQL backward compatibility mode (such as Alphora Dataphor's RealSQL).

Re:PGSQL has its own gotchas

[JohanV]

Performance is still an issue when you don't read the manual and never perform maintenance on your database. No matter how good the planner is, if it doesn't have acurate statistics because nobody ever runs ANAYZE your database will crawl. No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.

I use PostgreSQL on a daily basis and when I change a database schema I will make sure that I run a VACUUM ANALYZE after committing the changes. But many of the people that we host don't bother to read the manual and don't do this. Usually this is no big deal because it gets picked up by the scheduled jobs. But every now and then somebody calls in a panic because their website is slow. And it always turns out to be major schema changes throwing the planner off. A quick VACUUM ANALYZE gets the performance up again and the customer is happy and has (hopefully) learned a valuable lesson.

This is a real gotcha in PostgreSQL. It has made a few of our customers' websites crawl for a few hours on occasion. But it has never caused dataloss.

Re:PGSQL has its own gotchas

[Asgard]

The optimizer should make count(1) and count(*) equivalent as neither returns any of the fields to the user.

Re:PGSQL has its own gotchas

[leroybrown]

that's probably true now, i read that in a book several years ago.

Re:PGSQL has its own gotchas

[joib]

No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.


Actually, for 7.4 and newer that isn't true, since they include the aptly named "autovacuum" daemon. And yes, autovacuum also does "VACUUM ANALYZE", so no need to worry about that either.

Re:PGSQL has its own gotchas

[Anonymous Coward]

If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

How would you recommend doing it, and still supporting Reat Uncommitted, Read Committed, Repeatable Read, and Serializable Transaction Isolation Levels (part of SQL 1992) then.

Consider this scenario.

1. Connection A starts a transaction.
2. Connection A does a count(*) -- expect the original number or f rows.
3. Connection B starts a transaction.
4. Connection B inserts a number of rows into your table.
5. Connection A does a count(*) -- shouldn't see B's inserts
6. Connection B does a count(*) -- should see B's inserts.
7. Connection B commits
8. Connection A does a count(*) -- still shouldn't see B's inserts for some transaction isolation levels.

With your magic RDBMS, what would you do? If you say lock tables during these operations your DB won't scale.

Re:PGSQL has its own gotchas

[Sxooter]

But the autovacuum daemon isn't enabled or even compiled by default. You have to go into contrib, compile and install it, and then turn it loose.

Soon, 8.1 or so, it will be integrated into the backend. It almost made it into 8.0, but missed it by "" - that much.

Re:PGSQL has its own gotchas

[Anonymous Coward]

Sounds like poor design. count(*) on DB2 has been instant from day 1.

Re:PGSQL has its own gotchas

[einhverfr]

I've often wondered why the developers couldn't keep an internal count of all active rows, so count(*) would return immediately, but I'm sure they have their reasons.

The reason is called MVCC. With MVCC, the old tuples stay around, and uncommitted updates are not visible to other selects (among other things). This means that two different transactions running at the same time (but with different start times) may show different returns for count(*). This also is why PostgreSQL doesn't have to do row locking (because this can be handled automatically by MVCC, and updates only need to block other updates, not reads).

Now, if you only need an approximate answer, you can query the stat tables. Otherwise you are going to have to actually look at every row to see whether it is visible to your current transaction.

Re:PGSQL has its own gotchas

[ComputerSlicer23]

Not true. count(*) will only count rows that have a non-NULL field in them. count(1) will return all rows weather or not they have a non-null field in them. Run count(fieldName) where fieldName has NULL's in them. It should not count the NULL's.

They are not the same. However for 99.999% of all sane schema designs they are in fact the same. However, count(*) has to pull the data to ensure that there is a non-null field. More then likely if it was clever, it would not pull the data if there was a primary key on any of the tables in the select statement.

Kirby

Re:PGSQL has its own gotchas

[slave 6742]

Why is it there is no "ShortCommings" in the Wikipedia description of MySQL.

Hmmm, to be fair, you would think that MySQL would compare itself in the same manner.

Looks pretty biased to me!

Re:PGSQL has its own gotchas

[Anonymous Coward]

This was one of the optimizations for "Rushmore" technology that MS first added into FoxPro right when Access 1.x was released, and then added also into SQL Server.

Basically, I think the Access query optimizer does just what you think it does: simply returns a precalced value of the number of records or takes it from the primary key index, from criteria results applied to indexed fields, and in the worst case, from counting the # of records in the result set.

Anyways.

I've often wondered why the developers couldn't keep an internal count of all active rows,

Well, just like there are OO purists and OO pragmatists (and functional and procedural programmers who are simply weirdos or archaic dinosaurs, from the perspective of many OO purists), there are also SQL purists and pragmatists.

The Purists hate non-SQL "features" that do things like return row numbers (ROWNUM, in Oracle), and various other non-SQL hacks to make certain things that are hard or obscure to do with SQL easier (or possible...) to implement without resorting to other SQL hacks, like cursors in stored procs.

The Pragmatists are 80/20 or 90/10 types, and don't mind a non-pure solution (they're happy with 80-90% leveraging RDBMS features) because they just want to get the job done and working.

I would think that putting rules in for calculating COUNT(*) would probably not be that hard to do, and maybe it's not a high priority for the query optimizer programmers.

Re:PGSQL has its own gotchas

[jadavis]

DB2 uses locks to maintain transaction isolation.

So basically, in order to make an unqualified count(*) run fast (very rare operation for me), you're suggesting PostgreSQL lose it's MVCC, which has proven to be highly scalable and very successfull (which affects all transactions)?

Yeah, uh... nice trick. They keep a counter running so that something I don't care about (unqualified count(*)) returns quickly. If I really wanted that functionality I'd use a trigger counter in PostgreSQL. At least it's optional.

Re:PGSQL has its own gotchas

[jedidiah]

Defining a primary key would be a good start.

Of course your result will only be valid for the SCN when you started your "transaction". However, this is a problem that's not merely limited to functions and select statements.

Re:PGSQL has its own gotchas

[jadavis]

I'll just add that this is a recurring discussion on the lists, and someone may get around to trying to improve this situation in some way. There are a few ways of going about it, but all have a cost. The most likely would probably be an option for an index to store tuple visibility information. That way you can do a full index scan rather than a full table scan, which would take less time (only because the index is smaller in size).

Anyway, if someone cares about count(*), then PostgreSQL 8.0 is not for them. But for those who have other needs as well, I encourage them to actually try real data on PostgreSQL, because I think you'll be impressed in many regards.

Re:PGSQL has its own gotchas

[Anonymous Coward]

However, this is a problem that's not merely limited to functions and select statements.

Indeed! And the SQL spec defines exactly how the different transaction isolation levels require. And PostgreSQL (unlike some other databases) meets this spec. One side effect is that some operations (count(*), being one) are harder to handle "correctly" as defined by the SQL spec than it would naively seem.

Re:PGSQL has its own gotchas

[smchris]

You've got to be joking about PostgreSQL having a weak optimizer. If it's weak, only the computer can tell.

My experience with a small database I'm working on at home is similar. For example: 6600 record test DB, server-side procedure for a regional report by first three zip code numerals presented by descending sum with a "blank or foreign" bucket (an even 400 sums in this case) is an eyeblink across the home network coming from a 900 mhz slot II and a 5400 rpm IDE drive using PostgreSQL 7.6.4. I'm pleasantly surprised and am seriously considering using real data and not drawing from temp tables built overnight for all demographic summary reports.

Re:PGSQL has its own gotchas

[einhverfr]

Indexes don't contain MVCC visibility information. You have to actually look at the rows.

Now if you try to do this with an index scan, you end up looking at the index to find the rows, then looking at the rows to see if they are visible. It is actually beneficial to just look at the rows.

Now, if you say
select count(*) where primary_key > 390
then depending on the statistics of the table, it may use an index scan.

Re:PGSQL has its own gotchas

[Anonymous Coward]

There you go with the FUD. PostgreSQL now has auto vacuum. There are no performance gotchas with PostgreSQL, and it meets or beats MySQL in every area.

Re:PGSQL has its own gotchas

[Frank T. Lofaro Jr.]

count(*) counts even completely NULL rows in Oracle8i Enterprise Edition Release 8.1.7.4.0.

Is this an Oracle bug or is this correct? What does PostgreSQL do in this case?

Re:PGSQL has its own gotchas

[ComputerSlicer23]

Hmmm, curious, I might actually have to take back what I said. You are correct my copy of Oracle doesn't either (8.1.7.2). However, if you do a count on a field that is allows NULL's, it doesn't count the NULL's. I was taught that count(*) was only supposed to count rows where all fields that are NOT NULL. Sometimes there is a difference, and you need to be aware of it. However, in the Oracle documentation (Oracle 8i: The Complete Reference) it describes the behaviour you state. Hmmm, maybe that's in a different database (the guy I learned a lot of SQL from was a Firebird head, back when it was still a commercial product by Borland called Interbase). I of coure double checked what I wrote above by counting a field that was allowed to be NULL and assumed that it's behavior was consistant when applied to all fields.

I don't have a copy of the SQL standard handy to look at to see what behavior it describes.

Kirby

Re:PGSQL has its own gotchas

[Anonymous Coward]

And you pay for it on *every* insert and delete.

I don't. Sucks to be you.

Re:PGSQL has its own gotchas

[Anonymous Coward]

Wikipedia must have a page size limit.

Re:PGSQL has its own gotchas

[CliffEmAll]

Installing the version packaged for Debian Sarge systems automatically installs and enables the autovacuum daemon. Or at least it did about two months ago.

Re:PGSQL has its own gotchas

[ckaminski]

How about distributed txns?

Re:PGSQL has its own gotchas

[bdbafh]

where 1=1 chew on that a bit. if one is going to *maintain* an aggregate, that would imply locking the base table in order to get a consistent read of that entity. oh, you're going to maintain the SCN of when the aggregate was calculated and the difference from that? as of what point? talk about a race condition. as others have posted, the optimizer should use an index, PK first, that has not nulls in it to determine the row_count for the table, not something that is maintaining a row_count in a multiuser system.

Re:PGSQL has its own gotchas

[bdbafh]

SQL> set autotrace on explain statistics timing on or SQL> exec dbms_support.start_trace(binds=>false, waits=>true); and examine the trace file, hit it with tkprof. your (wrong) opinion will change. methinks that your statistics are stale, or non-existent. all of the oracle docs are up on otn.oracle.com. read them.

Re:PGSQL has its own gotchas

[bdbafh]

upgrade or die. 8i R3 is now desupported. logon to metalink. click on support/availability. enter your platform/version info. eureka! 8.1.7 is obsolete. pick one: 9.2.0.x 10.1.0.x wait for 10.2 upgrade or die.

Re:PGSQL has its own gotchas

[bdbafh]

do you have any idea as far as how many security vulnerabilities exist for oracle 8.1.7.2? Dude, I hope that the only ip address that the listener is configured for is 127.0.0.1. loadphp can crash your deal, and allow for takeover of the system. several iterations of remote vulnerabilities in the listener. several security alerts on the oracle executable. http://www.oracle.com/technology/deploy/security/a lerts.htm

Re:PGSQL has its own gotchas

[ComputerSlicer23]

Yes, yes. I know, I've been down that path. My problem is two fold. First off, I need to upgrade for several reasons. However, in my experience, upgrades leads to new bugs, new performance problems. Right now the machine is stable, and runs just fine. I'm leaving it alone. In general new problems all around. I need to get to 9i. That's all there is to it. I believe even 8.1.7.4 is being EOL'ed (I thought it was in Dec 31, 2004). I need to get to 9i for support reasons. I'll get the security fixes then. The machine is due to get upgraded RSN (and has been for 18 months *grin*).

Second, if anyone who isn't part of my organization can get anywhere near that machine, I'm already so incredibly compromised it's not even funny. There are several layers of firewalls between it and anyone not physically in the server room. There is an application server that is allowed to access it, and that's pretty much it. Any hacker worth his salt would have complete control of all of my machines by the time he could get past the firewalls to get to that machine.

Kirby

Re:PGSQL has its own gotchas

[Anonymous Coward]

Hey Sxooter-

Why didn't autovacuum make it in to 8.0?

CSN

Re:PGSQL has its own gotchas

[bdbafh]

yeah, I've got one of those boxes also. still running 9.2.0.3 on a RH8 install. only ports open are ssh and ssl, tns listener running on loopback only. gotta hit apache on it to get in, audited out the wazoo. can't wait to deprecate that box for Apache 2 / 10g / RHEL 3.0 ES update 4 / (something faster than a P II 350)

Re:PGSQL has its own gotchas

[jedidiah]

If Postgres has problems handling concurrency for the simple case of Select count(*) then there are likely some serious systemic problems with Postgres that makes it unsuitable for serious use. The "big boys" are engineered specifically so that queries of that kind are not a problem. If Postgres can't handle that degree of concurrency then it's still not quite ready for primetime. ...although that's not really the issue here. Postgres is scanning the table when it should be scanning an index.

Re:PGSQL has its own gotchas

[thing12]

exceptionally poor performance of aggregates like COUNT(*)

So, I guess you'd rather get the wrong answer quickly than the right answer in as much time as it takes? It's a shortcoming of MVCC - you get high concurrency and reader/writer isolation in exchange for problems like this. They'll probably change the structure of the indexes to avoid the sequential scans at the expense of extra storage but until then we're stuck with them.

No, no, no. Serious Question.

[Anonymous Coward]

Are there similar problems with oracle
or other "top of the line" database systems?

Or LAPP

[Quattro Vezina]

LAPP, maybe, but certainly not WAPP.

Linux + Apache2 + PostgreSQL + Python == kickass webserver

Re:Or LAPP

[Anonymous Coward]

except for Python, you're right.

Re:Or LAPP

[Dasch]

Yup, either PHP, Perl or Ruby (the latter would make it LAPR though...)

Re:Or LAPP

[Anonymous Coward]

FreeBSD + Zeus + PostgreSQL + Java

hmm. maybe LAMP was just a cute buzzword all along.

Re:Or LAPP

[Gherald]

except for Python, you're right.

Actually, Python is awesome for webservers. I take it you've never used Zope?

Re:Or LAPP

[Anonymous Coward]

Or, LARP?

Re:Or LAPP

[Anonymous Coward]

Sure I have. It sucked ass, with its poor transaction support, threading support and leaky libraries, not to mention the monkey patches the amateur developers used to get the piece of **** running in the first place.

Re:Or LAPP

[Anonymous Coward]

FAPP

FreeBSD + Apache + PostgreSQL + Perl

== more kickass webserver!

Also, 'FAPP' is amusing 'cos you know what fapping is, right? A tissue becomes a fapkin when you fap, etc, if you need a clue.

Re:Or LAPP

[archen]

Linux + Apache + Python + Pgsql = LAPP
Windows + Apache + Python + Pgsql = WAPP

Windows is an OS too.
too sounds like 2

Windows + Apache + Ruby + Pgsql = OS2 WARP

Re:Or LAPP

[jadavis]

A brighter LAMP: Linux Apache Middleware PostgreSQL

Re:Or LAPP

[Slime-dogg]

Or... inevitably:
WARP

Re:Or LAPP

[elemental23]

Better that than LARP.

Looking good!

[unixMafiA]

Good to see they're back in the race, coming up with something serious - and Thanks, Oracle! for being a tough competitor providing a solid standard to keep up with. PostgreSQL is, in my opinion, the most presentable of not-too-much-$ database solutions, I sure like to present clients with a new version of P rather than the crummy image of MySQL (by no means an inferior product but not profiled in the same, serious way). Ah and let's see how it runs on Mac OS/X - Darwin, too. Maybe my little 12" will become all the more a p0w4hful tool because of it - and it had better, I haven't paid the thing off, yet! ...

Two more features...

[teqo]

... and it would really really rock!

• Hot tablespace-based backups, combined with write-ahead log backups, as seen in Oracle since version 8 (or even 7?) This is an extremely nice feature when you have large databases and no chance for regular scheduled downtime and still want backups, both complete and incremental ones. Compared to the export-based feature of PostgreSQL, it would put way less load onto the server, because tablespace-based and WAL-based backup bypasses the SQL engine, so it is copying a (for example) 200 GB files vs. 200 GB query-based export
• Better and more integrated replication. There are a number of independent projects that want to create replication add-ons, like pgreplication and the older, more academic Postgres-R, but that's not really production quality so far. According to some consultant that is working tightly with PostgreSQL and the developers, they are working on it, but he was really hedging when asked about advanced features and was theorizing how practically impossible and/or expensive multi-master realtime replication would be... An optional feature for many users, granted, but still something you might want for scaling beyond certain limits.

Said that, PostgreSQL is a really great thing, and being FOSS, I could of course always go ahead and add the named features... .)

Re:Two more features...

[Anonymous Coward]

"Hot tablespace-based backups" - you need to look at point-in-time recovery. I think that's what you're talking about.

Re:Two more features...

[JohanV]

Most of your first request is already implemented in PostgreSQL 8. You can combine a hot backup of the files on the filesystem with the WAL-archiver to have the backup feature you want. It is not per tablespace (yet) so you have to backup your entire database.

For the second request, keep a close eye on the mailinglists. Affilias has hired a core developer to make it happen.
The first stage, master-slave replication, has been released in the form of Slony-1. Yes, it is an add on. No, it is not integrated. But you can add Slony-1 to a running system and add slaves without ever taking the master down, and it is backwards compatible so you can even use it to upgrade running 7.3.x installations to PostgreSQL 8.
The second stage, Slony-2, will be a full multi-master replication solution. (I read something about a 'kickoff' meeting today hosted by Affilias.) The goal is to be able to take a single, out of the box installation of PostgreSQL, plug Slony-1 into it, replicate the database to another box and when that box has caught up switch to full multi-master mode under Slony-2.
The code won't fall out of the sky tomorrow, but people are working on it.

Re:Two more features...

[StormReaver]

"But you can add Slony-1 to a running system and add slaves without ever taking the master down...."

I was under the impression that Slony was a patch to PostgreSQL that had to be specifically compiled into PostgreSQL. Is that not the case?

Re:Two more features...

[JohanV]

Hot installation was an explicit design goal of Slony-1.

You have to compile Slony-1 for which the sourcecode from PostgreSQL needs to be present on the system you are compiling on. After that, you log in to your database and run a SQL script to tell the database where the compiled code is. That is it, no restarts, no switching of the PostgreSQL binary required.
This dynamic adding of extensions is one of the standard features of PostgreSQL and is used for many extensions. For instance, all the stored procedure languages (pl/pgsql, pl/R, pl/PHP, pl/Perl etc.) can be added this way, as do large features such as PostGIS.

MySQL and Postgres

[ChristTrekker]

Just when I get about ready to start another project with MySQL, another bit of news about Postgres comes along, making me wonder if I should make the switch to a "real" RDBMS. After all, if I want to be a professional developer, I should be using "real" tools, not "toys", right? OTOH, I can find more references and resources for help if I go the MySQL route, making my life easier. Dilemmas, quandaries, decisions, oh my!

Re:MySQL and Postgres

[Anonymous Coward]

If MySQL's features are good enough for you but you want real transactions and ACID and you are sick of the gotchas, then try SQLite. If you are using Perl, then there is a DBI module on CPAN that contains the entire SQLite database, so you only have to run "cpan -i DBD::SQLite" and you can DBI->connect right away! You can even install it in your home directory when you are on a shared hosting, even if you don't have a shell access! Just make a CGI script with "use CPAN" that will install DBD::SQLite and run it with your browser! :) Now, how cool is that? Advantages: it is faster than both Postgres and MySQL, it support truly atomic, consistent, isolated, and durable (ACID) transactions, even after system crashes and power failures, no setup or administration is needed, a complete database is stored in a single disk file that can be freely shared between machines with different byte orders, it supports databases up to 2 terabytes in size, sizes of strings and BLOBs limited only by available memory, small code footprint (less than 30K lines of C code! less than 250KB code space!), it is FAST, it is ROBUST (well-commented source code with over 95% test coverage), it is self-contained (no external dependencies!), it is in the public domain. Cool, isn't it? I love the ability to move the database by just moving the file. That way my websites are truly portable across any platform with Perl (is there anything without Perl?), and I don't even care what database is prefered by my ISP. EOP (end of plug). :)

Re:MySQL and Postgres

[Zemplar]

Subscribe to the Postgres lists and you will find some activity of the highest caliber. Those that post replys to inqueries really know their stuff and are quite good at getting questions answered.

Re:MySQL and Postgres

[desoi]

The PostgreSQL documentation is very well done and I'm simply amazed at the quality of support on the PostgreSQL mailing lists. The core PostgreSQL developers contribute significantly to answering support questions.

Re:MySQL and Postgres

[TheLink]

In my experience going the postgresql route isn't difficult (as long as you don't want clustering - AFAIK clustering isn't that easy whether on Oracle, MySQL or Postgresql. It might on other platforms e.g. VMS or Tandem, but... :) ).

In fact there might even be less gotchas in some situations. MySQL seems to do a few more things that are strange/icky (though the ickyness is usually well documented, often with defense of the icky behaviour from the MySQL devs ;) ).

The issue with postgresql I can think of that could hit beginners is that you might have to recompile your kernel if you are using one of the *BSDs with too small shared mems and other stuff.

You should be able to get help from the mailing lists if stuff is not covered by the docs already.

MySQL does work for many people. But given that postgresql is free and not really difficult, I suggest it's worth just installing to mess around with.

p.s. rollback a "drop table" in front of an Oracle DBA for laughs... (make sure you do a BEGIN first and autocommits are off, or the laughs on you ;) ).

Re:MySQL and Postgres

[Martin Foster]

I switched a an open-source project from MySQL to PostgreSQL a year or so back. The application of proper transactions, referential integrity, views and stored procedures really cleaned up the code.

However, PostgreSQL is not necessarily the easiest DBMS to get used to. For one, some platforms such as *BSD will require a recompile of the kernel to support a bit more then 32 concurrent connections. While well documented if you know what your looking for, this can prove to difficult to implement if for example your on a shared hosting system.

Documentation can be cryptic, tending to be more like a reference manual then an actual manual to teach you how something works. When it comes to optimizing the database itself this becomes painfully obvious as certain switches and options in the postgresql.conf file do little more then offer a one line description with no real clue on how a change will affect it.

In MySQL for example there are sample configation files which show a 'typical' configuration for small, mid-sized and dedicated setups. I have yet to see something similiar and such discussions on the newsgroups have generally shot it down because of the exotic configurations out there.

Vacuuming is one of those things that can really confuse people. While in MySQL deleting and altering rows has no real lasting effect for the user, this is not the case with Postgres. When a row is deleted, the information remains, but is unlinked, making the system run less IO but forcing you to juggle vacuuming, re-indexing and server operations.

You could make use of the auto-vacuum daemon available. I found however, that performance suffered greatly when it was being used on my live system.

More often then not, documentation will speak of load testing and tweaking the server at different values to see how it works. This is sound advice if you have the hardware and time necessary to get things going properly.

On light loads, Postgres can be great, but once you start pounding at it it will slow down unless you know what your doing. The learning curve is far greater then MySQL, the documentation could stand to be more descriptive to end-users/non-Oracle DBA's and the tools available for Windows less advanced but the feature set will make it worth while if you can devote some time for trail and error.

Re:MySQL and Postgres

[quantum bit]

The issue with postgresql I can think of that could hit beginners is that you might have to recompile your kernel if you are using one of the *BSDs with too small shared mems and other stuff.

I don't know about Net and Open, but on FreeBSD these can all be set with boot-time tunables in loader.conf. No kernel recompile necessary. Unfortunately a lot of the postgresql howto docs are stuck back at FreeBSD 3.x and still show the kernel recompile as the only way.

A few of them can even be set hot with sysctl, but some do require a reboot.

Re:MySQL and Postgres

[ChristTrekker]

I do use a *BSD. Where can I get more detail on this issue? Kernel recompile, gahhh...

Re:MySQL and Postgres

[ChristTrekker]

Sounds like I might get a better database with PG, but it would also force me to become a better database admin. Dunno if I have the time for that, unfortunately. This is only a hobby project, and I have barely any time the way it is.

Re:MySQL and Postgres

[TheLink]

http://www.postgresql.org/docs/current/static/kern el-resources.html

Sure you may not have to recompile the kernel, but I actually find kernel recompiling and installing on a modern FreeBSD system a lot easier and _usually_ less problematic than say building/installing a new Linux kernel on RH/Suse Distros.

e.g.
backup the old kernel...
Edit the relevant kernel config file.
make buildkernel kernconf=KERNELNAME
make installkernel kernconf=KERNELNAME.
reboot.

I've had more probs with updating the kernel on Suse 9.1 (for dunno what reason) and RH Linux (esp back in the stupid lilo days - oops forgot to run lilo, oops forgot to ensure that stuff in /boot is fine)..

Sure you can do stuff with the loader in FreeBSD, but you still need to reboot, so I figure I might as well recompile with the necessary options. The main ickiness to me is the need to reboot.

A better question

[Anonymous Coward]

Can anyone say WAPP instead of LAMP? Maybe.

Can anyone say database instead of data store? YES.

A better question

[Anonymous Coward]

Can anyone say WAPP instead of LAMP? Maybe.

Can anyone say database instead of data store? YES.

(Sorry for the broken link in my previous post.)

An OOP DB

[p0rnking]

I've done a little bit of work with different DBs (Oracle 9i/10g, db2, Ms SQL 2000, MySQL, and some will laugh, Access) ... and as far as I know, they're not OOP DBs. If they're not, then whats the advantage of an OOP Db compared to everyone else?

Re:An OOP DB

[Anonymous Coward]

I've done a little bit of work with different DBs (Oracle 9i/10g, db2, Ms SQL 2000, MySQL, and some will laugh, Access) ... and as far as I know, they're not OOP DBs. If they're not, then whats the advantage of an OOP Db compared to everyone else?

OOP DB is just a buzz word. RDBMS != Object Store ergo Object Relational Database == Bullshit. See this post for more details.

correcting you if you're wrong ;-)

[illusion_2K]

SQL Server may have some missing functionality, but plain text dumps work perfectly well (albeit you might need to do them one table at a time). They're also incredibly simple to do.

Most of Microsoft's products may be crap, but SQL Server does seem to be an exception to the rule. All in all it works pretty well in my experience. However 9/10ths of SQL Server deployments would probably be just as well off with PostgreSQL. :-)

Re:correcting you if you're wrong ;-)

[mborland]

You're probably referring to BCP or a similar function, which dumps a single table of data at a time to a text file in a specified format. What the original poster is probably referring to is the ability to dump a database into an SQL script that, when run, completely re-creates the schema, data and other features of the database. It's a very simple backup/restore mechanism and makes various conversions and other operations much easier.

Re:correcting you if you're wrong ;-)

[Anonymous Coward]

Yeah, MS's branch of Sybase (aka SQL Server) is decent.

Riiight....

[Anonymous Coward]

PGSQL has its own gotchas. For example, exceptionally poor performance of aggregates like COUNT(*), relatively weak optimizer.

Who needs correct data if you can have incorrect data Real Fast? Riiight.... Have you ever managed IMPORTANT data? No? It shows.

Use Slony for replication

[chriskl]

Check out the new Slony replication engine:

http://www.slony.info/

It is probably the best master->slave data replication engine for PostgreSQL at the moment. It is free and developed by one of the core developers.

Yes! 8.0 - I have been waiting

[nighty5]

I've been running 8.0 RC1 for a while because of a few features I missed...Bare in mind my database projects are for fun so my featuresets may be considered low profile :)

Especially the ability to ALTER TABLE "x" RENAME TO "Y"; (!)

Yes mySQL has been able to do this for a while, I got sick of exporting out the data and re-importing, but now we dont have too!

It might be also a good time to mention that EMS have released Lite versions of their product that are free. (pgsql, mysql, mssql and interbase)

Although I'll probably end up buying a non-commerical license to support them.

Check it out here: http://sqlmanager.net/news/607

Re:Yes! 8.0 - I have been waiting

[chriskl]

Erm, you've been able to rename tables in PostgreSQL for many, many years.

Re:Yes! 8.0 - I have been waiting

[nighty5]

whoops - my mistake.

I meant to say changing a column type.....

It's still LAMP

[Anonymous Coward]

It's just Linux Apache Mono PostgreSQL

phpPgAdmin 3.5.1 works great with PostgreSQL 8.0.0

[chriskl]

For up-to-date web-based administration of PostgreSQL, try phpPgAdmin:

http://phppgadmin.sf.net/

It's easier to install and admin than mysql

[hqm]

The dirty secret is that Postgres is actually easier to install and administer than mysql. I don't want to get into a religious argument with mysql users but ... oh hell yes I do.

I have worked with self-educated programmers who did not know how to do simple table joins or even modestly complex SQL queries or transactions, because they had learned what they thought was SQL by using Mysql. There is a whole generation of developers who now think that transactions aren't really necessary in a database application.

Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

Re:It's easier to install and admin than mysql

[matchboy]

Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

Exactly, the biggest mistake that I see among people is that they compare MySQL and PostgreSQL when they're not even in the same arena. Both being open source databases, but their feature sets put them in much different categories. PostgreSQL is much more advanced than MySQL..and it's a lame argument at this point. Watch out Oracle. ;-)

Re:It's easier to install and admin than mysql

[mark-t]

It's close...

But not quite an oracle killer, IMO.

In particular, it needs native SQL support for handling of tree-like data structures, either via a construct like Oracle's CONNECT BY, or via SQL99's WITH RECURSIVE.

Now while I know that postgresql allows you to accomplish this with procedures, it would still be preferred if you could just do it inline. I'm equally aware of at leaast one freely available 3rd party patch to Postgresql that adds CONNECT BY, but it's not as elegant as a solution that is embedded into Postgresql itself.

Re:It's easier to install and admin than mysql

[Arkham]

It's just a shame that PHP doesn't include the same level of support for PostgreSQL as they do for MySQL. I like PostgreSQL very much, but whenever I look into the capabilities of a client's hosting facility, they always have MySQL and never PostgreSQL.

I don't know that PostgreSQL is an Oracle killer yet. Maybe for a single server/single database solution. But for mission-critical enterprise apps that need 100% uptime (4 nines anyway), you have to have active-active databases, backups while the app is running, etc. This release is a huge step in the right direction, but Oracle isn't replaceable just yet.

Re:It's easier to install and admin than mysql

[matchboy]

PHP/PostgreSQL hosting We FAVOR PostgreSQL. :-)

Re:It's easier to install and admin than mysql

[justins]

It's just a shame that PHP doesn't include the same level of support for PostgreSQL as they do for MySQL.

It's also a shame that they don't support the modern, multi-threaded version of Apache. At this point PHP pretty much deserves to be overtaken by another product, perhaps using the same language, it's just that nobody has bothered yet.

Re:It's easier to install and admin than mysql

[DigitalRaptor]

Threads have to do solely with 3rd party plugins, not with PHP itself. PHP5 is completely thread safe.

Re:It's easier to install and admin than mysql

[davegaramond]

It's not a patch, it's a contrib module that provides a function. And why would it be "less elegant" (whatever that means)? It comes with Postgres' distrib and you just need a line or two to use it. Also, adjacency model for medium to large trees is sloooooow. Use materialized path or nested set model.

Re:It's easier to install and admin than mysql

[justins]

PHP5 is completely thread safe.

Sure it is.

Re:It's easier to install and admin than mysql

[Desert Raven]

Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

Sorry, not yet.

I currently work in an environment using 5-way multi-master replication. Oracle does this, PostgreSQL does not. Granted, it's a feature very few developers need, but it negates your assertion that there is "no reason to use any other relational database at this point".

Re:It's easier to install and admin than mysql

[randall_burns]

Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

I'd be careful with statements like this. Postgres competes well with Oracle for a variety of applications. However, Oracle still has some VLDB and high availability features that aren't yet available for Postgres. Also, for a lot of folks, what matters isn't the database, but stuff like the availability of financial packages. There is also a lot of inertia with Oracle-that could be handled by creating libraries that give Postgres a higher degree of API and language compatibility with Oracle. I'm rooting for Postgres-but Postgres has a ways to go before Oracle is truly dead.

Re:It's easier to install and admin than mysql

[killjoe]

Have you seen oracle mode firebird?

Re:It's easier to install and admin than mysql

[DigitalRaptor]

According to the founder of PHP it is.

Re:It's easier to install and admin than mysql

[johnnyb]

"ut for mission-critical enterprise apps that need 100% uptime (4 nines anyway), you have to have active-active databases, backups while the app is running, etc."

I used to work at EDS back around 2001 and at that time Oracle clustering sucked majorly. It often did not perform as advertised.

Also, I'm pretty sure with this version you can do backups while the app is running (NOTE - you could always do them w/ dumps, but I think now you can even do them w/ binaries + transaction logs).

Re:It's easier to install and admin than mysql

[cpeterso]

no, he says the PHP core code is thread-safe, but many of the third-party libraries CALLED by the PHP core code are NOT thread-safe. If PHP was actually thread-safe, they would only call thread-safe libraries OR write thread-safe wrappers for unsafe libraries.

Re:It's easier to install and admin than mysql

[DigitalRaptor]

In that case, Apache isn't thread safe, because it calls PHP, which calls something else which isn't thread safe.

And Linux must not be thread safe, because requests to port 80 call Apache, which... and so on, and so forth. It's a matter of semantics.

Re:It's easier to install and admin than mysql

[electroniceric]

As someone currently using Postgres in production, I have to agree with the parent and another commenter in this thread on a couple issues.

Postgres is now quite easy to install and administer. The admin tool, pgAdmin3, which ships with the Windows distribution compares well with any admin tool I've ever used. There are quite a few procedural language bindings being built, from Python to Ruby to PHP to Java. This presents some great opportunities for integration with tools in those languages.

The fact that it is BSD-licensed makes it a no-brainer for uses involving developing custom software - it's great to be able to tell a client that nobody has to pay for or manage any licenses for this database.

Unfortunately, Postgresql does have some really key gotchas for use in demanding database applications:

1) Auditing
Out of the box, it is really difficult do auditing. Not only is there no standard auditing, but your option: rolling your own trigger-based auditing is also difficult due to limitations in various procedural languages and the versions in which they appear. This is really a shame, because given the number of people who have put effort into rolling auditing systems, it would be nice to see some code permanently attached to the distribution.

2) Documentation
Not only are the manuals sparse, but the books that exist tend to repeat what the manuals say, leaving some key tools and features relatively undocumented. A good book strictly on programming PL/PGSQL or PL/Perl would be of tremendous utility.

3) Backup and Migration
Within major releases, the utilities to backup and restore a database are actually quite good, if minimalist in design. However, there's very little provision for migrating between versions, and given the different features available in each version, migration is still something one has to consider regularly.

4)Replication and distributed processing
There is now a master-slave replication tool, Slony, that I believe is slated to be part of the next release. This is a good step forward. Work towards full multi-master replication and/or clustering would really make this a good contender for a lot of enterprise use.

Overall, Postgres is a pretty darn cool database. If it can make its overall presentation a bit slicker, it could really be a contender to topple Oracle, IBM and Microsoft.

Re:It's easier to install and admin than mysql

[mark-t]

The facilities that come with Postgresql do not add CONNECT BY and PRIOR, which comes from Oracle. Those keywords and the functionality they provide are added by a 3rd party patch to Postgresql.

And btw, less elegant means more ugly. If you have to ask what ugly code is, then you're probably not a programmer.

The elegant solution is to use a single SELECT statement. No function or procedure calls, just a single SELECT statement with no references to anything but table names and columns, and the conditions which guide the SELECT.

Ideally elegant IMO is SQL99's WITH RECURSIVE construct. Slightly less elegant is Oracle's usage of CONNECT BY and PRIOR in a SELECT statement. Postgresql's solution to this is at the very bottom of the list... use user-defined functions which are implemented in plsql or some other server-side language.

I don't buy the arguement that this is better than not being able to do it at all because this is not a mere one-time use feature that nobody in the real world uses, and WITH RECURSIVE support has been on Postgresql's TODO list for I don't know how many years now (and I notice it's been removed for 8.0's TODO list, and since it's not actally part of 8.0, I'm guessing it means they've given up trying to implement it because it's too hard).

Secondly, what makes postgresql's solution to querying tree structures extremely ugly is that a person who just wants to perform a customized SQL query on a tree-like structure to suddenly has to write their own functions, and they have to write a new function for every new type of table they want to query, rather than just creating the SELECT query ad-hoc, based on their requirements and knowledge of the structure of the table.

Re:It's easier to install and admin than mysql

[Anonymous Coward]

Don't forget true clustering capabilities, and ability to support a bevy of COTS products out there.

Re:It's easier to install and admin than mysql

[mw]

you probably want to have a look on the postgresql ltree stuff, and you will never look back to "connect by".

Postgres-R

[SuperBanana]

There are a number of independent projects that want to create replication add-ons, like pgreplication [postgresql.org] and the older, more academic Postgres-R, but that's not really production quality so far.

Well, the gig was up after everyone figured out that Postgres-R was just Postgres with a big muffler and wing.

awesome

[idlake]

Some people say that providing open source applications on Windows helps proprietary software and the Windows monopoly, but I don't think so. Software like Apache, PHP, PostgreSQL on the server, and Mozilla Firefox, Thunderbird, and OpenOffice on the client lets Windows users gradually move over to open source applications. I think that's a much better way of getting people to switch than to ask them to do an all-or-nothing switch.

Re:awesome

[pebs]

Some people say that providing open source applications on Windows helps proprietary software and the Windows monopoly, but I don't think so. Software like Apache, PHP, PostgreSQL on the server, and Mozilla Firefox, Thunderbird, and OpenOffice on the client lets Windows users gradually move over to open source applications. I think that's a much better way of getting people to switch than to ask them to do an all-or-nothing switch.

I agree. Especially when it comes to tools like this. A lot of developers are developing on Windows (not necessarilly by choice) and deploying to Linux. Having a Windows version helps for this case. At some point you find that all the tools you are using in Windows are available in Linux and the switch can be made. This would not work if there were no Windows versions.

Cool

[CaptainZapp]

Thanks for the link.

Actually it's good to be wrong when you get the chance to learn something in exchange.

Re:Cool

[DrSkwid]

I only found it myself a couple of weeks ago when I had to research the subject.

I'm sat here waiting to take delivery of a new server to try it out on =)

Way to go, Savepoints feature finally available

[Anonymous Cowherd X]

As a seasoned PostgreSQL user I'm very excited about this release, I especially like the new Savepoints feature (described in this article). This wonderful feature finally makes it possible to roll back only part of a transaction. Sweet!

Re:Way to go, Savepoints feature finally available

[chriskl]

Not only that, but inside a PL/PgSQL stored procedure you can use the new TRY/CATCH exception handling to do stuff like this:

1. try an insertion into a unique column
2. value already exists, so catch the unique violation exception
3. try again from phase 1 with a new value

For example...

Re:Win32?

[mborland]

Having PostgreSQL run native in Windows is a Good Thing. Although MSSQL does have some features that Postgres doesn't, most of them are not used (or should not be used!) by developers.

I expect this to take more of MSSQL's market share on the Windows space...for organizations that can't yet stop using Windows, but don't want to shell out for a database if they don't have to.

Re:Their database server is down

[cloudmaster]

Don't they know that MySQL is the one to use for web backends? :)

PostgreSQL 8 Hosting!

[matchboy]

We got up early to provide customers with the full release before they started their workday. :-)

PostgreSQL 8 Hosting!

Re:PostgreSQL 8 Hosting!

[Per Wigren]

Do you let users use stored procedures and triggers in the PostgreSQL databases?

Most PgSQL-providers don't, which annoys me to hell because then what's the point?

Re:PostgreSQL 8 Hosting!

[matchboy]

Yes you get your OWN running version of PostgreSQL. :-) The pgsql server runs as your user so you don't cause any problems to other customers. Your own instance..own port, etc.

Re:PostgreSQL 8 Hosting!

[Anonymous Coward]

Command Prompt, Inc. provides complete postgresql
hosting. Including stored procedures. The instance runs as your user so there are no security concerns.

http://www.commandprompt.com/

Switching from MySQL..

[slashmojo]

worth the effort? Is it in fact much effort or will my code work more or less the same with this anyway?

I mean it sounds great and all but maybe its a case of 'better the devil you know'..

Slony

[caluml]

But does the slony ebuild work yet? :)

Slony1 and PostgreSQL sitting in a tree...

[matchboy]

Slony1, open source postgresql replication!

I'm impressed with SqLIte.

[Futurepower(R)]

For lite uses, and many heavy ones, SQLite seems excellent. I haven't used it yet, but whoever writes for the project is an excellent communicator.

In my experience, most open source projects, and almost all commercial products, have a (maybe mostly unconscious) plan: "We will carefully measure how much hassle people will accept, and make sure we don't document anything more than enough to just barely keep people from rejecting us."

It's common to visit an open source project and find that, yes, they have a new version, but the manual is two years old. There are plenty of commericial projects that are the same way, like Netgear's FVS318. Their reference manual is for version 1.4, but the latest version is 2.4.

An advantage of open source projects is that they are usually far more honest than commercial projects. I love this from the PostgreSQL What's New page: "Although tested throughout our release cycle, the Windows port does not have the benefit of years of use in production environments that PostgreSQL has on Unix platforms and therefore should be treated with the same level of caution as you would a new product." Marketing people are generally so dishonest that they would not allow an honest statement like this.

MySQL is a non-standard implementation of SQL. That's a problem that's probably partly caused by not doing good documentation. If they had documented everything as they wrote MySQL, they might have seen what a mess they were making. Bad documentation obscures programming messes.

PostgreSQL has an elaborate documentation system, and the new features are very impressive.

Re:I'm impressed with SqLIte.

[LetterJ]

I actually use SQLite in a lot of places that people use MySQL. A lot of the time, data that's shoved into MySQL would work just fine in a flat file of some sort (i.e. it's in a single table and doesn't change much). However, SQL is a nice way to find things and doesn't requires writing your own parser for your new data format. SQLite basically gives you a single file that's your database and SQL to interface with it. Since the database is self-contained, it can be moved around, easily backed up and replaced/upgraded, etc. It can be included with the web application without having to do an SQL dump, etc.

Re:I'm impressed with SqLIte.

[Anonymous Coward]

> Marketing people are generally so dishonest that they would not allow an honest statement like this.

Oracle's marketroids are well-known for discouraging use of Windows as a host platform, actually. Solaris was their favorite, though internally they're mostly using Linux now, so possibly favor has shifted.

Re:I'm impressed with SqLIte.

[a_karbon_devel_005]

I haven't used it yet,

... post over.

Re:I'm impressed with SqLIte.

[LurkerXXX]

Microsoft sells a database (SQL 2000) as one of their major products. Sun boasts about it's Oracle ties. Think there might be some other reason than stability that Oracle's marketroids might push the Solaris platform instead of Windows...?

Re:I'm impressed with SqLIte.

[jadavis]

I haven't used it yet, but whoever writes for the project is an excellent communicator.

I was initially interested in the project a while back, just because I thought it might have some simple uses. I was very turned off by the website actually.

They are trying to show how it's faster than MySQL or PostgreSQL (I'll ignore the fact that they use really old versions and haven't updated the site), and the "common operations" they show are completely rediculous.

They say that to acheive speed you have to put multiple operations in one transaction. Ok, fine, good advice for any database. But some operations don't belong in the same transaction. And 25k insert statements in one transaction is not exactly a "common operation" it's more like a data load of some kind (for which postgresql has long had an alternate mechanism called COPY).

For live application performance, the average TPS is what matters, because the application spec determines what is inside one transaction and what is inside the next.

The bechmark is the worst kind: completely contrived, and misleading to those that read it. And worse, the author misses an opportunity to educate the novice readers, and instead misinforms them.

Maybe SQLite is a good product. But trying to impress people by showing the speed of one big transaction makes no sense in the real world, even for small sites.

Tablespaces are pretty huge

[justins]

I don't do database stuff anymore but, looking back, it seems to me that every design I worked on could run on PostgreSQL, now that it has tablespaces. It's a useful design feature but perhaps more importantly, people now have a really good tool to do performance tuning with really large databases. There aren't any other OSS databases with tablespaces as a feature, AFAIK.

Pervasive

[prandal]

Postgres has a big (well, almost) company behind it too, now.

Re:Pervasive

[jadavis]

I think the largest company to support PostgreSQL would be Fujitsu.

There are many others I would like to list, but I don't want to leave anyone out.

But yes, I welcome Pervasive's new support, and hope to see many great new things.

Re:Pervasive

[Anonymous Coward]

A couple links on Fujitsu support here and here

At 156,000 employees and $4.7 Trillion Yen (over 40 billion USD) I think that's bigger than Oracle.

plPHP

[matchboy]

Don't forget plPHP:

plPHP

Re:plPHP

[snorklewacker]

plpython for me. pure yumminess. still, lot of oracle shops wouldn't think of switching until it has pljava.

Re:plPHP

[jadavis]

plJava

Re:plPHP

[snorklewacker]

I had forgotten that pljava was still active ... I meant to say "until pljava is in the core", i.e. it comes with pljava. It can be hard to trust when it's third-party, not the least concern is that it may not be rigorously tested against one version or another.

Still, the existence of pljava does give me warm fuzzies.

Re:plPHP

[jadavis]

It's possible plJava will make it into the core, and possible that it won't.

There are many good reasons to leave it a project outside of core, and such a project should not be considered "lower quality".

The main benefits to it being outside of core have to do with the release. For instance, you might not want to wait a year between releases of pljava. Similarly you wouldn't want the release of PostgreSQL held up because pljava wants to sneak another patch in before the feature freeze.

Garage product name is....

[Hammer]

Oracle...

Re:Garage product name is....

[ahmusch]

Bzzzt. Whether count(key_column), count(1), count(*), or count(non_key_column), Oracle will tend to prefer to perform a fast full scan of the primary key index, never accessing the underlying table. Obviously, this depends on little things like how the optimizer's been tuned, whether there are optimizer statistics, and the version of Oracle.

Re:Garage product name is....

[bdbafh]

Best post on this entire thread. Give that poster a copy of any of Jonathan Lewis' books.

Re:Garage product name is....

[Hammer]

I stand corrected! The problem may be corrected in versions newer than 8 of Oracle.

RDBMS != Object Store !!!

[Anonymous Coward]

Can anyone explain to me how it is "Object Relational" as opposed to just straight "Relational" or is this just another case of /. bullshit?

Short answer:

RDBMS != Object Store ergo Object Relational Database == Bullshit

Long answer:

It's true that an RDBMS doesn't map well to the object-oriented ideology. That's because an RDBMS does not store objects, or anything like them.

The object-oriented ideology as instantiated in C++ and Java is founded upon breaking data into objects, bearers of identity, which belong to classes, bearers of structure and behavior. (C++ and Java make little account of metaclasses, which are used in more dynamic object systems such as Python's class system and Common Lisp's CLOS. Templates are not metaclasses.) Objects have identity, so they can be equated; they are the unique bearers of attributes about themselves; and each object's structure is dictated by the class to which it belongs.

When object-oriented partisans look at a database, they see its relvars (or table headers) as bearers of structure and think of classes, and its tuples (or rows) as bearers of identity and think of objects. They see a database as a place to store objects persistently.

But this is not what an RDBMS does. An RDBMS isn't an object store; its relvars are not classes and its tuples are not objects. So what is an RDBMS? What is "relational" anyhow? Relational databases are founded upon relational mathematics, which is what you get when you cross set theory with predicate calculus.

Set theory is the branch of math that deals with collections of elements which behave according to formal axioms. Set theory lets you say, for instance, that if you have a non-null set R and a non-null set S, that you can construct a set R*S of all the possible pairs of elements from R and S.

Predicate calculus is the branch of logic that deals with quantified statements about entities. It lets you formalize logical arguments such as the syllogism: All men are mortal; Socrates is a man; therefore, Socrates is mortal. Predicate calculus deals with generalizations and instantiations of those generalizations.

What do we get by combining set theory and predicate calculus? We get a system that allows us to operate upon sets of tuples of values satisfying predicates. A relation holds tuples of values which make some predicate true. For instance, consider the predicate "Person x owes me y dollars." Tuples which satisfy this predicate will be pairs (x,y) for which the sentence is true. For instance, if Fred owes me 40 dollars, (Fred, 40) satisfies the predicate. It could thus be a tuple in the relation described by the predicate -- the one relating people's names to how much they owe me.

With the relational algebra (or an RDBMS) we can do operations upon this relation and others. We could, for instance, select a result set of all those people who owe me more than 50 dollars -- or join this result set with those people's addresses. Whatever result set we ask for will be calculated from the facts in the database. We might get back this result set:

(Barney, 75, 40 Elm Road)
(Megan, 60, 9 High Street)

Now, are the elements of this result set objects in the object-oriented sense? They are not. They do not have identity. The tuple about Barney is not Barney himself, or even a machine representation of him. It doesn't uniquely store attributes of Barney -- after all, we created it by joining tables which also contain such attributes. It is not even, truly, a fact about Barney exclusively -- for it is also a fact about the number 75, and about the address 40 Elm Road. It isn't an object; it's a tuple value, and values do not have identity as objects do.

Moreover, note that by joining, we can construct new relations from old one

Re:RDBMS != Object Store !!!

[Anonymous Coward]

In summary: There is an insoluble tension, or impedance mismatch, between OO and RDBMS. OO is about classes with structure and objects with identity. RDBMS is about relations bearing tuples satisfying predicates. OO lets you pass around structure, data, and behavior by handing someone a pointer. RDBMS lets you derive new facts (in new structures) from known facts.

Wrong!!! There is no "impedance mismatch". They both represent data. This term was invented by object practitioners who want to denigrate or marginalize the relational database they are "forced" to work with in their day jobs.

The reason there is a problem at all is because of several factors.

First "objects" are ill-defined. What *is* an object? If you attempt to create a formal definition based on existing type theory, an object is simply a *value* that belongs to multiple types. For example, some particular square is a Square as well as a Rectangle. Various implementation details such as "methods" and "object identity" cloud the issue, but it *is* possible to reconcile objects with classical type theory. See, for instance, The Third Manifesto or the relevant chapters from Date's intro book.

Second, the major problem is mapping objects to *tuples* instead of to *attributes* of tupples. This ruins the ability of the database to perform arbitrary queries. For instance if you have Invoices, LineItems, and InventoryItems, you can't get a list of InventoryItems with their associated LineItem prices in a particular invoice via the object model. Because you have separate objects for each entity. Maybe you could create a new class, InvoiceLineItem which has all the fields from both InventoryItem and LineItem. But then what about a summary query ("GROUP BY"), or some other ad-hoc query?

You seem to know what you're saying here:

These are not the same kind of thing, and there exists no natural mapping from one to the other.

Yes, you can't map classes (types.. which are like sets of values) to rows (tuples.. which are values) because they aren't the same things at all. You can only jam them together and have it work some of the time (or most of the time if you are writing very simple apps like, say, a blog or a guestbook app).

You can however map classes to attribute types because you *can* formally map one to the other. ("formal" means: repeatable, complete, automatable, as opposed to "ad-hoc" which means you just make it up as you go along).

What somebody needs to do, to wake people up, is to write a truly relational database and tie it to some language like Ruby. Let people put objects directly into columns. Include powerful indexing that lets you index on, e.g., "customer.first_name". Implement only relational operators, and not this SQL junk. Use theory as a guide.

I bet people will be amazed at how *powerful* the relational model is. No more ORM layer, no more "SELECT x,y,z FROM Foo" verbose junk. No more "impedance mismatch". No more handling of data integrity in the app. No more NULLs!!! yay!

Oh well. I'm waiting, someone will do it someday.

Windows Version

[LetterJ]

Does the Windows version still require running as an administrator?

Re:Windows Version

[chriskl]

I believe the windows version specifically CANNOT run as administrator, same as the unix version cannot run as root. For security reasons.

Re:Windows Version

[stoolpigeon]

The installer creates a postgres user on the system if there is not one already and gets the service running.

Re:Windows Version

[LetterJ]

*That's* the problem I remember from earlier setups. Basically, you have to *install* it as an administrator so that it can *run* as a non-administrator.

That's a problem for users in a corporate environment where they aren't authorized to create users on their workstations.

Re:Windows Version

[stoolpigeon]

Ooops - sorry about that. I use postgreSQL professionally but I work for a small company (300 people or so) and our IT department is very small. I have admin rights on everything so I forget what can happen in the environment you describe. Thanks for correcting me.

Commercial Support for PostgreSQL 8.0

[vaidhy]

Pervasive, the company that previously brought Btreieve, provides commercial support for PostgreSql 8.0. I believe, this is the first "database" company that provides support. You can find the news at http://www.pervasive-postgres.com/ Hopefully, this should enable penetration of postgres into more established enterprises. Disclaimer: I submitted the story before and it was rejected.

Re:Commercial Support for PostgreSQL 8.0

[tweek]

Not at all. Redhat sells PGSQL under the moniker RedHat Database if I'm not mistaken.

Re:Commercial Support for PostgreSQL 8.0

[Anonymous Coward]

Not to mention Command Prompt, Inc. who is the only dedicated PostgreSQL support company. They can be found at http://www.commandprompt.com/

Re:Commercial Support for PostgreSQL 8.0

[Anonymous Coward]

Looks like there are are whole bunch of companies that specialize in PostgreSQL. Not sure where you get only from. http://techdocs.postgresql.org/companies.php

Commercial Support

[Octos]

There's also some new commercial support offerings from an established database company. Pervasive started offering a distribution recently.

When will OSS projects start making use of it.

[Anonymous Coward]

Yes, Postgresql is fabulous. I use it daily.

But what about OSS projects such as Bugzilla, blogging, CMS, etc. which only support MySQL. When are they going to add support for Postgresql ?

Re:When will OSS projects start making use of it.

[matchboy]

When they start using db abstraction layers and stop doing that. ;-)

Re:When will OSS projects start making use of it.

[jhoffmann]

When they start using db abstraction layers

The problem is that when things are developed for MySQL, you get get a product designed to work best with MySQL - nonstandard features/types like ENUM which aren't supported by PostgreSQL so you have to work around that, plus the SQL has to be specially crafted to take account the lack of features/nonstandard features in MySQL and you're stuck trying to work the way it has to be done in MySQL instead of a cleaner, faster native way. All in all, you need to have a totally different mindset to develop efficiently with each database and when you try to be agnostic, a lot of times you end up handcuffing one or the other or both.

Re:When will OSS projects start making use of it.

[realkiwi]

I like this feature! Any project that is tied to MySQL does not get my business. Thanks to this feature I discovered VDR when everyone else is using MythTV with its crummy backend requirement.

opencms has added postgreSQL support - I chose opencms as my future CMS for example.

Point-in-time-recovery - replication?

[captainclever]

This sounds like mysql's binlog feature.
Hopefully this will make it easier to make a kickass replication engine for pg.

Point-In-Time Recovery

In previous releases there was no way to recover from disk drive failure except to restore from a previous backup or use a standby replication server. Point-in-time recovery allows continuous backup of the server. You can recover either to the point of failure or to some transaction in the past.

Related Objects

[Doc Ruby]

When will someone graft something like Tomcat or JBOSS into Postgres? I want my Object Relational DB to do something like:

(
SELECT p.person, m.show
FROM people p, subscription s, status t, media m
WHERE s.address=p.address
AND p.id=t.person AND (NOT t.state=expired)
AND m.id=s.media
).iterate(m.send(p.person));

Not only relate the objects in the tables, but internally retrieve Java objects, pass them to the VM, along with the messages and data to call, from the resultset. I also want every object to be related, so resultsets records can include fields which point to running objects, and running VMs. Who's got it?

Re:Related Objects

[danharan]

Seems to me Hibernate is the closest there is in that field. IIRC its lead is now working on EJB3 specs. It's a Jboss project, and I've used it with Tomcat without problems.

Re:Related Objects

[michaelggreer]

This is EXACTLY what Hibernate does, as the previous post states. Hibernate is excellent!

Re:Related Objects

[tweek]

Except that hibernate generates horribly inefficient sql at times.

Re:Related Objects

[Doc Ruby]

All code generators I've ever seen do that. Can I throw away the generated SQL, and easily replace it with more succinct, or just more reflective of my architecture, SQL? And by inefficient, do you mean just verbose, which the scheduler/optimizer can turn to (possibly better than my own) efficiently executing SQL? Or does it generate SQL that will execute slower than even lame, obvious (and possibly redundant) handcoded SQL?

Re:Related Objects

[java-lawson]

I know lots of folks will suggest Hibernate but also take a look at iBatis SqlMaps. It's a lot simpler than Hibernate and I've used it in some pretty high load situations (read - commercial sites in the insurance industry)

Re:Related Objects

[Doc Ruby]

You haven't dealt with MONY or The Guardian, have you? I architected some really big systems for them several years ago, and I'm curious what's happened with my baby since then.

Re:Related Objects

[tweek]

Our experience is that it executes slower. Take DB2, our database. The SQL generated by hibernate can have preparation times of 15s and hand-coded sql doing the same thing can prep in 4ms. Alot of it can be fixed with indices and what not but they have to be more universal in the generation of SQL.

One thing I don't like about hibernate is that you have to bypass it totally to use any custom SQL. At least that's my understanding. And the only way you have insight into the SQL it generates is to run in debug mode and catch it in the console OR snoop at the database level. For databases that don't include dynamic sql values when snapshotting, it's a real pain in the ass.

Then again I'm not one of the developers but when a system performs poorly, I'm the one that gets called first so I have to know everything - from dev to dba so I can ask the right person.

Re:Related Objects

[michaelggreer]

Whenever I've run into inefficient SQL in Hibernate, I've always been able to solve it. What cases are you talking about? You might want to take this to the forums for help. Hibernate will never run as fast as well-optimized custom SQL, but I've always had great performance from it or been able to tune it to the performance I want. Hibernate 3 will allow you to specify custom SQL to your heart's content, I believe.

Re:Related Objects

[Doc Ruby]

This is great insight. Once the optimizer caches the compiled SQL passed from your app, does performance approach that which you'd expect from simpler or more optimized SQL strings?

Re:Related Objects

[tweek]

You would think so but it's dynamic SQL so you can't really optimize it that much. The way hibernate generated the SQL, DB2 couldn't really optimize it.

You can bring the prep times down with heavy indexing and what not but the best solution is to move it into a stored proc (which, by the way, there was a bug in the DB2 JDBC driver provided in 8.1fp4. Sprocs would stop executing after an undeterminable time period or number of uses. We only saw it with Hibernate though. The JDBC driver in 8.2 Stinger or fp7 resolves it).

The generated SQL was realy the problem in the end though. Take a SELECT query on a customer table generated by hibernate from a form with multiple optional search criteria. Hibernate would pass wildcards for unused search criteria and thus bypass all of our indices (or at least the way DB2 does it. DB2 indices are case sensitive FYI). We would ALWAYS escalate to a table scan because we weren't using our indices. A bit of recoding to handle case, additional indices and a sproc later and the problem was resolved.

And my favorite features are

[plopez]

playing around with the beta and RCs several items impressed me:

1) GIS support. Very important for what I do. This is probably due to how closely they work with GRASS (which I haven't used yet).

2) Ability to define and bind operators. Very flexible.

3) Much more relationally compliant while also supporting OOP.

4) PGAdmin II is very handy. A few rough points but now there is no excuse for those afraid of a command line.

It just gets better every release. I am currently porting a MMSQL database over and so far so good.

Re:And my favorite features are

[phiala]

1) GIS support. Very important for what I do. This is probably due to how closely they work with GRASS (which I haven't used yet).

Hm... I guess I'll have to look into it. I've been using MySQL because of the abundance of documentation available. It does talk to GRASS (GIS) and to R (stats software) but an improved interface would be a possible reason to switch. I rely heavily on those two programs.

Too bad there isn't the abundance of documentation available for PostgreSQL (as in, I can't go to my university library and check out a couple books).

Re:And my favorite features are

[einhverfr]

Hm... I guess I'll have to look into it. I've been using MySQL because of the abundance of documentation available. It does talk to GRASS (GIS) and to R (stats software) but an improved interface would be a possible reason to switch. I rely heavily on those two programs.

With PostgreSQL you can use GIS objects for field types and and put your R functions into the backend so you can use them to generate views.

the Oracle comparison

[justins]

Oracle will always have more features than any other given database product you care to compare it to, and there will always be those who find some of those Oracle-unique features irresistable. That's what it means to be the central product of a very large, wealthy and reasonably effective software company.

It is not a very meaningful comparison, though. Oracle also has a lot of stuff that nobody would be happy to see tacked on to PostgreSQL. Oracle is notoriously difficult to administer. The download of Oracle's database product is probably 500 times larger than PostgreSQL. (and 20 times larger than Oracle itself was just a few versions ago, which is interesting, and yes I'm guesstimating but still...)

In short, PostgreSQL doesn't need all the features of the Oracle db to be successful, as *nix and NT did not need all the features of a mainframe OS to be successful.

Re:the Oracle comparison

[bdbafh]

Oracle Support will bail your arse out. Provided that your support contract is paid up, and sometimes, even if it is not - you will be able to get your data back out even of a corrupted database. I have only seen 2 corrupted databases, due to massive hardware failures (degraded over time). six grand USD for Oracle Standard Edition One, about a grand a year for maintenance (upgrades) and support - and this big, bad, expensive RDBMS seems quite inexpensive. A properly configured database can run unattended for 3 months at a time, provided that it has been setup correctly, and someone is grepping the logs. (quarterly security patchsets are now the norm). At $15K USD per CPU, I'm interested in Postgres. At $5K USD per CPU, applications run on Oracle. bdbafh

Different tasks different tools

[ducomputergeek]

Chances are MySQL will continue to be my DB of choice for most of my web-based developement. Why? Two major reasons: still more widely available with most web-hosting packages these days and MySQL select speeds. While in the past 3 years the folks at MySQL have been adding features to make MySQL look more like a complete DB package, I think a lot of people forget that MySQL is an extremely niche product. Orginally it sacfriced features for speed, especially on select queries. That makes it ideal for web content hosting where select queries, at least on my sites, out number update and insert queries at least 40 to 1.

Now wanting to develop a database system for an internal intranet for a business to run an OSS CRM or ERP solution, unless specifically developed for and only for MySQL, PostgreSQL's fetures are ideal especially since the ratio of selects vs other queries are not going to be like hosting a website CMS.

The /. crowd always seem to want to make things "This is better than that" and vice-versa rather than "there is the right tool for the right job".

Re:Different tasks different tools

[coogan]

Agreed, however it is foolish to think that a certain DB backend will serve your purposes indefinately. I refuse to use anything or allow my developers to code anything these days that does not at least allow me the possibility of migrating my backend seamlessly - read ADODB etc.

Re:Different tasks different tools

[tweek]

Actually we're currently working on putting our new warehouse in MySQL but I think that might have been a poor decision. We may end up moving to PostgreSQL if the ODBC driver plays nice with Informatica.

One of the problems that our warehouse guy keeps complaining about is lack of views and sprocs. They were the ones that picked mysql over postgresql because the ODBC driver actually worked with Informatica.

I'm going to install a test environment today and give it a shot.

Take a letter, baby.

[Doc Ruby]

OnLAPP! Finally, the Life of O'Reilly becomes worth imagining.

MOD PARENT UP

[Anonymous Coward]

This is a GREAT comment explaining what relational DBs are all about and what's wrong with the recend OOPDB buzz. Please MOD PARENT UP.

That's because you are clueless.

[Anonymous Coward]

SQL is a very basic standard, and is quite useless for writing stored procedures. This is why MS has their TSQL, oracle has their PLSQL, and postgresql is nice enough to have PLPGSQL, perl, C, and others.

Re:No, no, no. Serious Question.

[IANAAC]

Are there similar problems with oracle or other "top of the line" database systems?

If you spend any amount of time at MetaLink, you'll see that Oracle has just as many gotchas as any other DB.

What about case sensitivity?

[Anonymous Coward]

Will postgres handle case insensitive string comparisons in the where clause? Yeah its broken behavior, but there are apps(including ones I work with) that can not use postgres because of it.

Re:What about case sensitivity?

[matchboy]

it handles regex's...

Re:What about case sensitivity?

[Qzukk]

You'll have to use fieldname ILIKE 'IbRoKeMyShIfTkEy'.

Re:What about case sensitivity?

[Tablizer]

Will postgres handle case insensitive string comparisons in the where clause? Yeah its broken behavior, but there are apps(including ones I work with) that can not use postgres because of it.

Can you change the code to do something like?:

WHERE upperCase(x) LIKE '%FOO%'

Generally there will be some SQL changes needed for converting from any SQL-DB to another unless you purposely stick to a limited sub-set.

Re:What about case sensitivity?

[Anonymous Coward]

Use ILIKE instead of LIKE

Re:What about case sensitivity?

[Anonymous Coward]

The problem with ILIKE and regular expressions is that you lose indexing. It is possible to do a functional index and use UPPER, but it is not same thing as natively handling case insensitivity.

Re:What about case sensitivity?

[Anonymous Coward]

You can define a case-insensitive character set for your data as well.

Or, you can redefine operators, so you can amke case insensitive comparison operators for strings..

Re:What about case sensitivity?

[Anonymous Coward]

Postgres can perform a case insensitive query, you use the operator ~~* instead of =, e.g.

SELECT * FROM customers WHERE name ~~* 'bob';

matches bob, Bob, BOB, etc.

I hope you find this useful.

Re:What about case sensitivity?

[mw]

wrong: use
SELECT * FROM customers WHERE name ~~* '^bob$';
and it will use indices

Re:What about case sensitivity?

[Tablizer]

That reduces portability, however. (I thought I said that already, but cannot find my reply.)

Re:What about case sensitivity?

[Sierpinski]

You have always been able to remove case sensitivity by using the ILIKE operator, even without the wildcard % signs.

select * from table where firstname ILIKE 'bob'

Selects Bob, BOB, BoB, bOB, etc.

PITR ?

[Baal Sebub]

Pain In The Rear?

Re:PITR ?

[adric]

Pain In The Rear?

Point-In-Time Recovery

Re:PITR ?

[Tablizer]

[Pain In The Rear?] Point-In-Time Recovery

I bet it was a pain-in-the-rear to implement point-in-time recovery.

Yeah? How about unique keys?

[Jeppe Salvesen]

If there is a unique key (like an ID), it should be able to automatically use that for counting - but it doesn't.

Re:Yeah? How about unique keys?

[Anonymous Coward]

The problem is that postgres doesn't keep tuple
visibility in the index, so an index scan over
the whole table will be slower than a normal
sequential scan, since the heap has to be visited
for every tuple anyway and now you are reading
things out of order and reading the index in
addition to the heap.

The right way to solve this problem for people who
do lots of count(*)'s without where clauses
(compared to inserts and deletes) is to use triggers to maintain a count.

MySQL vs PostgreSQL (database journal article)

[Anonymous Coward]

Here is an interesting article in Database Journal about MySQL vs PostgreSQL:

PostgreSQL vs MySQL: Which is better?

And here is a 3-point summary for the impatient:

MySQL versus Postgresql (summary)

nice try

[MasTRE]

Why can't you dweebs understand? No matter how fancy you make your OS X^M^M^M^MpostgreSQL, those of us that prefer to use filesystem^M^M^M^M^M^M^M^M^M^MmySQL as database will never switch.

Re:nice try

[geniusj]

^M is carriage return. I think you meant ^H ;)

Re:nice try

[MasTRE]

You're an^H^Hright! Blame it on morning sickness (-:

Replication

[dmadole]

If only a simple and integrated replication mechanism would appear, I would consider switching back to PostgreSQL from MySQL.

I miss PostgreSQL, but too many things are made easier by replication.

Re:Replication

[rkit]

Did you already take a look at slony?
http://gborg.postgresql.org/project/slony1/projdis play.php

Cost Complexity

[Doc Ruby]

In other words, Postgres scales in simple cost, while MySQL scales in complex cost. Simple cost problems can usually be solved with more money. Many complex cost problems can't be solved with any amount of money. Simple cost problems don't contribute to the risk, except where more money can't be raised, while complex cost problems directly increase the risk to everything. You can't fix complex cost problems "in the mix" - you're actually "starting over" when you change some of the factors, or their relationship. Give me simple cost problems, except when I can't afford them at all - a risk of 0 is best, then some risk, but preferable to a risk of 100%.

Re:Cost Complexity

[nbert]

Í just happen to build a low budget customer database for small hotels - using any of the major ones seems to be overkill, but since they are already there it might be reasonable to use them.

So MySQL Database Server retails at 500 euros (that's the package we usually use for mythtv or whatever non-profit use we can think of.

There is nothing wrong about MySQL's license, but if you intend to sell at 300 euros per customer it doesn't stand a chance. Even if I would purchase one of the flattened versions of MySQL I would still pay more than I can afford.
I really wonder why there isn't a license granting a 10% profit share to MySQL for people which have less than ~30.000 entries. If they would offer it I wouldn't even think about alternatives at all.

Re:Cost Complexity

[Doc Ruby]

What happens when those hotels lose 2% of their reservations over a couple of years? The database license for a different RDBMS server would have paid for itself.

Re:Cost Complexity

[nbert]

I already mentioned that I want to sell this for 300 Euro per license (targeting a local low budget branch of hotels). It can't pay for itself in my regards because a full MySQL license would result in a 200 Euro loss per item sold.

On the other hand you've got a good point. But still I wouldn't even think about a 2% loss of customers, because the consequences would really get me into trouble. Let's just assume that I'll manage not to fuck it up regardless of the DB running below. ;)

Having said this I must admit that both solutions do much more than required. But both offer a level of integration with other apps which I would never get with a solution suitable to my needs.

Re:Cost Complexity

[Doc Ruby]

I know how that can be, especially selling it to cheapo business customers one at a time. But, to return to the upshot of the cost analysis in which I'm indulging, this is an argument to use Postgres. I don't know what a "commercial license" costs, though you'd want one with some amount of support. But Postgres won't be anywhere as likely to lose data due to referential integrity, whether or not you program it right, as is MySQL (notorious for that). And I don't think Postgres has a charge for unsupported distros - it's an open-source onetime UC Berkeley project, with several companies keeping it hot, not a company itself. BTW, that overkill in RDBMS features can come in handy later, eg when you set up a cross-hotel reservation referral system among your installed clients. I know how good that can be, because I set up the first website for the Caribbean Hotel Association, and it's still running, 9 years later :).

Yes...

[Aldric]

I'm really going to spend the next few months converting the back office system I'm responsible to a new database server when MySQL is handling things just fine.

Zealots are the #1 problem with the open source community. Who cares whether someone is using MySQL or PostgreSQL? AT least they aren't using MS SQL.

Re:Yes...

[einhverfr]

Personally I don't care what RDBMS you use. However, I think it is important to understand what the ramifications of using MySQL for, say, an accounting system are.

The majority of MySQL implimentations are for web apps in which it works relatively well. These include content management systems, forums, etc. However, due to very specific issues in how it handles numbers, I would not feel comfortable running a shopping cart on it, at least not if I needed it to be an authoritative record of sales.

My company works with MySQL, PostgreSQL, and FIrebirdSQL. Each have their place, but for accounting software, MySQL is not adequate, and PostgreSQL is ideal.

Re:Yes...

[jadavis]

This is a slashdot discussion. Some of the people in this discussion are interested in trying a new database (unhappy with old one, new project, whatever) and don't know much about MySQL or PostgreSQL.

They want real life impressions even from nonexperts. They want to know: "After I install this thing, will I see results quickly or am I going to be digging in books for a while first". They want to understand a little of the theory and be warned of potential pitfalls or benefits.

Advocates can come in and defend the DB against myths and falsehoods, and provide advice to people considering switching but haven't signed up for the mailing list yet.

There are a lot of facts coming out in this discussion, and I see no problem with it. In fact, why are you reading these comments if you aren't either an advocate, or someone who wants to know something they can't get from the websites?

Re:Yes...

[einhverfr]

They want real life impressions even from nonexperts. They want to know: "After I install this thing, will I see results quickly or am I going to be digging in books for a while first". They want to understand a little of the theory and be warned of potential pitfalls or benefits.

I will say for the record that MySQL's documentation is more newbie-friendly, while PostgreSQL's documentation tends to assume a little more experience.

Of course just because a database doesn't enforce bounds-checking for business critical inforation isn't a problem. I will tell you why on Feb 31, 2005.

Re:Yes...

[jadavis]

I'm not sure what that has to do with my comment, but I agree with both points you made.

This release fixes many of these.

[Anonymous Coward]

• "256 * 256 * 256 * 256 = 0?"
  "Affects: PostgreSQL
• "1.5. Implicit FROM item and unintended cross joins"
  A config variable disables this misfeature.
• "RANDOM() failures"
  Two calls to random(), which is declared as a 'volatile' function _should_ return two different functions. You could, of course define your own function that is not-"volatile" and get the behavior they wanted.

PGSQL non standards complient.

[Anonymous Coward]

This page is a great reference for where Postgresql, Oracle, DB2, and others fail to conform to the SQL standards.

Re:phpPgAdmin 3.5.1 works great with PostgreSQL 8.

[nvivo]

For up-to-date web-based administration of PostgreSQL, try phpPgAdmin

It says support for 8.0 beta 4... not even RCs. If the code is up-to-date, the site really isn't.

Almost accurate

[einhverfr]

While I think that PostgreSQL is the best open source database out there, there is one very important gotcha wrt: MVCC which can cause data integrity. This only happens when autovacuum is not running, of course.

If you run enough transactions between vacuum runs (iirc a billion), the transaction counter will wrap around and suddenly your data does not have a consistant point of reference regarding visible transactions. Now, if you wait for a billion transactions to run VACUUM, you either:
1) Have extremely poor performance anyway (not to mention having all your stats off so the planner is doing seq scans when it should use an index)
2) Are doing something with the database which I cannot imagine (I guess a huge number of select statements could cause this, but updates cause old tuples to sit around, so you would have bad performance).

Now, I am not aware of this ever actually having happened, but it is in the documentation, so I figure I should point it out. Of course if you let the database get to this point, then you have bigger problems than your data (chief among them being the IT staff and/or management)..

In general, PostgreSQL focuses on data integrity to a degree not seen elsewhere in the open source database world. Even Firebird does not have such a heavy focus in this area, though to be fair it is a different enough product that their focus works well in their target markets.

My company offers application development, remote administration, and implimentation services for PostgreSQL, MySQL, and Firebird. I am very excited about this release because it will enable us to do more with the database manager which makes us most productive.

As a side note, PostgreSQL-Win32 will not run on Win9x because it requires an NTFS filesystem, iirc. So it is not a perfect solution for Windows development yet (until Win9x fades into the distance or until they decide that they should port it to FAT). Of course you could still use the Cygwin installation, I think. But it is better, IMO, to run it on a arguably stable system anyway.

Re:Almost accurate

[statemachine]

If you run enough transactions between vacuum runs (iirc a billion), the transaction counter will wrap around and suddenly your data does not have a consistant point of reference regarding visible transactions.

Nice to see that someone else knows about this "feature" too.

I've seen it happen several times. There's absolutely no warning before it does -- the database just starts returning multiple versions of the same records. On a busy database, this can happen every few days.

To set up a cron job is a kludge. An autovacuum daemon is also a kludge. Why? Because if the database gets busier, a vacuum would need to be run near constantly in order to beat the clock.

Those who say PGSQL is all about data integrity are fooling themselves.

Re:Almost accurate

[Cajal]

How is the autovacuum daemon a kludge? It scans the internal state of tables and vacuums when necessary. And with the cache manager (ARC) and I/O throttling (bgwriter) work in PostgreSQL 8.0, vacuum doesn't impose nearly the overhead that it used to. What's the problem?

Re:Almost accurate

[Anonymous Coward]

NTFS is not the only reason 9x is not supported. There are many other features missing on that platform.

Re:Almost accurate

[Nutria]

every few days

10^9/(3*24*60*60)
3858.02469135802469135802

What kind of x86 system does 3,900 TPS?????

Re:Almost accurate

[einhverfr]

10^9/(3*24*60*60)
3858.02469135802469135802

What kind of x86 system does 3,900 TPS?????

I hate to know what the peak load on a system like that would be.....

And if you are under that kind of load, you probably need something in a bit of a different league....

Re:Almost accurate

[ultranova]

What kind of x86 system does 3,900 TPS?????

More importantly, if you can do 3900 TPS, why is running "vacuum analyze" once per hour a problem ?

Re:Almost accurate

[ckaminski]

It will not run on 9x, because it depends on the WIN32 Service architecture, which is only present on NT/XP/200x.

Change Column Types - finally

[EugeneK]

from the release notes :

Change Column Types

A column's data type can now be changed with ALTER TABLE.

Before, to do this, you had to create a temporary table with the changed column type, copy all the data over to it, and then rename the temp table as the old table.

Thank you Postgres team! Now, if we can rename a column, that would be a nice bonus.

Re:Change Column Types - finally

[JoshRosenbaum]

Or you could just create a new column, update the new column with the old columns values, drop the old column, and rename the new column to the old column.

Still a pain that way too.

Re:Change Column Types - finally

[tweek]

I think DB2 and other commercial databases do it this way anyway. They just encapsulate all the steps in an the ALTER table function.

One thing to add is that you might have to rebuild any indices that use that column though. That's bitten us a few times using third party tools.

Re:Change Column Types - finally

[Anonymous Coward]

In postgresql 7.4.6:

ALTER TABLE foo RENAME COLUMN name TO new_name;

Re:Change Column Types - finally

[EugeneK]

I stand corrected; thanks; I wrote renaming columns, but actually I meant to say deleting columns - as far as I know this is not possible yet in Postgres.

Re:Change Column Types - finally

[Anonymous Coward]

again in 7.4.6:

test=# ALTER TABLE foo DROP COLUMN name;
ALTER TABLE

Re:Change Column Types - finally

[EugeneK]

Aha, I see my problem. My knowledge of Postgres is coming from 7.2.1 and I haven't tried anything since. Apparently a lot of good things have happened between 7.2.1 and 8.

That's really dumb.

[emil]

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

I'm an Oracle guy, and this is how they do it:

• If you have an index on a NOT NULL column, count(*) will count the index, and it will be fast(er). If you don't have a suitable index, you will incur a full table scan. This is reasonable.
• An Oracle table can have one or more "FREELISTS" - an insert will attempt to find a block from an available freelist, and a row lock will be applied to the transaction header of that block. If you have high insert activity, you can (re)build your table with more FREELISTS. You can have multiple transactions hitting a block if you don't exceed MAXTRANS. If you decided to shove the block clear full of data and you set INITTRANS to 1, you effectively have block-level locking, not row-level locking.
• Oracle NEVER escalates a lock, and there is no table of locked rows in memory (and you should run away from any database that has such a structure).

Oracle has the best technology in the industry, hands down (DB2 didn't even get triggers until v5). Postgres appears to be paying much more attention to Oracle's methods than MySQL.

Guess which database I'd use if I had no money to spend?

Re:That's really dumb.

[ahodgson]

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees

Yep, it sure does.

Re:That's really dumb.

[einhverfr]

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

Why do you think MySQL performs so poorly with large number of concurrent users?

Re:That's really dumb.

[Alan]

I thought that this only happened on some table types, and if you used myisam (or innodb?) then it only locked the row?

Re:That's really dumb.

[Cajal]

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

MySQL can use different "storage engines" for different tables. One of their older (and arguably, more widely deployed) engines in MyISAM, which does table-level locking (their newer engine, InnoDB, does row-level locking). From the MySQL Manual (sec 7.3.1):

Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

and from Section 7.3.2:

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

This is one of the many problems with MySQL. I agree with you that Oracle (and PostgreSQL) are much better designed.

Re:That's really dumb.

[nconway]

If you have an index on a NOT NULL column, count(*) will count the index, and it will be fast(er).

Unfortunately, this can't be done in PostgreSQL. Tuple visibility information (for MVCC) is only stored in heap tuples, not index entries, so index-only plans like you're suggesting are not possible (you'd still need to lookup the index entry's heap tuple to see if it was visible to your transaction). It might be possible to add tuple visibility information to indexes, but that would be very complicated and have a lot of drawbacks. Also, index-only plans would only result in a marginal speedup in count(*), although of course they are useful for other queries.

Re:That's really dumb.

[Anonymous Coward]

read the new features guide for 9i R2 and 10g R1.
Automated segment storage management (ASSM) does not use freelists, it uses bitmaps, similar to how locally managed tablespaces (LMTs) use bitmaps to manage extents, instead of encurring recursive SQL to manage uet$ and fet$.

hopefully, you're not still supporting 7.3.4, 8.0.x, 8.1.x databases, as they're all de-supported.

bdbafh

I have used SqLite, but not in production software

[Futurepower(R)]

There are a lot of very negative-thinking people who make comments on Slashdot. I have used SqLite, but not in production software, only to try it.

Try SqLite, it's excellent.

[Futurepower(R)]

And, I should say SqLite is very impressive for many of the small uses of databases, or places where people should use databases, and haven't because of the complexity of the other ones.

Don't miss this: Java in PostgreSQL.

[Anonymous Coward]

You can use Java as a stored procedure language in Postgres.

Subselects and SQL as a programming language.

[delmoi]

One of the missing features in MySQL programming is the lack of IF statements. Using Microsoft SQL Server 2000 I can do just about anything in a query, including testing to see if something exists. Is there a way to do the following in MySQL with a single query?:

1) insert a row if it dosn't exist, or update one if it does?

2) insert something with automaticaly generated Private Key value, read that value back and use it to have another record in another table point to the record you just made?

Re:Subselects and SQL as a programming language.

[Anonymous Coward]

> 1) insert a row if it dosn't exist, or update one if it does?

This is really basic: read the manual, specifically where it pertains to INSERT OR UPDATE.

> 2) insert something with automaticaly generated Private Key value, read that value back and use it to have another record in another table point to the record you just made?

Ahh, the good old "last inserted row ID" problem. For the most part, it's actually a sign of a poor design to come across this problem, but it comes up pretty often, and pragmatically speaking, it's going to even in otherwise pretty good designs. For MySQL's strange and disgusting idiom, check the mysql gotchas page (it has to do with a magic behavior of NULL). In sybase and mssql, it's a magic variable, in pgsql it's a function to get the rowid, then you select the row using the rowid (it's literally random access, so it's plenty fast). In oracle, TMTOWTDI, but normally you'd use a sequence and just read back the last value of the sequence.

Re:Subselects and SQL as a programming language.

[Kent Recal]

I'm not a database "pro" but what you describe sounds strange.

1) insert a row if it dosn't exist, or update one if it does?

Your DB will have to go and see whether your "row exists" (perform whatever check necessary). Putting that logic in SQL rather than the app doesn't seem like a big improvement to me. I'd rather like to see a "REPLACE" statement (somebody might come up with a better name) that works like an INSERT but will not fail on a conflicting row but overwrite it.

2) insert something with automaticaly generated Private Key value, read that value back and use it to have another record in another table point to the record you just made?

Postgres can do that kind of stuff. You'd need a good deal of faith into your RDBMS to go that route, though...

Re:Subselects and SQL as a programming language.

[bdbafh]

in Oracle, one would use the RETURNING clause to get the sequence value used to populate the column. Or did you want to encur more SQL*Net round trips just to get back .currval? of course you didn't.

Re:Subselects and SQL as a programming language.

[curri]

BTW, the 'right' way in PostgreSQL is using a sequence, like in Oracle. You're not supposed to use rowid's :)

hi.

[delmoi]

600 records?

What you're doing is more commonly done using a spreadsheet like Excel or OpenOffice, a high-end database like postgresql is totaly overkill for your project (not that they'res anything wrong with that, I'm sure it was a fun learning experiance).

The ability out output a plain text is universal in spreadsheet programs, as far as I know. And Microsoft's SQL enterprise manager can output plain text copies of your database.

EMS PostgreSQL Data Pump

[leono]

I converted a fairly large db (~15 million rows in 100+ tables) from MS SQL Server to postgres using EMS's PostgreSQL Data Pump software. It's not free, but it's cheap, and saves you a LOT of trouble. You can find it at http://www.sqlmanager.net/products/postgresql/data pump.

We still had to re-write all of our stored procs as postgresql functions, but that was a pretty good learning experience and enabled us to trim some serious fat as well.

Dump data - one time task

[RoboProg]

I had a similar issue with an Oracle database at work. We "defeated" the DBAs (their unwillingness to lift a finger on our behalf or grant us access to much of anything even in development) by writing a little java program to get an JDBC connection, then "select * from $1", puking out a list of inserts with an occasional commit. Wrap this in a little script to get each table (yes, if I were really gung ho I would have made the java program start a transaction, then pull an array of tables for consistancy). Thus, we were able to back up the production database and re-import it into development.

Assuming no more than minor changes to the insert statements syntax, and JDBC driver for database of the day, this should allow you to port data without too much fuss. (e.g. - me replicating the production database by piping said insert dump-scripts through psql)

I realize I'm replying to a joke post, but the data extraction issue is a recurring nuisance.

One Oracle Gotcha

[einhverfr]

Empty strings are stored as nulls (or is it the other way around). These aren't really the same thing, and can lead to issues if, for example, you use an empty string to mean "no data" and a Null to mean "unknown."

Can someone answer my question?

[agraupe]

It seems that people consistently prefer PostgreSQL over MySQL (which I currently use). I use my database for things like trying out CMSs on my personal server (definitely not mission-critical!) and storing my music collection for amarok. Is there some way I can use Postgre for all these MySQL-only things? I want to try it, as MySQL seems kinda... old.

Caveat about MySQL gotcha list

[einhverfr]

This guy is obviously of the opinion that everything should work on the same principles that ORacle does. Hence no Oracle gotchas.....

A couple of the MySQL gotchas (while problematic) are a bit misleading. Except in Oracle, an empty string and a NULL are different, and in relational theory, these have different meanings. While I think that it is unacceptable for MySQL convert NULL's to distinct types to match the constraints set on the database, it is not an issue of it inserting NULL's but rather inserting empty non-null values (empty string, for example). This is serious but it is an altogether different (and, I believe, more serious) problem.

Readable version

[Anonymous Coward]

http://shit.slashdot.org/article.pl?sid=05/01/19/1 312224

Installing PostgreSQL 8.0 alongside older version

[matchboy]

http://blog.planetargon.com/archives/36-Installing -PostgreSQL-8.0-alongside-7.4.html

Two questions

[Anonymous Coward]

What are you smoking, and where can I get some?

Re:Two questions

[Doc Ruby]

I'm SMOKING YOU, right where you site, Anonymous dullard Coward.

new LAMP

[Frank T. Lofaro Jr.]

Linux + Apache2 + Mod_perl + PostgreSQL

There's a dangerous trap with table inheritance

[jesterzog]

I'd love it if I could do some real object oriented storage with PostgreSql, but with the way it's currently implemented, I haven't found it all that useful... at least for what I want.

In particular I fell into a serious trap, partly as a consequence of not reading the documentation properly. It turns out that although the sub-tables will inherit properties from the super-tables, the data and any indexes are stored entirely separately.

Consider the following, for instance. You have a table to represent something like "places". Each has an ID, a name and a population, with an index on the ID. Perhaps this is inherited by other tables to represent things like "villages", "cities", "rivers", whatever, all representing their own information. Then load the tables with all of the relevant information, perhaps millions of records.

At this point, you can select from the "places" table to get a complete list of ID's an names. The problem is that, because the data is stored in all of the individual sub-tables, the "places" table still has zero records in it. (Unless you've explicitly inserted them into that table, of course.) When selecting from the "places" table, postgresql will query all of the tables derived from it, union them together and provide a virtual view. This means a tablescan of several tables upon any select.

In particular, if you plan to look for a place with a particular ID and do a few joins to other derivitive tables, everything slows to a crawl as the asymptotic complexity of all the unions and joins involved becomes exponential.

Anyway, I'd designed a database like this without realising, but eventually converted it back to a regular relational database. Apart from semantics, there just didn't seem to be any big advantage to it and there was a huge cost with anything more than a trivial amount of data involved.

I'm sure it is useful for some things, but the way it's currently managed (and it's possible this has been changed in 8.0) means that it's not suitable for a lot of tasks that people might otherwise assume it is.

It looks like a great year for PostgreSQL

[smchris]

Oxford University announced a while back that they will be scrapping most of their proprietary DBs for PostgreSQL over the course of '05:

http://news.zdnet.co.uk/software/applications/0% 2C 39020384%2C39173013%2C00.htm

Replication

[tarvin]

I'm rather excited by Slony. And it's nice to know the road-plan for Slony.

However, multimaster or not is one parameter for replication. Another is if replication is synchronous or asynchronous; and it's my impression that synchronous replication will not be in Slony any time soon (I could be wrong, though).

And then I stumbled across http://www.csra.co.jp/~mitani/jpug/pgcluster/en/
Does anyone have experiences with that?

Object persistance layers

[einhverfr]

Unfortunately, now the pendulum's swung the other way and we find ourselves with staggering persistence layers like EJB that wind up making it almost impossible to use a database's native features.

People rely on these persistance because they don't understand what a relational database can offer the, Often they are only interested in being able to persist objects across a set of uses (for example, a set of page views in a web app). These persistance layers in general are not good at gathering and maintaining meaningful data for business purposes. Indeed, why should I use an SQL back-end for this sort of app when something like Berkely DB is closer to what I want to start with? No wonder MySQL is so popular....

The real power of a real RDBMS is in its ability to store, manage, and present meaningful data to applications that can then use them as they see fit. This means that if you need a real enterprise app, you should start with designing your database to hold the appropriate information in a meaningful way, and then write object wrappers around this. You can then have custom actions on insert/update/delete using triggers (custom actions on select statements in PostgreSQL requires using a view). THen you can write an object-oriented app to use this structure.

I don't trust persistance objects to store meaningful information (sometimes you have to have a break between a logical and physical storage in order to keep the data meaningful).

Re:Object persistance layers

[AhBeeDoi]

Indeed, why should I use an SQL back-end for this sort of app when something like Berkely DB is closer to what I want to start with? No wonder MySQL is so popular....

MySQL seems like an example of less is more. However, when I do want more, less seems less like more and more like less.

It's doing wonderfully

[Anonymous Coward]

Yes, wow, finally get to meet you, my idol, my hero, you! Your design is divine! There hasn't been any changes in years now. We just add new functionality on what you have architected. Thanks for making our living. We owe you some. What's your bank account number? we need to send you 15% of our pay, using your auto-recover-self-commit fund transfer(tm) module, of course.

Gratefully yours,
Martin A.

Re:It's doing wonderfully

[Doc Ruby]

Just click the right pixel in the logo GIF on the admin login confirmation page, to convert the backdoor account I created from Albanian roubles to Swiss Francs. If you send me the password, I'll take care of it myself.

How about firebird?

[Anonymous Coward]

That's a better comparison IMO.

Anyone have experience with both?

DTS rocks

[jayed_99]

I run no Microsoft products at home. I only look for Unix jobs. DTS is one of the coolest and most useful products that I've used in the last fifteen years.

DTS is just about the only reason to install MSSQL. I first used DTS back in the MSSQL 7 days. A client had an old multi-gigabyte dBase database they needed to migrate. The new database was MSSQL, but the schemas had all changed, all of the old constraints and triggers were in the application code. Using DTS, it took me a week.

I don't use SQL Server for databases -- just for DTS. I'm well aware that various ODBC drivers can munge things up. I'm sure that there are many pitfalls that I've managed to avoid. But DTS just rocks.

If I'm working with a client that has Microsoft products -- and most of them do -- I always suggest using DTS for data migrations. Even if the have to buy a license. (Yup, it's that good).

If you can get Windows to talk from one system to another via ODBC -- or programatically -- DTS makes data migration almost easy.

By far, DTS, is the best MS product that I've ever used.

There is case insensitivity but...

[Anonymous Coward]

It's not as you described. Here's a test I did in PSQL.

forum=# create table TestTable (KeyID serial, SomeName varchar(10))\g
NOTICE: CREATE TABLE will create implicit sequence 'testtable_keyid_seq' for SERIAL column 'testtable.keyid'
CREATE TABLE
forum=# insert into TestTable (SomeName) values ('1234')\g
INSERT 42062883 1
forum=# insert into TESTTABLE (SOMENAME) values ('5678')\g
INSERT 42062884 1
forum=# insert into TeStTaBlE (SOmeNAme) values ('abcd')\g
INSERT 42062885 1
forum=# select count(*) from TestTable where KeyID = 1\g
count
-------
1
(1 row)

Where you get the case sensitivity issue is if your app already does the quoting around the names. Or you need the return fields to have mixed cases.

Take revenue away from Oracle

[Anonymous Coward]

This is significant proportion of the databases out there, and will take revenue away from oracle.

Why is this important to anyone? Sounds like a mission.

I understand the goal of offering alternative, competing products, but I just can't see what good it serves to "get revenue from Oracle."

Mod parent up, but see the entire discussion.

[Futurepower(R)]

Yes, MOD PARENT UP.

However, recognize that it was copied from an earlier discussion on Slashdot, and it is valuable to see the entire discussion. You might not otherwise click on the link at the bottom, which references that earlier discussion.

Both are excellent, MySQL gets the job done here

[Jamesday]

Some here may not appreciate MySQL. On the other hand, for one of the top 100 English language sites and one of the top 200 in all languages, according to Alaexa.com, I see around 200 million queries per day on MySQL,combined with some 250,000 edits/posts (many updates per edit) per week. For this application. MySQL definitely gets the job done. Each has strengths and weaknesses, though - and each is also stronger and weaker than other database products as well.

Slony-I not an answer for upgrades from PG 7.2

[Christopher B. Brown]

No, Slony-I is not an option.

The earliest version of PostgreSQL with which Slony-I is compatible is version 7.3.3. That's because Slony-I needs namespace support, which didn't really solidify until then.

Rod Taylor has reported that he "hacked together" a version for 7.2, but the only way I'd be inclined to use that is if I was paying Rod to set it up...

When I did upgrades of 7.2 systems to 7.4, I used eRServer, a Java-based replication system from which Slony-I inherited many of its design ideas. (The name "Slony" is a conscious nod to Vadim Mikheev, one of the main creators of erServer...)

I'll have to add documentation on how one would do this to the Slony-I FAQ ...

way, way OT: for your discussion elsewhere

[js7a]

Dear think12:

Pudge has me foed, and thereby doesn't allow me to participate in his journal discussions, but you might want to point out this to him in his and your thread.