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."
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.
Hold up, wait a minute, let me put some pimpin in it
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.
Can I do a cross database join yet? To date this has still been a feature that has yet to be implemented that I can do in just about every other RDBMS.
This is my sig. There are many like it but this one is mine.
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?
Very impressive. Thanks PosgreSQL developers.
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.
What I'd like to say is:
Access is to Microsoft's Jet Engine while {name it>} is to PostgreSQL. By the way I would like an Open Source implementation.
" .... This should help users migrating from MySQL. ... "
Because we know they are sure to be coming in droves whether they know it or not. :)
Is there a significant performance difference between WITH RECURSIVE and a recursive function?
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?
SQL Server has always been a decent database. Going to PostgreSQL would probably be a downgrade. Though if I have to say one thing, it would be this: SQL Server 2005 is a lot better in my experience than 2000.
Are you adequate?
...slashdot!
This unique sig is intended to make this user more recognisable.
SQLite on the low end, Postgres for mid-range apps, and Oracle on the high end.
Where is the niche for MS SQL and MySQL in this picture?
A slashdotter who didn't build his own computer is like a Jedi who didn't build his own lightsaber.
Take a look at the more general SAVEPOINT feature. They can be thought of as sub-transactions.
Any transactional statement may have the exception caught and effects rolled back within the parent transaction but it will accommodate complex logic:
BEGIN;
SAVEPOINT trying_complex_action; ... ... ... ...
DELETE
UPDATE
DELETE
INSERT
-- INSERT threw exception. Catch it!
-- Determine this is the type (say unique value exception)
-- we don't care about and get rid of this work
-- continuing with the outer transaction.
ROLLBACK TO SAVEPOINT trying_complex_action;
I regularly use this to ignore unique value violations but still pass something like a bad date format upstream to the application. You can opt to rollback on all errors if you choose.
Rod Taylor
One phrase: repeatable SQL.
How about: "insert ... select ... where not in (...)"?
Social scientists are inspired by theories; scientists are humbled by facts.
There are two versions of this argument:
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.
Are you adequate?
I think the addition of convenient bulk loading tools could be a game changer for potential enterprise users, or anyone loading high volumes of data.
There is no off postion on the genius switch. - David Letterman
In place migration tool is still in beta
Check it in a couple days. As I understand it, it was mostly waiting for 8.4.0 to be released.
Online replication. Now you mostly have offline filesystem based replication
The most common replication schemes in postgresql are probably warm standby (which doesn't currently allow reads on the slave), Slony (not builtin) and londiste (not builtin). All of those are online replication (unless you consider warm standby to be offline). Maybe you already know this, but I am just clarifying.
The next release will prioritize hot standby (same as warm standby but you can read the slave), and sync rep as built-in replication systems.
Social scientists are inspired by theories; scientists are humbled by facts.
I have what has become a fair-sized database program that I wrote in a version of Basic several years ago. There were good reasons for doing it that way at the time, mostly related to cross-platform requirements, available hardware and a limited set of options at the time, all of which have since become pretty much irrelevant as the years went by. However, the program keeps cranking and has grown and been expanded and added to (by me) into what now amounts to a "management suite" for the business that I wrote it for.
More and more lately, I've been thinking that while this Rube Goldberg contraption is currently working fine, it might be smart to move it onto a real database. So I'm thinking about rewriting the whole thing over the course of time, and the little that I know about Postgresql makes it seem to be just exactly the tool for the job. The whole business runs on Centos Linux now, anyway.
Accordingly, I guess I should get started learning Postgresql. I've been writing programs for 30 years but the only real database I have ever worked with before was dBase and I haven't touched that for years either. Accordingly, I would appreciate recommendations for relevant books, websites, tutorials, whatever.
If you're a zombie and you know it, bite your friend!
Here's one with the people with mod points today: "You Boobs".
You work for idiots, and should really start looking for a better job. Seriously.
--- It is not the things we do which we regret the most, but the things which we don't do.
Actually you kinda have it backwards on "what could go wrong". PostgreSQL has a more unix like philosophy, in that it gives you lots of small sharp tools you can use to build a solution to any problem. MySQL, instead of providing these tools, builds a custom but non-standard SQL answer to each and every problem, that mostly works for most people, and when it doesn't oh well, that's what you get.
begin;
select * from table where id=$a for update ;
-- (check number of rows returned if > 1 then)
update table set yada=$b where id=$a;
-- else
insert into table values ($a,$b);
-- if no errors musta worked
commit;
turn it into a function (pick your favorite language if you don't wanna learn plpgsql) and it's all wrapped up and ready to go. And, if that doesn't do exactly what you want, you can CHANGE IT. Something you can't really do with MySQL without forking the code base.
--- It is not the things we do which we regret the most, but the things which we don't do.
I've been using Slony for years, and while it's not the simplest nor easiest replication to setup, it works, and it works very well, within it's domain of functionality. Yeah, I look forward to hot standby servers in a year or two, but til then, I guess I'll keep using slony.
--- It is not the things we do which we regret the most, but the things which we don't do.
There are some first runs at such a thing, but they're all alpha level code or have serious restrictions. Look for Postgres-R and Bucardo to see if they can do what you need. Neither are RAC, but then RAC isn't necessarily the best answer all the time either for multi-master, as sometimes you need real shared_nothing clustering.
--- It is not the things we do which we regret the most, but the things which we don't do.
I use PostgreSQL daily and I love it. The window functions are an enormous boon! It's still the best.
We need some distribution happening a la Netezza, Greenplum, etc:
Anyone know better?
postgres=# select count(*) from enhancements;
count
-------
293
(1 row)
I would give a +100 insightful, but I have no mod points.
Sadly most people forget exactly that. I did one project of transforming an application from mysql to postgres. This was a very small application, but it still took an amazing long time to transform it over.
So most the time I would say, if it works, stick with it.
"Freiheit ist immer auch die Freiheit des Andersdenkenden" - Rosa Luxemburg, 1871 - 1919