PostgreSQL 8.4 Out

TheFuzzy writes "PostgreSQL version 8.4 is now out and available for download. The main cool features in this version are: recursive queries (for doing trees etc.), windowing functions (for doing reports) column-level permissions, parallel database restore, a beta in-place upgrade tool, and a host of administrative improvements. And, of course, better performance, mainly on reporting queries. Some of the over 200 new or enhanced features are listed here."

Comments

So why

[jlechem]

Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.

Re:So why

[ducomputergeek]

No, but Oracle taking over MySQL and the community already showing signs of forking in 4 different directions might be a reason to seriously look at PostgreSQL.

Re:So why

[Anonymous Coward]

No, the replication and hot standby patches were not considered of high enough quality to be a part of 8.4. They will be a high priority during development of 8.5.

PostgreSQL would never, ever ship something which has a WTF-list as long as MySQL's replication does: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

Re:So why

[Phroggy]

Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.

Because I don't need features like this, and don't know how to use them. At least, as far as I know, I don't need features like this. Since I don't know how to use them, it doesn't really matter, does it?

MySQL is what I've been running for several years; I'm familiar with the software. I use DBD::mysql in my Perl scripts; I'm sure most things would work fine if I simply switched to DBD::Pg, but would any queries need to be changed? I have no idea. Of course I'd need to migrate my data from MySQL to PostgreSQL; I'm not even sure of the "correct" way to migrate data from one installation of MySQL to another (copying the data files and then fixing whatever's broken usually works well enough). Of course, I'm running a few PHP-based webapps that currently use MySQL; I don't know if it's possible to get them to work with PostgreSQL or not (switching database engines in PHP isn't as simple as it is in Perl).

I could take the time to do the research and find answers to these questions. Or I could keep using what I know works just fine. Maybe someday I'll have some compelling need to try PostgreSQL and see if switching is practical. Today is not that day.

Does this answer your question?

Re:So why

[Just Some Guy]

Honestly, the more rabid and on-the-offense a community gets, the more a am suspicious of the product.

First, the only on-the-offense part was a list of new high-end features. Second, it's pretty easy to become rabid when you try to tell people about your quad-turbo Ferrari dumptruck that does 0-60 in 4 seconds fully loaded and pulls 1.5g on the skidpad while getting 137 miles per gallon, but keep getting shouted down by Kia Fanbois who make fun of every feature your truck has that they don't - until they get a half-assed version of it and then act smug like they invented it.

PostgreSQL fans have nothing on rabid MySQL fans, I promise you.

Re:So why

[rasherbuyer]

The only reason there are comparisons between MySQL and PostgreSQL is because they are both Open Source. Otherwise there is no comparison.

PostgreSQL is a fully featured, enterprise ready, RDBMS and stands for comparison with Oracle and DB2 and to a lesser extent Sybase/SQL Server. MySQL is not even in the same league as any of the previously mentioned. However, web developers seem to like it...

I'll show my colours and say that I've been working with Oracle for over 10 years, and I love it, it gets better all the time. I can't comment on DB2 as I haven't used it for about 10 years and then only briefly.

I've just finished managing a six month data migration project from SQL Server to Oracle. Oracle is head and shoulders above SQL Server in the query stakes and stomps all over it with it's procedural language (PL/SQL vs TSQL). PostgreSQL is much more Oracle-like than anything else, pl/pgsq is even comparable with PL/SQL although not as feature rich.

I recon the Postgres guys are at least at a par with 8i comparing against Oracle, which is pretty damned impressive.

Re:So why

[digitalunity]

It's pretty much spot on though. When a new feature comes out for MySQL, the fanatics love to shove it in your face, even if the real RDBMS have had it for years.

PostgreSQL does everything mySQL does, but better. I would have thought great unicode support alone would be enough to sway the mySQL believers but I was wrong. Now I don't even debate the issue unless I'm sure I am even talking to a reasonable human. The whole mySQL vs. PostgreSQL debate usually descends into the mud quickly with neither side listening to the other.

I'll stick with PostgreSQL, thank you very much.

Re:So why

[Sxooter]

Your arguments ring hollow. In PostgreSQL, using schemas within a database, you get the same basic functionality that MySQL does. I.e. what is a database to MySQL is a schema to PostgreSQL. And you can do cross db / cross network queries, but they are uglier since you have to use dblink or pl/proxy.

OTOH, if you crank up innodb and start doing crossdb transactional queries, guess what? They're not really transactional. Half the transaction can fail and half can succeed and your data is now incoherent.

