Re: [GENERAL] PostgreSQL vs. MySQL benchmarks on SMP FreeBSD 7.0
On Wed, 5 Mar 2008, [EMAIL PROTECTED] wrote: b) PostgreSQL is in general 35%-45% faster. And this is using sysbench, which is an old MySQL benchmark with rudimentary PostgreSQL support bolted on. Last time I checked it didn't even put statements into transaction blocks correctly under PostgreSQL when you used the write tests (because the MySQL it was written for can't do that at all). The FreeBSD tests only run the read tests which avoid that, but still it's clearly not a benchmark optimized for good PostgreSQL performance. I suspect if the sysbench schema/queries were ported to pgbench the gap would widen considerably. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
While no one in thier right mind should be using wikipgedia, I'm sympathetic to those who might still be stuck on it for some reason, so if you guys can produce a patch against the wikipgedia cvs, I'd be happy to apply it. I'd like to patch that name. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Andreas 'ads' Scherbaum wrote: Hello, On Thu, 22 Feb 2007 00:16:24 +0800 Lincoln Yeoh lyeoh@pop.jaring.my wrote: Want transactions? Use innoDB. Want to restore a multi-gigabyte database fast from backups, sure use MyISAM (too many people seem to have probs doing that with innoDB). sure you want to do this? http://bugs.mysql.com/bug.php?id=11151 I won't trust a database who prefers speed over data integrity even if it's named transaction. [6 Apr 2006 20:50] Chad MILLER This is behavior that we can not fix. Extremely large transactions cause several storage engines to behave very poorly, and we consider that more important than making the rare load data infile transaction-safe. This must be documented, since it is surprising behavior. I'll say it's surprising. I must say I thought they'd left the transactions not important approach behind some time ago. Clearly not. Ho hum - doesn't affect me much any more I suppose. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On Friday 23 February 2007 16:43, Chad Wagner wrote:
On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote:
In any case if anyone is interested I was able to reproduce the changes
that
wikipgedia made and applied those changes (as well as others) all the
way up
to the 1.6.10 codebase. The only reason I mention this is because
1.6is the only choice for PHP4 users. If anyone is interested I can
provide
the
codebase, the schema still has to be created manually as was the case
with
wikipgedia.
I would be interested. I'm probably expected to maintain this thing ...
You can download it from:
http://www.postgresqlforums.com/downloads/pgmediawiki-1.6.10.tar.gz
Again, like wikipgedia you have to create a schema (manually) named
mediawiki and like wikipgedia (because the port more or less used some of
the same mods they made) MySQL support is probably broken.
While no one in thier right mind should be using wikipgedia, I'm sympathetic
to those who might still be stuck on it for some reason, so if you guys can
produce a patch against the wikipgedia cvs, I'd be happy to apply it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
On Thu, 22 Feb 2007 17:14:11 -0600 Jim Nasby [EMAIL PROTECTED] wrote: On Feb 20, 2007, at 11:59 PM, Adam Rich wrote: As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. Yes, but any session is free to change that setting and insert whatever garbage they want. AFAIK there's absolutely no way to prevent that. So your data is still very much subject to getting trashed. Even if you activate the strict mode, you cannot be sure, that Mysql will not result invalid data from the table inserted by another session not using strict mode. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
Hello, On Thu, 22 Feb 2007 00:16:24 +0800 Lincoln Yeoh lyeoh@pop.jaring.my wrote: Want transactions? Use innoDB. Want to restore a multi-gigabyte database fast from backups, sure use MyISAM (too many people seem to have probs doing that with innoDB). sure you want to do this? http://bugs.mysql.com/bug.php?id=11151 I won't trust a database who prefers speed over data integrity even if it's named transaction. Want foreign keys to work? Use innoDB. MyISAM tables allow you to specify foreign keys but ignores AND forgets them. As example, you have to say FOREIGN KEY ... REFERENCES cause REFERENCES itself is (was?) even in innodb just syntax sugar and get's ignored. Standard tells, REFERENCES as an alias for the full syntax is just fine, but in Mysql you won't even get an error. You can mix MyISAM tables with innoDB tables in the same database. That's a minus. Thats a feature. You can even mix both table types in a transaction: thats a real bug. ** D'oh level release gotchas Example: Before MySQL 5.0.13, GREATEST(x,NULL) and LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13, both functions return NULL if any argument is NULL, the same as Oracle. This change can cause problems for applications that rely on the old behavior. Between 5.0.24a and 5.0.27 the behaviour of SELECT COUNT(1) has changed and now returns 1 as expected. Previous versions returned 0 but of course behaviour changes in minor releases and no announcement was made. This one seems easy on the first look but i was told that it is only a result of a bigger change somewhere else in the code which will or will not interfere with other results as well. Not saying Postgresql is perfect - rather that MySQL makes Postgresql look really good. Hehe, sure ;-) Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
Hello all, On Wed, 21 Feb 2007 10:02:04 -0600 Scott Marlowe [EMAIL PROTECTED] wrote: It swallows column level foreign key contraints and does nothing with them, no errors nothing, even if you're defining innodb tables. I.e. this produces not errors: mysql create table a (id int primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql create table b (a_id int references a(id)) engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql insert into a values (1); Query OK, 1 row affected (0.03 sec) mysql insert into b values (1); Query OK, 1 row affected (0.03 sec) mysql insert into b values (2); Query OK, 1 row affected (0.03 sec) That last statement should fail. Or the creation of table b should throw a warning. Or something. It will not fail, cause REFERENCES without FOREIGN KEY get's ignored :-( Thats documented somewhere, but not really fixed, cause standard '92 says, just writing REFERENCE is ok. Oh, and no warning at all, since it is a valid (but ignored) language thing of Mysql. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
On Fri, 23 Feb 2007 13:49:06 +1300 Andrej Ricnik-Bay [EMAIL PROTECTED] wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. Wikipedia is, like ./, heavily cached. Almost every answer you get comes from a proxy, not from the database itself. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? The port of MediaWiki is going well: it is certainly usable, and is already being used by a number of sites. I would not say it is quite competitive yet as far as being ready to run Wikipedia, as the codebase has a lot of very mysql-specific stuff that has yet to be fixed/coded around. There are also a few lingering bugs, most related to the fact that the MediaWiki on Mysql stores dates as char(14). For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) http://www.mediawiki.org/ - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200702250925 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFF4ZyDvJuQZxSWSsgRA8c6AJ95oTX9YQ38VyPvFyhd54S3rHAZSACgh/tC uqcAmRFuRnMUdPL7sO/eoP0= =w2KL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Perhaps the project should be *gasp* deleted then? ;-) Or is there actual historical information there that someone would be interested in? //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/25/07, Greg Sabino Mullane [EMAIL PROTECTED] wrote: For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Some of us are still using php4 :)
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/25/07, Magnus Hagander [EMAIL PROTECTED] wrote: For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Perhaps the project should be *gasp* deleted then? ;-) Or is there actual historical information there that someone would be interested in? As I said in my other mail, some folks are still using PHP4 -- which is why MediaWiki still maintains the 1.6 branch. I am more than willing to contribute the most recent 1.6.10 codebase w/ PostgreSQL modifications to the foundry. I am actively maintaining my own codebase for my site. I agree with Greg, if you are already using PHP5 then use the MediaWiki distribution, but if your stuck on PHP4 like me then you really don't have a choice other than what is being offered on pgfoundry. :)
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: I installed wikipgdia for the WPLUG wiki: http://wplug.ece.cmu.edu/wiki/ Isn't that the same wikipgedia that is found at pgFoundry? The only issue I really had the the wikipgedia port is that the codebase is 1.6alpha, and it seemed like it wasn't being actively maintained anymore (infact that is what the description says), so I am not sure it has all of the bug fixes up to 1.6.10. In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6 is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia.
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
In response to Chad Wagner [EMAIL PROTECTED]: On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: I installed wikipgdia for the WPLUG wiki: http://wplug.ece.cmu.edu/wiki/ Isn't that the same wikipgedia that is found at pgFoundry? Yes. The only issue I really had the the wikipgedia port is that the codebase is 1.6alpha, and it seemed like it wasn't being actively maintained anymore (infact that is what the description says), so I am not sure it has all of the bug fixes up to 1.6.10. I installed it as an experiment, then (while my back was turned) a bunch of people started using it ... now it's a mission-critical part of the WPLUG organization ... Hopefully there aren't any serious bugs hiding anywhere ... In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6 is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia. I would be interested. I'm probably expected to maintain this thing ... -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/22/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I use mediawiki with postgres and it works fine, except for a bug regarding timestamps. That bug is due to mysqlism of the code. Once that's fixed, it will be ready as far as I'm concerned. editorialThere have been some tragic and embarrassing data losses by some big sites that should know better because they used mysql without the heroic measures that are needed to make it safe. I don't care that much that big sites use it, big sites start small and don't always start with the best tools. Once started, it's hard to switch over to better tools. If you used enough volkswagen beetles you could move the same number of passengers on the same routes as Greyhound does with buses, but that doesn't mean they are the right tool./editorial - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Le vendredi 23 février 2007 16:37, Ian Harding a écrit : On 2/22/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I use mediawiki with postgres and it works fine, except for a bug regarding timestamps. That bug is due to mysqlism of the code. Once that's fixed, it will be ready as far as I'm concerned. I get an error with tsearch2 query parser, and patch that. ( http://bugzilla.wikimedia.org/show_bug.cgi?id=8958 , thanks Greg ) editorialThere have been some tragic and embarrassing data losses by some big sites that should know better because they used mysql without the heroic measures that are needed to make it safe. I don't care that much that big sites use it, big sites start small and don't always start with the best tools. Once started, it's hard to switch over to better tools. If you used enough volkswagen beetles you could move the same number of passengers on the same routes as Greyhound does with buses, but that doesn't mean they are the right tool./editorial - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? which is what mySQL does by default :-) The statement CREATE TABLE foo (bar INTEGER NOT NULL, rab VARHCAR(123) NOT NULL, oof DATETIME NOT NULL,); will be rewritten automatically by mySQL to CREATE TABLE foo (bar INTEGER NOT NULL DEFAULT 0, rab VARHCAR(123) NOT NULL DEFAULT '', oof DATETIME NOT NULL DEFAULT '-00-00 00:00'); Maybe if you really want to enforce a NOT NULL constraint in mySQL, you have to declare a column as NOT NULL DEFAULT NULL, explicitly as was suggested somewhere else in this thread. Fascinating how they probably thought that was a good idea. -- Tommy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
In that case, the distinction just adds work. In that case you declare the column not null and don't use nulls. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Fri, Feb 23, 2007 at 01:49:06PM +1300, Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And wikipedia has a massive distributed caching layer the spans the glob (IIRC there's 128 cache machines). I think a better example might be livejournal; the last time I ran the numbers it should have been very reasonable to handle the entire update load with a single database server and add slony slaves for read access as needed. Instead they have a very, very complex system of spreading user load across multiple clusters, etc. Because of that and mysql in general, they've suffered a lot of pain and some lost data. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Crawford Sent: Friday, February 23, 2007 1:04 PM To: Mark Walker Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql vs mysql Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Glen Parker wrote: Buy the same token, some application have no use whatsoever for the distinction between NULL and ''. In that case, the distinction just adds work. True, I suppose. But if I need that, I can live with a one-time ...not null default ''... addition to my table definition. Or a coalesce(mycolumn, '') if I only need the null to equal '' in specific queries or views. I would love to see different ways to handle NULL implemented by the server. For what I do, NULL could always compare equal to zero and ''. I have no use for NULL in text values. I do need it for numerics, however it doesn't mean unknown, it just means not entered, which is different because I always treat it as zero. If that works for your app, great. But in many (most?) cases it doesn't. A survey, for example, might ask for age or income. Some people will decline to answer one or both of those questions. When someone asks for the average age of respondents, they want exactly what avg() returns - the sum of the non-null ages divided by the count of non-null ages. If the nulls were treated as zeros, the answer could be severely skewed. Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Thursday 22 February 2007 05:10, Rich Shepard wrote: On Thu, 22 Feb 2007, Tim Tassonis wrote: I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Tim, Long ago, a lot of database applications used 99, or 999, or -1 to indicate an unknown value. However, those don't fit well with a textual field and they will certainly skew results if used in arithmetic calculations in numeric fields. The concept of NULL representing an unknown value, and therefore one that cannot be compared with any other value including other NULLs, is no different from the concept of zero which was not in mathematics for the longest time until some insightful Arab Indian, the Arabs learned of zero from the Indians. mathematician saw the need for a representation of 'nothing' in arithmetic and higher mathematics. There was probably resistance to that idea, too, as folks tried to wrap their minds around the idea that 'nothing' could be validly represented by a symbol and it was actually necessary to advance beyond what the Greeks and Romans -- and everyone else -- could do. Now, one would be thought a bit strange to question the validity of zero. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. HTH, Rich ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
Steve Crawford schrieb: Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Well I indeed have such a weather database, taking about 2 minute snapshots of a couple of sensors. If one sensor does not respond or is ignored due to error constraint, I just dont insert a row: timestamp, sensor_id, sensorvalue, errorvalue To do something usefull w/ the data you need to interpolate anyway. Just an example of how you can indeed avoid null values :-) Regards Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? Because I DO need NULLS for non text fields, and I still want NULL to compare equal to, say, '' and 0. I don't think you read what I wrote... Put another way, I would like to redefine NULL to mean BLANK or NOT ENTERED. Totally different concept. -Glen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
That's absolutely correct. What I want is a totally non standard *optional* extension, recognizing that many, even if not most, applications could benefit from it. I think there's a clean way to do it. I would never ask for such a thing if I thought it would effect an out of the box installation. -Glen If that works for your app, great. But in many (most?) cases it doesn't. A survey, for example, might ask for age or income. Some people will decline to answer one or both of those questions. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Glen Parker schrieb: Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? Because I DO need NULLS for non text fields, and I still want NULL to compare equal to, say, '' and 0. I don't think you read what I wrote... Put another way, I would like to redefine NULL to mean BLANK or NOT ENTERED. Totally different concept. Not wise concept, but here you go: WHERE coalesce(sometimesnull,'') = '' or WHERE coalesce(sometimesnull,0 ) = 0 ... Regards Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia. I would be interested. I'm probably expected to maintain this thing ... You can download it from: http://www.postgresqlforums.com/downloads/pgmediawiki-1.6.10.tar.gz Again, like wikipgedia you have to create a schema (manually) named mediawiki and like wikipgedia (because the port more or less used some of the same mods they made) MySQL support is probably broken.
Re: [GENERAL] postgresql vs mysql
Brandon Aiken wrote: That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. No, you put them all on one table and put nulls in places where no data is available. With real database systems, there's no excuse not to. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
It cannot already do what I want, unless you blatantly ignore what I wrote. Putting coalesce() calls *everywhere* counts as more work, don't you agree? -Glen Ben wrote: But, why do you need an extension when the existing system can already do what you want? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/23/07 15:47, Peter Eisentraut wrote: Brandon Aiken wrote: That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. No, you put them all on one table and put nulls in places where no data is available. With real database systems, there's no excuse not to. Each of the daily/hourly/etc temperature readings are independent. Therefore they should each have their own row in the meteorology readings table. I *think* that breaks 3NF. This should be 3NF: CREATE TABLE T_READING_TYPE ( READING_CODECHAR(4) PRIMARY KEY, READING_DESCRIP TEXT ); CREATE TABLE T_MET_READINGS ( _DATE DATE, _HOUR SMALLINT CHECK (HOUR BETWEEN 0 AND 23), READING_CODE CHAR(4) REFERENCES T_READING_TYPE(READING_CODE), READING_VALUE NUMERIC(8,3), PRIMARY KEY (_DATE, _HOUR) ); -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF32j3S9HxQb37XmcRAgsgAKC7m74VtyU5rnOI0gF2VXjHxk9kXgCfVY86 i5hgysDkC7EUJWlbGL+vyZM= =RN+L -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Ben wrote: What I read was that you have no use for NULLs, and that they're equivilant to zero or an empty string or some other known value. Sorry if I misunderstood that. Equivalent, yes, because NULL doesn't usually mean UNKNOWN in this system, just NOT ENTERED. I do still have use for NULL in data types that don't inherently have a blank value (numerics, dates, etc.) I can and do solve the problem by simply not using NULL in character fields, and by the rather gratuitous use of coalesce() in queries. The problem is, it places a burden on people doing ad hoc queries who, because of the type of data they work with, have no reason to understand the concept of NULL as it exists in standard SQL. These aren't computer scientists, they are accountants and managers. The result is queries that either return bad data, or that appear much more complex than should be required to people who can't see why NULL == zero is NULL. And as I said, I really don't know what a fully functional solution would look like, I just know that it would be useful to a large cross section of users. -Glen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Ron Johnson wrote: Each of the daily/hourly/etc temperature readings are independent. Therefore they should each have their own row in the meteorology readings table. I *think* that breaks 3NF. If everything is, as you say, independent, then there can be no 3NF violation, because that only happens when you have functional dependencies within a table. The question that you raise is more a matter of deciding which aspects of a problem are data and which are data structure. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
On Fri, Feb 23, 2007 at 02:50:48PM -0800, Glen Parker wrote: I can and do solve the problem by simply not using NULL in character fields, and by the rather gratuitous use of coalesce() in queries. I'm confused. If you don't use NULLs then you don't need coalesce either. The problem is, it places a burden on people doing ad hoc queries who, because of the type of data they work with, have no reason to understand the concept of NULL as it exists in standard SQL. These aren't computer scientists, they are accountants and managers. The result is queries that either return bad data, or that appear much more complex than should be required to people who can't see why NULL == zero is NULL. Is it really that hard to understand that UNKNOWN == zero is UNKNOWN? And again, if NULL is confusing on your systems, don't use it. They don't appear out of nowhere. Outer joins are really the only place you can't avoid them. And as I said, I really don't know what a fully functional solution would look like, I just know that it would be useful to a large cross section of users. Useful, maybe. Confusing, absolutly. I'm just wondering how it would interact with foreign keys for example. Different people can't have different ideas about '' = NULL, else you'd get constraints that are violated depending on who's looking. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] postgresql vs mysql
That's why you make a table for every device or every measurement, and then use a view to consolidate it. With update-able views, there's no excuse not to. I would be interested on here some of your experiences on this? I've built and made use of table hierarchies three levels deep and about twenty classification types wide that I rolled up into separate update-able view. However, I found the process of cascading the updates to all three levels of each classification type using the TID rather tedious. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) 0)); What's wrrong with using a '' sd the check? Or is this just a flavour thing? -- To the extent that we overreact, we proffer the terrorists the greatest tribute. - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
he he. what does the PHP of databases mean? Joshua D. Drake wrote: John Smith wrote: On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. Oops he probably should not have used MySQL because it is trademarked... mysql: The PHP of databases ;) Sincerely, Joshua D. Drake jzs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
Chris írta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) 0)); What's wrrong with using a '' sd the check? Or is this just a flavour thing? Nothing, I just thought of the other way first :) Probably better doing it as a '' otherwise postgres might have to run the char_length function every time you do an insert (ie might be a very slight performance issue). I would do a CHECK (trim(a) '') ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
Zoltan Boszormenyi wrote: Chris írta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) 0)); What's wrrong with using a '' sd the check? Or is this just a flavour thing? Nothing, I just thought of the other way first :) Probably better doing it as a '' otherwise postgres might have to run the char_length function every time you do an insert (ie might be a very slight performance issue). I would do a CHECK (trim(a) '') If you were ok with a string consisting soley of whitespace. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote: Chris ?rta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: create table a(a text not null check (char_length(a) 0)); What's wrrong with using a '' Nothing, I just thought of the other way first :) I would do a CHECK (trim(a) '') Whitespaces are values too, you know. -- To the extent that we overreact, we proffer the terrorists the greatest tribute. - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My definition is, toy used/trumpeted by pseudo-professionals as a professional tool, when it just doesn't measure up. On 02/22/07 02:08, Tyarli wrote: he he. what does the PHP of databases mean? Joshua D. Drake wrote: John Smith wrote: On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. Oops he probably should not have used MySQL because it is trademarked... mysql: The PHP of databases ;) Sincerely, Joshua D. Drake -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3WioS9HxQb37XmcRAvs3AJ48X+8O+0pXa7ynUrkoiJ4hxXM73ACgixHV HD5+J/MQMk+mlTHYG7thNnM= =wW6Y -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
CaT írta: On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote: Chris ?rta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: create table a(a text not null check (char_length(a) 0)); What's wrrong with using a '' Nothing, I just thought of the other way first :) I would do a CHECK (trim(a) '') Whitespaces are values too, you know. Yes, I know. But e.g. for a real people name, would you store accidentally entered spaces before or after the actual name, too? Which would also ruin sorting by name. But of course, it doesn't make sense in every case. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 11:27:18AM +0100, Zoltan Boszormenyi wrote: I would do a CHECK (trim(a) '') Whitespaces are values too, you know. Yes, I know. But e.g. for a real people name, would you store accidentally entered spaces before or after the actual name, too? Which would also ruin sorting by name. But of course, it doesn't make sense in every case. Yeah but if you're going down that path then you either trim on the insert or use a trigger (rule?) to automatically trim your data for you. Doing it in a check wont do much of anything for you in the case you describe. -- To the extent that we overreact, we proffer the terrorists the greatest tribute. - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My definition is, toy used/trumpeted by pseudo-professionals as a professional tool, when it just doesn't measure up. /me Tries really hard to resist responding /me Fails I'm sorry, but having just been described as a 'pseudo-professional' I simply have to defend myself, and my colleagues. I don't want to start a language war here, and this is the last post that I will make on this subject, but it's perfectly possible to criticise pretty much any programming language out there with a whole raft of reasons as to why it's not a good language to use professionally. Take perl for example. I have still yet to see readable Perl code. Unless you're very careful C lets you write code that leaks or overwrites memory left right and centre with all kinds of security flaws possible as a result. I could go on with other languages but I think you get my point. There is however a point in saying that while PHP does have its numerous inconsistencies and oddities, it is *perfectly* possible to write good quality, well performing, maintainable, clean and well designed code in PHP. Without blowing my own trumpet, I and my colleagues do it every day, and we work very hard at it too. Oh yes, we've seen plenty of bad code, but that's the skill of a good developer (to spot the bad code) and the challenge of a professional one (taking the care to deal with the bad code rather than just leaving it be). You just have to be more careful in PHP not to fall into the traps (that a good developer knows about), which is exactly like a C developer knowing how not to introduce buffer overruns etc. This again applies to any language (so no language war!) OK, I've done. I won't say any more. But please consider who might be reading before making unprofessional sweeping statements like that. On 02/22/07 02:08, Tyarli wrote: he he. what does the PHP of databases mean? Joshua D. Drake wrote: John Smith wrote: On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. Oops he probably should not have used MySQL because it is trademarked... mysql: The PHP of databases ;) Sincerely, Joshua D. Drake -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3WioS9HxQb37XmcRAvs3AJ48X+8O+0pXa7ynUrkoiJ4hxXM73ACgixHV HD5+J/MQMk+mlTHYG7thNnM= =wW6Y -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My definition is, toy used/trumpeted by pseudo-professionals as a professional tool, when it just doesn't measure up. Boah, here surely speaks a true professional playing in the league of Donald Knuth or even Alan Kay, as opposed to all the pseudos like me out there. Is it Assembler or Smalltalk you write your web pages with? PHP absolutely is a professional tool as a scripting language, of course with all the downsides of any scripting language. I'll choose php over Perl any day, as it is syntactically much cleaner and performs sufficiently well for usual scripting needs. Of course, I wouldn't write an operating system with it. Tim ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Chris wrote: Erick Papadakis wrote: So how should I make a database rule in MySQL to not allow blank strings. Basically to REQUIRE a value for that column, whether it is NULL or NADA or VOID or whatever you wish to call it. I just want to make sure that something, some value, is entered for a column. Would appreciate any thoughts or pointers. Does PostgreSQL suffer from this oddity as well? This distinction between an empty string and a NULL? Could you also please give me an example of where this would be useful from a business logic standpoint? Why should a NULL be different from an empty string, what's the big mysterious difference? It's not an oddity. An empty string is a KNOWN value. You know exactly what that value is - it's an empty string. A NULL is UNKNOWN - it doesn't have a value at all. I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. But as it is a fundamental, defined part of the sql standard, one just has to live with it. Tim ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
Zoltan Boszormenyi wrote:
I would do a CHECK (trim(a) '')
If you were ok with a string consisting soley of whitespace.
I meant NOT NULL CHECK(trim(a) ''), keeping the context of the
above example.
Right. I plead that it was late when i replied. I honestly don't know
what i was thinking.
trim() trims whitespace only from the beginning and the end of the
strings but not from the middle:
# select trim(' a b c ');
I realise that. I was thinking of this case:
select trim('');
Though, i'm not sure what my point was. Again, it was late. Carry on!
brian
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo That one actually comes in handy ;) Especially in older versions (4.0) that don't support subselects.. I must say I don't see any reasonable way of interpreting the above query. Is the value of bar selected randomly? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] postgresql vs mysql
2007/2/22, Russ Brown [EMAIL PROTECTED]: Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My definition is, toy used/trumpeted by pseudo-professionals as a professional tool, when it just doesn't measure up. /me Tries really hard to resist responding /me Fails I'm sorry, but having just been described as a 'pseudo-professional' I simply have to defend myself, and my colleagues. I don't want to start a language war here, and this is the last post that I will make on this subject, but it's perfectly possible to criticise pretty much any programming language out there with a whole raft of reasons as to why it's not a good language to use professionally. Take perl for example. I have still yet to see readable Perl code. Unless you're very careful C lets you write code that leaks or overwrites memory left right and centre with all kinds of security flaws possible as a result. I could go on with other languages but I think you get my point. There is however a point in saying that while PHP does have its numerous inconsistencies and oddities, it is *perfectly* possible to write good quality, well performing, maintainable, clean and well designed code in PHP. Without blowing my own trumpet, I and my colleagues do it every day, and we work very hard at it too. Oh yes, we've seen plenty of bad code, but that's the skill of a good developer (to spot the bad code) and the challenge of a professional one (taking the care to deal with the bad code rather than just leaving it be). You just have to be more careful in PHP not to fall into the traps (that a good developer knows about), which is exactly like a C developer knowing how not to introduce buffer overruns etc. I think you made a very good point and I want to second it. Although I have been a Python enthusiast for some years and today I do all new developments with Python, I have written somethings with PHP (and ASP) which were very succesfull. Indeed when I have to install sofware I look at the final product not at the language in which it was written. As an example I have installed Mediawiki (php) in instead of Moinmoin (python) because Mediawiki is years ahead of Moinmoin. Regards, -- Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
On Thu, 22 Feb 2007, Tim Tassonis wrote: I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Tim, Long ago, a lot of database applications used 99, or 999, or -1 to indicate an unknown value. However, those don't fit well with a textual field and they will certainly skew results if used in arithmetic calculations in numeric fields. The concept of NULL representing an unknown value, and therefore one that cannot be compared with any other value including other NULLs, is no different from the concept of zero which was not in mathematics for the longest time until some insightful Arab mathematician saw the need for a representation of 'nothing' in arithmetic and higher mathematics. There was probably resistance to that idea, too, as folks tried to wrap their minds around the idea that 'nothing' could be validly represented by a symbol and it was actually necessary to advance beyond what the Greeks and Romans -- and everyone else -- could do. Now, one would be thought a bit strange to question the validity of zero. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. HTH, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Rich Shepard wrote: On Thu, 22 Feb 2007, Tim Tassonis wrote: I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Tim, Long ago, a lot of database applications used 99, or 999, or -1 to indicate an unknown value. However, those don't fit well with a textual field and they will certainly skew results if used in arithmetic calculations in numeric fields. I remember, my first database to write stuff for was an IMB IMS hierarchical/network one. The concept of NULL representing an unknown value, and therefore one that cannot be compared with any other value including other NULLs, is no different from the concept of zero which was not in mathematics for the longest time until some insightful Arab mathematician saw the need for a representation of 'nothing' in arithmetic and higher mathematics. There was probably resistance to that idea, too, as folks tried to wrap their minds around the idea that 'nothing' could be validly represented by a symbol and it was actually necessary to advance beyond what the Greeks and Romans -- and everyone else -- could do. Now, one would be thought a bit strange to question the validity of zero. That's one point for me, then!. NULL exactly is _not_ the equivalent the the number 0, but the mentioned strange symbol that has to be treated specially and does not allow normal calculation, like '0' does in mathematics. I don't know how many times I had to write a query that ends with: - or column is null - and column is not null exactly because it is a special symbol. In mathematics, the only special case for zero that springs to my mind is the division of something by zero (I'm by no means a mathematician). As a completely irrelevant sidenote to the discussion, I'm greek and not arabic, but I certinly do accept the superiority of the arabic notation. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. As I said, I don't deny it solves some problems (that could be solved in a different way, too), but in my opinion, it creates more (that also can be solved, as above examples show). Tim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
Russ == Russ Brown [EMAIL PROTECTED] writes: Russ Take perl for example. I have still yet to see readable Perl code. I could say the same for greek, and pl/pgsql. You can't read it if you're not familiar with it. Please stop bashing Perl until you've read at least Learning Perl or the equivalent. Please. You have no right. It's pure prejudice, and usually just parroted from others. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 merlyn@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
At 01:11 PM 2/22/2007, John Smith wrote: On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. jzs OK sorry... That was more of a footnote. Link. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
At 10:22 PM 2/22/2007, Tim Tassonis wrote: Chris wrote: An empty string is a KNOWN value. You know exactly what that value is - it's an empty string. A NULL is UNKNOWN - it doesn't have a value at all. I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. But as it is a fundamental, defined part of the sql standard, one just has to live with it. Well it can be useful to have a column like: foo integer not null default null That means someone/something must specify a value for foo when doing an insert. They can't just hope for the best that there's a default... I think that works on postgresql but not on MySQL (see back on topic :) ). Have fun! Link. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql (OT: perl)
Randal L. Schwartz wrote: Russ Take perl for example. I have still yet to see readable Perl code. You can't read it if you're not familiar with it. Seconded. Perl is like the churkendoose -- hybrid strength, ugly as hell, only poultry known that can scare off a fox every time, whole barnyard loves having it around. The better I know it, the better I like it. A language's appeal always depends on your prior language and operating system experience, not to mention what you're trying to use it for, how you learn it, the support infrastructure around you while learning it, how much you're getting paid (in fame, fortune, and/or fun) to program in it, how long you've used it, how often you use it, what other languages you use alongside of it, who your friends are, how much of a sucker you are for what you read in the tech press, how patient you are, how creative you are, your tolerance for abstraction, the number and quality of programmers you want to be able to potentially contribute to or maintain your code, etc, etc. It's a big barnyard, and it all stinks. So let's just roll around in the mud and have some fun. -Kevin Murphy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
On 2/22/07, Martijn van Oosterhout kleptog@svana.org wrote: On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo That one actually comes in handy ;) Especially in older versions (4.0) that don't support subselects.. I must say I don't see any reasonable way of interpreting the above query. Is the value of bar selected randomly? The value of bar happens to be the first value fetched based on the GROUP BY of foo, not sure how predictable and repeatable it is.
Re: [GENERAL] postgresql vs mysql
If you can remove NULLs without breaking OUTER JOIN, more power to you. In the vast majority of cases, all fields in a table should have a NOT NULL constraint. Storing a NULL value makes little sense, since you're storing something you don't know. If you don't know something, why are you trying to record it? From a strict relational sense, the existence of NULL values in your fields indicates that your primary keys are not truly candidate keys for all your fields. That means your database isn't [BCNF] normalized. Arguments about de-normalization generally result in the basic limitation in nearly all RDBMS's that they do not allow you to optimize how data is physically stored on disk. That is, a generalized SQL database like Oracle, MySQL, PostgreSQL, etc. sacrifice the ability to control how data is physically store in order to be a generalized database that can store generic domains in the form of the most common datatypes that computer programs use. This is a basic limitation of using a generalized database engine, and if your application demands higher performance than you can get with a general RDBMS, you'll have to develop your own task-specific RDBMS or modify your schema so that the problem can be mitigated. Schema de-normalization is a way of purposefully degrading the normal quality of your schema in order to make up for shortcomings of the database engine and limitations of computerized data storage. As long as you understand that de-normalization is a practical workaround and never a wise logical design choice from the get-go, you shouldn't feel too bad about doing it. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Tassonis Sent: Thursday, February 22, 2007 10:31 AM To: Rich Shepard Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql vs mysql Rich Shepard wrote: On Thu, 22 Feb 2007, Tim Tassonis wrote: I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Tim, Long ago, a lot of database applications used 99, or 999, or -1 to indicate an unknown value. However, those don't fit well with a textual field and they will certainly skew results if used in arithmetic calculations in numeric fields. I remember, my first database to write stuff for was an IMB IMS hierarchical/network one. The concept of NULL representing an unknown value, and therefore one that cannot be compared with any other value including other NULLs, is no different from the concept of zero which was not in mathematics for the longest time until some insightful Arab mathematician saw the need for a representation of 'nothing' in arithmetic and higher mathematics. There was probably resistance to that idea, too, as folks tried to wrap their minds around the idea that 'nothing' could be validly represented by a symbol and it was actually necessary to advance beyond what the Greeks and Romans -- and everyone else -- could do. Now, one would be thought a bit strange to question the validity of zero. That's one point for me, then!. NULL exactly is _not_ the equivalent the the number 0, but the mentioned strange symbol that has to be treated specially and does not allow normal calculation, like '0' does in mathematics. I don't know how many times I had to write a query that ends with: - or column is null - and column is not null exactly because it is a special symbol. In mathematics, the only special case for zero that springs to my mind is the division of something by zero (I'm by no means a mathematician). As a completely irrelevant sidenote to the discussion, I'm greek and not arabic, but I certinly do accept the superiority of the arabic notation. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. As I said, I don't deny it solves some problems (that could be solved in a different way, too), but in my opinion, it creates more (that also can be solved, as above examples show). Tim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail
Re: [GENERAL] postgresql vs mysql
If you don't know something, why are you trying to record it? From a strict
relational sense, the existence of NULL values in your fields indicates that
your primary keys are not
truly candidate keys for all your fields. That means your database isn't [BCNF]
normalized.
I agree that there are very few times when NULL is appropriate in a database. I can't think of a single concrete example to use it in a database field.
It has its use in programming, mainly as a memory management/trash collection
mechanism. Basically, you don't want to delete something that doesn't exist.
For example:
Statement *st = NULL;
ResultSet *rs = NULL;
try
{
st = prepareStatement(select * from customers);
rs = st-executeQuery();
while (rs-next())
{
do something
}
delete st;
delete rs;
}
catch (Exception e)
{
if (st != NULL)
delete st;
if (rs != NULL)
delete rs;
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On 22/02/07, Shashank Tripathi [EMAIL PROTECTED] wrote: I would do a CHECK (trim(a) '') TRIM() would add some processing time, so I'd include it only if there was a chance of spaces getting added. From a puritanical point of view, it is definitely a good idea. To the original poster, this syntax should work in MySQL as well: create table mytable (mycol text not null check (mycol '')); Problem is, if you created your table before MySQL 5, and now simply want to ALTER your table (which is what I gather you wish to do, as you already have the table) then adding the CHECK condition may not work. I cannot help in this case, and from the turn this thread has taken, not many others I suppose. Why not try a MySQL experts list instead of PostgreSQL, but be prepared to have to recreate the table in MySQL 5 with the CHECK constraint, and then importing your data in to it. Good luck! Sorry, I spoke too soon. MySQL does not do the constraints jig yet. The CHECK clause is parsed but ignored by all storage engines. - From http://dev.mysql.com/doc/refman/5.0/en/create-table.html So you may want to adopt some kludges, such as updatable views: http://arjen-lentz.livejournal.com/49881.html If I were you, I'd just stick to error-checking in the application layer for now, or consider slowly switching to PostgreSQL. (No plug intended) Shanx ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
I would do a CHECK (trim(a) '') TRIM() would add some processing time, so I'd include it only if there was a chance of spaces getting added. From a puritanical point of view, it is definitely a good idea. To the original poster, this syntax should work in MySQL as well: create table mytable (mycol text not null check (mycol '')); Problem is, if you created your table before MySQL 5, and now simply want to ALTER your table (which is what I gather you wish to do, as you already have the table) then adding the CHECK condition may not work. I cannot help in this case, and from the turn this thread has taken, not many others I suppose. Why not try a MySQL experts list instead of PostgreSQL, but be prepared to have to recreate the table in MySQL 5 with the CHECK constraint, and then importing your data in to it. Good luck! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
On Feb 20, 2007, at 11:59 PM, Adam Rich wrote: As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. Yes, but any session is free to change that setting and insert whatever garbage they want. AFAIK there's absolutely no way to prevent that. So your data is still very much subject to getting trashed. Of course, there's also plenty of ways to do that on accident, mostly involving mistakenly ending up with a MyISAM table instead of an InnoDB one. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote: The only thing I can think of that rewrites a whole postgresql table would be reindexing it, or an update without a where clause (or a where clause that includes every row). Normal operations, like create index, add column, drop column, etc do not need to rewrite the table and happen almost instantly. Reindexing won't re-write a table; clustering will. Also some ALTER TABLE commands will (such as changing the data type of a column, or creating a new column that's NOT NULL). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote: IMX, the only things going for MySQL are: 1. It's fast. That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/07 17:17, Jim Nasby wrote: On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote: The only thing I can think of that rewrites a whole postgresql table would be reindexing it, or an update without a where clause (or a where clause that includes every row). Normal operations, like create index, add column, drop column, etc do not need to rewrite the table and happen almost instantly. Reindexing won't re-write a table; clustering will. Also some ALTER TABLE commands will (such as changing the data type of a column, or creating a new column that's NOT NULL). Man, that's just Not Right. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3itiS9HxQb37XmcRAqnWAJ0R9MmdmlUR92B3F81vlGI/D7Es8ACgmiKI F6BrBA/ZeTsciqJAdiYfTCo= =KJDL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Digg and Slashdot use MySQL databases, so clearly they *can* be made to support a high-load, high-performance, limited-write style web application. You might remember a few months back when SlashDot had to turn off threaded replies because the schema for the parent-child field was still an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the maximum value of the field (16.7 million). Obviously, I have no knowledge of the server configuration, hardware configuration, or schema, but in-the-wild examples of high performance MySQL installations are trivial to find (as are PostgreSQL installations such as the .org DNS TLD root). I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system (that is, fsync in the same state and with the same level of ACID compliance) subject to a battery of test schema types (OLTP, OLAP, etc.). -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Jim Nasby [mailto:[EMAIL PROTECTED] Sent: Thursday, February 22, 2007 6:28 PM To: Brandon Aiken Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql vs mysql On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote: IMX, the only things going for MySQL are: 1. It's fast. That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
On Thu, 2007-02-22 at 18:48 -0500, Brandon Aiken wrote: Digg and Slashdot use MySQL databases, so clearly they *can* be made to support a high-load, high-performance, limited-write style web application. You might remember a few months back when SlashDot had to turn off threaded replies because the schema for the parent-child field was still an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the maximum value of the field (16.7 million). Obviously, I have no knowledge of the server configuration, hardware configuration, or schema, but in-the-wild examples of high performance MySQL installations are trivial to find (as are PostgreSQL installations such as the .org DNS TLD root). Actually this has been mentioned before, Slashdot is a good example of how poorly MySQL scales. Almost every page you view on slashdot is actually a static page harvested every x minutes by another process because dynamically generating those pages is very expensive. If slashdot was able to run on top of MySQL without all that hand holding, then it would be a showcase for it. I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system (that is, fsync in the same state and with the same level of ACID compliance) subject to a battery of test schema types (OLTP, OLAP, etc.). Me too. But I gave up on using MySQL for serious uses some time ago when I realized that updates for serious bug fixes took years to come out of the pipeline, and some will simply never be fixed. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
Ron Johnson wrote: On 02/21/07 18:09, Erick Papadakis wrote: How would you like to use a database that has nuances like these -- http://forums.mysql.com/read.php?20,141120,141120#msg-141120 Huh? A blank string (does that mean '' or ' '?) is not NULL, so of *course* it should pass the NOT NULL constraint. Or am I missing something? Not sure what the OP was getting at but of course an empty string is not the same as a NULL. You have to follow that MySQL forum thread a few messages to see the real screwup. Upon learning that an empty string is not the same a a NULL, he adds a constraint: ALTER TABLE `tbl` CHANGE `col` `col` VARCHAR( 3 ) CHECK (`col` '') NOT NULL Unfortunately for him, this does not work either. The reason is clarified in the following message: Currently MySQL accepts CHECK syntax but does not implement them. You can enforce such a rule at database level with a trigger, or at application level as another contributor suggested. Huh? Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
Tim Tassonis wrote: Chris wrote: Erick Papadakis wrote: So how should I make a database rule in MySQL to not allow blank strings. Basically to REQUIRE a value for that column, whether it is NULL or NADA or VOID or whatever you wish to call it. I just want to make sure that something, some value, is entered for a column. Would appreciate any thoughts or pointers. Does PostgreSQL suffer from this oddity as well? This distinction between an empty string and a NULL? Could you also please give me an example of where this would be useful from a business logic standpoint? Why should a NULL be different from an empty string, what's the big mysterious difference? It's not an oddity. An empty string is a KNOWN value. You know exactly what that value is - it's an empty string. A NULL is UNKNOWN - it doesn't have a value at all. I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Well, your mileage must vary. The absence of nulls would make my life difficult. Just substitute unknown for null as mentioned above and the various operations with null make sense. For example, take some days and low-temperatures: Mon: 30 Tue: 10 Wed: 0 Thu: unknown Fri: 0 Sat: unknown Sun: -5 Was the low temperature the same on: Mon/Tue: no Wed/Fri: yes Thu/Fri: unknown Thu/Sat: unknown - the always seemingly confusing null=null is null. So what do we do without a null? Does the helpful app convert the unknowns to zero? That's not right. Are we forced to specify a special value like 999 for the unknown data? Then we have to add extra code to create that value when the value is unknown and more code still to check for that value when, say, looking for the lowest or average temperatures. And we're set up for disaster when someone starts measuring furnace temps instead of outdoor temps. Look no further than Y2K to see what happened to those apps that gave special meaning to 12/31/99. Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Joshua D. Drake Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Well, your mileage must vary. The absence of nulls would make my life difficult. Just substitute unknown for null as mentioned above and the various operations with null make sense. For example, take some days and low-temperatures: Mon: 30 Tue: 10 Wed: 0 Thu: unknown Fri: 0 Sat: unknown Sun: -5 Was the low temperature the same on: Mon/Tue: no Wed/Fri: yes Thu/Fri: unknown Thu/Sat: unknown - the always seemingly confusing null=null is null. So what do we do without a null? Does the helpful app convert the unknowns to zero? That's not right. Are we forced to specify a special value like 999 for the unknown data? Then we have to add extra code to create that value when the value is unknown and more code still to check for that value when, say, looking for the lowest or average temperatures. And we're set up for disaster when someone starts measuring furnace temps instead of outdoor temps. Look no further than Y2K to see what happened to those apps that gave special meaning to 12/31/99. Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
Buy the same token, some application have no use whatsoever for the
distinction between NULL and ''. In that case, the distinction just
adds work.
I would love to see different ways to handle NULL implemented by the
server. For what I do, NULL could always compare equal to zero and ''.
I have no use for NULL in text values. I do need it for numerics,
however it doesn't mean unknown, it just means not entered, which is
different because I always treat it as zero.
I haven't put enough thought into this to make any sort of comprehensive
proposal, but it occurs to me that perhaps it could be integrated into
the type system. If I were able to specify, for any given type, a value
that should compare equal to NULL ('' for varchar, 0 for int4, for
example), that, in combination with NOT NULL constraints, might just do
it for me.
-Glen
Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute unknown for null as mentioned above and the various
operations with null make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown - the always seemingly confusing null=null is null.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Alvaro Herrera wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? I believe it would suffer less outage yes. How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I don't know, I believe citizideum or whatever it is called is PostgreSQL based. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/07 19:04, Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? But you still need NULL/UNKNOWN for outer joins. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3kkVS9HxQb37XmcRAv7yAJ9Py3X/WGt+qe7R8WF1zyFIO38tNQCfZBaN GCWtuQ48h1dh5eTL5TSv0cA= =oFIk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Joshua D. Drake escribió: Alvaro Herrera wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? I believe it would suffer less outage yes. And how is SourceForge doing these days, by the way? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Alvaro Herrera wrote: Joshua D. Drake escribió: Alvaro Herrera wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? I believe it would suffer less outage yes. And how is SourceForge doing these days, by the way? Wonderful of course :) Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
On 2/23/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Well, there is that ... I didn't throw that in because I think MySQL is great, on the contrary - we're having some issues with it here at work (don't ask), but it is being used for large installations, too. And then of course there is this http://www.linuxquestions.org/questions/showthread.php?p=2141344#post2141344 post where this guy (no idea whether he is (or was) what he claimed to be) rambling on about how MySQL is better than PostgreSQL. Joshua D. Drake Cheers, Andrej ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
I'm sorry maybe I missed something, but if you don't need NULLs and
feel they just add extra work, why don't you just declare all your
columns to be not null and have them default to zero or an empty string?
On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:
Buy the same token, some application have no use whatsoever for the
distinction between NULL and ''. In that case, the distinction
just adds work.
I would love to see different ways to handle NULL implemented by
the server. For what I do, NULL could always compare equal to zero
and ''. I have no use for NULL in text values. I do need it for
numerics, however it doesn't mean unknown, it just means not
entered, which is different because I always treat it as zero.
I haven't put enough thought into this to make any sort of
comprehensive proposal, but it occurs to me that perhaps it could
be integrated into the type system. If I were able to specify, for
any given type, a value that should compare equal to NULL ('' for
varchar, 0 for int4, for example), that, in combination with NOT
NULL constraints, might just do it for me.
-Glen
Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute unknown for null as mentioned above and the
various
operations with null make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown - the always seemingly confusing null=null is null.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Ben wrote:
I'm sorry maybe I missed something, but if you don't need NULLs and feel
they just add extra work, why don't you just declare all your columns to
be not null and have them default to zero or an empty string?
Stop making sense!
Joshua D. Drake
On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:
Buy the same token, some application have no use whatsoever for the
distinction between NULL and ''. In that case, the distinction just
adds work.
I would love to see different ways to handle NULL implemented by the
server. For what I do, NULL could always compare equal to zero and
''. I have no use for NULL in text values. I do need it for
numerics, however it doesn't mean unknown, it just means not
entered, which is different because I always treat it as zero.
I haven't put enough thought into this to make any sort of
comprehensive proposal, but it occurs to me that perhaps it could be
integrated into the type system. If I were able to specify, for any
given type, a value that should compare equal to NULL ('' for varchar,
0 for int4, for example), that, in combination with NOT NULL
constraints, might just do it for me.
-Glen
Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute unknown for null as mentioned above and the various
operations with null make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown - the always seemingly confusing null=null is null.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
sounds like you aren't happy with one of the products your company offers at http://www.commandprompt.com/community/plphp/ - plphp stands for procedural language php. the language has the php engine at its core and provides php scripting support for procedures and functions in postgresql. written by command prompt, inc. plphp is open source and licensed under the php license and the postgresql (bsd) license. maybe you should also remove php 5.1.2 from http://planetpostgresql.org/-hosting provided by the postgresql company, command prompt, inc. our very own http://www.postgresql.org/ also uses php 5.2.0. yeah yeah tco blah blah blah. try stopping support for php and watch the use plummet. and all this crap about php not having a firm design philosophy. hey, it works well and is very widely adopted. so maybe that's what a good language design should have- no firm set-in-stone straitjacket philosophy. somebody else made a point about how jsp is better than php and then went onto prove the reverse. if you design a language that has such a high tco that common isps can't use it, it ain't good design- design doesn't work in isolation. speaking of obfuscatingly bloated 90s code- there're quite a few jsp programmers who can't stomach getting kicked around by php, ajax and flash. to tie this back to postgresql - want to grab the market share from mysql? fork your strategy - postgresql v oracle and postgresqlite v mysql. jzs On 2/22/07, Joshua D. Drake [EMAIL PROTECTED] wrote: John Smith wrote: On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: MySQL: the PHP of databases. 'd appreciate if you stick to the subject. Oops he probably should not have used MySQL because it is trademarked... mysql: The PHP of databases ;) Sincerely, Joshua D. Drake jzs
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I installed wikipgdia for the WPLUG wiki: http://wplug.ece.cmu.edu/wiki/ We haven't had a lick of trouble with it since it went up. I don't believe it's experienced any downtime in many months. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
John Smith wrote: sounds like you aren't happy with one of the products your company offers at http://www.commandprompt.com/community/plphp/ - plphp stands for procedural language php. the language has the php engine at its core and provides php scripting support for procedures and functions in postgresql. written by command prompt, inc. plphp is open source and licensed under the php license and the postgresql (bsd) license. maybe you should also remove php 5.1.2 from http://planetpostgresql.org/-hosting provided by the postgresql company, command prompt, inc. our very own http://www.postgresql.org/ also uses php 5.2.0. yeah yeah tco blah blah blah. try stopping support for php and watch the use plummet. *cough*, you will note that I have already made the argument *for* PHP if you read my posts. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
On 2/20/07, gustavo halperin [EMAIL PROTECTED] wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? How about the fact that MySQL accepts the following query as legal: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo And produces, naturally, an unexpected result instead of an error. Totally annoying, I don't know if it was ever fixed. It seems that MySQL's parser is generally weak at syntax validation in it's default configuration. -- Chad http://www.postgresqlforums.com/
Re: [GENERAL] postgresql vs mysql
This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY to the sql_mode setting. As Ron mentioned though that can be happily overridden on a per-session basis so it's not as 'strict' as it makes out... Chad Wagner wrote: On 2/20/07, *gustavo halperin* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? How about the fact that MySQL accepts the following query as legal: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo And produces, naturally, an unexpected result instead of an error. Totally annoying, I don't know if it was ever fixed. It seems that MySQL's parser is generally weak at syntax validation in it's default configuration. -- Chad http://www.postgresqlforums.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote:
Adam Rich [EMAIL PROTECTED] writes:
I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively.
Really?
[EMAIL PROTECTED] ~]$ mysql test
... snip ...
It gets better: The problem is not just feb 35, it's also that it doesn't warn
you that it didn't like the input format:
[head sep-head 08:49]$ mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.33 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql create table test ( td DATE );
Query OK, 0 rows affected (0.11 sec)
mysql insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql select * from test;
++
| td |
++
| -00-00 |
++
1 row in set (0.00 sec)
mysql insert into test values ('17-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
++
2 rows in set (0.01 sec)
mysql insert into test values ('2007-02-19');
Query OK, 1 row affected (0.00 sec)
mysql select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
| 2007-02-19 |
++
3 rows in set (0.00 sec)
mysql insert into test values ('2007-02-35');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
| 2007-02-19 |
| -00-00 |
++
4 rows in set (0.00 sec)
mysql
--
--
Jan de Visser [EMAIL PROTECTED]
Baruk Khazad! Khazad ai-menu!
--
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:
It gets better: The problem is not just feb 35, it's also that it doesn't
warn
you that it didn't like the input format:
Actually it did, sort of.
mysql insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)
^
mysql show warnings;
+-+--+-+
| Level | Code | Message |
+-+--+-+
| Warning | 1265 | Data truncated for column 'td' at row 1 |
+-+--+-+
1 row in set (0.00 sec)
Not as good as ERROR: hey bonehead, there ain't no such date but
at least it's something :-)
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
On Tue, 2007-02-20 at 15:25, gustavo halperin wrote: Hello I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? My personal peeves: It's got a query parser that's dumb as a brick. Basically it seems to work like this: Got an index? Cool, use it. Complex queries quickly bog down on large data sets in MySQL. Just read the database forums at phpbuilder.com or anywhere else that people use mysql a lot and you'll see request after request to fix up a query performance-wise that PostgreSQL would run with decent speed. Further, the output of Explain is damned near useless. You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really big table, you need that much space free on the drive to alter the table for the rewrite that's going to take place. It swallows column level foreign key contraints and does nothing with them, no errors nothing, even if you're defining innodb tables. I.e. this produces not errors: mysql create table a (id int primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql create table b (a_id int references a(id)) engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql insert into a values (1); Query OK, 1 row affected (0.03 sec) mysql insert into b values (1); Query OK, 1 row affected (0.03 sec) mysql insert into b values (2); Query OK, 1 row affected (0.03 sec) That last statement should fail. Or the creation of table b should throw a warning. Or something. This is with 5.0.19. So, innodb tables pay for the sins of the fathers (i.e. myisam tables) and by extension, so do you. My main gripe about MySQL is that it teaches you bad habits. It plays loose and fast with your data, and teaches you to do that too. If it was a lot faster than PostgreSQL (like it was back in the days of 7.1 or 7.2) it might be worth the effort to overcome its shortcomings, but it's not. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Scott Marlowe wrote: You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really big table, you need that much space free on the drive to alter the table for the rewrite that's going to take place. Forgive a dumb question: What does postgresql do with ALTER TABLE? What sort of modifications do not require time proportional to the number of rows in the table? Jack Orenstein ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote: Scott Marlowe wrote: You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really big table, you need that much space free on the drive to alter the table for the rewrite that's going to take place. Forgive a dumb question: What does postgresql do with ALTER TABLE? What sort of modifications do not require time proportional to the number of rows in the table? It's an interesting subject, and it's not a dumb question. In PostgreSQL, indexes live in another file than the table. In MySQL they are part of the main table file with myisam tables. I don't know what innodb does in this regard. The only thing I can think of that rewrites a whole postgresql table would be reindexing it, or an update without a where clause (or a where clause that includes every row). Normal operations, like create index, add column, drop column, etc do not need to rewrite the table and happen almost instantly. For instance, on a table with about 30 columns and 100,000 rows, I can add a column this fast: alter table brs add column a int; ALTER TABLE Time: 57.052 ms alter table brs rename column b to c; ALTER TABLE Time: 33.281 ms alter table brs drop column c; ALTER TABLE Time: 31.065 ms Of course, mvcc (which both postgresql and innodb use) have other issues, like doubling the table size if you update every row until the dead tuples can be reclaimed. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql vs mysql
At 07:31 PM 2/21/2007, Chad Wagner wrote: On 2/20/07, gustavo halperin mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? How about the fact that MySQL accepts the following query as legal: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo And produces, naturally, an unexpected result instead of an error. Totally annoying, I don't know if it was ever fixed. It seems that MySQL's parser is generally weak at syntax validation in it's default configuration. ** syntax/misc gotchas Too many. See other emails. Or search for MySQL gotchas. ** Feature gotchas At first look MySQL seems to have all sorts of nice features and great performance. BUT, when you start to get to the details, too often you'd find that some features aren't so compatible with others or take a bit (lot?) more effort to get working properly. Want transactions? Use innoDB. Want to restore a multi-gigabyte database fast from backups, sure use MyISAM (too many people seem to have probs doing that with innoDB). Want foreign keys to work? Use innoDB. MyISAM tables allow you to specify foreign keys but ignores AND forgets them. You can mix MyISAM tables with innoDB tables in the same database. That's a minus. Want to back up a consistent snapshot of the database AND still have users using the database live? Use only InnoDB tables. Because to ensure consistency when dumping MyISAM tables you should lock all the tables involved. You still want a live consistent backup of a database with some MyISAM tables? Here's a method I suggested: use multiple MySQL servers with replication - do the backup snapshot off a slave, while users are using the master (or other slaves). If anyone has better ideas do let me know :). Do not use innoDB on a filesystem that does not support files 2GB in size. Though MySQL +innoDB supports a configurable like autoextend:max:1000M, this only works if you using a single shared tablespace, doesn't work if you are using one innodb_file_per_table. BUT if you are using a single shared tablespace be aware that you can't easily shrink such tablespaces and reclaim unused space. Too many IFs, BUTs, ONLYs, etc. ** D'oh level release gotchas Example: Before MySQL 5.0.13, GREATEST(x,NULL) and LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13, both functions return NULL if any argument is NULL, the same as Oracle. This change can cause problems for applications that rely on the old behavior. Or release 5.0.19: The InnoDB storage engine no longer ignores trailing spaces when comparing BINARY or VARBINARY column values. This means that (for example) the binary values 'a' and 'a ' are now regarded as unequal any time they are compared, as they are in MyISAM tables. (Bug#14189) ** Commercial/strategic gotchas Oracle owns the companies that make the transactional backends for MySQL (innoDB, sleepycat). ** Conclusion In my opinion, if you don't have anything that specifically requires MySQL, but where MySQL is suggested, it's better to use Postgresql. Not saying Postgresql is perfect - rather that MySQL makes Postgresql look really good. Unfortunately, I have to deal with MySQL at work. *sigh*. Regards, Link. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
At 12:02 AM 2/22/2007, Scott Marlowe wrote: You can't change a table in any way without rewriting the whole thing, resulting in a very long wait and a complete table lock on any alter table action on big tables. Don't forget that if you've got a really Oh yeah, that reminds me. rewriting the whole thing means in most cases the _entire_ table is temporarily _duplicated_ (with all the associated increased space requirements)![1] WORSE: This happens if you are creating or deleting indexes, or even changing a column definition! So say you have a 40GB table, and have 30GB free space. Life is good right? Then someone makes a reasonable request - Big Boss wants an important report sped up, and it turns out you just need to create an index. Enjoy :). Running low on space and think you can get more space by deleting some unused indexes? Probably not a good idea! And even if disk space is cheap, IO bandwidth usually isn't... Regards, Link. [1] If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table http://dev.mysql.com/doc/refman/5.0/en/alter-table.html MySQL: the PHP of databases. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
It's got a query parser that's dumb as a brick. While we're on this topic... I have a question on these series of queries: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) from customers c where customer_id not in ( select customer_id from orders) -- Query C select count(*) from customers c left join orders o on c.customer_id = o.customer_id where o.order_id is null I believe they all achieve the same thing. i.e. How many customers have never placed an order? I ran these 3 on MySQL PG with the following results: Query A: MySQL=4.74s PostgreSQL=4.23s Query B: MySQL=4.64s PostgreSQL=? Query C: MySQL=5.07s PostgreSQL=3.39s MySQL's time is pretty consistent for all 3. As you said, the output from explain is pretty useless so there's not much else to look at. PostgreSQL runs AC slightly faster, which I expected. However, waiting for query B exceeded my patience and I had to abort it. The explain output is below, is this result due to some incorrect setting? benchdb=# explain select count(*) from customers c benchdb-# where customer_id not in ( select customer_id from orders); QUERY PLAN Aggregate (cost=16406564027.00..16406564027.01 rows=1 width=0) - Seq Scan on customers c (cost=41578.00..16406562777.00 rows=50 width=0) Filter: (NOT (subplan)) SubPlan - Materialize (cost=41578.00..69391.00 rows=200 width=4) - Seq Scan on orders (cost=0.00..31765.00 rows=200 width=4) (6 rows) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Wed, 2007-02-21 at 10:54, Adam Rich wrote: It's got a query parser that's dumb as a brick. While we're on this topic... I have a question on these series of queries: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) from customers c where customer_id not in ( select customer_id from orders) -- Query C select count(*) from customers c left join orders o on c.customer_id = o.customer_id where o.order_id is null I believe they all achieve the same thing. i.e. How many customers have never placed an order? I ran these 3 on MySQL PG with the following results: Query A: MySQL=4.74s PostgreSQL=4.23s Query B: MySQL=4.64s PostgreSQL=? Query C: MySQL=5.07s PostgreSQL=3.39s MySQL's time is pretty consistent for all 3. As you said, the output from explain is pretty useless so there's not much else to look at. PostgreSQL runs AC slightly faster, which I expected. However, waiting for query B exceeded my patience and I had to abort it. The explain output is below, is this result due to some incorrect setting? Nope, more like incorrect usage / inability to optimize by postgresql due to architecture. The B query (like the B arc) is a bad choice here because PostgreSQL has to actually create a giant OR list of all the customer_ids from order. But the queries I was referring to were more along the lines of multiple level subselect queries with lots of aggregation on the outside, the kind used for business intelligence reporting. There might be some optimization trick for the B query I'm not familiar with (cause every time I turn around, Tom has gone and made the query optimizer smarter) but I haven't heard of it. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Adam Rich wrote: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) from customers c where customer_id not in ( select customer_id from orders) -- Query C select count(*) from customers c left join orders o on c.customer_id = o.customer_id where o.order_id is null I believe they all achieve the same thing. I think not. When using INSERT INTO customers VALUES (1); INSERT INTO customers VALUES (2); INSERT INTO customers VALUES (NULL); and INSERT INTO orders VALUES (1); INSERT INTO orders VALUES (3); INSERT INTO orders VALUES (NULL); I get Query A: 2 Query B: 0 Query C: 3 -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
On 2/21/07, Lincoln Yeoh lyeoh@pop.jaring.my wrote: At 07:31 PM 2/21/2007, Chad Wagner wrote: On 2/20/07, gustavo halperin mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? How about the fact that MySQL accepts the following query as legal: SELECT foo, bar, COUNT(*) FROM baz GROUP BY foo And produces, naturally, an unexpected result instead of an error. Totally annoying, I don't know if it was ever fixed. It seems that MySQL's parser is generally weak at syntax validation in it's default configuration. ** syntax/misc gotchas Too many. See other emails. Or search for MySQL gotchas. ** Feature gotchas At first look MySQL seems to have all sorts of nice features and great performance. BUT, when you start to get to the details, too often you'd find that some features aren't so compatible with others or take a bit (lot?) more effort to get working properly. boy, you hit the nail on the head. mysql supports views and subqueries, but apparently not at the same time. also, complex views (such as you can write without subqueries) tend to run slower than identical counterpart in .sql. mysql supports pl/psm (yay) but unfortunately no FOR loops (yikes). the mysql planner is an unpredictable thing, producing huge surprises to the upside and the downside...however taken as a whole it is a completely inferior planner. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/21/07 08:42, Michael Fuhr wrote:
On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:
It gets better: The problem is not just feb 35, it's also that it doesn't
warn
you that it didn't like the input format:
Actually it did, sort of.
mysql insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)
^
mysql show warnings;
+-+--+-+
| Level | Code | Message |
+-+--+-+
| Warning | 1265 | Data truncated for column 'td' at row 1 |
+-+--+-+
1 row in set (0.00 sec)
Not as good as ERROR: hey bonehead, there ain't no such date but
But it *inserts the data*!
at least it's something :-)
Sure, at the interactive command line.
What kind of error code does this return to applications? Can a PHP
or C programmer catch this warning, or does MySQL return a success code?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF3KFES9HxQb37XmcRAlyNAKCiEIAbywwa3jL0q1jlnx+9AfZVIwCg4dOu
cdgyFYs1ECl9Jh7JJ7XLZ9Y=
=ioTM
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
IMX, the only things going for MySQL are: 1. It's fast. 2. It's easy to install and administer. 3. It's cheap and cross-platform. 4. It's popular. The problem is: 1. It's fast because fsync is off by default, and MyISAM is not transactional and doesn't support basic features like foreign keys. That basically means it's fast because it ignores Boyd and Cobb. Guess what? The same can be said of flat files. 2. Most other RDBMSs have seen the advantage and done this now, too. Installing an RDBMS is no longer more difficult than installing the rest of the system. 3. MySQL is no longer the only thing available. PostgreSQL is on Windows now, MS SQL 2005 Express, SQLite, Oracle Express, Firebird, etc. 4. So is Windows. MySQL isn't quite as bad as PHP for internal inconsistencies and developer aggrivations, but it comes close enough for me to want to avoid them both. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gustavo halperin Sent: Tuesday, February 20, 2007 4:26 PM To: pgsql-general@postgresql.org Subject: [GENERAL] postgresql vs mysql Hello I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? Thank you, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ //| _ _ | \\/ `/ `.|| /~\\ \//~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ |||| ] \ | o|o | / ] Y |||| Y [ \___\_--_ /_/__/ | \_|l,--.l|_/ | /.-\() /--.\ | ' ` | `--(__)' \ (/~`----'~\) / U// U / \ `-_-__-_-'/ \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/`.`' / [ /__`--'__\ |`-'| /\(__,-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ ^\ /^ | ` ( \\ _\ -__- /_ ,-\ ,-~~-. \ `:.___,/ (___\/___) (/()`---' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
How would you like to use a database that has nuances like these -- http://forums.mysql.com/read.php?20,141120,141120#msg-141120 ep ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/21/07 18:09, Erick Papadakis wrote: How would you like to use a database that has nuances like these -- http://forums.mysql.com/read.php?20,141120,141120#msg-141120 Huh? A blank string (does that mean '' or ' '?) is not NULL, so of *course* it should pass the NOT NULL constraint. Or am I missing something? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3OFHS9HxQb37XmcRAsdwAJ9Ew3pb2huydeP14Bn8NsWuWn1TnACgw+Ru qD0UuPJcJukugpER51HMXDs= =DHkn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
