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.
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.
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
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.
by Anonymous Coward
on Wednesday July 01, @02:16PM (#28547435)
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.
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.
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,
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
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.
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
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.
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.
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.
You stopped using a DB system because a app related to but not developed by PostgreSQL didn't do something you wanted it to? I stopped using Oracle because I didn't like the colour scheme in Toads tools. No seriously, I say that with no hint of sarcasm.
For a serious project, I would expect someone to use scripts anyway.
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.
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.
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.
Can I do a cross database join yet? To date this has still been a feature that has yet to be implemented that I can do in just about every other RDBMS.
That's not quite fair. mysql doesn't really have them either. mysql cross database joins are simply working around there lack of support for schemas. Schemas are better in virtually every way...
if you need x database joins in postgres, you can always use dblink of course.
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.
I don't know why that got modded troll because it's essentially true. A PostgreSQL database is subdivided into schemas, each schema being much like an entire MySQL database. If you have a database named "mycompany" with schemas "financial" and "hr", you could have something like
select employee.name, budgetitems.lineitemamount from hr.employee, financial.budgetitems where employee.paylevelid = budgetitems.lineitemid
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.
We run into cross database links all the time, from a data consistency perspective it's much better to have one system be the system of record and all others pull from that master server. We even do cross platform links (SQL Server to Oracle) with no problems. Of course db-link and dbi-link seem to fulfill that need just fine. We also have a function where we export a CSV file and an outside vendor uses similar functionality to pull the data in by treating the file as a source table.
Deadlocked Query Display No more log detective work to track down which operations deadlocked; the information is right there.
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.
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.
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.
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:)
I've been fighting for 3 years now to get them to move to 2005.. Basically the DBA doesn't want to learn how to use the new admin tools, and he makes up excuses for why 2000 is "better". I point out the flaws in that logic, but since the only audience is corporate execs and salesmen, they prefer a simple answer (from the DBA) to a correct answer from me.
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
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.
Parent
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: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)
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]
Parent
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?
Parent
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: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)
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: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.
Parent
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.
Parent
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)
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)
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.
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)
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
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.
Parent
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)
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.
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.
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)
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)
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