I have to say I find pgsql far more flexible than MySQL. And you can't put ACID in the app. Sorry, but whoever told you that works lied to you.

Also, take a MySQL server in mid transaction with it's wonderful mixed table design, and pull the power plug / trip a circuit breaker / have some idiot at the hosting center power everything down and you can spend a day or two bringing it back up and restoring your data. I'll just turn my PostgreSQL server back on and keep going.

How many pl languages does MySQL support? I can name over a dozen for pgsql.

Also, I'd rather my database yell at me for trying to insert "stan" as a date and just silently turn it into '0000-00-00'. Or try to insert 123876348763 into an int4 and have it silently truncate to 2^31-1 on me. But hey, that's some flexibility, eh!

Re:So why

[h4rm0ny]

Well I did say that once he learnt PostgreSQL, he'd probably find that MySQL no longer met his needs simply because he realised what more was possible. ;)

By the sounds of his other posts, he's not doing much more than using databases for a bit of data persistence for websites, in which case, admirer of PostgreSQL that I am, he's still probably right that MySQL meets his needs. It would open more doors for him if he knew PostgreSQL, of course. But equally he could be learning Python, perfecting his golf or playing with his kids. PostgreSQL is quite frankly, great. But he who fights fanboys must be careful, lest he himself becometh a fan boy. ;)

PostgreSQL's feature list and reputation speak for itself. I prefer to evangelise gently. ;)

Re:So why

[Sxooter]

MySQL definitely supports foreign keys. Just make sure you're not accidentally using a myisam table, and don't use column level reference defs, cause both will result in a silent ignoring of what you asked it to do.

Wish there was a way to tell it to only use certain table types and throw errors when things go wrong though.

And more...

[jadavis]

Upgrade in place is done via pg_migrator.

VACUUM now makes use of a "visibility map", which means that it doesn't need to process old data each time VACUUM is run. If you run VACUUM on a large table, and then immediately run it again, the second run will be instant.

The recursive queries are the SQL standard common table expressions, that is, WITH and WITH RECURSIVE.

The window functions is a great addition, but with PostgreSQL it's even better because you can define your own custom window functions with CREATE FUNCTION.

There are also a huge number of little improvements, like "auto explain" which is a module that can automatically log the "EXPLAIN ANALYZE" output when a query takes a long time. This is a great convenience for DBAs, because you don't have to look at long-running queries in the log and attempt to EXPLAIN ANALYZE them manually.

Oh, hallelujah!

[Just Some Guy]

Deadlocked Query Display
No more log detective work to track down which operations deadlocked; the information is right there.

That alone is worth the upgrade to me. Currently, if you have two deadlocked transactions, it's a pain in the neck to figure out exactly which ones are involved.

Windowing Functions

[ProfFalcon]

OK, the windowing functions are exactly what I was waiting for. RANK and DENSE_RANK are phenomenal.

See the presentation by Hitoshi Harada here: PDF Presentation

Re:cross database joins??

[Anonymous Coward]

If you're coming from MySQL: What MySQL calls "databases" are called "schemas" in PostgreSQL. MySQL has no equivalent of PostgreSQL's "database".

There is also db-link and dbi-link.

Parent is correct

[Just Some Guy]

I don't know why that got modded troll because it's essentially true. A PostgreSQL database is subdivided into schemas, each schema being much like an entire MySQL database. If you have a database named "mycompany" with schemas "financial" and "hr", you could have something like

select
employee.name,
budgetitems.lineitemamount
from
hr.employee,
financial.budgetitems
where
employee.paylevelid = budgetitems.lineitemid

You can have multiple schemas with the same table names and disambiguate them by referring to schema.table instead of just table. In other words, they're like MySQL databases, and some mod owes the parent an apology.

*sigh*

[Estanislao Mart�nez]

You have given what is, I believe, the most common reason for not switching: MySQL works well enough for what you need so it doesn't make sense to expend additional time and effort (you probably don't have much of the former) to learn something that you will only use for the same purposes. It makes sense.

There are two versions of this argument:

1. The version that applies to a system that already exists, and has been developed with MySQL.
2. The version that applies to a system that has yet to be built.

I can buy the argument in case (1), but not in case (2). In particular, when people say that MySQL works "well enough" for what they need, I simply do not believe them. They are simply not counting the amount of time they've wasted on data integrity issues over the years, because they just don't know better that with a superior RDBMS, those problems could be solved from day one.

Re:cross database joins??

[Foofoobar]

I use the same definition for 'database' everyone else in the industry uses. What the hell are you talking about 'schema' boy?