Slashdot Mirror
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."
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.
Social scientists are inspired by theories; scientists are humbled by facts.
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.
"The problem with socialism is eventually you run out of other people's money" - Thatcher.
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
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.
Dewey, what part of this looks like authorities should be involved?
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?
$x='S24;r)>63/* h@<5+oZ)32"5cz';$me='phroggy'x$];
$x=~y+ -xz+\0-Tx+;print$_^chop$me for split'',$x;
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
Simply stating [Citation Needed] does not automatically make you insightful or brilliant.
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.
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.
Dewey, what part of this looks like authorities should be involved?
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.
Dewey, what part of this looks like authorities should be involved?
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.
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.
You can't legislate goodness. Let each to his own destiny, by will of his freely made choices.