Re: Integrity on large sites - Mailing list pgsql-general

> Some big sites do of course juggle performance vs in-database run-time
> checks, but the statements as typically presented by MySQL partisans,

    Live from the front :

    This freshly created database has had to endure a multithreaded query
assault for about 2 hours.
    It gave up.

TABLE `posts` (
   `post_id` int(11) NOT NULL auto_increment,
   `topic_id` int(11) NOT NULL,
etc...

mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
|       591257 |
+--------------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
ERROR 1062 (23000): Duplicate entry '591257' for key 1

mysql> CHECK TABLE posts;
+-------------------+-------+----------+-----------------------------+
| Table             | Op    | Msg_type | Msg_text                    |
+-------------------+-------+----------+-----------------------------+
| forum_bench.posts | check | warning  | Table is marked as crashed  |
| forum_bench.posts | check | error    | Found 588137 keys of 588135 |
| forum_bench.posts | check | error    | Corrupt                     |
+-------------------+-------+----------+-----------------------------+

mysql> REPAIR TABLE posts;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| forum_bench.posts | repair | status   | OK       |
+-------------------+--------+----------+----------+

mysql> INSERT INTO posts (topic_id,post_text,user_id) VALUES (1,'DIE
BASTARD',666);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'post_time' doesn't have a default value |
+---------+------+------------------------------------------------+

mysql> SELECT max(post_id) FROM posts;
+--------------+
| max(post_id) |
+--------------+
|       591257 |
+--------------+

mysql> SELECT count(*) FROM posts UNION ALL SELECT sum( topic_post_count )
 FROM topics;
+----------+
| count(*) |
+----------+
|   588137 |
|   588145 |
+----------+

mysql> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT topic_id
 FROM posts);
+----------+
| count(*) |
+----------+
|    11583 |
+----------+

(Note : there cannot be a topic without a post in it, ha !)

Try Postgres :

forum_bench=> SELECT count(*) FROM posts UNION ALL SELECT
sum( topic_post_count ) FROM topics;
  count
--------
  536108
  536108
(2 lignes)

forum_bench=> SELECT count(*) FROM topics WHERE topic_id NOT IN (SELECT
topic_id FROM posts);
  count
-------
      0
(1 ligne)