Re: 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(dot)develop(at)gmail(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general(at)postgresql(dot)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(dot)r(at)sbcglobal(dot)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(at)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Johnson
> Sent: Tuesday, February 20, 2007 11:35 PM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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(dot)develop(at)gmail(dot)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/

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(dot)develop(at)gmail(dot)com
> <mailto:ggh(dot)develop(at)gmail(dot)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(dot)r(at)sbcglobal(dot)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(at)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(at)digitalfairway(dot)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

At 07:31 PM 2/21/2007, Chad Wagner wrote:
>On 2/20/07, gustavo halperin
><<mailto:ggh(dot)develop(at)gmail(dot)com>ggh(dot)develop(at)gmail(dot)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.

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 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(at)pop(dot)jaring(dot)my> wrote:
> At 07:31 PM 2/21/2007, Chad Wagner wrote:
> >On 2/20/07, gustavo halperin
> ><<mailto:ggh(dot)develop(at)gmail(dot)com>ggh(dot)develop(at)gmail(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of gustavo
halperin
Sent: Tuesday, February 20, 2007 4:26 PM
To: pgsql-general(at)postgresql(dot)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-----

>> 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.

Chad Wagner wrote:
> On 2/20/07, *gustavo halperin* <ggh(dot)develop(at)gmail(dot)com
> <mailto:ggh(dot)develop(at)gmail(dot)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/

-----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(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)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.

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

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(at)pop(dot)jaring(dot)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(at)pop(dot)jaring(dot)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(at)postgresql(dot)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(at)pop(dot)jaring(dot)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(at)postgresql(dot)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.

> 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!

On 22/02/07, Shashank Tripathi <shanx(at)shanx(dot)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

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(at)pop(dot)jaring(dot)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(at)pop(dot)jaring(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

2007/2/22, Russ Brown <pickscrape(at)gmail(dot)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

>>>>> "Russ" == Russ Brown <pickscrape(at)gmail(dot)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(at)stonehenge(dot)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!

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

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(at)pop(dot)jaring(dot)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(at)svana(dot)org> wrote:
>
> On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote:
> > >SELECT foo, bar, COUNT(*)
> > >FROM baz
> > >GROUP BY foo
>
> > That one actually comes in handy ;) Especially in older versions (4.0)
> > that don't support subselects..
>
> I must say I don't see any reasonable way of interpreting the above
> query. Is the value of bar selected randomly?
>

The value of bar happens to be the first value fetched based on the GROUP BY
of foo, not sure how predictable and repeatable it is.

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(at)omnicode(dot)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(at)stonehenge(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tim Tassonis
Sent: Thursday, February 22, 2007 10:31 AM
To: Rich Shepard
Cc: pgsql-general(at)postgresql(dot)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 values in your fields indicates that your primary keys are not
truly candidate keys for all your fields. That means your database isn't [BCNF] normalized.<<<

I agree that there are very few times when NULL is appropriate in a database. I can't think of a single concrete example to use it in a database field.

It has its use in programming, mainly as a memory management/trash collection mechanism. Basically, you don't want to delete something that doesn't exist.

For example:

Statement *st = NULL;
ResultSet *rs = NULL;
try
{
st = prepareStatement("select * from customers");
rs = st->executeQuery();
while (rs->next())
{
do something
}
delete st;
delete rs;
}
catch (Exception e)
{
if (st != NULL)
delete st;
if (rs != NULL)
delete rs;
}

>
> 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(at)nasby(dot)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(at)nasby(dot)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(at)nasby(dot)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(at)decibel(dot)org]
Sent: Thursday, February 22, 2007 6:28 PM
To: Brandon Aiken
Cc: pgsql-general(at)postgresql(dot)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(at)nasby(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)commandprompt(dot)com> wrote:
> Andrej Ricnik-Bay wrote:
> > On 2/23/07, Jim Nasby <decibel(at)decibel(dot)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/

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(at)commandprompt(dot)com> wrote:
> John Smith wrote:
> > On 2/21/07, Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> Joshua D. Drake escribió:
> > Andrej Ricnik-Bay wrote:
> > > On 2/23/07, Jim Nasby <decibel(at)decibel(dot)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.

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(at)collaborativefusion(dot)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? 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(dot)wagner(at)gmail(dot)com>:

> On 2/23/07, Bill Moran <wmoran(at)collaborativefusion(dot)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(at)commandprompt(dot)com> wrote:
> Joshua D. Drake escribió:
> > Andrej Ricnik-Bay wrote:
> > > On 2/23/07, Jim Nasby <decibel(at)decibel(dot)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(at)commandprompt(dot)com> wrote:
> > Joshua D. Drake escribió:
> > > Andrej Ricnik-Bay wrote:
> > > > On 2/23/07, Jim Nasby <decibel(at)decibel(dot)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(at)killerbytes(dot)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(at)decibel(dot)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(at)nasby(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steve Crawford
Sent: Friday, February 23, 2007 1:04 PM
To: Mark Walker
Cc: pgsql-general(at)postgresql(dot)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.