But postgresql supports user written functions in C, as well as supporting Tcl, Perl, and Python as procedural languages.
That's convenient but a) it's probably not very efficient because the script interpreter has to be loaded up everytime and b) if the interpreter crashes, your datas are left in a very inconsistent state.
And while business rules may belong to the middle tier, data integrity (not null, primary keys, unique, references, constraint check in, etc... all belong to the database.
If you use Innodb it has a double-write system that takes care of graceful recovery in case of crashes. MyISAM tables don't have any specific protection but they seem to never ever get corrupted, safe for index which might need to be repaired in case of crash. Either way Mysql is pretty solid and has never lost datas on me in years, and seldom crashes (I have uptime for the database between 1 to 3 months, which is not too bad IMHO)
I don't need hotbackup - I already have a live backup database using replication (something that is not yet available in Postgresql unless you want to mess with quirky unofficial patches). For archival needs, I can just stop the replication slave, make a dump then restart the replication slave the the livebackup catch up with the master database.
Well subselects are coming in MySQL anyway, you can already use them for UPDATE in 4.0 and in SELECT in 4.1. I've also a pretty big ecommerce application on MySQL and after weighting the idea of rewritting everything for PostgreSQL I realised that by the time the rewrite is finish most of the features I need will already be in MySQL (transactions and foreign key are already available in the 3.23 stable branch for example and they work just as expected).
um, how are they not portable? its pretty easy to copy and paste them, assuming that your DB came with a GUI
Well as pointed out by another poster, every vendor use a different language for stored proc.
also, they are (usually) compiled (as opposed to interpreted) and can potentially take a considerable processing load - especially if there's complex logic - off of the web server.
That's true but only assuming you want to move load from the web server to the database. It's way preferable to have the Web server do the hard work than the database. A cluster of Apache server and a load balancer is cheap compared to an Oracle or DB2 cluster (or some big iron with 32 CPU)
I agree on about everything but point 11: Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above
I'm following pretty closely both releases and frankly I've the exact opposite feeling. While MySQL adds features one by one, Postgresql seems to absorb lots of stuff at each releases. While this is great feature-wise, it can lead to pretty nasty bugs and instabilities.
Just look a the latest release for example (from postgresql history) :
"it fixes a critical bug in v7.2: sequence counters will go backwards after a crash "
That bug is really critical (can you imagine the state of your datas when counters go backward ?), but it took 2 months to be discovered and fixed. On the other hand the MySQL history list only very minor bug fixes, most of them that you are highly unlikely to meet and that are of little effects. Actually the MySQL developpers make the effort of documenting every petty bug fix, which is a very professional thing.
I was the lead developer on a e-commerce site. The guy that started the company in his basement picked MySQL because the benchmarks beat the shit out all the other DBs. Now we're stuck with it, due to legacy.
That was more likely because at the time, MySQL was the only proper open source database around and that had good interfaces in web languages. Postgresql was still unstable, slow and full of dumb limitation (32 KB row sizes anyone ?). Interbase was closed source.
Nowaday we got plenty of excellent open source databases to choose from (including the modern Postgresql) and you can make different choices than MySQL, but this is a fairly new situation.
MySQL works great, as long as you're prepared to make a bunch of bastard hacks to get around the limitations.
Most of MySQL limitations are not limitation on what you can do but on how easy you can do it. Stored proc, nested selects and views are all nice to use but you can write clean code without using them (it's just more lines of codes). There's no "hack" to do here.
If you're going three-tier, then you do it in objects (et all) that sit between database and clients, anyway.
That's exactly my point. And that means that you don't write it with stored procedure (especially since PL/SQL is an horrible language to use for this task)
If you are not hellbet on "free as in speach" database, I heard Sybase is offering for free (as in "beer") it's database for Linux. It's not the latest verision (that one has to be paid for) but it's already a very mature product and it's free for developpers AND commercial use as well. Or you can have a look at Firebird (Interbase), it's pretty mature.
Or triggers. Or sub-selects. Or row locking. Or check constraints. Or views. Or inherited tables. Or performance that doesn't disappear as soon as you have a moderately complex database structure.
You got part of it right. Row level locking (and not page level locking like most other databases) are in MySQL as well as foreign key constraint (and their associated "on cascade" triggers). And as far as moderately complex databases go I don't know what you mean by that but I've seen systems with several dozens of tables combined with gigabyte sized tables work flowlessly and quickly. It sure isn't a DB2 or Oracle cluster, but it's already overkill for 99% of peoples who need a database. As for the open-source competition, Postgresql & interbase also have their own quirks and limitations.
Stored proc can be useful but you have to remember that: a) They are not portable at all. Moving to another database means rewriting all of them. b) They tend mix business logic and data which is a big no-no as far as three-tier architecture goes
For these two reason I think everyone should restrict stored procedure to trivial repetitive things and keep the complex logic in a separate layer (outside the database that is)
The shinkensen (sp?) is not the fastest. The fastest commercial trainn is still the French TGV, while the japanese have the fastest prototype (a maglev train)
How come I just installed XFree 4.2 and still have to go thru the same stupid configuration and was never offered to use the monitors plug'n play option ? (or is it hidden somewhere in a command line option, in which case it defeats the whole "plug'n play" thing IMHO)
Many times the X config utilities where enable to configure my screens correctly. Both the textline version and the GUI one.
On the other hand windows knows how to handle plug'n play monitors who return their supported frequencies thru the VGA connector (they all do it now). Why can't Linux/X detect a plug'n play monitor ? The specs and monitors have been out there for half a decade now.
Simple example why : I've actually a Inspiron 8000. I only took a laptop because I need to be able to cary my PC from work to home and back. When I get to my work or home, I have a keyboard and mouse that I plug. I don't use it in the plane or train, and like the majority of laptop users, it is almost always plugged in the electric outlet.
So I need something as powerful as possible, with the largest screen as possible, yet that I can carry from home to work easely. That Sony looks mighty good to me:) I don't care if it's a bit heavier than your normal laptop, since I don't need to move it much anyway. If there was a 17" screen laptop, I'd go for it as well:)
MySQL 3.23 supports procedure since the old days it integrated BDB table handlers. Most people would use InnoDB for that nowadays as it seems it's a bit better supported.
The French TGV has been going at 515 kph several years ago (albeit it was not pulling regular cars). And that was with a regular train (no funny shit with the wheels, just a long straight railway)
It gets very ambarassing when your SO best friends get to know everything about your romantic and sex life. Everything you do/tell your girlfriends has to be considered public.
Because the charge on the CC can be done at shipping time and not ordering time (customers like it better this way), because you need to cancel a charge sometimes, or because a customer ask you to "charge the same card as the last time". All perfectly good reasons to keep the numbers.
What should be done : encrypt the CC number on the server, and decrypt it only on the employee personnal system using a custom-made software. This avoid leaving the private key on the server.
Beside CC have a "built in" security feature : they all expire sometime:)
Slashdot Mirror
But postgresql supports user written functions in C, as well as supporting Tcl, Perl, and Python as procedural languages.
:)
That's convenient but a) it's probably not very efficient because the script interpreter has to be loaded up everytime and b) if the interpreter crashes, your datas are left in a very inconsistent state.
And while business rules may belong to the middle tier, data integrity (not null, primary keys, unique, references, constraint check in, etc... all belong to the database.
Agreed. And that's all supported in mysql btw
how does MySQL recover from crashes
If you use Innodb it has a double-write system that takes care of graceful recovery in case of crashes. MyISAM tables don't have any specific protection but they seem to never ever get corrupted, safe for index which might need to be repaired in case of crash. Either way Mysql is pretty solid and has never lost datas on me in years, and seldom crashes (I have uptime for the database between 1 to 3 months, which is not too bad IMHO)
I don't need hotbackup - I already have a live backup database using replication (something that is not yet available in Postgresql unless you want to mess with quirky unofficial patches). For archival needs, I can just stop the replication slave, make a dump then restart the replication slave the the livebackup catch up with the master database.
That's assuming you use the innodb tables which are not the default normally.
Judging by how things goes, Innodb seems to become the default recommanded table handlers by MySQL and will probably replace myisam in the long run.
Well subselects are coming in MySQL anyway, you can already use them for UPDATE in 4.0 and in SELECT in 4.1. I've also a pretty big ecommerce application on MySQL and after weighting the idea of rewritting everything for PostgreSQL I realised that by the time the rewrite is finish most of the features I need will already be in MySQL (transactions and foreign key are already available in the 3.23 stable branch for example and they work just as expected).
So if we use Slashdot as an example (and I have to admit it's the only big MySQL application I know much about),
That other site does run MySQL as well (with InnoDB I think)
um, how are they not portable? its pretty easy to copy and paste them, assuming that your DB came with a GUI
Well as pointed out by another poster, every vendor use a different language for stored proc.
also, they are (usually) compiled (as opposed to interpreted) and can potentially take a considerable processing load - especially if there's complex logic - off of the web server.
That's true but only assuming you want to move load from the web server to the database. It's way preferable to have the Web server do the hard work than the database. A cluster of Apache server and a load balancer is cheap compared to an Oracle or DB2 cluster (or some big iron with 32 CPU)
I agree on about everything but point 11 :
Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above
I'm following pretty closely both releases and frankly I've the exact opposite feeling. While MySQL adds features one by one, Postgresql seems to absorb lots of stuff at each releases. While this is great feature-wise, it can lead to pretty nasty bugs and instabilities.
Just look a the latest release for example (from postgresql history) :
"it fixes a critical bug in v7.2: sequence counters will go backwards after a crash "
That bug is really critical (can you imagine the state of your datas when counters go backward ?), but it took 2 months to be discovered and fixed. On the other hand the MySQL history list only very minor bug fixes, most of them that you are highly unlikely to meet and that are of little effects. Actually the MySQL developpers make the effort of documenting every petty bug fix, which is a very professional thing.
I was the lead developer on a e-commerce site. The guy that started the company in his basement picked MySQL because the benchmarks beat the shit out all the other DBs. Now we're stuck with it, due to legacy.
That was more likely because at the time, MySQL was the only proper open source database around and that had good interfaces in web languages. Postgresql was still unstable, slow and full of dumb limitation (32 KB row sizes anyone ?). Interbase was closed source.
Nowaday we got plenty of excellent open source databases to choose from (including the modern Postgresql) and you can make different choices than MySQL, but this is a fairly new situation.
MySQL works great, as long as you're prepared to make a bunch of bastard hacks to get around the limitations.
Most of MySQL limitations are not limitation on what you can do but on how easy you can do it. Stored proc, nested selects and views are all nice to use but you can write clean code without using them (it's just more lines of codes). There's no "hack" to do here.
If you're going three-tier, then you do it in objects (et all) that sit between database and clients, anyway.
That's exactly my point. And that means that you don't write it with stored procedure (especially since PL/SQL is an horrible language to use for this task)
If you are not hellbet on "free as in speach" database, I heard Sybase is offering for free (as in "beer") it's database for Linux. It's not the latest verision (that one has to be paid for) but it's already a very mature product and it's free for developpers AND commercial use as well. Or you can have a look at Firebird (Interbase), it's pretty mature.
Or triggers. Or sub-selects. Or row locking. Or check constraints. Or views. Or inherited tables. Or performance that doesn't disappear as soon as you have a moderately complex database structure.
You got part of it right. Row level locking (and not page level locking like most other databases) are in MySQL as well as foreign key constraint (and their associated "on cascade" triggers). And as far as moderately complex databases go I don't know what you mean by that but I've seen systems with several dozens of tables combined with gigabyte sized tables work flowlessly and quickly. It sure isn't a DB2 or Oracle cluster, but it's already overkill for 99% of peoples who need a database. As for the open-source competition, Postgresql & interbase also have their own quirks and limitations.
Stored proc can be useful but you have to remember that :
a) They are not portable at all. Moving to another database means rewriting all of them.
b) They tend mix business logic and data which is a big no-no as far as three-tier architecture goes
For these two reason I think everyone should restrict stored procedure to trivial repetitive things and keep the complex logic in a separate layer (outside the database that is)
The shinkensen (sp?) is not the fastest. The fastest commercial trainn is still the French TGV, while the japanese have the fastest prototype (a maglev train)
How come I just installed XFree 4.2 and still have to go thru the same stupid configuration and was never offered to use the monitors plug'n play option ? (or is it hidden somewhere in a command line option, in which case it defeats the whole "plug'n play" thing IMHO)
Many times the X config utilities where enable to configure my screens correctly. Both the textline version and the GUI one.
On the other hand windows knows how to handle plug'n play monitors who return their supported frequencies thru the VGA connector (they all do it now). Why can't Linux/X detect a plug'n play monitor ? The specs and monitors have been out there for half a decade now.
Simple example why : I've actually a Inspiron 8000. I only took a laptop because I need to be able to cary my PC from work to home and back. When I get to my work or home, I have a keyboard and mouse that I plug. I don't use it in the plane or train, and like the majority of laptop users, it is almost always plugged in the electric outlet.
:) I don't care if it's a bit heavier than your normal laptop, since I don't need to move it much anyway. If there was a 17" screen laptop, I'd go for it as well :)
So I need something as powerful as possible, with the largest screen as possible, yet that I can carry from home to work easely. That Sony looks mighty good to me
MySQL 3.23 supports procedure since the old days it integrated BDB table handlers. Most people would use InnoDB for that nowadays as it seems it's a bit better supported.
I'm curious : where did you find stuff about PostgreSQL replication ? Never found it in the doc. Can you point some URL ?
The French TGV has been going at 515 kph several years ago (albeit it was not pulling regular cars). And that was with a regular train (no funny shit with the wheels, just a long straight railway)
It gets very ambarassing when your SO best friends get to know everything about your romantic and sex life. Everything you do/tell your girlfriends has to be considered public.
Because the charge on the CC can be done at shipping time and not ordering time (customers like it better this way), because you need to cancel a charge sometimes, or because a customer ask you to "charge the same card as the last time". All perfectly good reasons to keep the numbers.
:)
What should be done : encrypt the CC number on the server, and decrypt it only on the employee personnal system using a custom-made software. This avoid leaving the private key on the server.
Beside CC have a "built in" security feature : they all expire sometime
Can you imagine a beowulf cluster of these ! (wait ! this time it really makes sense :)
XP Home is exactly the same minus the biprocessor support (often not needed) and a few graphical gizmos. And it is about half the price...
That sounds pretty shaky as far as consummer protection laws are concerned (but then I live in Europe...)