Comparing MySQL and PostgreSQL 2 902
Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today.
Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?
Helllooo?? Editors?? (Score:4, Informative)
This sort of whoring-for-comments article should be a poll.
My point of view (Score:2, Informative)
Postgres 7.5
Pros:
- Supports stored procedures
- Supports triggers
- Supports schemas
Cons:
- Heavy on resources
MySQL 4.0
Pros:
- Fast
- Easier to find PHP scripts that use it
Cons:
- Bad relational support (and yes, I know about InnoDB, but even then, it's a bit
- No stored procedures/triggers
- Easily corrupted by crashes.
Re:Another question (Score:2, Informative)
Re:Been using MySQL, but... (Score:3, Informative)
Found one! :) (Score:3, Informative)
It was updated March '05.
Spatial databases (Score:5, Informative)
Re:popularity (Score:5, Informative)
PostgreSQL has this nice Object-Relational model where tables can be derived from each other, but there are some nasty bugs that mean I think those features are still best avoided.
Overall, though, I think PostgreSQL is by far the better RDBMS.
Re:My point of view (Score:4, Informative)
Mysql 4.1 is the current stable version, and 5.0 is nearing release.
4,1 has excellent relational support, it is damn near impossible to corrupt if db design is correct, and innodb is great. IIRC
5.0 has views, triggers, stored procedures etc, and it's still amazingly fast.
Note: I base my mysql 4.1 comments on the linux version. I have heard that there is a version of mysql for windoze, but I can't vouch for it, and for all I know, it could be a disaster, but I don't really have anything definitive to say about it. Who knows, maybe original poster is talking about mysql on windoze (shrug)
I have a small problem... (Score:1, Informative)
Now I am stuck. I have to either a) switch to postgres, which does not seem too bad an option, or b) pay for MySQL (and prices are not even listed online *shudder*). What is the learning curve for postgres? Are there any good books? What about tools like SQLyog?
GPL - Got punk'd lately?
MySQL vs. Oracle (Score:2, Informative)
I recently moved my deployments from Oracle to MySQL because:
1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.
2. MySQL's new administration tools are significantly better than Oracle's out of the box tools (This is why a year ago I refused to use MySQL for production, and now I've switched everything).
3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).
5. MySQL's supposed gotchas pale in to comparison to Oracle's. When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)
I think that anybody deploying Oracle for non-Oracle applications is going to have to very seriously consider MySQL if for no other reason than all the DBA salaries you can get rid of.
If you want to buld a $1M cluser, stick with Oracle (for now). If you want to run application specifically designed by (or for) Oracle, stick with Oracle. Otherwise, switch at the first opportunity.
Don't forget Interbase/Firebird (Score:3, Informative)
Re:My point of view (Score:5, Informative)
There is no postgresql release 7.5. The last 7.x release was 7.4 , the current stable is 8.0 with 8.1 in beta.
Re:Another question (Score:5, Informative)
Re:I used to like MySQL (Score:3, Informative)
+----------+
| count(*) |
+----------+
| 93 |
+----------+
1 row in set (0.12 sec)
mysql> create table strings (stuff varchar(8));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into strings values ('abcdefghijk');
ERROR 1406 (22001): Data too long for column 'stuff' at row 1
mysql>
Heavy (Score:5, Informative)
1. I've never encountered corrupted data with mysql (It seems to be urban legend), and I have worked on tables with billions rows for two years.
2. PostgreSQL has more features and/or is more complete (simple example can be auto_increment vs. sequences)
3. PostgreSQL is heavier, and I hate statistics collector subprocess via udp (which seems to be eating 1-2% cpu all the time)*
4. mysql isn't much (if any) faster.
* - it's unlikely but possible my configs are to blame.
Re:Found one! :) (Score:3, Informative)
If they're going to compare releases, compare apples to apples. Either pick stable releases or development releases. And FWIW, nobody that I know personally has been able to get MySQL 5.0 to run reliably, if they can even get it to launch. If we use MySQL for a project, it's 4.1.
Re:I used to like MySQL (Score:3, Informative)
I'm certainly no power user but Postgres strikes me as an extremely well featured DB. I use MSDE / MS SQL server at work for an app with transactions, stored procedures, triggers and views and expect it would be straightforward (not trivial but straightforward) to port it to Postgres.
Sadly though, there's no incentive since MSDE is "free". Basically MSDE is a cut down SQL server which is no bad thing. But I'd love to see Postgres bundled in a similar form on XP - just the engine but nothing else. I reckon lots of apps need a database engine and Postgres fits the bill quite nicely. It would even be a smaller redistributable than MSDE too.
Haw haw (Score:5, Informative)
So, if I try to insert, say, a string of 10 chars into a varchar(9) field, what will it do? Will the magic version 5 reject it, as ever real database does, or will it truncate it silently, just as Toy databases (ala MySql 4.x) are wont to do?
What about the whole not-null thing? You know, if a field is set to NOT NULL and you don't populate it when you insert a row, a real database will reject it, where as a Toy database will accept it (MySql 4.x again!) and populate it with ... some other value.
Sco Partners With MySQL AB (Score:5, Informative)
"As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs"
http://www.mysql.com/news-and-events/news/article
burnin
Re:I used to like MySQL (Score:4, Informative)
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)
mysql>
Re:Mysql is very isp friendly (Score:2, Informative)
Forget Interbase/Firebird (Score:3, Informative)
To each his own, of course, and the situation may indeed have improved. I don't see any clear advantage that it has over PostgreSQL, though, and I doubt it'll ever gain much momentum.
One thing to consider - collations and Unicode sup (Score:5, Informative)
There's a discussion about including support for IBM ICU, but as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.
MySQL is much better in this regard.
The scoreboard (Score:5, Informative)
Changes/corrections since that study was made:
PostgreSQL now natively supports BLOBs directly in tables (bytea type) as opposed to using oid references.
PostgreSQL has always had "better than row level" locking, Multi-Version Concurrency Control.
PostgreSQL has added Java and Ruby to its list of stored procedure languages.
----------------
Now, here's the caveat. MySQL 5.0 is still marked as a "development release (use this for previewing and testing new features)" so I didn't include it in the above. If we include MySQL 5.0, we must also include PostgreSQL 8.1, currently in beta.
MySQL 5.0 adds views, stored procedures, triggers, cursors, the bit data type, up to 65K varchar fields, two new storage engines (federated and archive), and a strict mode.
PostgreSQL 8.1 adds two-phase commits, a role system, shared row level locks using SELECT, and many speed improvements.
The strict mode in MySQL is most exciting to me. I always bought the argument that MySQL could have fewer features in exchange for greater speed. But there is no excuse (in my opinon of course) to accept random strings into numeric fields and other such contrivances (MySQL gotchas [sql-info.de]). Data integrity in a database should not be an optional feature.
Re:PostgreSQL is supreme A LOT (Score:3, Informative)
Re:MySQL vs. Oracle (Score:5, Informative)
As of today, MySQL 4.1 [mysql.com] is the current release. 5.0, the current development snapshot, is the first to support stored procedures. Since the choice today is between a tested system and stored procedures, it most certainly does not "support all the Oracle procedures [I] need to build and operate an enterprice software system".
Next year? Maybe. Right now? No way, according to mysql.com.
Re:Another question (Score:2, Informative)
Please, Oracle has a ton of features that just aren't there in PostgreSQL. For features a mom&pop shop need, Postgres is usually fine. But for serious enterprise work PostgreSQL is not anywhere on the same playing field as Oracle.
I'm not dissing PostgreSQL. I like it, and I use it. I also use MS SQL server and when I have no other choice, MySQL.
The feature list difference between each of the databases is quite large. Simply saying "PostreSQL compares very well to Oracle" is NOT informative.
A small thread from the past (Score:3, Informative)
Re:MySQL vs. Oracle (Score:5, Informative)
HAHA. Right. Tablespaces? Failover? High availability? Row-level locking? Stored procedures? Triggers? Multimaster replication? SQL conformity? I could go on, and on...
Yes, Oracle's builtin tools suck. However, others are available. This is basically "I'm not an Oracle DBA, but MySQL was easy for me, so it's better than Oracle!"
Yes, and anyone who's using a db tuned out of the box isn't doing significant work.
Blah, blah, Oracle is hard. Get a DBA and a real developer. This is what they're paid for.
Oh, that's right, you want us to get rid of the people with a clue, because you have to pay them. Brilliant! So I guess we'll call you at 3am on Sunday morning when our servers crashed, we have to restore from rollback segments on our failover cluster... oh wait. MySQL can't do that.
If you're building a big expensive app, you might look and see if PgSQL can support you. If you're building a crappy little webapp, you might check out PgSQL, because it's fun and you'll get some experience with a real database.
Given PgSQL is free and not all that hard to manage, I can't think of a single reason for switching to MySQL.
Re:MySQL vs. Oracle (Score:5, Informative)
You can use MySQL for your enterprise apps, but it is not Oracle. MySQL, while boasting impressive database sizes, is not even close to competing with Oracle (or DB2 or Sybase) on the largest deployed database sizes.
The enterprise is not as price-sensitive as the SOHO market. Very few that buy an enterprise Oracle license use the out-of-the-box tools.
See my answer to number 2.
Only in environments that MySQL can handle. Oracle can handle scenarios where MySQL cannot run at all let alone run fast.
Agreed. Oracle definitely has its warts.
That said, migration to and from Oracle is easier with PostgreSQL or Firebird -- especially if you start on the lower end. MySQL has been so far from SQL standard compliance, you may not know when you're doing something really weird. MySQL 5.0's strict mode has helped tremendously with this. Too bad it's not ready for production yet.
Re:I have a small problem... (Score:3, Informative)
SCO issued a press release. (Score:5, Informative)
There isn't one from MySQL AB.
Actually there is a press release on MySQL's website:
SCO Partners With MySQL AB to Lower Costs and Increase the Power & Scalability of Modern Database Solutions [mysql.com]
FalconRe:Another question (Score:3, Informative)
There are good reasons to pay someone for support, if the people you're paying know their stuff. If you're building enterprise level, mission critical data warehouses, you'll want immediate access to expert help when things go horribly wrong. And Sorbannes/Oxley reinforces that need.
For those seeking paid support, there are several companies working to do interesting things with Pg:
There are some other outfits dedicated to Pg support, but I can't recall the particulars...
Meanwhile, MySQL still seems to be having difficulty getting stored procs and real views released...5.0 is starting to make Longhorn's development schedule look like a quarterly maintenance release.
It's also interesting that TFA didn't mention the rise of alternatives ranging from SQLite (which pretty much does everything that folks used MySQL for in the first place, but wo/ any license confusion), to Firebird, to the recently open'd Ingres.
Re:Mysql is very isp friendly (Score:4, Informative)
Gentoo: emerge postgresql
Fedora: rpm -Uvh postgresql-8.0.3.i386.rpm (or select the "Database" package during install)
Windows: setup.exe
Easy administration from Windows, OS X, Linux, and BSD with PgAdmin [pgadmin.org].
Or were you talking about a manual install? Sure that's harder, but most of us don't do manual installs. Just those crazy Slackware folks and their ilk.
Re:Popularity (Score:4, Informative)
MySQL is far away and completely alone in it's flavor of SQL and behavior. There are all kinds of strange things it does, like use the "`" to quote identifiers. Oracle, PostgreSQL, SQL Server, &c. all have their differences. But they all look a lot more like eachother than any of them look like MySQL.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.
Actually, the biggest differences are regarding types and constraints, in my opinion. For instance, MySQL thinks February 31st is a date, PostgreSQL does not. PostgreSQL enforces constraints, MySQL does not. MySQL NULL handling is non-standard.
Re:It really doesn't matter. (Score:5, Informative)
I disagree.
Putting everything (by everything I mean business logic) in the DB is the only sane way to keep your data consistent across multiple access methods. You simply can't thow data at a DB and then try to code and maintain consistent business logic in a half dozen client apps. You might be able to get away with a shared client access lib but even that can get messy.
Let's face it: Your data's in the DB. Why pull it all into the application to work on some small subset? Do all the queries and joins and clauses and increments in the database. The DB knows best where the data is and how you're going to be tinkering with it (so long as you give it sufficent hints), so it's the only sane method to access your data in a logical fashion. That's precisely why all these scripting languages and language interfaces exist.
I too use XML-RPC and SOAP (moreso the latter it seems, as XML-RPC is a little too light IMO) to access my data, but you can bet your sweet bippy I'm having the DB do as much as possible in order to transfer as little data as possible across my app-db link.
Re:What does postgres mean? (Score:4, Informative)
MySQL vs. PostgreSQL -- Real World (Score:5, Informative)
The more I learn about MySQL (from the perspective of someone who was initially gung-ho about PostgreSQL), the more I realised the shortcomings of MySQL weren't really shortcomings. They were misunderstandings. Yes, this can sometimes be as bad, when a default option is a stupid option (like table-level locking, as the parent and other PostgreSQL fans complain about).
Then I quit that job and went to work at Friendster, which is also a big MySQL shop. What I learned then was that when used properly, MySQL can scale to amazing proportions. Millions of transactions per hour (I won't be too specific being as I don't want to be sued into oblivion now that I'm an ex-Friendster employee).
Keep in mind that Friendster isn't alone. Google and Yahoo! use MySQL. For production loads. Big, big production loads.
What I didn't like about PostgreSQL was the weird licensing problems. Yes, bizarre as it may be, the BSD license they chose over GPL causes it to be bizarre. You can't get replication without downloading some weird third-party patch and recompiling (because the patch is GPL). Screw that. MySQL has it built in to the supported binaries you get from their site.
Without replication, your DBMS is useless. It's pretty clear from reading the parent post that Michalf doesn't really understand replication. If he did, he might think a moment about his statement that MySQ can't scale to more than 100 users at once. Friendster had millions (at once). Yahoo! has at last estimate nearly a hundred million users at once.
Last I checked PostgreSQL (admittedly, 6-9 months ago?) it just wasn't viable. Really replication was about the only thing holding it up, except I know another engineer who worked extensively with PostgreSQL internals (hacking it up to create a DBMS cluster, actually) and he said their I/O internals were bad/slow. Hopefully he's wrong, but I know before I deploy PostgreSQL I'm going to be carefully benchmarking it before doing so. Keeping in mind that I never deploy an RDBMS in a tiny little "more than 100 users" environment like the parent poster.
Sorry for the long-winded rant. It's just that I've been wishing/hoping/praying PostgreSQL would be the winning RDBMS in this battle for years, and every time I think it's going to be any good, it goes and shoots itself in the foot somehow, which makes me sad. Currently, I'm still a fulltime MySQL DBA.
Caveat: Much of what I've said here only applies in high volume RDBMS environments. If your environment is low volume, PostgreSQL may be a better choice.
Re:Another question (Score:3, Informative)
I loved the CHAR type section, specifically the MySQL entry:Beautiful.
Check Out PostgreSQL 8.1 Beta (Score:4, Informative)
Not really. PostgreSQL had MySQL beat there. You can assign permissions to groups. With large numbers of accounts, this becomes problematic with MySQL.
PostgreSQL 8.1 beta further improves this by supporting the concept of roles. A role is like a user or group except that they can be nested. This allows even easier administration of databases with hundreds or thousands of user accounts.
Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.
Commercial as in what? A mom-and-pop web-store? I would call that commercial and because MySQL will *truncate* numbers to fit fields if they are too big, I would say PostgreSQL is needed for that.
You are right in that MySQL is quite adequate for content management provided (of course) that you don't need to integrate it with other buisness apps. If you do, you can either go to PostgreSQL (and take advantage of schemas) or use PostgreSQL with DBI-Link to pull the data from MySQL. DBI-Link is easily one of the coolest new projects for PostgreSQL as it allows any data source reachable via Perl's DBI interface to be seen as a table from within PostgreSQL.
Comparison of MySQL, PostgreSQL, Oracle, MS SQL... (Score:5, Informative)
Oracle: Very portable database, replaces many OS functions and is extremely tunable. Downside: $$ and the fact that the tuning options are extremely complex allowing your DBA to spend all his time tuning the database, and your second DBA to spend all his time tuning the tables..... (/sarcasm)
One of the odd problems with Oracle is that empty strings and nulls are seen as equivalent (and Oracle DBA's seem to think that an empty string and a null are the same thing). The general concensus in the RDBMS industry is that these are not the same.
MS SQL Server: A Windows-only RDBMS which is tightly integrated with Windows in terms of memory management. Quite extensible, less costly and simpler to administrate than Oracle. Will tie you to Windows. Troubled security history.
PostgreSQL: An Open Source RDBMS designed to target Oracle's market. Extremely powerful and full featured. Attempts to tune itself to the greatest extent possible and relies on the OS for additional tuning. Downside is that it is not as widely used as the others listed above. Stored procedures are available in a much wider number of languages than in any other RDBMS in this comparison.
MySQL: A popular open source database manager (neither really relational nor a management system). Provides a simple non-standard subset of SQL for the interaction with various resources. Downside is that it does not do much integrity checking and does not enforce much integrity (valid dates include 0000-00-00 and 2004-02-31). Furthermore it will *truncate* numbers that are too large to fit in a number field making it unfit for any purpose where it must track money. It is more widely used than any other open source RDBMS.
FirebirdSQL. A good RDBMS designed really for Windows but ported over to UNIX/Linux. Fairly extensible and stable but largely undocumented. Lacks many of the data types available in all other databases listed here.
Not what we are talking about here (Score:2, Informative)
SELECT ('Hello ' || 'World!') as greeting FROM my_table;
What does it mean in MySQL?
Yes, every RDBMS has extensions to the standard and this is important but MySQL *breaks* standard behavior in many profound ways.
Another example: What does the ANSI standard say should happen here:
CREATE TABLE my_table (
myfield CHAR(4)
);
INSERT INTO my_table (myfield) values ("Hello World!");
What does MySQL do?
MySQL does the same with numbers.
These are areas where standard behavior is actually broken by the RDBMS.
Re:Another question (Score:3, Informative)
Re:Spatial databases (Score:1, Informative)
Note this is not ideal and the PostGIS community is trying to scare up funds for a couple of mad finns (Oleg and Teodor) to fix up Postgres with the required concurancy support for GiST and row level locking.
http://www.directionsmag.com/article.php?article_
http://archives.postgresql.org/pgsql-hackers/2005
Actually if you wanted to actually speed things up implementing row level locking would help everyone .
Potgres knows where it needs to improve, support the mad hackers.
Different join algorithms (Score:1, Informative)
select books.title, publishers.address, cities.timezone
from books
join publishers using(publisher_id)
join cities on(address_city=city.name);
PostgreSQL - and presumably most of the other DBs - will guess how many rows will match based on a precomputed statistical analysis of the tables and choose one of several different join algorithms, as appropriate. One of the possible algorithms is nested loops, i.e.
for b in books
for p in publishers
for c in cities
if b.publisher_id = p.publisher_id
and p.address_city = c.name
This is obviously very inefficient in the cases where the data sets are large; you'll get much better results using other techniques using e.g. hashing, or a merge-sort type approach. What indexes are present is also important.
PostgreSQL has the "EXPLAIN ANALYSE" command that you can use to find out what strategy it has chosen for your query. Just about the most common FAQ on the PostgreSQL mailing lists is "Why is PostgreSQL using an inefficient method and ignoring my indexes?". The answer is nearly always "its statistics have got out of date, run a periodic cron to update them" or "it is confused about the relative cost of reading from disk and sorting in memory, tweak something". Less often the answer is "add another index" or "re-write the query".
By poking around a bit you can nearly always get it to do the right thing. In your case, if PostgreSQL really is taking 18 hours vs. 5 minutes (200x slower), then do go and ask on the lists. Someone is sure to help if you can show an "EXPLAIN ANALYSE" output. It could be that MS_SQL is faster, but I find it inconcievable that the difference is that great.
Solved: CREATE INDEX id1 ON T1 (lower(x)); (Score:2, Informative)
> comparisons. Or, more exactly it does, but you
> end up doing full table scan and converting
> everything into upper/lowercase
The case-insensitive comparison is done like this:
select * from T1, T2 where lower(x)=lower(y);
If you have normal indexes on x and y, this won't use them and you'll get a sequential table scan. To be useful for this query, you need indexes on lower(x) and lower(y), e.g.
CREATE INDEX idx1 ON T1 (lower(x));
CREATE INDEX idx2 ON T2 (lower(y));
This is described on the reference page for CREATE INDEX.
I suggest that you ask on the mailing list next time you find a "showstopper" problem - people really are using PostgreSQL for serious applications, and an obvious thing like this would have been fixed long ago.
CPanel is very isp friendly (Score:3, Informative)
That's the primary reason, as far as I can see.
Re:Just compared MySQL 4.0.12 vs PG 8.0.3 (Score:4, Informative)
What Friendster did (Score:3, Informative)
Re:PostgreSQL is supreme A LOT (Score:5, Informative)
Slashdot has a subtle ``down'' state where they only serve static pages. It causes neat things to break like the RSS feed that I get for my home page (any request returns a static page).
Wikimedia Foundation also runs on a small cluster of MySQL servers
Perhaps you don't remember their recent outtage that took the entire thing off the internet for a day or two while they had to completely rebuild their database from backups. All of the mySQL apologists were quick to point out that databases should be expected to be all corrupt and stuff when they lose power. Users of real databases were amazed that anyone would think that.
Re:Comparison of MySQL, PostgreSQL, Oracle, MS SQL (Score:3, Informative)
SQL-Server holds the dubious honor of being the host for the virus which spread around the world fastest.
Comparison Grid (Score:3, Informative)
Re:I have a small problem... (Score:1, Informative)
The MOST IMPORTANT THING you need to know about running postgresql is how to set the shared_buffers setting correctly.
Look at this page and follow how to tune it for your particular memory size. This will save you a LOT of heartache. You'll see speed increases from that one setting alone of ten or twenty times. Good luck -- this is critical:
http://www.varlena.com/varlena/GeneralBits/Tidbits /perf.html [varlena.com]
Re:Another question (Score:3, Informative)
Re:One thing to consider - collations and Unicode (Score:1, Informative)
Yeah, dipshit: the solution is store your fucking character data in UTF-8.
Re:Another question (Score:3, Informative)
uber partioned indexes
uber materialized views
outstanding mvcc transaction support
SQL Server 2005 is supposed to have some of these features in it, plus the kitchen sink, but then I would expect the price to move on that.
The other thing is that with Microsoft you generally are going to spend an extra $2000 / developer for tools.
Re:Slashdot uptime (Score:5, Informative)
We haven't had any serious MySQL load problems in over a year, with the exception of one targeted DDoS which wedged up our search DB slave for a while. Slashdot hasn't had any MySQL reliability problems since we moved to 4.0. Our master DB has been running the same version of 4.0.x since early 2003 and it just keeps going, it never crashes. Later versions of 4.0.x are probably more reliable, but we have no need to upgrade because it just works. The only time it went down was last month when the OS finally threw a kernel panic, which sucked, but wasn't MySQL's fault.
Anyway, the point someone was trying to make is that MySQL isn't ready for high-traffic enterprise sites, which I hope we can all agree is just silly. Slashdot's not even the best example, go look at Wikipedia, CraigsList, LiveJournal, Yahoo, Google, etc. [mysql.com]
Re:Solved: CREATE INDEX id1 ON T1 (lower(x)); (Score:3, Informative)
The instructions for filing a bug report are at:
http://www.postgresql.org/docs/current/static/bug
Answers: (Score:5, Informative)
2: ANSI is error, and abort the inserting transaction. MySQL inserts 'Hell'.
Another case in point:
mysql> create table test (
-> test numeric(4,2));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (test) values (10000000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+--------+
| test |
+--------+
| 999.99 |
+--------+
1 row in set (0.00 sec)
So if this number was important and meant something (which it would in production) you just entered bad data into your database!
Re:Another question (Score:3, Informative)
When you're sueing someone, there's no extradition--that's solely for criminal proceedings. There is no analogous concept in civil litigation. It doesn't matter WHERE the violation of the contract takes place. You could have someone in Venezuala, or on Mars, perform the benchmarking, and you'd STILL get sued in a California court (assuming that's what Oracle wrote into the license agreement).
So if Oracle has a contract/license agreement with a customer that says "no benchmarking", and another clause that says "all disputes will be settled in Marin Co., CA", they don't have to bother with Buttfuck, IL at all:
1) Oracle files lawsuit against customer in Marin Co. court.
2) Marin Co. court looks at the contract clause governing jurisdiction, agrees that Marin is a valid court to hear the case.
3) Lawsuit proceeds.
If the defendant doesn't respond or show up, Oracle automatically wins the suit by default, and a judgement is entered against the defendant. Then Oracle has a court order, valid in EVERY other county in the USA, demanding that the defendant pay the judgement.
And Buttfuck, IL will enforce the order.
(If it were THAT easy to get out of a contract clause, wouldn't
Re:MySQL vs. Oracle (Score:3, Informative)
Some things are not that bad to port to psql and some well... are.
I've seen more then one project using a multi-vendor db API. Didn't work 100% last time I used something like this, but it wasn't horrible to make a few adjustments to get searches working correctly. (The outstanding one was all searches in mysql were case insensitive and for postgres I had to change a parameter for case insensitive operation)
Re:SQL Loop example? (Score:3, Informative)
For example.
Suppose I do:
SELECT * from mytable_1 where f_key IN (select p_key from mytable_2 where condition IS TRUE);
The simplest way to try to do this is to select every p_key from mytable_2 where condition IS TRUE and then query table_1 for the row in which mytable_1.f_key = mytable_2.p_key
This is the nested loop. It wasn't too long ago when nested loops where the default query plan for all types of joins. Now (for obvious reasons) they are the plan of last resort. More likely, PostgreSQL would choose a merge join for this type of subquery, or so we would hope.
Subqueries used to be popular when all joins were handled via nested loops because you could force the RDBMS engine to run fewer loops by forcing the inner-most subquery to be the one returning the fewest rows. Nowadays, all really good RDBMS's rewrite subqueries as if they were joins and will try to do other query plans on them instead.*
Does this make sense?
My favorite nested loop nightmare was when PostgreSQL was running a nested loop against an empty table thousands of times and eating up CPU activity doing it. Three seconds or so of doing something entirely meaningless.....
* This is not always possible. I have written many queries looking for possible duplicate entries into a database. These often run nested loop query plans because of the complexity of the search conditions in the self join.
Re:What does postgres mean? (Score:2, Informative)
Care to name a couple? DB2 comes from System/R. Oracle seems to be home grown. Sybase came indirectly from Britton Lee, but was mostly written from scratch. SQL Server came from Sybase. Certainly RTI's product was based on Ingres.
The original coder of Ingres later started a follow on project called Postgres and when SQL was standardised it became PostgreSQL.
Hate to break it to you, but there was a HELL of a lot more than one person coding Ingres, over a very long time.
Joe Kalash
Chief Programmer, Ingres Project
1982-1985
(I may still have some business cards to prove it)
Re:PostgreSQL is supreme A LOT (Score:2, Informative)
Wikipedia has never had a substantial mysql related failure. The outage you're talking about was due to the upgrade to mediawiki 1.5, it was a planed and announced outage that took about as long as we announced it would take, although we'd been hoping for about half the time. MySQL performed flawlessly during this process which involved churning around more than 80gb of data and hundreds of millions of rows.
The only MySQL related issue that was had in the whole thing was a MySQL quirk triggered bug in mediawiki 1.5 that wasn't discovered until we went live with the new code. A race condition due to table level locking was causing hard cpu pegging and timeouts as every session tried to process the expired blocked user list at once. This wasn't directly a MySQL bug but wouldn't have been an issue with PgSQL (due to MVCC) or if our software wasn't coded with a very MySQL mindset (treat the database like a dumb object store) with 99.99% of the smart stuff in the same PHP that serves every page up.
Re:MySQL vs. PostgreSQL -- Real World (Score:4, Informative)
PostgreSQL replication? is here [slony.info]. BSD licensed too.
Oh, and
MySQL MAXDB, Ingres + pricing on various DBs (Score:3, Informative)
Who supports XA Transactions?
MySQL - Not yet (planned in 5.0.12?)
SAPDB/MAXDB - Yes (limitations?)
Postgresql - No - in the works (8.1/8.2?)
Firebird - Yes
Berkeley DB - Yes
Ingres - Yes (limitations?)
Pricing vs Proprietary
Oracle: $58K CDN per CPU + 10% maint
DB2: $55K CDN per CPU + 10% for maint
MS SQL Server: $3K CDN per year per server (enterprise edition)
MySQL MAX DB: $1,800 CDN per CPU + 10% maint
Postgresql: Free + Support
Ingres: Free + support
Re:PostgreSQL is supreme A LOT (Score:3, Informative)
No, the outtage I'm talking about was described on the wikipedia site as having been caused by a power outtage that caused database corruption.
Here's the slashdot article [slashdot.org] to jog your memory.
Re:Dates without year? (Score:0, Informative)
You have about 20 options for date/time display at the top.
Re:Another question (Score:3, Informative)
You got me. Nothing serious, anyway.
With updateable views, you can separate the physical data storage mechanism from the way it is presented to the application.
mmm... but updatable views are problematic on a number of levels. For one thing, they don't work reliably, since you can't update a derived value, for instance. And theroretically they're a mess, since you should be updating a transient derived relation that strictly speaking ceases to exist before you even issue the update.
I'm not sure I'd want users inputting data into a data warehouse via the same view that they use to access the data anyway. There's too much derived and summarised data kicking around for my liking.
It does sound a cool way to present a read-only interface though.