PostgreSQL 8.4 Out 191
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."
So why (Score:3)
Re: (Score:2)
Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.
Re:So why (Score:5, Interesting)
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: (Score:2)
Re: (Score:2)
Re: (Score:2)
I'll concede your point, but the original poster did state that his app was working fine as is with MySQL. If the app is simple enough you might simply not have to use stored procedures at all.
Truthfully though, I'm not sure what keeps drawing users back to MySQL as a default. As the OP stated, it's often not worth the effort to switch a working app to a different database (and I've still got one application hitting a MySQL database because it works fine - all it does it let the company secretaries enter
Re: (Score:2)
"the original poster did state that his app was working fine as is with MySQL."
So he thinks. From past experience I'd bet he simply doesn't know better.
"I've still got one application hitting a MySQL database because it works fine - all it does it let the company secretaries enter in phone calls that their bosses receive in a help desk type fashion)"
And since you are using MySQL all your referential management is done in code (phone table should have person's id column as a foreign key from persons table,
Re: (Score:3, Informative)
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.
Re: (Score:2)
I can't fathom why anyone chooses it when starting out from scratch anymore.
The excuses are quickly running out, that's for sure. Lack of "easy" master/slave replication with readable slave is probably the only serious complaint.
Even in that case, it's not like you can't do it, but it will be a challenge. Slony is my favorite of the current systems (assuming you have to read from the slave), but "easy" is not one of its strong points. Robust and flexible, but not easy.
I might say that SQL standard MERGE wou
Re: (Score:2)
Also, it [Toad] seems to get wedged if you enable DBMS_OUTPUT to one of its multitudinous window panes, but maybe I was just doing something wrong there. Urgh. I hate this utter piece of crap.
When polling for dbms_output is enabled, and it tries to poll while a query is running, it freezes until the query finishes. When there's a huge amount of output to fetch, I think it also freezes while fetching it.
Re: (Score:2)
I don't know, I think windowing functions are pretty significant,
Sure, it depends on whether you have an app that benefits from them, but assuming you do, it's pretty significant. Doing sub-sorts on large datasets is a killer without functions like these.
Re: (Score:2)
Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.
Maybe not the new features, but all the good features that PostgreSQL already had were plenty reason for me to switch my MySQL apps. I suppose it depends on your definition of 'working'. I found that modeling and enforcing constraints in application code instead of in the database was becoming prohibitive for any application of decent complexity. Of course, I know that there are two camps on that issue, and the OOP/Framework crowd (Ruby on Rails, DJango, etc...) tend to take the philosophy that the database
Re: (Score:2)
I would agree that none of the new features would make switching from MySQL to PostgreSQL compelling. OTOH, the features PostgreSQL has had for quite some time ARE compelling to switch. Like storing the db meta data in transaction safe tables (pgsql) versus storing them in non-crash / non-transaction safe tables (MySQL). Or making sure that what you put in either goes in or throws an error without mangling the data (i.e. MySQL inserts 'stan' into a timestamp field as 0000-00-00 00:00:00 a non-existent an
Re: (Score:2)
Does it have built in replication yet?
Re:So why (Score:5, Informative)
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 [mysql.com]
Re: (Score:2)
git repo [postgresql.org]
Design spec [postgresql.org]
This is the feature that didn't quite make it in 8.4. Look at how much effort and research went into the design. This isn't a situation where someone threw some code together; this is real design and real code by a highly reputable hacker, that answered feedback from other highly reputable hackers.
The only reason this didn't make it is that PostgreSQL has a reputation for getting things right (really right), and that's more important than one feature.
But try it out. If the code hasn't rot
Re:So why (Score:5, Interesting)
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: (Score:2)
PHP supports PostgreSQL its as simple as doing a global find-replace of mysql_ with pg_ there done. Okay a bit too simple but the mysql functions and the pg functions have the same names other than the prefixes and they behave in the same exact manner. You may need to change a little bit of SQL.
Once you get to start to use the more advanced features of postgresql you'll look at mysql more as a toy rather than a fully featured RDBMS.
It's rather like when you first started playing with mysql and went "Wow,
Re: (Score:2)
... or you could just use PDO [php.net], the way God intended.
Re: (Score:2)
Re: (Score:2)
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.
But in a friendly response, I'll just mention that once you are familiar with PostgreSQL, you will be aware of what more you can do with it and that may lead to the second part of your logic (you will o
*sigh* (Score:2)
There are two versions of this argument:
I can bu
Re: (Score: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.
But I haven't had issues with data integrity. No, I don't have a large database. I simply don't have that much data. If I did, and I had enough users accessing the data that data integrity ever became an issue, then yeah, of course I would want to fix it. That would be a compelling reason to switch, certainly. But my future projects aren't likely to be much bigger than my previous projects, and MySQL has performed well enough in the past that I expect it to work in the future too.
Re: (Score:2)
"Really, you don't need to learn PostgreSQL if MySQL is meeting your needs."
The real problem is that MySQL only *seems* to meet some needs because the one with them doesn't know any better.
Nine out of ten times (to say the least) when somebody has told me "but MySQL covers my needs" it turned out he had no idea what their real needs were and what were the tools he could use (both "real" and logical) so he could pick the best fitted.
While ignorance is a reason, it's not a very glamurous one.
Re: (Score:3, Insightful)
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
Re: (Score:2)
I think it might be worth examining the differences between the two dbs. I've found some truly compelling things about pgsql. Here's a short list of the things I really like about pgsql:
Transactional DDL statements. Sounds kinda trivial, but it lets you do things like alter a table, update a field, and then, if things go horribly wrong, you can roll it back. For production updates, this can be a life saver. In pgsql everything except create / drop database and create drop tablespace are transactable.
The
Re: (Score:2)
Re: (Score:2)
Fortunately, it's getting much easier to find a hosting provider that offers PostgreSQL. Of course, if you just rent a bare machine and install your own image, you've always been able to install PostgreSQL.
ask... (Score:2)
Re: (Score:3, Interesting)
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 al
Re: (Score:2)
Maybe you can relay back to Oracle please that the eBusiness java interface is god awful slow.
Thanks. You might have more sway than I do.
Re:So why (Score:5, Insightful)
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: (Score:2)
Re: (Score:2)
Yeah, but kind of funny though.
Re:So why (Score:4, Insightful)
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: (Score:2)
Re: (Score:2)
MySQL, for all it's so-called flexibility, has abysmal indexing capabilities. It's unicode support is far inferior. MySQL might be faster on a connections per second rate, but falls behind on update speed.
I'm not oblivious to PG's inferiorities though. I guess the biggest issue is that PG's replication is inefficient compared to MySQL.
Re: (Score:2)
s/inefficient/reliable/
Re: (Score:3)
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
Re: (Score:2)
I would also suggest lear
Re: (Score:2)
RPF: Rabit Postgres Fanbois.
Honestly, the more rabid and on-the-offense a community gets, the more a am suspicious of the product.
Why be suspicious? It's pretty easy to get actual knowledge of what the product does without regard to fanbois or flamers. I don't make any choices for or against a product based on that. Also, if you browse the PostgreSQL mail lists, I think you'll see very little rabid-ness. You *will* see an intense dedication to quality, especially if you read the core developers' lists. Quite interesting discussions, there.
Anyway, isn't it just a little disingenuous to come to a PostgreSQL thread, and then accuse Postg
Re: (Score:2)
Really? I've found there to be plenty of rabid fanboi types on both side of the fence. Pgsql users often still have a chip on their shoulder from the totally slanderous statements MySQL AB used to make about PostgreSQL, and they made a lot of them, and for a long time, it stuck, and I heard them repeated over and over from mysql fanbois.
I tend to ignore fanbois on both sides of the fence and consider the two positions based on reason, evidence, experience, testing, and support. I find that pgsql beats my
Re: (Score:2)
Either that or the postgress fanbois really are getting all the chicks and I am on the wrong bandwagon.
I'd reply to this, but my Postgres DBA is here, working on my equipment. Before she got her PhD, she was an Olympic gymnast. She can suck the chrome off a trailer hitch and gives a whole new meaning to the phrase 'hot swap'. Now, if you'll excuse me, I have some... uh, maintenance to perform.
Re: (Score:2)
For a serious project, I would expect someone to use scripts anyway.
Re: (Score:2)
Eh? Why would that be a problem? Just use the supplied backup & restore tools (pg_dump and pg_restore), they work just fine. If you really can't back up a database without a pointy-clicky GUI, you have no business being a DBA.
Mart
Re: (Score:2)
pgadmin is the official frontend to pg_dump. Since you've no clue what YOU'RE talking about, I hope you're not a DBA. I'm not, by the way.
Re: (Score:2)
No, it's not. Whoever told you that lied. The official front end to pg_dump is bash. or csh, or tsh, or ksh. And yea, if you can't drive a database without pointy clicky you should get a job with lots of round, non-sharp objects so as not to hurt yourself or others.
Re: (Score:2)
I believe this is by design.
Dumping it from one database and loading it into the other will always work, and, I believe, is the recommended procedure.
Re: (Score:2)
I was about to predict some abuse coming your way, but I see it's already appeared. : /
If PostgreSQL is not for you, no problem, but seriously, just using pg_dump would be the proper way to do this and it seems a shame to change to a different database if that's the sole issue. </friendlycomment>
Re: (Score:2)
We've always used PostgreSQL for development when the plans were to move to an enterprise class HA cluster down the road. Usually if it worked well in PostgreSQL, things really worked well when we moved to DB2 or Oracle.
Re: (Score:2)
LunarPages [lunarpages.com] is around that range and they provide both PostgreSQL and MySQL as options.
Re: (Score:2)
Have you used a recent version of pgsql with the windows installer? It's really quite nice.
And more... (Score:5, Informative)
Upgrade in place is done via pg_migrator [pgfoundry.org].
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.
Re: (Score:2)
> Upgrade in place is done via pg_migrator
Outstanding. That was kind of painful when I upgraded RubyForge to PostgreSQL 8.3 [blogs.com]; looking forward to a much smaller downtime window for the upgrade to 8.4.
Too bad replication didn't make it in there... maybe in 8.5.
Thanks for all the good work. (Score:2)
Re: (Score:2)
Re: (Score:2)
That was a feature that caught my eye, too, which is why I mentioned it. It seems like a small thing, but it makes life easier.
Not only that, but it allows you to explain nested plans, like those from within functions executed in the outer plan.
cross database joins?? (Score:2, Interesting)
Re: (Score:2)
Re: (Score:3, Funny)
Re: (Score:2)
if you need x database joins in postgres, you can always use dblink of course.
Re: (Score:2)
Can I do a cross database join yet?
No. They where not implemented in this version, and the workaround is still the one using contrib/dblink, which allows cross-database queries using function calls.
Still, even if I sound like a fanboy, this is a very minor annoyance: PG is the best OSS, Free, Gratis, RDBMS available.
Re:cross database joins?? (Score:5, Informative)
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 (Score:5, Informative)
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.
Re: (Score:2)
Thats perfectly fine and well, but can you put two different schemas in two different boxes? no, you canÂt.
Re: (Score:2)
Thats perfectly fine and well, but can you put two different schemas in two different boxes
First, give us a use case explaining what it is you're actually wanting to do. Second, do db-link and dbi-link (from the GP post) not do exactly that?
Re: (Score:2)
Re: (Score:2)
Thats perfectly fine and well, but can you put two different schemas in two different boxes? no, you can't.
Depending on what you're trying to achieve with that, you could store the data with partitioning and network file systems (not that I'd risk my data that way). But can you actually do hr.employee.id=job.task.employee on mysql when hr and job are "databases" on different servers?
Anyway, if you really have an external database (that may be somewhere entirely different) there's DBlink [postgresql.org] and its friend DBI- [pgfoundry.org]
Oh, hallelujah! (Score:4, Informative)
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.
Re: (Score:2)
Good lord, I didn't see that in the new feature list. Thank you, good sir, for bringing this to our attention. I've spent many frustrating hours trying (and often failing) to track this information down.
Re: (Score:2)
Windowing Functions (Score:3, Informative)
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 [pgcon.org]
What compares to Access on PostgreSQL? (Score:2)
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.
Re: (Score:2)
Oh really? The last time I checked, I could not implement business logic using this. I will check again.
Re: (Score:2)
OOo Base does indeed with with PG directly or as an ODBC connection.
I've got to say now though, if you're using Base for creating reports on tables that are large, expect it to run painfully slow, even if the data being used in the report is small. It seems to fetch the entire table from the database, regardless of which fields are needed to compile the report.
I like this one snippet (Score:2)
" .... This should help users migrating from MySQL. ... "
Because we know they are sure to be coming in droves whether they know it or not. :)
Recursive Performance (Score:2)
Is there a significant performance difference between WITH RECURSIVE and a recursive function?
my opinion (Score:2)
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?
Re: (Score:2)
The MS SQL niche is for those that loyal to Microsoft, regardless of cost. For those who will write Microsoft any size check because they are already using all Microsoft's other software, so what's a few more dollars... even though their company just cut staff by a few percent, you can't decide to simply not pay your license fees.
That said, save for what I consider to be a lack of data types, SQL server is better than MySQL.
Re: (Score:2)
Re: (Score:2)
The niche for MS SQL comes from all of the developers who have developed apps built on top of it. Most of those apps have been around for longer than a few years. From the point of view of a developer coding a new application from the ground up, it might not make much sense to saddle customers with the cost of MS SQL licenses when something like Postgres could get the job done.
Another issue might come from scalability. MS SQL scales pretty well. Postgres is still waiting into introduce it. Once they in
Re: (Score:2)
"SQL Server is better than Postgres, period, and has tons of features that Postgres doesn't"
[Citation needed]
PostgreSQL is teh awesomz (Score:2)
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?
Re: (Score:2)
You work for idiots, and should really start looking for a better job. Seriously.
Re: (Score:2)
Of course. But then again, 2008 is leaps and bounds beyond 2005 too.
2000 was good, but paled compared to some of the more expensive competition, and lacked some "enterprise" features. 2005 was a competitive offering. 2008 is in many ways ahead of the competition. And the dev tools rock :)
Re: (Score:2)
Re: (Score:2)
Many more data types, including a datetime type that doesn't suck. A new datatype that acts like it stores files in the database but keeps them in an external folder (that one is probably one of the biggest. This is really a big deal. Much more powerful than rolling up your own solution). Major enhancements of the devtools (native SQL intellisense). ETL and business intelligence stuff got big improvements (ability to use any .NET language in SSIS and better use of multi-core CPU for ETL, enhancements to SSA
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
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
Re: (Score:2)
One phrase: repeatable SQL.
How about: "insert ... select ... where not in (...)"?
Re: (Score:2)
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
Re: (Score:2)
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 ju
Re: (Score:2)
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.
Re: (Score:2)
The way I see your problem, you have either a very bad data set (millions of unique constraint violations) or a bad schema (a field that's marked as unique that shouldn't be). Yes, correcting this is a massive PITA, but PostGres is relatively intolerant of those sorts of faults for a reason. Both the INSERT IGNORE and INSERT REPLACE look to me (from the documentation, I'm not a DBA) like they'd be prone to losing some data in a way that you don't notice. Which for anything mission-critical is really really
Re: (Score:2)
My experience with dealing with large-ish data loads (max of about 500 million records) is that for anything that large you probably want to build a migration app of some kind anyways, in part because of point (d), and in part because you want a consistent state and recoverability in the event of trouble (e.g. server crash). At which point the application can and should be coded to handle the UCVs in some graceful way.
I also was basing my response a bit on the MySQL folks I've come in contact with, who ofte
Re: (Score:2)
You should look up both pg_loader (which acts a lot like oracles bulk loader) or pg_bulkloader which does the dirty under the covers trick to just stick data into tables while the db is down and when it comes back up viola, there it is. Both are quite impressive, but have different use cases.
Re: (Score:2)
Head First SQL is good. It teaches SQL which is essentially the lingua franca of modern day rdbms. You may be put off by the illustrations and the seemingly non-techie approach, but it does a good job in teaching so you learn it. Download or bookmark the postgresql documentation from www.postgresql.org as an adjunct.
Also, if you haven't, you may want to consider using a different programming language. For instance ruby and java have frameworks, such as ActiveRecord and Hibernate, which free you from a lot o
Re: (Score:2)
The PostgreSQL documentation is about as good as I've ever seen in an Open Source product. The manual is typically well written, and it is full of good examples. It makes something like JBoss look line an underdocumented mess.
And if at any point you find that a page of documentation is missing some detail, you can always post it as a comment in that same page, and save the rest of us some grief.
Re: (Score:2)
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.