Accumulating data in this manner is only trivial as long as the data is kept in-house by bud. However, once they sell that data to a broker it becomes just another data point used for profiling - and its value sky-rockets.
Before the industrial age profiling of the community members wasn't a mass-produced product, available for pennies. Soon your insurance company , prospective employer, etc will be able to pay a few pennies to determine your "long term health costs" - based on *exactly* this kind of data.
Before the industrial age you weren't likely to encounter some greasy peddler who would walk up to you and say, "Hi Jim, I've noticed that your contraceptive purchases have stopped, and you're now getting that Parent magazine, and well - since this will be Beth's first child, and she's 39 - I'd like to let you know that I can hook you up with some really good tests for birth defects...". It won't be long.
The only thing rediculous here is that in the info-age so few of the info-workers really understand the how the economics of information work, or have even the vaguest of ideas of what the social impacts of free information are.
This isn't static data functionality - it's standard data warehousing functionality.
And none of the open source databases have yet caught up with the features that the commercial databases have been adding over the past seven years in support of this.
Still, I've used postgresql for some reporting, and it isn't the end of the world to drop back to older technology in warehousing. But the lack of materialized views isn't the real issue - it's the lack of any form of partitioning.
> While it took $45 million to achieve the crime rates Chicago has today, as an open source programmer, > I have to think that they wasted about $42 million.
Sure - if the 'bunch of programmers' work for free. Odds are - that over $30 million of that total went to labor. Now - that is a pathetic and inflated figure - but most government projects are hideously bureacratic. And Oracle's hardly a lean and efficient contractor to work with ($300/hr isn't unusual for them).
Would open source have made a difference? Yeah, probably would have pushed the costs up even more. Oracle has far better parallelism, load, and partition management features than either mysql or postgresql.
So, open source has a lot of wins under its belt, but that doesn't mean it can out-compete closed-source on all fronts.
ok, so postgresql has two issues with syntax, and mysql has dozens of situations in which it will silently fail.
And that's comparable?
If you want to read something amazing, go ahead and read the MySQL manuals, look for design deficiencies: http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Bugs
You'll see more than just minor syntax issues - you'll see that mysql spends most of its time in silent error mode.
Since analog is the original form, and has the most sophsticated interface...it kind of follows that the digital watch is really just a technical triviality, doesn't it?
How about working inside an 8x20 trailer in during the summer with a 450-lb ex-wrestler with almost zero sphincter control?
The guy farted so often it just became background noise, and there's no way the ventilation allowed by the trailer door could keep up with his CFM. When contronted - he's say "hey, in all those years of wrestling I tore a lot of muscles - it isn't my fault".
Another problem in the trailer is that it was so small he'd have to squeeze by me to get past my desk and out the door. Invariably this lead to another outburst. At least when that happened I knew it was coming and could lean a little out of the way.
Just as I was starting to look for another job I got lucky - the place hired other new guy, and so I only spent two weeks in that trailer.
As long as someone in the world is: - getting shot at - selling sex - selling organs - having to shoot others - having to torture others - risking life & limb in otherwise dangerous situations...
nobody gets to complain. So, just suck it up with your crummy desk jobs.
And no complaining about those monster executive salaries & perks either - they've also got hard jobs and deserve every penny.
1. at one system integrator that I worked at we used their apps to manage our finances and also resold their database. Anyhow their financial app (which we all had to use to record our time) became our #1 example of how *not* to design a UI.
2. they used java to make it platform independent, but then require IE - which requires windows. Is this brain-dead or what? They might as well have developed the apps in ASP!
3. in almost any business activity where there's real competition - they stink. They were pushing a CRM package that was a laughable pile of poo. But that didn't stop their hard-charging salesdrones from hammering people with it.
Only after using their clunky applications (where they try to rewrite all the rules on UI design) can you really appreciate the mess that is the oracle database. Sure, the oracle database is more mature, more stable, and better in class than oracle financials. But - it is still hopelessly complex, error-prone, and bloated.
> There are more languages than python and C and C++ (two different languages).
Sure, though I think that they express the extremes pretty well.
My shop is primarily java, but I introduced python since it's much faster/easier to develop and maintain, and we have few real java experts. Python was an exception, but I've got an ace in the hole if I get challenged on standards-compliance: I can run it in a JVM with a seamless bean/class interface (jython). I'm not eager to push yet another exception on the standards guys. At least not yet.;-)
> I know of projects using interpreted languages that have failed - the application did work - albeit, it > took 30 seconds (best time) to 5 minutes to respond to a single keystroke. It was later abandoned because > despite buying faster hardware, the programmers couldn't get better than a 20 second response time. > An average improvement of just 15 milliseconds per module would have saved the project!
Or how about - some one could have made the design decision to avoid going through 1000 layers of abstraction? I mean, you do begin to hit diminishing returns at some point, eh?
Right now I'm using python to perform heavy transformations on firewall data - tens of millions of rows per day. Looks like this process will handle around 300 million rows per day on a single robust server.
1. Would it be faster in C or C++? Sure 2. Would the change of a defect increase in C or C+ vs python: Sure, far more chance to screw something up 3. Is it worth rewriting in order to get 1200 million firewall events / day instead of 300 on a single server? Not now - I'd rather keep it in python and just split the processing across two servers than implement in C or C++. Of course, we could always go to psycho, or rewrite small parts first. But eventually, if those steps failed to keep the load manageable, then *maybe* i'd consider rewriting in C/C++.
Of course, keep in mind that this is python handling 1.2 *billion* heavy transformations a day by the time I'm starting to think about a real performance problem. So, can it handle performance-intensive projects - sure. Is it the fastest solution out there? not by a longshot. Are there things that matter more than speed. Yeah, a ton.
Keep in mind that the most powerful factor in performance-tuning has nothing to do with design - but rather with requirements. I've done a lot of performance-tuning in which I completely eliminated elegant and efficient designs - that simply weren't needed. They were solving a problem that didn't exist. One advantage of a higher-level language like python is that you spend more time at the requirements level - and can more easily detect this problem.
*why* you can't say things
on
What You Can't Say
·
· Score: 3, Insightful
> As for other things you can't say, here are some that I'm going to say... > *IS NOT NORMAL* > *your weird way* > *crackpot parents* > *offensive to me*
ah but there's a world of a difference between a crackpot yelling at the world and thoughtful discussion of serious topics. All it takes is a few cranks arguing this way and everyone that follows looses their credibility!
> In a way, it's pathetic that this is the only way to do it; in another direction, though, it's a very smart thing to move any activity > that doesn't need to happen on the primary (bottleneck) server to a secondary server instead.
Sure - but in the case of backups - how do you perform a restore based off a replicated database - and then reapply all the missing transactions? I don't think it can be done. Which means that these are the equivilent of offline backups, not online as far as recovery goes (though online as far as availability goes I suppose).
> A similar practice is commonplace in the MS SQL Server world, running lengthly reporting queries on a secondary reporting database > instead of on the primary.
Yep, this isn't too expensive - aside from the licenses and hardware. I'd typically recommend folks to use light ETL and convert the data in transit. That'll give you better performance and accuracy than you can get out of reporting on an oltp model.
> Again you missed the point - which was that the zealots were trumpeting PostgreSQL as the perfect solution > for hair loss even back when these limitations were in place.
Ah maybe so. Well, in the interest of helping to set the record straight, here's my take on that:
- 8k wasn't much of a limit until we started storing multi-media in databases. Prior to that it was rare to hit an 8k limit. Postgresql was a little late with their support here.
- mandatory maintenance downtime was common even in commercial databases until about a year ago I think, at least for reorgs. This wasn't too much of a problem really until the internet made it possible to do work around the work 7x24. Even today you're still looking at downtime - for backups with mysql and probably most configuration changes, upgrades, expansions, and tunings of either database.
So yeah - postgresql fell short of mysql in content management solutions two years ago, just as mysql fell short of postgresql in non-content management solutions. Today neither of the above are true. Postgresql has pushed back these limits and has improved performance. Mysql has made significant strides in improving its dbms functionality, but still has large gaps. It'll hopefully resolve these gap soon - but until then its primary value proposition is that it has better windows support, isp support, and documentation than postgresql. Over the long-term this strategy may pay off for mysql - build market share with a unique content management position, put out a lot of fud to deflect database criticisms, then bust its butt to get essential functionality in place before users begin to migrate other solutions.
But until this functionality in place mysql just isn't competitive in most database applications besides content management.
> Sorry, but Yes, it IS a real database. And no, I truly don't appreciate the dismissive, elitist attitudes of people like you.
Dude, don't take criticism of the mysql product so personally. Relational databases have been around a *long* time, and the basic functionality is *well* understood. If mysql ab hadn't taken the equivilent position to declaring OO unnecessary and delivering a new implementation of COBOL '68 - then they wouldn't be the recipient of so much *legitimate* criticism.
And don't act like we're calling your baby ugly. I viited your website - it looks fine, feels crisp, and appears to have reasonable functionality. Great. If you want to use mysql - and deal with silent errors, non-standard sql, sql limitations, etc - fine. Go for it. Whatever - you can still work around these problems and get a good product in the end. But please don't demonize everyone that levels a valid complaint about the mysql non-relational philosophy.
> You conveniently ignored the 8k row limits and the VACUUM blocking yet again, because it doesn't fit with your cozy world view. Oh well.
> I get so tired of reading all these "MySQL sucks" comments from the PostgreSQL crowd every time there is any mention of the two databases. > Well, for whatever reason, this time I feel compelled to comment in return.
Point taken: postgresql used to have some performance and availability limitations, mysql still has functional and usability limitations.
> MySQL *does* have real transactions. which are implemented using a third-party product, are limited and aren't seamlessly integrated, and which can fail silently due to syntax violations!
> I have never missed subqueries yep, I've occasionally developed trivial databases without them. But I've seldom developed a serious database without them.
> There is a definite sense of intellectual snobbery and elitism in many of the comments from PostgreSQL fans slamming MySQL. Nah, more likely it's the disgust that the more experienced developers feel with an obvious charletan that claims that well-proven techniques aren't necessary. Kinda like if ten years ago Yuga had insisted that air-bags, seat belts, and bumpers weren't needed "by most people in most situations". Yeah, right.
Keep in mind that without referential integrity, transactions, and some other missing features - the product isn't a relational database. It's just an isam file with a non-standard sql interface.
And don't forget that each one of the basic core functional parts of a relational database (like referential integrity) are added gradually - with initial limitations that are incrementally removed.
That's a fine way to improve the product. But it was disingenuous and irresponsible of mysql ab to defend its weak functionality by claiming that transactions aren't necessary.
> So far, i'm not doing anything out of the ordinary. nothing too complicated database wise. > What exactly would be the advantage of using Postgres.
How's this - you'll be using a mature database that has a good implementation of almost all standard database functionality - vs one that has only implemented some of it, and has repeatedly claimed that this standard functionality wasn't necessary.
So, now MySQL is quickly trying to check off all these deficiencies, which include:
- transactions
- unions
- subselects
- triggers
- views
- stored procedures
- stored procedures
- parallelism
- online backups
- replication
- clustering
- etc Some of these have been partially implemented in the 4.1 release, some are now being addressed in this new alpha. Most exist today in postgresql - and are now being incrementally improved.
Who knows? Perhaps they'll do a competant job - on the other hand functionality like 'subselects' isn't something you simply 'check off'. You want a carefully-designed implementation that covers much of the functionality implied here - not just a minimal job.
Not to say that mysql can't do a fabulous job at improving their product in record time. However, I see no reason to give the same company that insisted this functionality wasn't necessary the benefit of the doubt here. Especially when their current stable product has so many unacceptable silent failure modes.
> The difference between 1981 and 2003 (for big projects) is that the database is usually touched only in an > object create, store, retrieve and delete methods and in rare cases one or two more application specific methods.
It's really not that much different today than it was 20 years ago: we knew then as well as today to isolate the database interface. It was more difficult then (they were often just paragraphs in COBOL)- because the languages weren't generally as sophisticated as they are today. The real problem occured over time - over 5-10 years of modification with new technologies - the isolation would typically break down. Today the same is true - you build your app layer in Java - and then someone wants to connect their.NET application to it. Now you're screwed.
Another reason to avoid trying to perform this code in the application layer - is that in the db layer it is declarative - easily created, easily enforced, 100% guaranteed to work, and easily tested. Do it in the app layer and you will frequently screw it up. Sure - maybe *you* are good enough not to, but 95% of the folks out there aren't.
Another reason to avoid driving the database design from OO - is that OO has only caught up with transactions in about the last ten years. It still hasn't caught up with decision support. So, the developers can bloody well encapsulate the hell out of their data, keep it in an object store, etc. Then when management starts to demand robust analysis of the data and the developers discover only at this time * that much of their private data - really isn't * that their data is nearly impossible to compare from one part of the application to another (because they thought it was data - when it was supposed to be information) * that they have invalid data - because of defects in their referential integrity rules * that they have inconsistent data - because their referential integrity rules changed over time - and they just handled in in their API
It's about that time that management will replace the designers and architects of that solution with new ones - that understand more than just 50% of the problem.
> I just wanted to point out that, as of 7.3, PostgreSQL fully supports result sets returned from functions/stored procedures.
I had heard that this was coming - but while using 7.1 the documentation and some of the offered implementations were so poor that I couldn't count on it. The shared-cursor (I forget its exact name) was an especially awful-sounding substitute for what I was looking for.
> Hopefully replication has improved by now. I know there are a lot of people using it, > but I don't know enough to comment.
Yeah, I'm seeing progress here. It's not really at the top of my list though - aside from an occasional need I view it more as a liability in a production system.
> As far as python is concerned, wouldn't you normally write the functions in a seperate editor > and then just pass it through an escaping filter?
Not sure what you mean by an escaping filter - I'm assuming that I can access code with any vanilla editor - without having to rely on intelligent parsing. Not sure how that could work anyway - since I've got python variables mixed with sql code...
> I recognize that some of the bigger DBs have more features
Right - and as long as the core functionality is solid, I don't mind if some of the fringe stuff is missing. But postgresql is there. It's enough there to be seriously considered for quite a few production tasks.
Sounds like a perfect mysql strategy - you don't need all the extra 'fluff' in the database - because you don't mind getting garbage into your database. Cleaning it up later while the database is idle will also generally be faster.
Makes perfect sense - if you don't mind invalid data. Also note that performance problems with referential integrity is generally a sign of bad design - you should have (in general) neither locking nor performance issues with this built-in protection.
Also note: this sounds a lot like arguments I remember from the early 80s - in which the old spahgetti programmers using flat files were quite upset that we would dare to create data stores with built-in protection against invalid data.
Of course, the reason that we did this is that they continuously tossed garbage into the database. Most of them considered themselves 'designers' - but failed to understand the economic value of the data - and that given enough time they were certain to screw it up. Instead they focused on performance.
So here we are with mysql - catching up to 1981...
> What functionality left out of postgresql do you find most important?
On a recent conversion (14 months ago) of a large MS SQL Server database to Postgresql the single biggest issue was the inability to return a result set from a stored procedure.
Another issue is that stored procedure support for most languages isn't complete. For example, since python is run outside of the database there are strange, bizarre, and ultimately unacceptable quoting and escaping that needs to be done. That's a shame - since I *really* wanted to use python here.
Another issue is that there was no support for easy replication/federation of data. I was developing a large reporting solution and planned to exploit the free licensing of postgresql to distribute queries across a dozen low-end servers. Not a problem - but I lacked an easy method of managing multiple copies of common user data. What I really wanted was something like a 'remote table' that would point to another database. Failing that - replication could meet this need. Without either, I was looking at a more complexity in the application as well as a home-made (though simple) replication solution.
Ultimately, these three issues forced us to stick with SQL Server - in spite of considerable progress in postgresql. The killer was the result set issue.
Note that I won't hesitate to use the product again - and will hopefully find a few of these short-comings resolved when I do.
> And even in current incarnation they still don't (always) get ANSI joins right.
Yep, it's great that they still have some of their legacy join syntax working - otherwise multiple outer joins would have to sometimes be done with temp tables and multiple stages!
> It is all very personal -- you either like it or you don't basically. It is like vi vs. EMACS of databases argument.
Nah, it's more like notepad vs vi or EMACS: with the vi/EMACs crowd trying to educate the notepad crowd regarding the value of undo, syntax-checking, etc. Or in this case support for ansi-sql, transactions, views, unions, subselects, etc.
> As a matter of fact, until Oracle's release of 10g, MS SQL beat all commercial offerings in the TPC benchmarks.
pardon?
which benchmarks? tpc-c, tpc-h, tpc-r, tpc-w? It actually has some strong entries in tcp-c (oltp) but that's about it. DB2 and Oracle are all over it most of the time - and not just oracle 10g, but 9i as well.
I do agree with you though - that sql server is about the easiest database to develop on. I still won't give it much credit here though - the fact that it *isn't as bad* as oracle, et al isn't much to celebrate.
And when it comes to supporting it in a production environment, that's when it really begins to stink.
Slashdot Mirror
Accumulating data in this manner is only trivial as long as the data is kept in-house by bud. However, once they sell that data to a broker it becomes just another data point used for profiling - and its value sky-rockets.
Before the industrial age profiling of the community members wasn't a mass-produced product, available for pennies. Soon your insurance company , prospective employer, etc will be able to pay a few pennies to determine your "long term health costs" - based on *exactly* this kind of data.
Before the industrial age you weren't likely to encounter some greasy peddler who would walk up to you and say, "Hi Jim, I've noticed that your contraceptive purchases have stopped, and you're now getting that Parent magazine, and well - since this will be Beth's first child, and she's 39 - I'd like to let you know that I can hook you up with some really good tests for birth defects...". It won't be long.
The only thing rediculous here is that in the info-age so few of the info-workers really understand the how the economics of information work, or have even the vaguest of ideas of what the social impacts of free information are.
This isn't static data functionality - it's standard data warehousing functionality.
And none of the open source databases have yet caught up with the features that the commercial databases have been adding over the past seven years in support of this.
Still, I've used postgresql for some reporting, and it isn't the end of the world to drop back to older technology in warehousing. But the lack of materialized views isn't the real issue - it's the lack of any form of partitioning.
> While it took $45 million to achieve the crime rates Chicago has today, as an open source programmer,
> I have to think that they wasted about $42 million.
Sure - if the 'bunch of programmers' work for free. Odds are - that over $30 million of that total went to labor. Now - that is a pathetic and inflated figure - but most government projects are hideously bureacratic. And Oracle's hardly a lean and efficient contractor to work with ($300/hr isn't unusual for them).
Would open source have made a difference? Yeah, probably would have pushed the costs up even more. Oracle has far better parallelism, load, and partition management features than either mysql or postgresql.
So, open source has a lot of wins under its belt, but that doesn't mean it can out-compete closed-source on all fronts.
Is some of the NPR resistence due to fear that they'll get drowned out by far-right small radio stations?
Could this be done?
ok, so postgresql has two issues with syntax, and mysql has dozens of situations in which it will silently fail.
r /manual_Introduction.html#Bugs
And that's comparable?
If you want to read something amazing, go ahead and read the MySQL manuals, look for design deficiencies: http://www.mysql.com/documentation/mysql/bychapte
You'll see more than just minor syntax issues - you'll see that mysql spends most of its time in silent error mode.
Since analog is the original form, and has the most sophsticated interface...it kind of follows that the digital watch is really just a technical triviality, doesn't it?
oh yeah?
How about working inside an 8x20 trailer in during the summer with a 450-lb ex-wrestler with almost zero sphincter control?
The guy farted so often it just became background noise, and there's no way the ventilation allowed by the trailer door could keep up with his CFM. When contronted - he's say "hey, in all those years of wrestling I tore a lot of muscles - it isn't my fault".
Another problem in the trailer is that it was so small he'd have to squeeze by me to get past my desk and out the door. Invariably this lead to another outburst. At least when that happened I knew it was coming and could lean a little out of the way.
Just as I was starting to look for another job I got lucky - the place hired other new guy, and so I only spent two weeks in that trailer.
As long as someone in the world is:
- getting shot at
- selling sex
- selling organs
- having to shoot others
- having to torture others
- risking life & limb in otherwise dangerous situations...
nobody gets to complain. So, just suck it up with your crummy desk jobs.
And no complaining about those monster executive salaries & perks either - they've also got hard jobs and deserve every penny.
The Oracle apps really stink, a few data points:
1. at one system integrator that I worked at we used their apps to manage our finances and also resold their database. Anyhow their financial app (which we all had to use to record our time) became our #1 example of how *not* to design a UI.
2. they used java to make it platform independent, but then require IE - which requires windows. Is this brain-dead or what? They might as well have developed the apps in ASP!
3. in almost any business activity where there's real competition - they stink. They were pushing a CRM package that was a laughable pile of poo. But that didn't stop their hard-charging salesdrones from hammering people with it.
Only after using their clunky applications (where they try to rewrite all the rules on UI design) can you really appreciate the mess that is the oracle database. Sure, the oracle database is more mature, more stable, and better in class than oracle financials. But - it is still hopelessly complex, error-prone, and bloated.
> There are more languages than python and C and C++ (two different languages).
;-)
Sure, though I think that they express the extremes pretty well.
My shop is primarily java, but I introduced python since it's much faster/easier to develop and maintain, and we have few real java experts. Python was an exception, but I've got an ace in the hole if I get challenged on standards-compliance: I can run it in a JVM with a seamless bean/class interface (jython). I'm not eager to push yet another exception on the standards guys. At least not yet.
But I will take a look at O'Caml anyway
Thanks
> I know of projects using interpreted languages that have failed - the application did work - albeit, it
> took 30 seconds (best time) to 5 minutes to respond to a single keystroke. It was later abandoned because
> despite buying faster hardware, the programmers couldn't get better than a 20 second response time.
> An average improvement of just 15 milliseconds per module would have saved the project!
Or how about - some one could have made the design decision to avoid going through 1000 layers of abstraction? I mean, you do begin to hit diminishing returns at some point, eh?
Right now I'm using python to perform heavy transformations on firewall data - tens of millions of rows per day. Looks like this process will handle around 300 million rows per day on a single robust server.
1. Would it be faster in C or C++? Sure
2. Would the change of a defect increase in C or C+ vs python: Sure, far more chance to screw something up
3. Is it worth rewriting in order to get 1200 million firewall events / day instead of 300 on a single server? Not now - I'd rather keep it in python and just split the processing across two servers than implement in C or C++. Of course, we could always go to psycho, or rewrite small parts first. But eventually, if those steps failed to keep the load manageable, then *maybe* i'd consider rewriting in C/C++.
Of course, keep in mind that this is python handling 1.2 *billion* heavy transformations a day by the time I'm starting to think about a real performance problem. So, can it handle performance-intensive projects - sure. Is it the fastest solution out there? not by a longshot. Are there things that matter more than speed. Yeah, a ton.
Keep in mind that the most powerful factor in performance-tuning has nothing to do with design - but rather with requirements. I've done a lot of performance-tuning in which I completely eliminated elegant and efficient designs - that simply weren't needed. They were solving a problem that didn't exist. One advantage of a higher-level language like python is that you spend more time at the requirements level - and can more easily detect this problem.
> As for other things you can't say, here are some that I'm going to say...
> *IS NOT NORMAL*
> *your weird way*
> *crackpot parents*
> *offensive to me*
ah but there's a world of a difference between a crackpot yelling at the world and thoughtful discussion of serious topics. All it takes is a few cranks arguing this way and everyone that follows looses their credibility!
> In a way, it's pathetic that this is the only way to do it; in another direction, though, it's a very smart thing to move any activity
> that doesn't need to happen on the primary (bottleneck) server to a secondary server instead.
Sure - but in the case of backups - how do you perform a restore based off a replicated database - and then reapply all the missing transactions? I don't think it can be done. Which means that these are the equivilent of offline backups, not online as far as recovery goes (though online as far as availability goes I suppose).
> A similar practice is commonplace in the MS SQL Server world, running lengthly reporting queries on a secondary reporting database
> instead of on the primary.
Yep, this isn't too expensive - aside from the licenses and hardware. I'd typically recommend folks to use light ETL and convert the data in transit. That'll give you better performance and accuracy than you can get out of reporting on an oltp model.
> Again you missed the point - which was that the zealots were trumpeting PostgreSQL as the perfect solution
> for hair loss even back when these limitations were in place.
Ah maybe so. Well, in the interest of helping to set the record straight, here's my take on that:
- 8k wasn't much of a limit until we started storing multi-media in databases. Prior to that it was rare to hit an 8k limit. Postgresql was a little late with their support here.
- mandatory maintenance downtime was common even in commercial databases until about a year ago I think, at least for reorgs. This wasn't too much of a problem really until the internet made it possible to do work around the work 7x24. Even today you're still looking at downtime - for backups with mysql and probably most configuration changes, upgrades, expansions, and tunings of either database.
So yeah - postgresql fell short of mysql in content management solutions two years ago, just as mysql fell short of postgresql in non-content management solutions. Today neither of the above are true. Postgresql has pushed back these limits and has improved performance. Mysql has made significant strides in improving its dbms functionality, but still has large gaps. It'll hopefully resolve these gap soon - but until then its primary value proposition is that it has better windows support, isp support, and documentation than postgresql. Over the long-term this strategy may pay off for mysql - build market share with a unique content management position, put out a lot of fud to deflect database criticisms, then bust its butt to get essential functionality in place before users begin to migrate other solutions.
But until this functionality in place mysql just isn't competitive in most database applications besides content management.
> Sorry, but Yes, it IS a real database. And no, I truly don't appreciate the dismissive, elitist attitudes of people like you.
Dude, don't take criticism of the mysql product so personally. Relational databases have been around a *long* time, and the basic functionality is *well* understood. If mysql ab hadn't taken the equivilent position to declaring OO unnecessary and delivering a new implementation of COBOL '68 - then they wouldn't be the recipient of so much *legitimate* criticism.
And don't act like we're calling your baby ugly. I viited your website - it looks fine, feels crisp, and appears to have reasonable functionality. Great. If you want to use mysql - and deal with silent errors, non-standard sql, sql limitations, etc - fine. Go for it. Whatever - you can still work around these problems and get a good product in the end. But please don't demonize everyone that levels a valid complaint about the mysql non-relational philosophy.
> You conveniently ignored the 8k row limits and the VACUUM blocking yet again, because it doesn't fit with your cozy world view. Oh well.
Yes - because these limitations are *gone*.
> I get so tired of reading all these "MySQL sucks" comments from the PostgreSQL crowd every time there is any mention of the two databases.
> Well, for whatever reason, this time I feel compelled to comment in return.
Point taken: postgresql used to have some performance and availability limitations, mysql still has functional and usability limitations.
> MySQL *does* have real transactions.
which are implemented using a third-party product, are limited and aren't seamlessly integrated, and which can fail silently due to syntax violations!
> I have never missed subqueries
yep, I've occasionally developed trivial databases without them. But I've seldom developed a serious database without them.
> There is a definite sense of intellectual snobbery and elitism in many of the comments from PostgreSQL fans slamming MySQL.
Nah, more likely it's the disgust that the more experienced developers feel with an obvious charletan that claims that well-proven techniques aren't necessary. Kinda like if ten years ago Yuga had insisted that air-bags, seat belts, and bumpers weren't needed "by most people in most situations". Yeah, right.
Keep in mind that without referential integrity, transactions, and some other missing features - the product isn't a relational database. It's just an isam file with a non-standard sql interface.
btw, you forgot:
- triggers
- stored procedures
- views
- unions
- online backups
- etc
And don't forget that each one of the basic core functional parts of a relational database (like referential integrity) are added gradually - with initial limitations that are incrementally removed.
That's a fine way to improve the product. But it was disingenuous and irresponsible of mysql ab to defend its weak functionality by claiming that transactions aren't necessary.
> But WHY???? There are free alternatives out there that don't have key missing features, and don't force you to write these work arounds.
nostalgia? I mean, there are some folks who really miss that 70s pre-RDBMS coding in IMS, ISAM, etc....
> So far, i'm not doing anything out of the ordinary. nothing too complicated database wise.
> What exactly would be the advantage of using Postgres.
How's this - you'll be using a mature database that has a good implementation of almost all standard database functionality - vs one that has only implemented some of it, and has repeatedly claimed that this standard functionality wasn't necessary.
So, now MySQL is quickly trying to check off all these deficiencies, which include:
- transactions
- unions
- subselects
- triggers
- views
- stored procedures
- stored procedures
- parallelism
- online backups
- replication
- clustering
- etc
Some of these have been partially implemented in the 4.1 release, some are now being addressed in this new alpha. Most exist today in postgresql - and are now being incrementally improved.
Who knows? Perhaps they'll do a competant job - on the other hand functionality like 'subselects' isn't something you simply 'check off'. You want a carefully-designed implementation that covers much of the functionality implied here - not just a minimal job.
Not to say that mysql can't do a fabulous job at improving their product in record time. However, I see no reason to give the same company that insisted this functionality wasn't necessary the benefit of the doubt here. Especially when their current stable product has so many unacceptable silent failure modes.
> Using replication you can easily do backups without taking down your live server.
> Now, if folk want to start criticising MySQL without complaining about these features being absent, that'd be great.
You bet - so when are they going to support a free online backup solution that isn't a complete hack?
I mean really - backing up your replication server? That's a pathetic solution...
> The difference between 1981 and 2003 (for big projects) is that the database is usually touched only in an
.NET application to it. Now you're screwed.
> object create, store, retrieve and delete methods and in rare cases one or two more application specific methods.
It's really not that much different today than it was 20 years ago: we knew then as well as today to isolate the database interface. It was more difficult then (they were often just paragraphs in COBOL)- because the languages weren't generally as sophisticated as they are today. The real problem occured over time - over 5-10 years of modification with new technologies - the isolation would typically break down. Today the same is true - you build your app layer in Java - and then someone wants to connect their
Another reason to avoid trying to perform this code in the application layer - is that in the db layer it is declarative - easily created, easily enforced, 100% guaranteed to work, and easily tested. Do it in the app layer and you will frequently screw it up. Sure - maybe *you* are good enough not to, but 95% of the folks out there aren't.
Another reason to avoid driving the database design from OO - is that OO has only caught up with transactions in about the last ten years. It still hasn't caught up with decision support. So, the developers can bloody well encapsulate the hell out of their data, keep it in an object store, etc. Then when management starts to demand robust analysis of the data and the developers discover only at this time
* that much of their private data - really isn't
* that their data is nearly impossible to compare from one part of the application to another (because they thought it was data - when it was supposed to be information)
* that they have invalid data - because of defects in their referential integrity rules
* that they have inconsistent data - because their referential integrity rules changed over time - and they just handled in in their API
It's about that time that management will replace the designers and architects of that solution with new ones - that understand more than just 50% of the problem.
> I just wanted to point out that, as of 7.3, PostgreSQL fully supports result sets returned from functions/stored procedures.
I had heard that this was coming - but while using 7.1 the documentation and some of the offered implementations were so poor that I couldn't count on it. The shared-cursor (I forget its exact name) was an especially awful-sounding substitute for what I was looking for.
> Hopefully replication has improved by now. I know there are a lot of people using it,
> but I don't know enough to comment.
Yeah, I'm seeing progress here. It's not really at the top of my list though - aside from an occasional need I view it more as a liability in a production system.
> As far as python is concerned, wouldn't you normally write the functions in a seperate editor
> and then just pass it through an escaping filter?
Not sure what you mean by an escaping filter - I'm assuming that I can access code with any vanilla editor - without having to rely on intelligent parsing. Not sure how that could work anyway - since I've got python variables mixed with sql code...
> I recognize that some of the bigger DBs have more features
Right - and as long as the core functionality is solid, I don't mind if some of the fringe stuff is missing. But postgresql is there. It's enough there to be seriously considered for quite a few production tasks.
Sounds like a perfect mysql strategy - you don't need all the extra 'fluff' in the database - because you don't mind getting garbage into your database. Cleaning it up later while the database is idle will also generally be faster.
Makes perfect sense - if you don't mind invalid data. Also note that performance problems with referential integrity is generally a sign of bad design - you should have (in general) neither locking nor performance issues with this built-in protection.
Also note: this sounds a lot like arguments I remember from the early 80s - in which the old spahgetti programmers using flat files were quite upset that we would dare to create data stores with built-in protection against invalid data.
Of course, the reason that we did this is that they continuously tossed garbage into the database. Most of them considered themselves 'designers' - but failed to understand the economic value of the data - and that given enough time they were certain to screw it up. Instead they focused on performance.
So here we are with mysql - catching up to 1981...
> What functionality left out of postgresql do you find most important?
On a recent conversion (14 months ago) of a large MS SQL Server database to Postgresql the single biggest issue was the inability to return a result set from a stored procedure.
Another issue is that stored procedure support for most languages isn't complete. For example, since python is run outside of the database there are strange, bizarre, and ultimately unacceptable quoting and escaping that needs to be done. That's a shame - since I *really* wanted to use python here.
Another issue is that there was no support for easy replication/federation of data. I was developing a large reporting solution and planned to exploit the free licensing of postgresql to distribute queries across a dozen low-end servers. Not a problem - but I lacked an easy method of managing multiple copies of common user data. What I really wanted was something like a 'remote table' that would point to another database. Failing that - replication could meet this need. Without either, I was looking at a more complexity in the application as well as a home-made (though simple) replication solution.
Ultimately, these three issues forced us to stick with SQL Server - in spite of considerable progress in postgresql. The killer was the result set issue.
Note that I won't hesitate to use the product again - and will hopefully find a few of these short-comings resolved when I do.
> And even in current incarnation they still don't (always) get ANSI joins right.
Yep, it's great that they still have some of their legacy join syntax working - otherwise multiple outer joins would have to sometimes be done with temp tables and multiple stages!
> It is all very personal -- you either like it or you don't basically. It is like vi vs. EMACS of databases argument.
Nah, it's more like notepad vs vi or EMACS: with the vi/EMACs crowd trying to educate the notepad crowd regarding the value of undo, syntax-checking, etc. Or in this case support for ansi-sql, transactions, views, unions, subselects, etc.
> As a matter of fact, until Oracle's release of 10g, MS SQL beat all commercial offerings in the TPC benchmarks.
pardon?
which benchmarks? tpc-c, tpc-h, tpc-r, tpc-w? It actually has some strong entries in tcp-c (oltp) but that's about it. DB2 and Oracle are all over it most of the time - and not just oracle 10g, but 9i as well.
I do agree with you though - that sql server is about the easiest database to develop on. I still won't give it much credit here though - the fact that it *isn't as bad* as oracle, et al isn't much to celebrate.
And when it comes to supporting it in a production environment, that's when it really begins to stink.