No, I didn't mean it like that. If you just typed in three DDL statements one after they other, they'd each be in their own individual transaction, and if the third one failed, you'd have no way to rollback the other two.
BUT, if you do them all in one transaction, ie:
begin; alter table... create index... alter table... commit; OR rollback;
Then they'd all be 100% or 0%. Any error on any statement would mean that they would all be rolled back as well.
Oh, and sorry, I got you confused with a different OP. Didn't mean to do that. But my point still applies, I'd rather use a free database with no artificial limits in it than a "demo" version of a commercial db.
Odd, the "free" version of postgresql has no such limitations. Your original post made it seem like the two databases were somewhat equivalent. I'd hardly consider SQL Server an equal with PostgreSQL, having used both quite a bit in production environments.
Oh, and plus, you can make those changes to a postgresql table IN A TRANSACTION (however, this may extend the time the lock is held, so don't do it and sit there staring at the screen drinking a diet coke and contemplating the sunset) so that should something not go quite right with your schema change script, you can roll the whole thing back and no change to the schema or data therein.
No, postgresql does NOT have the same problem. The issue with MySQL is that when you alter a table, by adding an index or a column or dropping a column, it locks the table with an exclusive lock and then proceeds to copy the entire thing. So, if you've got a 2 gigabyte table you want to alter you need at least 2 gigs free to make the change.
PostgreSQL adds columns, drops columns, and adds indexes without copying the whole table. Now, it DOES take an exclusive lock, for all of about a millisecond or two, while it makes the change, but then it's right back in use.
Seriously, try it. Build a table with 10,000,000 rows in each database, then alter the table and look at the speed difference in those changes.
The same is true for adding fk references and such on a live database for postgresql.
I bet you're reading at a level where comments rated 0 don't show up. I was replying to an anon coward with a post rated 0, and I bet you missed the double indentation between the GP and my post where the anon coward post would have been...
Just FYI, on the mysql side, setup a replicated pair, initiate a hundred or so simultaneous transactions, then pull the plug on both machines a couple seconds apart.
Restart the database. you will likely need to restore from backup now.
Do the same to a slony pair running postgresql, and they will both be operational, and at worst you might have to reinitiate replication. something slony can do "on the fly" but MySQL requires the whole cluster be shut down.
For serious enterprise use, MySQL's replication is not really usable.
Just saying, I don't really hate MySQL, just am amazed how many people think their replication is good, when in fact, it's a pretty mediocre system.
Uh, no. Regular joins are neither left nor right. If some field means a row in either side has no match on the other, then the rows are not in the output. A left join is the SAME THING as a left outer join, the outer is syntactic sugar.
If you're gonna correct people, try to actually have the right answer, k?
Hell, where I used to work, the Windows sysadmins were the ones who brought the viruses and trojans in while reading their hot mail accounts logged onto the primary domain controllers.
If you scan through the postgresql-performance mailing list, you will see, time and again, that the AMD based machines routinely stomp the intel offerings into the ground. And not by 5 or 10%. By 50 to 200%.
Golly gee, did you think that maybe, just maybe, since the year 1999 I'd maybe, just sorta, installed another database SINCE then? Christ on a crutch. See, there's this thing called context. I know, it's a bit TWO syllable word and all, but you might try looking it up. And taking a logic class. And removing your head from your ass. Just a thought.
And maybe DB2's gotten a little easier in the convening years, but it sure as hell wasn't that simple "back in the day".
Thanks for the tip on installing DB2. I might have to try it out again, 5 years later.
I've been using pgsql and mysql since 1999, back in the days of postgresql 6.5.2. It was dirt simple to install, and it took me all of a morning to read enough to figure out to cron up vacuum.
You just sound like a lazy whiner to me.
The process, and I quote, was:
tar xvzf postgresql-6.5.2.tar.gz cd postgresql-6.5.2./configure make make install mkdir/usr/local/pgsql/data adduser postgres chown postgres.postgres/usr/local/pgsql/data su - postgres cat "export PGDATA=/usr/local/pgsql/data" >>.bash_profile source.bash_profile initdb
Yeah, it was done before lunch, and I'd never installed any other databases in my life.
Try installing db2. Now that is a pain in the ass.
If you were too lazy to install PostgreSQL, you're too lazy to be a good dba or developer.
oh yeah, I'm 100% with you on that. There's really no reason why a generic package like a bulletin board or what not should be using direct database functions like pg_, mysql_, or OCI_...
It's not like the DB layer just showed up last week or something.
Actually, I like that feature. I just wish it was possible to restrict the allowable table types, for things like big companies where you don't want joe developer to use the wrong kind of table. I wish you could set a different default that myisam, so that if you forgot the type you'd get, for instance, innodb or bdb. Finally, I wish it would throw some errors every now and then. I hate that I can create an innodb table, fk a myisam to it, begin a transaction, roll it back and THEN find out that some rows can't be rolled back.
Honestly, there's plenty about MySQL that's nice, but the silent data mangling and inability to easily customize certain behaviours makes it a poor choice for work where data integrity is paramount.
Slashdot Mirror
"He had an abyssinian cat named max..."
No, I didn't mean it like that. If you just typed in three DDL statements one after they other, they'd each be in their own individual transaction, and if the third one failed, you'd have no way to rollback the other two.
BUT, if you do them all in one transaction, ie:
begin;
alter table...
create index...
alter table...
commit; OR rollback;
Then they'd all be 100% or 0%. Any error on any statement would mean that they would all be rolled back as well.
Oh, and sorry, I got you confused with a different OP. Didn't mean to do that. But my point still applies, I'd rather use a free database with no artificial limits in it than a "demo" version of a commercial db.
Odd, the "free" version of postgresql has no such limitations. Your original post made it seem like the two databases were somewhat equivalent. I'd hardly consider SQL Server an equal with PostgreSQL, having used both quite a bit in production environments.
Oh, and plus, you can make those changes to a postgresql table IN A TRANSACTION (however, this may extend the time the lock is held, so don't do it and sit there staring at the screen drinking a diet coke and contemplating the sunset) so that should something not go quite right with your schema change script, you can roll the whole thing back and no change to the schema or data therein.
No, postgresql does NOT have the same problem. The issue with MySQL is that when you alter a table, by adding an index or a column or dropping a column, it locks the table with an exclusive lock and then proceeds to copy the entire thing. So, if you've got a 2 gigabyte table you want to alter you need at least 2 gigs free to make the change.
PostgreSQL adds columns, drops columns, and adds indexes without copying the whole table. Now, it DOES take an exclusive lock, for all of about a millisecond or two, while it makes the change, but then it's right back in use.
Seriously, try it. Build a table with 10,000,000 rows in each database, then alter the table and look at the speed difference in those changes.
The same is true for adding fk references and such on a live database for postgresql.
I bet you're reading at a level where comments rated 0 don't show up. I was replying to an anon coward with a post rated 0, and I bet you missed the double indentation between the GP and my post where the anon coward post would have been...
Ummmm. No. I wasn't replying to the GP, I was replying to THIS post:
All joins are left joins, by default, unless otherwise specified. You want a LEFT OUTER join.
Pot, kettle, black, etc.
Not the parent to that one.
No. Reread what he wrote. He said that ALL joins are left joins by default. The SQL 1992 spec is quite clear that the defaul join is an inner join.
Further, the spec is quite clear that in an outer join (i.e. a left or right join) the outer is syntactic sugar.
It's in section 7.5 of the sql92 spec by the way, if you wanna look it up.
And I don't need to run a query to know that a left join and a left outer join are the same thing.
Just FYI, on the mysql side, setup a replicated pair, initiate a hundred or so simultaneous transactions, then pull the plug on both machines a couple seconds apart.
Restart the database. you will likely need to restore from backup now.
Do the same to a slony pair running postgresql, and they will both be operational, and at worst you might have to reinitiate replication. something slony can do "on the fly" but MySQL requires the whole cluster be shut down.
For serious enterprise use, MySQL's replication is not really usable.
Just saying, I don't really hate MySQL, just am amazed how many people think their replication is good, when in fact, it's a pretty mediocre system.
Uh, no. Regular joins are neither left nor right. If some field means a row in either side has no match on the other, then the rows are not in the output. A left join is the SAME THING as a left outer join, the outer is syntactic sugar.
If you're gonna correct people, try to actually have the right answer, k?
Actually, sir, the two are in fact the same thing. Save points are the SQL spec way of doing nested transactions.
And if you don't believe me, please, feel free to post the DIFFERENCE between them. Hint: the differences are syntactical sugar.
Hell, where I used to work, the Windows sysadmins were the ones who brought the viruses and trojans in while reading their hot mail accounts logged onto the primary domain controllers.
Tell your windows people to get up to speed!
I also found this page, NASA STD 8739.3 which is the nasa high reliability soldering standard.
Wow, I used to teach a course on that. WAYYY BACK...
Any chance the OOM killer / overcommit issues will be fixed? They've been keeping the 2.6 series out of the hosting center since day one.
If you stretch our the word just, it makes sense.
It juuuusssst works.
If you scan through the postgresql-performance mailing list, you will see, time and again, that the AMD based machines routinely stomp the intel offerings into the ground. And not by 5 or 10%. By 50 to 200%.
Golly gee, did you think that maybe, just maybe, since the year 1999 I'd maybe, just sorta, installed another database SINCE then? Christ on a crutch. See, there's this thing called context. I know, it's a bit TWO syllable word and all, but you might try looking it up. And taking a logic class. And removing your head from your ass. Just a thought.
And maybe DB2's gotten a little easier in the convening years, but it sure as hell wasn't that simple "back in the day".
Thanks for the tip on installing DB2. I might have to try it out again, 5 years later.
Oh puhleeze
./configure /usr/local/pgsql/data /usr/local/pgsql/data .bash_profile .bash_profile
I've been using pgsql and mysql since 1999, back in the days of postgresql 6.5.2. It was dirt simple to install, and it took me all of a morning to read enough to figure out to cron up vacuum.
You just sound like a lazy whiner to me.
The process, and I quote, was:
tar xvzf postgresql-6.5.2.tar.gz
cd postgresql-6.5.2
make
make install
mkdir
adduser postgres
chown postgres.postgres
su - postgres
cat "export PGDATA=/usr/local/pgsql/data" >>
source
initdb
Yeah, it was done before lunch, and I'd never installed any other databases in my life.
Try installing db2. Now that is a pain in the ass.
If you were too lazy to install PostgreSQL, you're too lazy to be a good dba or developer.
Actually, that exact attitude was promulgated by MySQL the company in the documentation they themselves wrote.
So, it's kind of disingenious of you to pretend that they weren't a big part of making that attitude so common.
5 levels of isolation? The SQL spec only defines four what's this fifth one innodb allows? Seriously, I wanna know.
Choosing between a Republicrat or a Demublican is like choosing your favorite serial killer.
oh yeah, I'm 100% with you on that. There's really no reason why a generic package like a bulletin board or what not should be using direct database functions like pg_, mysql_, or OCI_ ...
It's not like the DB layer just showed up last week or something.
Actually, I like that feature. I just wish it was possible to restrict the allowable table types, for things like big companies where you don't want joe developer to use the wrong kind of table. I wish you could set a different default that myisam, so that if you forgot the type you'd get, for instance, innodb or bdb. Finally, I wish it would throw some errors every now and then. I hate that I can create an innodb table, fk a myisam to it, begin a transaction, roll it back and THEN find out that some rows can't be rolled back.
Honestly, there's plenty about MySQL that's nice, but the silent data mangling and inability to easily customize certain behaviours makes it a poor choice for work where data integrity is paramount.
Fly out to Cali and claim your damned property. Bought with your SSN, must be yours, that name is just one of your aliases, right?