Thread: 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 |--:.
'\/ <^\ /^> | ` ( < \\
_\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/
(___\ /___) (____/ (____) `---'
On 2/20/07, gustavo halperin <ggh.develop@gmail.com> 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 ? > in MySQL if you have tables MyISAM they will ignore all rollback commands you exexute, so you will have inconsistencies in databases... worst, if you have tables MyISAM and tables InnoDB the first will ignore all rollback commands and the laters won't... of course the legendary speed in mysql can be obtained if you use tables MyISAM :( some other issues (some of them had been resolved in 5.x i don't know wich ones) http://sql-info.de/mysql/gotchas.html one last thing mysql team doesn't afraid to change behaviours between minor releases, look at this thread http://archives.postgresql.org/pgsql-general/2005-12/msg00487.php http://dev.mysql.com/doc/refman/5.1/en/join.html (Join Processing Changes in MySQL 5.0.12) -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook
> one last thing mysql team doesn't afraid to change behaviours between > minor releases, look at this thread > That is so true, all the differences between minor versions made creating Lightning Admin for MySQL a pain in the rear... After I did the port I really appreciated how clean PostgreSQL is. -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/20/07 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 ? The only reason I'd need is that MySQL (even InnoDB) lets you accidentally insert intrinsically bad data. According to the official v5 docs, it's the app programmer's fault if s/he tries to insert 35-Feb-2007 into the database. MySQL will purposefully convert it to '0000-00-00'. http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J 98mWmmEqtFKGaDX4ZvU87J4= =EPxL -----END PGP SIGNATURE-----
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."
mysql> use test
Database changed
mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.0.27-standard |
+-----------------+
1 row in set (0.00 sec)
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 02/20/07 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 ?
The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '0000-00-00'.
http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
"Adam Rich" <adam.r@sbcglobal.net> 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?
[tgl@rh2 ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32 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.01 sec)
mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
mysql>
Note that this case is *not* testing whether mysql knows that
February has less than 31 days.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/20/07 23:59, Adam Rich wrote: > 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." Only if you set sql modes STRICT_TRANS_TABLES and STRICT_ALL_TABLES (which *still* allow bogus dates like 2007-02-00!!) or TRADITIONAL. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html It's (finally) a step in the right direction, but is really only a "pretty please", since SQL modes are session-time changeable. > > mysql> use test > Database changed > mysql> create table test ( td DATE ); > Query OK, 0 rows affected (0.01 sec) > mysql> insert into test values ('35-Feb-2007'); > ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td' > at row 1 > mysql> select version(); > +-----------------+ > | version() | > +-----------------+ > | 5.0.27-standard | > +-----------------+ > 1 row in set (0.00 sec) > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson > Sent: Tuesday, February 20, 2007 11:35 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] postgresql vs mysql > > > On 02/20/07 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 ? > > The only reason I'd need is that MySQL (even InnoDB) lets you > accidentally insert intrinsically bad data. According to the > official v5 docs, it's the app programmer's fault if s/he tries to > insert 35-Feb-2007 into the database. MySQL will purposefully > convert it to '0000-00-00'. > > http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html > - ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings - ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF2+SQS9HxQb37XmcRAqh4AJwJz41yaTzIkqcAIr1wi7gK7J1QPACgvl07 fVNXVeoJo4vWhbIeGWM5MWs= =Px12 -----END PGP SIGNATURE-----
On 2/20/07, gustavo halperin <ggh.develop@gmail.com> wrote:
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/
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/
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* <ggh.develop@gmail.com > <mailto:ggh.develop@gmail.com>> 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/
On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote:
> "Adam Rich" <adam.r@sbcglobal.net> 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?
>
> [tgl@rh2 ~]$ 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 |
+------------+
| 0000-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 |
+------------+
| 0000-00-00 |
| 0000-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 |
+------------+
| 0000-00-00 |
| 0000-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 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 2007-02-19 |
| 0000-00-00 |
+------------+
4 rows in set (0.00 sec)
mysql>
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------
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
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.
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
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.
At 07:31 PM 2/21/2007, Chad Wagner wrote: >On 2/20/07, gustavo halperin ><<mailto:ggh.develop@gmail.com>ggh.develop@gmail.com> 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.
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.
> 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 A&C 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=500000 width=0)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=41578.00..69391.00 rows=2000000
width=4)
-> Seq Scan on orders (cost=0.00..31765.00
rows=2000000 width=4)
(6 rows)
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 A&C 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.
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/
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:ggh.develop@gmail.com>ggh.develop@gmail.com> 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
-----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-----
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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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.
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
-----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-----
Chad Wagner wrote: > On 2/20/07, *gustavo halperin* <ggh.develop@gmail.com > <mailto:ggh.develop@gmail.com>> 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. That one actually comes in handy ;) Especially in older versions (4.0) that don't support subselects.. -- Postgresql & php tutorials http://www.designmagick.com/
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) I had a similar sort of query and got told that work_mem is the most important thing here. See http://archives.postgresql.org/pgsql-general/2007-02/msg00986.php -- Postgresql & php tutorials http://www.designmagick.com/
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 Err - an empty string is not the same as null, so that is perfectly valid. Null means unknown, an empty string is not unknown - it's a known value (which happens to be nothing, but it's still known). -- Postgresql & php tutorials http://www.designmagick.com/
>> 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? --- I agree with you, although Oracle won't, they really blurred the line between the empty string and NULL. As for MySQL maybe this article makes more sense: http://www.databasejournal.com/features/mysql/article.php/3519116 Mike.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/21/07 19:01, Brusser, Michael 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? > > --- > > I agree with you, although Oracle won't, they really blurred the line > between the empty string and NULL. Well that bites. We don't use NULLs a lot, but still: NULL is null, not a blank string. How do "they" represent NULL in a numeric field? -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3O8cS9HxQb37XmcRAgk3AJ9uB0Z0X9tUOdSgeBggC1UivekXhACgsaLr ZVYsQ5NaFCuCwgER6mboH70= =2arU -----END PGP SIGNATURE-----
On Wed, Feb 21, 2007 at 01:45:08PM -0600, Ron Johnson wrote: > On 02/21/07 08:42, Michael Fuhr wrote: > > Not as good as "ERROR: hey bonehead, there ain't no such date" but > > But it *inserts the "data"*!!!!! I didn't say otherwise and I'm not defending MySQL's behavior. I was simply refuting the statement that "it doesn't warn you that it didn't like the input format." > > 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? Beats me; I care about my data so I don't use MySQL. Since it's a warning I expect the query returns success. The C API has a mysql_warning_count() function that appears to be exposed in PHP's mysqli extension as mysqli_warning_count. That C function doesn't appear in the source code for any of the other MySQL extensions in PHP 5.2.1. -- Michael Fuhr
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? Thanks. On 2/22/07, Ron Johnson <ron.l.johnson@cox.net> wrote: > -----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?
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? Just an example: middle name = '' mean no middle name middle name = NULL mean "dB does not know if there is or not middle name" I hope you understand the difference between empty and null. and for numbers is other thing....in average for example null is not considered, other values, yes > > Thanks. > > > > On 2/22/07, Ron Johnson <ron.l.johnson@cox.net> wrote: >> -----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? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Thu, 22 Feb 2007, Erick Papadakis wrote: > Why should a NULL be different from an empty string, what's the big > mysterious difference? Long ago and far away, when I was in the Army, we had quite a few soldiers whose name took the form 'John NMI Doe.' That's because the Army -- even before computers were ubiquitous -- needed something in the forms for middle initials. If you did not have a middle name or initial, one was assigned to you: NMI (No Middle Initial). This did not mean that the middle initial was unknown (NULL), but that it did not exist (blank). If you have a credit card, would you prefer the balance to be blank or unknown? How would the bank select all those with no balance rather than those for whom a balance is unknown? I'm sure you can think of dozens of more situations like these. 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
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. In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) > 0)); though that allows a single space in.. See http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html and http://www.postgresql.org/docs/8.2/interactive/ddl-alter.html#AEN2302 No idea about how to do this in mysql, search their documentation. -- Postgresql & php tutorials http://www.designmagick.com/
> 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?
Nulls are also useful for data that is imported where there is not always a way to determine
certain field values for a record.
Now suppose in a query you want to return all record that meet your criteria as well as any that
might meet your criteria depending if the null value was actually known.
Select * from table
Where ((field1,field2,field3) = ('farmer','baker','shoemaker')) is unknown;
Now you can get all records that may meet your criteria.
Regards,
Richard Broersma Jr.
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). -- Postgresql & php tutorials http://www.designmagick.com/
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
Chris wrote: > > 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. > Just to expand on that (and to drag this thread out a little longer), i find that a pretty good way to get across this difference is to refer to it as *the* empty string. I prefer this description because it is implicit that it is still a string. NULL has no type, regardless of the column it is inserted into. Thus, the empty string is NOT NULL. brian
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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=== 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/
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 majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> > > >
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) <> '')
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.
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
-----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-----
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.
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
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
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
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
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
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.
Attachment
2007/2/22, Russ Brown <pickscrape@gmail.com>: > 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
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
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
>>>>> "Russ" == Russ Brown <pickscrape@gmail.com> 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!
totally off topic, Tim Tassonis schrieb: > 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? No, python, java ;) > 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 Well no. PHP is not a professional language because it has no really design - and that has nothing to do with the fact it beeing a scripting language. Its a bad scripting language. (Say namespaces for example, confusing function interfaces, unicode flaws, missing usable frameworks, silly type handling, quoting hell) > Perl any day, as it is syntactically much cleaner and performs > sufficiently well for usual scripting needs. ah... yes. Dont like perl either but its at least carrying some actual language design. > Of course, I wouldn't write an operating system with it. Would you write a language with it? :-) Btw, "professional programmers" can indeed use funny languages - they are professional by they earning their living with it. T.
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.
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.
Tino Wildenhain wrote: > > totally off topic, > > Tim Tassonis schrieb: >> 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? > > No, python, java ;) > >> 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 > > Well no. PHP is not a professional language because it has no really > design - and that has nothing to do with the fact it beeing a scripting > language. Its a bad scripting language. (Say namespaces for example, > confusing function interfaces, unicode flaws, missing usable frameworks, > silly type handling, quoting hell) - What do you mean by confusing function interfaces and unicode flaws? - A lot of "professional" languages don't support namespaces and frameworks are not part of a language, as I understand a language. I think we really have different ideas about professional, can you point me to a reference of your definition? I'd definitely say that php is not really an all-purpose language, but that doesn't make it unprofessional to me. C is not all-purpose, but still professional. > >> Perl any day, as it is syntactically much cleaner and performs >> sufficiently well for usual scripting needs. > > ah... yes. Dont like perl either but its at least carrying some > actual language design. Like what, as opposed to php? > >> Of course, I wouldn't write an operating system with it. > > Would you write a language with it? :-) No, I actually solely write scripts with it :-) And web applications. Guess what most people use php for. > > Btw, "professional programmers" can indeed use funny languages > - they are professional by they earning their living with it. I have yet to see an unfunny language.
>> Well no. PHP is not a professional language because it has no really
>> design - and that has nothing to do with the fact it beeing a scripting
>> language. Its a bad scripting language. (Say namespaces for example,
>> confusing function interfaces, unicode flaws, missing usable frameworks,
>> silly type handling, quoting hell)
>
> - What do you mean by confusing function interfaces and unicode flaws?
This whole discussion is about a language lawyer and a professional. The
reality is, professional programmers do use PHP. I would say probably
more than any other language out there.
Does that mean that PHP is a technically sound language? No.
Does that mean some of these professional programmers are smarter than
the language lawyers, because they can get their web apps done, quickly
and have a huge community backing them up? Yes.
Use what works for you, don't listen to others about what language to
code in.
Sincerely,
Joshua D. Drake
P.S. I don't particularly like PHP either, but our company website is
coded in it because no other language (for the web) could have done the
job at the same TCO.
--
=== 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/
Joshua D. Drake wrote: >>> Well no. PHP is not a professional language because it has no really >>> design - and that has nothing to do with the fact it beeing a scripting >>> language. Its a bad scripting language. (Say namespaces for example, >>> confusing function interfaces, unicode flaws, missing usable frameworks, >>> silly type handling, quoting hell) >> - What do you mean by confusing function interfaces and unicode flaws? > > > This whole discussion is about a language lawyer and a professional. The > reality is, professional programmers do use PHP. I would say probably > more than any other language out there. > > Does that mean that PHP is a technically sound language? No. > > Does that mean some of these professional programmers are smarter than > the language lawyers, because they can get their web apps done, quickly > and have a huge community backing them up? Yes. > > Use what works for you, don't listen to others about what language to > code in. > > Sincerely, > > Joshua D. Drake > > P.S. I don't particularly like PHP either, but our company website is > coded in it because no other language (for the web) could have done the > job at the same TCO. > > PHP is easy and cheap to start, so there are lots of programmers using it, and someone like you, or any other company, can take a cheap programmer to do the work. Most of programmer use it with mysql, now this is the question to answer....why? Maybe there is something else that can be useful for PostgreSQL (this list is about postgres right?). Now, comparing MySQL and PostgreSQL is something that maybe does not make sense....but the question is why most newbies go to MySQL and not PostgreSQL and say that MySQL is faster, better, easier and so on than pgsql.... Answer for this is a bit complex, more newbies howtos, more people saying that is better and so on.... And I dont agree with that, but there is something I really think is good about mysql installation and postgresql should take..... You have 4 or 5 or 6 (I dont remember) examples in doc when you install from package, or in contrib I think in source code.....so anyone with just knowledge about his hardware and usage can tune it. Why not put some examples with postgresql about that? for example for OLTP with 2 GB RAM blah blah.... Just an idea Best regards Rodrigo
Joshua D. Drake schrieb: >>> Well no. PHP is not a professional language because it has no really >>> design - and that has nothing to do with the fact it beeing a scripting >>> language. Its a bad scripting language. (Say namespaces for example, >>> confusing function interfaces, unicode flaws, missing usable frameworks, >>> silly type handling, quoting hell) >> - What do you mean by confusing function interfaces and unicode flaws? > > > This whole discussion is about a language lawyer and a professional. The > reality is, professional programmers do use PHP. I would say probably > more than any other language out there. Yes, by the definition that they earn money by doing it. > Does that mean that PHP is a technically sound language? No. > > Does that mean some of these professional programmers are smarter than > the language lawyers, because they can get their web apps done, quickly > and have a huge community backing them up? Yes. Does that mean they would not write better apps in the same time if they were so smart using better languages and actual frameworks? Do we really need so many flawed PHP products regulary appear on bugtraq for the same reasons every time? (Since the many programmers there just copy and paste their code to get their results fast - hey thats smart in some sense) > Use what works for you, don't listen to others about what language to > code in. Yes but please dont open all these works for the public ;) Thanks postgres has not coding and project standards like PHP has, otherwise we would have a MySQL2 ;) T.
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
>> P.S. I don't particularly like PHP either, but our company website is
>> coded in it because no other language (for the web) could have done the
>> job at the same TCO.
>>
>>
>
> PHP is easy and cheap to start, so there are lots of programmers using
> it, and someone like you, or any other company, can take a cheap
> programmer to do the work. Most of programmer use it with mysql, now
> this is the question to answer....why?
Easy, it was what PHP the project recommended until PHP 5. Also through
the history of the project, MySQL developers (as in the actualy MySQL
people) were also part of PHP.
And no, Command Prompt's website does not use MySQL.
>
> Maybe there is something else that can be useful for PostgreSQL (this
> list is about postgres right?).
>
> Now, comparing MySQL and PostgreSQL is something that maybe does not
> make sense....but the question is why most newbies go to MySQL and not
> PostgreSQL and say that MySQL is faster, better, easier and so on than
> pgsql....
MySQL is faster, better, easier... out of the box for the applications
that MySQL is good at, which is throw away data and websites.
The key here is that MySQL and PostgreSQL do not compete any more than
PostgreSQL and SQLLite. They are different beasts. They serve different
purposes.
> Why not put some examples with postgresql about that? for example for
> OLTP with 2 GB RAM blah blah....
That isn't the purpose of reference documentation. It is the purpose of
a tuning doc or howto and there have been plenty of those on the web.
Sincerely,
Joshua D. Drake
>
> Just an idea
>
> Best regards
>
> Rodrigo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== 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/
>> This whole discussion is about a language lawyer and a professional. The
>> reality is, professional programmers do use PHP. I would say probably
>> more than any other language out there.
>
> Yes, by the definition that they earn money by doing it.
>
>> Does that mean that PHP is a technically sound language? No.
>>
>> Does that mean some of these professional programmers are smarter than
>> the language lawyers, because they can get their web apps done, quickly
>> and have a huge community backing them up? Yes.
>
> Does that mean they would not write better apps in the same time if they
> were so smart using better languages and actual frameworks?
Define better? It is an opinion. Personally I think Ruby on Rails is a
pile of dung that is slow and surrounded by zealots who don't know reality.
I am sure Ruby on Rails people would disagree ;)
> Do we really
> need so many flawed PHP products regulary appear on bugtraq for the
> same reasons every time? (Since the many programmers there just copy and
> paste their code to get their results fast - hey thats smart in some
> sense)
You are arguing something different than I am. I have already conceded
that PHP is not technically a sound language.
But in the real world, it doesn't matter. What matters is, "How can I
get my project done, so that it works"
It doesn't matter what it looks like underneath. It matters what it
looks like on top.
It is a frosted cake made of human feces, but it is still looks
delicious from 3 feet away.
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/
At 12:54 AM 2/23/2007, Rodrigo Gonzalez wrote: >PHP is easy and cheap to start, so there are lots of programmers >using it, and someone like you, or any other company, can take a >cheap programmer to do the work. Most of programmer use it with >mysql, now this is the question to answer....why? > >Maybe there is something else that can be useful for PostgreSQL >(this list is about postgres right?). > >Now, comparing MySQL and PostgreSQL is something that maybe does not >make sense....but the question is why most newbies go to MySQL and >not PostgreSQL and say that MySQL is faster, better, easier and so >on than pgsql.... > >Answer for this is a bit complex, more newbies howtos, more people >saying that is better and so on.... Yeah. Would be good if we can figure out something that would help postgresql increase its usage or mind share. At least so that even the big bosses might be fine with using postgresql. I'd really rather use postgresql. Back in the postgres95 days, MySQL just won hands down. Then it started getting much better for 6.5.x then from 7.2 onwards things started rapidly getting better and better.. Oh well... Link.
>> Answer for this is a bit complex, more newbies howtos, more people
>> saying that is better and so on....
>
> Yeah. Would be good if we can figure out something that would help
> postgresql increase its usage or mind share.
O.k. this is bizarre. One, this discussion belongs on -advocacy not
-general.
Two, you do realize that we have huge mind share right?
>
> At least so that even the big bosses might be fine with using postgresql.
Oh you mean like a good portion of the largest entities in the world
already do?
Not to be sarcastic but it sounds to me like your boss isn't paying
attention.
I spent Tuesday at one of the largest companies in the world discussion
how we can deploy PostgreSQL enterprise wide.
I spent the week before that teaching a class for a global 2000 that is
about to deploy postgresql (in the next 2 years) to over 5000 installations.
I will spent a week on the east coast doing the same thing for another
division of said company.
We regularly support a company that has over 4000 installations in Europe.
And that is just the tip of the iceberg.
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/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/22/07 10:40, Joshua D. Drake wrote: >>> Well no. PHP is not a professional language because it has no really >>> design - and that has nothing to do with the fact it beeing a scripting >>> language. Its a bad scripting language. (Say namespaces for example, >>> confusing function interfaces, unicode flaws, missing usable frameworks, >>> silly type handling, quoting hell) >> - What do you mean by confusing function interfaces and unicode flaws? > > > This whole discussion is about a language lawyer and a professional. The > reality is, professional programmers do use PHP. I would say probably > more than any other language out there. This is what I get for writing an email at 4AM after dying at the top of Sokoban. Sooo close to the treasure room! Anyway... Personal Home Page Tools was designed to personal home pages in the low-threat environment of 1995, the same year as MySQL. Whereas some languages and RDBMSs have a firm design philosophy, both PHP & MySQL were built as personal/small-systems tools, and then wildly expanded. Current design, security & feature issues bear out that there's a lot of ad hocery in both systems. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF3dWbS9HxQb37XmcRAmZOAKDcricsMJl3SUrfJ/s6yzAzIOOCeQCgwcLX bmxwfTonJ5xZOBSkknR03Po= =6mAB -----END PGP SIGNATURE-----
On 2/22/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
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.
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.
At 01:30 AM 2/23/2007, Joshua D. Drake wrote: > >> Answer for this is a bit complex, more newbies howtos, more people > >> saying that is better and so on.... > > > > Yeah. Would be good if we can figure out something that would help > > postgresql increase its usage or mind share. > > >O.k. this is bizarre. One, this discussion belongs on -advocacy not >-general. > >Two, you do realize that we have huge mind share right? Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major suppliers[1] of postgresql's "backend" tech yet. Link. [1] e.g. Tom Lane.
Lincoln Yeoh wrote: > At 01:30 AM 2/23/2007, Joshua D. Drake wrote: > > >Two, you do realize that we have huge mind share right? > > Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major > suppliers[1] of postgresql's "backend" tech yet. > > Link. > > [1] e.g. Tom Lane. How do you know? I'm pretty sure if Tom wanted to work for Oracle he would have sent a resume already :-) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>> O.k. this is bizarre. One, this discussion belongs on -advocacy not
>> -general.
>>
>> Two, you do realize that we have huge mind share right?
>
> Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major suppliers[1]
> of postgresql's "backend" tech yet.
Ahh, because they can't? Oracle can't stop PostgreSQL because PostgreSQL
is BSD licensed.
Sure Oracle could purchase CMD (or more likely EDB just to remove a
small thorn) but it wouldn't do anything the PostgreSQL machine.
You are mistaking pretty pictures of non-reality with hard core truth.
They are not the same.
I have never lost a contract to MySQL. I have lost contracts to Oracle.
We do not compete with MySQL.
Does MySQL have the mindshare of the ignorant? Yes.
Does MySQL have the mindhare of the knowledgeable? No.
Our mindshare is *huge* with the knowledgeable.
I will take mindshare with the knowledgeable over the ignorant, every
second of the day. They pay better.
Joshua D. Drake
>
> Link.
>
> [1] e.g. Tom Lane.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== 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/
I think a lot of the reasons people use LAMP is that inexpensive ISPs use LAMP. The reasons ISPs use LAMP as opposed to other, in my opinion more powerful tools has to do with the complexities of hosting large numbers of user applications on single machines. For instance, I don't know anybody who's ever coded in both PHP and Java/Servlet/jsp who would choose PHP. You'd have to be an idiot to choose PHP over Java/jsp. However, PHP is designed to very quickly induce an application state, run the application, and then archive the application state out of processor memory. Servlets are not designed to do that. They are designed to run on a Java Virtual Machine that remains resident indefinitely. As an ISP you have the choice to let all your users share the same application state with Java, very insecure, or run multiple jvms, very memory intensive. PHP works much better for you because apps induce their state, do what they need to do for each request, and then disappear. Similar issues with Mysql. It's faster, yet less powerful for transaction processing and data integrity than Postgresql. Well, most people who own their own servers are going be more worried about data integrity and transaction processing than pure speed. An ISP, however, wants speed less processor usage. The other thing is that Mysql has a faster connection/disconnection cycle. You can use connection pooling with Postgresql which is very fast, but again, more memory and application cycle problems. Given the choice, I think Postgresql is easier to work with, more robust, and easier to maintain than Mysql. However, if you want a $10 a month shared hosting account, you're going to almost definitely be forced to use Mysql. Issues of speed? I don't know, but I haven't reached any speed bottlenecks with Postgresql. Internet connection speeds and other application speed issues are usually the bottlenecks. It may just be what I do, but I'm definitely more worried about the integrety/security of my data than I am about speed.
At 02:16 AM 2/23/2007, Joshua D. Drake wrote: >We do not compete with MySQL. > >Does MySQL have the mindshare of the ignorant? Yes. >Does MySQL have the mindhare of the knowledgeable? No. > >Our mindshare is *huge* with the knowledgeable. > >I will take mindshare with the knowledgeable over the ignorant, every >second of the day. They pay better. The ignorant outnumber the knowledgeable by magnitudes. Lots of ignorant people paying small sums of money add up to quite a lot (politicians exploit that regularly). Also those expensive consultants find it much easier to get lots of money from ignorant people on a regular basis :). But if you're the sort who actually cares, it's easier to sleep at night the postgresql way ;). Regards, Link.
On Thu, 2007-02-22 at 12:17, Mark Walker wrote: While I'll admit to some similarities between PHP/java and mysql/pgsql, I'd say that th gulf between php and java is far less than the gulf is between mysql and pgsql. Take a list of a hundred or so db based projects written in each language. Correlate it to the db used. It's likely you'll find a better correlation to the db used than the language.
Hmm, I've never heard of an application that's written in both php and Java. However, I know of many applications that run on both mysql and postgresql. For instance phpbb which is the most common MB software is written in php and runs with either postgresql or mysql. Database server independence is definitely a goal for most non custom applications. Scott Marlowe wrote: > On Thu, 2007-02-22 at 12:17, Mark Walker wrote: > > While I'll admit to some similarities between PHP/java and mysql/pgsql, > I'd say that th gulf between php and java is far less than the gulf is > between mysql and pgsql. > > Take a list of a hundred or so db based projects written in each > language. Correlate it to the db used. It's likely you'll find a > better correlation to the db used than the language. > > >
I wasn't referring to projects written in both languages. I was referring to projects written primarily for MySQL or "real" databases (i.e. oracle, pgsql, mssql, db2, and on and on). No matter what language is used, I think you'll find that apps written primarily for mysql have poorer code than the ones written primarily for other apps. The ones that are truly transportable will generally be the best, but if they try to support MySQL AND the other real databases, you'll likely find lots of hacks in the code to keep MySQL happy, that wouldn't be needed if they didn't support it. Does that make more sense? On Thu, 2007-02-22 at 13:36, Mark Walker wrote: > Hmm, I've never heard of an application that's written in both php and > Java. However, I know of many applications that run on both mysql and > postgresql. For instance phpbb which is the most common MB software is > written in php and runs with either postgresql or mysql. Database > server independence is definitely a goal for most non custom applications. > > Scott Marlowe wrote: > > On Thu, 2007-02-22 at 12:17, Mark Walker wrote: > > > > While I'll admit to some similarities between PHP/java and mysql/pgsql, > > I'd say that th gulf between php and java is far less than the gulf is > > between mysql and pgsql. > > > > Take a list of a hundred or so db based projects written in each > > language. Correlate it to the db used. It's likely you'll find a > > better correlation to the db used than the language. > > > > > > > > > ---------------------------(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
You're probably right. A good example of that is the difference between the excellent pgadmin and the desktop mysql administrator which is very buggy and strangely laid out. Whenever I have to deal with mysql I get the feeling I'm messing around with a bunch of hacks. It's very strange to deal with. Simple things like just granting access to a database you have to issue perhaps 3 different commands some from the os command line, some from the db interperter. I think people who understand the importance of administrative simplicity probably also make good coders and vice versa. Scott Marlowe wrote: > I wasn't referring to projects written in both languages. I was > referring to projects written primarily for MySQL or "real" databases > (i.e. oracle, pgsql, mssql, db2, and on and on). No matter what > language is used, I think you'll find that apps written primarily for > mysql have poorer code than the ones written primarily for other apps. > The ones that are truly transportable will generally be the best, but if > they try to support MySQL AND the other real databases, you'll likely > find lots of hacks in the code to keep MySQL happy, that wouldn't be > needed if they didn't support it. > > Does that make more sense? > >
>>>>> "Mark" == Mark Walker <furface@omnicode.com> writes: Mark> Similar issues with Mysql. It's faster, But it doesn't matter *how* fast you get the *wrong* answer. :) -- 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!
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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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
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.
>>>If you don't know something, why are you trying to record it? From a strict relational sense, the existence of NULL
valuesin 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
exampleto 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
deletesomething 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;
}
Re: Checking for string data that makes sense Re: postgresql vs mysql
From
"Shashank Tripathi"
Date:
> 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!
Re: Checking for string data that makes sense Re: postgresql vs mysql
From
"Shashank Tripathi"
Date:
On 22/02/07, Shashank Tripathi <shanx@shanx.com> 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
> > Mark> Similar issues with Mysql. It's faster, > > But it doesn't matter *how* fast you get the *wrong* answer. :) > I thought one of the first rules of software engineering was "First make it right and only then make it fast!" Granted, most of my experience has more to do with number crunching and scientific computing, but for the past 27 years, I always first ensured the code was provably correct before worrying about optimization. And this always involved a test suite that applied every possible mathematical test with randomly generated test data. For example, my code for matrix algebra problems generally came with test code and driver that would exercise the code with tens of millions of random matrices and vectors. One failure, even in millions of test cases, means the code is somehow broken. I have seen, and fixed, such cases before. One of my problems with database development is how to construct analogously strong test cases in order to prove the code correct. And it seems to get harder as the database gets larger. :-( In other words, I know how to do this with algebra and calculus, and C++/Java/FORTRAN programming, but not with sets and SQL programming. Cheers, Ted
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 jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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 jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>>>One of my problems with database development is how to construct analogously strong test cases in order to prove the code correct. <<< I have found the best method is to be as random as possible. I think coders subconsciously only test with data they think will work so they don't have worry about working late. They're biased. We're all so programmed to think in deterministic ways that we don't recognize the power of pure random guessing.
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 jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On 22.02.2007, at 16:03, Ted Byers wrote: > One of my problems with database development is how to construct > analogously strong test cases in order to prove the code correct. With tests you can't prove that your code is correct. You can only show that your code works with the test cases. There are ways of proving that code is correct, but I don't know anybody using that on "real world code". If you are using Java as a client you can use DbUnit for unit testing: http://www.dbunit.org/ This can or cannot show that your latest changes didn't break stuff that used to work ... ;-) cug
-----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-----
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:decibel@decibel.org] 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 jim@nasby.net 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.
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.
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
On 2/23/07, Jim Nasby <decibel@decibel.org> 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
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
Andrej Ricnik-Bay wrote: > On 2/23/07, Jim Nasby <decibel@decibel.org> 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/
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
>
>
Joshua D. Drake escribió: > Andrej Ricnik-Bay wrote: > > On 2/23/07, Jim Nasby <decibel@decibel.org> 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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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.
Alvaro Herrera wrote: > Joshua D. Drake escribió: >> Andrej Ricnik-Bay wrote: >>> On 2/23/07, Jim Nasby <decibel@decibel.org> 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/
-----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-----
Joshua D. Drake escribió: > Alvaro Herrera wrote: > > Joshua D. Drake escribió: > >> Andrej Ricnik-Bay wrote: > >>> On 2/23/07, Jim Nasby <decibel@decibel.org> 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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Joshua D. Drake escribió: >> Alvaro Herrera wrote: >>> Joshua D. Drake escribió: >>>> Andrej Ricnik-Bay wrote: >>>>> On 2/23/07, Jim Nasby <decibel@decibel.org> 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/
On 2/23/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Andrej Ricnik-Bay wrote: > > On 2/23/07, Jim Nasby <decibel@decibel.org> 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
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.
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/
Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Joshua D. Drake escribió: > > Andrej Ricnik-Bay wrote: > > > On 2/23/07, Jim Nasby <decibel@decibel.org> 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.
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 <jd@commandprompt.com> 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
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 <jd@commandprompt.com> 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
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/
On 2/23/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
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.
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.
In response to "Chad Wagner" <chad.wagner@gmail.com>: > On 2/23/07, Bill Moran <wmoran@collaborativefusion.com> 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.
On 2/22/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Joshua D. Drake escribió: > > Andrej Ricnik-Bay wrote: > > > On 2/23/07, Jim Nasby <decibel@decibel.org> 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. <editorial>There 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
Le vendredi 23 février 2007 16:37, Ian Harding a écrit : > On 2/22/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Joshua D. Drake escribió: > > > Andrej Ricnik-Bay wrote: > > > > On 2/23/07, Jim Nasby <decibel@decibel.org> 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 ) > > <editorial>There 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
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 '0000-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
> In that case, the distinction just > adds work. In that case you declare the column not null and don't use nulls. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
On Fri, Feb 23, 2007 at 01:49:06PM +1300, Andrej Ricnik-Bay wrote: > On 2/23/07, Jim Nasby <decibel@decibel.org> 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 jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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
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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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.
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
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
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
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
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.
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
On 2/23/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
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.
> 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 ...
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.
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/
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?
-----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_CODE CHAR(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-----
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
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/
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.
Attachment
> 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.
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
-----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 greg@turnstep.com 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-----
> 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
On 2/25/07, Greg Sabino Mullane <greg@turnstep.com> wrote:
Some of us are still using php4 :)
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 :)
On 2/25/07, Magnus Hagander <magnus@hagander.net> wrote:
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. :)
> 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. :)
On Thu, 22 Feb 2007 17:14:11 -0600
Jim Nasby <decibel@decibel.org> 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)
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)
Hello all,
On Wed, 21 Feb 2007 10:02:04 -0600
Scott Marlowe <smarlowe@g2switchworks.com> 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)
On Fri, 23 Feb 2007 13:49:06 +1300 "Andrej Ricnik-Bay" <andrej.groups@gmail.com> wrote: > On 2/23/07, Jim Nasby <decibel@decibel.org> 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)
On Friday 23 February 2007 16:43, Chad Wagner wrote: > On 2/23/07, Bill Moran <wmoran@collaborativefusion.com> 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
> 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.
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
Hi,
I have a GiST index on st_geometry type (a user defined type). It looks
like index is not getting hit when I use some geometric operator. Here
is the example of st_contains operator.
EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
a
Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
1949440,
6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
a.shape) = 1;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------
Aggregate (cost=79132.24..79132.25 rows=1 width=4)
(actual time=49614.399..49614.400 rows=1 loops=1)
-> Seq Scan on parcel_l a (cost=0.00..79122.79 rows=3778 width=4)
(actual time=2.343..49388.591 rows=184750 loops=1)
Filter: (st_contains('ST_POLYGON'::st_geometry, shape) = 1)
Total runtime: 49614.479 ms
The time 49614 ms is too high for 184k rows. I have tried various
configuration parameters as mentioned in section 17.6 (Query planning)
of PostgreSQL 8.2.1 Documentation. For example setting
Set enable_seqscan = off;
Set random_page_cost = 10; etc.,
Changing these parameters did not improve performance.
If I call one of the operators (~) of GiST operator class, then it takes
1015 ms for 184k rows.
EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
a where
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
't';
------------------------------------------------------------------------
------------------------------------------
Aggregate (cost=2827.78..2827.79 rows=1 width=4)
(actual time=1015.025..1015.026 rows=1 loops=1)
-> Bitmap Heap Scan on parcel_l a (cost=46.05..2825.89 rows=756
width=4)
(actual time=213.914..876.122 rows=180512 loops=1)
Filter: ('ST_POLYGON'::st_geometry ~ shape)
-> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
rows=756 width=0)
(actual time=202.629..202.629 rows=180170 loops=1)
Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
Total runtime: 1015.223 ms
Here is information about table, type, index and rows in the table.
pg=# SELECT relname, relkind, reltuples, relpages FROM pg_class
WHERE relname LIKE 'parcel_l%';
relname | relkind | reltuples | relpages
--------------------+---------+-----------+----------
parcel_l_pkey | i | 755653 | 1665
parcel_l | r | 755653 | 67788
parcel_l_ind | i | 755653 | 9582
(3 rows)
pg=# \d parcel_l
Table "sde.parcel_l"
olumn | Type | Modifiers
---------------+-----------------------------+-----------
objectid_1 | integer | not null
area | numeric(38,8) |
.....
fid_len | numeric(38,8) |
shape | st_geometry |
Indexes:
"parcel_l_pkey" PRIMARY KEY, btree (objectid_1)
"parcel_l_ind" gist (shape)
How can I force or direct the planner to use the GiST index? Am I
missing something?
Thanks.
Ale Raza.
araza@esri.com wrote: > Hi, > > I have a GiST index on st_geometry type (a user defined type). It looks > like index is not getting hit when I use some geometric operator. Here > is the example of st_contains operator. > <snip> > > How can I force or direct the planner to use the GiST index? Am I > missing something? > > For the index to be used you need to use an operator that can make use of it. eg something like: select parcel1.id, count(*) from parcel1, polygons where contains(polygons.the_geom, parcel1.the_geom) and parcel1.geom && polygons.the_geom group by parcel1.id; the && (inside bounding box) is able to use the gist index, whilst the exact contains is not able to. Hope that helps, Joe
araza@esri.com wrote:
Have you considered using PostGIS (www.postgis.org) to provide OGC
compliant
spatial data management for Postgresql, including projection support,
indexing &
a good selection of spatial query functions?
Cheers,
Brent Wood
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> -----------------------------------------
> Aggregate (cost=79132.24..79132.25 rows=1 width=4)
> (actual time=49614.399..49614.400 rows=1 loops=1)
> -> Seq Scan on parcel_l a (cost=0.00..79122.79 rows=3778 width=4)
> (actual time=2.343..49388.591 rows=184750 loops=1)
> Filter: (st_contains('ST_POLYGON'::st_geometry, shape) = 1)
> Total runtime: 49614.479 ms
>
> The time 49614 ms is too high for 184k rows. I have tried various
> configuration parameters as mentioned in section 17.6 (Query planning)
> of PostgreSQL 8.2.1 Documentation. For example setting
> Set enable_seqscan = off;
> Set random_page_cost = 10; etc.,
>
> Changing these parameters did not improve performance.
>
> If I call one of the operators (~) of GiST operator class, then it takes
> 1015 ms for 184k rows.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a where
> (st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
> 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
> 't';
> ------------------------------------------------------------------------
> ------------------------------------------
> Aggregate (cost=2827.78..2827.79 rows=1 width=4)
> (actual time=1015.025..1015.026 rows=1 loops=1)
> -> Bitmap Heap Scan on parcel_l a (cost=46.05..2825.89 rows=756
> width=4)
> (actual time=213.914..876.122 rows=180512 loops=1)
> Filter: ('ST_POLYGON'::st_geometry ~ shape)
> -> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
> rows=756 width=0)
> (actual time=202.629..202.629 rows=180170 loops=1)
> Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
> Total runtime: 1015.223 ms
>
> Here is information about table, type, index and rows in the table.
>
> pg=# SELECT relname, relkind, reltuples, relpages FROM pg_class
> WHERE relname LIKE 'parcel_l%';
> relname | relkind | reltuples | relpages
> --------------------+---------+-----------+----------
> parcel_l_pkey | i | 755653 | 1665
> parcel_l | r | 755653 | 67788
> parcel_l_ind | i | 755653 | 9582
> (3 rows)
>
>
> pg=# \d parcel_l
> Table "sde.parcel_l"
> olumn | Type | Modifiers
> ---------------+-----------------------------+-----------
> objectid_1 | integer | not null
> area | numeric(38,8) |
> .....
> fid_len | numeric(38,8) |
> shape | st_geometry |
> Indexes:
> "parcel_l_pkey" PRIMARY KEY, btree (objectid_1)
> "parcel_l_ind" gist (shape)
>
>
>
> How can I force or direct the planner to use the GiST index? Am I
> missing something?
>
> Thanks.
>
> Ale Raza.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
No, this is my own type and I want to use this. PostGIS is another
option.
Ale.
-----Original Message-----
From: Brent Wood [mailto:b.wood@niwa.co.nz]
Sent: Wednesday, March 07, 2007 5:43 PM
To: Ale Raza
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to force planner to use GiST index?
araza@esri.com wrote:
Have you considered using PostGIS (www.postgis.org) to provide OGC
compliant
spatial data management for Postgresql, including projection support,
indexing &
a good selection of spatial query functions?
Cheers,
Brent Wood
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It
looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from
sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
>
> QUERY PLAN
>
------------------------------------------------------------------------
> -----------------------------------------
> Aggregate (cost=79132.24..79132.25 rows=1 width=4)
> (actual time=49614.399..49614.400 rows=1 loops=1)
> -> Seq Scan on parcel_l a (cost=0.00..79122.79 rows=3778 width=4)
> (actual time=2.343..49388.591 rows=184750 loops=1)
> Filter: (st_contains('ST_POLYGON'::st_geometry, shape) = 1)
> Total runtime: 49614.479 ms
>
> The time 49614 ms is too high for 184k rows. I have tried various
> configuration parameters as mentioned in section 17.6 (Query planning)
> of PostgreSQL 8.2.1 Documentation. For example setting
> Set enable_seqscan = off;
> Set random_page_cost = 10; etc.,
>
> Changing these parameters did not improve performance.
>
> If I call one of the operators (~) of GiST operator class, then it
takes
> 1015 ms for 184k rows.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from
sde.parcel_l
> a where
> (st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
> 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
> 't';
>
------------------------------------------------------------------------
> ------------------------------------------
> Aggregate (cost=2827.78..2827.79 rows=1 width=4)
> (actual time=1015.025..1015.026 rows=1 loops=1)
> -> Bitmap Heap Scan on parcel_l a (cost=46.05..2825.89 rows=756
> width=4)
> (actual time=213.914..876.122 rows=180512 loops=1)
> Filter: ('ST_POLYGON'::st_geometry ~ shape)
> -> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
> rows=756 width=0)
> (actual time=202.629..202.629 rows=180170 loops=1)
> Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
> Total runtime: 1015.223 ms
>
> Here is information about table, type, index and rows in the table.
>
> pg=# SELECT relname, relkind, reltuples, relpages FROM pg_class
> WHERE relname LIKE 'parcel_l%';
> relname | relkind | reltuples | relpages
> --------------------+---------+-----------+----------
> parcel_l_pkey | i | 755653 | 1665
> parcel_l | r | 755653 | 67788
> parcel_l_ind | i | 755653 | 9582
> (3 rows)
>
>
> pg=# \d parcel_l
> Table "sde.parcel_l"
> olumn | Type | Modifiers
> ---------------+-----------------------------+-----------
> objectid_1 | integer | not null
> area | numeric(38,8) |
> .....
> fid_len | numeric(38,8) |
> shape | st_geometry |
> Indexes:
> "parcel_l_pkey" PRIMARY KEY, btree (objectid_1)
> "parcel_l_ind" gist (shape)
>
>
>
> How can I force or direct the planner to use the GiST index? Am I
> missing something?
>
> Thanks.
>
> Ale Raza.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
It helps a bit but not much.
pg=# EXPLAIN analyze Select count(a.objectid_1) as contains from
parcel_l a where
st_contains(st_geometry('polygon ((6221958 1949440, 6349378 1949440,
6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
a.shape) = 1 AND
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
't';
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------
Aggregate (cost=2829.68..2829.69 rows=1 width=4)
(actual time=43371.933..43371.934 rows=1 loops=1)
-> Bitmap Heap Scan on parcel_l a (cost=46.05..2829.67 rows=4
width=4) (actual time=217.830..43155.610 rows=180512 loops=1)
Filter: ((st_contains('ST_POLYGON'::st_geometry, shape) = 1)
AND ('ST_POLYGON'::st_geometry ~ shape))
-> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
rows=756 width=0) (actual time=197.052..197.052 rows=180170 loops=1)
Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
Total runtime: 43372.142 ms
(6 rows)
pgsde=# EXPLAIN analyze Select count(a.objectid_1) as contains from
parcel_l a where
st_contains(st_geometry('polygon ((6221958 1949440, 6349378 1949440,
6349378
2033808, 6221958 2033808, 6221958 1949440))'::cstring,3), a.shape) = 1
AND
(st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
2033808,
6221958 2033808, 6221958 1949440))'::cstring,3) && a.shape) = 't';
QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------
Aggregate (cost=20.48..20.49 rows=1 width=4)
(actual time=43898.908..43898.909 rows=1 loops=1)
-> Index Scan using parcel_l_ind on parcel_l a (cost=0.00..20.47
rows=1 width=4) (actual time=0.500..43680.894 rows=180170 loops=1)
Index Cond: ('ST_POLYGON'::st_geometry && shape)
Filter: ((st_contains('ST_POLYGON'::st_geometry, shape) = 1)
AND ('ST_POLYGON'::st_geometry && shape))
Total runtime: 43899.025 ms
(5 rows)
Thanks.
Ale
-----Original Message-----
From: Joe Healy [mailto:joe@omc-international.com.au]
Sent: Wednesday, March 07, 2007 4:42 PM
To: Ale Raza
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to force planner to use GiST index?
araza@esri.com wrote:
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It
looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
> <snip>
>
> How can I force or direct the planner to use the GiST index? Am I
> missing something?
>
>
For the index to be used you need to use an operator that can make use
of it. eg something like:
select parcel1.id, count(*) from parcel1, polygons where
contains(polygons.the_geom, parcel1.the_geom) and parcel1.geom &&
polygons.the_geom group by parcel1.id;
the && (inside bounding box) is able to use the gist index, whilst the
exact contains is not able to.
Hope that helps,
Joe
On Wed, Mar 07, 2007 at 04:00:14PM -0800, araza@esri.com wrote:
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
I don't know whether you noticed, but a function call can never use an
index like that. Index scans *only* work with operators, not with
functions.
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
So no matter what you do, this can never be an index scan, because
there's no operator postgres can apply to the index...
If you really want to use function names also, I beleive you can make
in inline SQL function to convert from function form to operator form.
The query planner will only look to the index if there's an operator.
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.
Attachment
"If you really want to use function names also, I beleive you can make
in inline SQL function ... if there's an operator."
Correct, this is what I am doing now.
Ale.
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Thursday, March 08, 2007 10:35 AM
To: Ale Raza
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to force planner to use GiST index?
On Wed, Mar 07, 2007 at 04:00:14PM -0800, araza@esri.com wrote:
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It
looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
I don't know whether you noticed, but a function call can never use an
index like that. Index scans *only* work with operators, not with
functions.
> EXPLAIN analyze Select count(a.objectid_1) as contains from
sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
So no matter what you do, this can never be an index scan, because
there's no operator postgres can apply to the index...
If you really want to use function names also, I beleive you can make
in inline SQL function to convert from function form to operator form.
The query planner will only look to the index if there's an operator.
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.