Busy configuring slony 1.1.0 for replicating between various hosts. It is way easier to configure slony 1.1.0 over the 1.0.x series.
Does anyone know of any decent open source PostgreSQL benchmarking software similar to the Super Smack utility for benchmarking MySQL database servers?
Practical Postgresql is an excellent resource for getting more out of Postgresql as you get out of MySQL. Definately a must read for MySQL users moving over to Postgresql or users starting out with Postgresql.
Jim Winstead, Jr. brings up a point which deals with the subject of bad code readability which Neil has brought up previously.
It's the simple things like taking the time to make your programming easier. For example Jim shows us the following example:
$query = "SELECT id,name,url,rss,md5sum,method,updated AS up,"
. " UNIX_TIMESTAMP(lastchecked) AS lastchecked,"
. " UNIX_TIMESTAMP(updated) AS updated"
. " FROM blogs "
. " WHERE updated > NOW() - INTERVAL 10 MINUTE AND method = 0"
. " ORDER BY up DESC"
. " LIMIT 10";
Which he goes onto mention that he is now doing it like:
$query= "SELECT id,name,url,rss,md5sum,method,updated AS up,
UNIX_TIMESTAMP(lastchecked) AS lastchecked,
UNIX_TIMESTAMP(updated) AS updated
FROM blogs
WHERE updated > NOW() - INTERVAL 10 MINUTE AND method = 0
ORDER BY up DESC
LIMIT 10
";
Which makes it easier to copy and paste into the mysql command line utility. I'm known for having extremely long lines of code for SQL queries when I was programming in PHP back in the day. Also in certain ways it can make it worse when you have long long lines which you are trying to debug and are unable to figure out quickly what your 900+ character SQL query is doing!
A line like:
$categories = $dbh->getall ("SELECT directory_company_category_map.company_id, directory_categories.id AS category_id, directory_sub_categories.id AS sub_category_id, directory_categories.category, directory_sub_categories.sub_category_name FROM directory_categories LEFT JOIN directory_sub_categories ON directory_sub_categories.category_id=directory_categories.id LEFT JOIN directory_company_category_map ON directory_company_category_map.category_id=directory_categories.id LEFT JOIN directory_companies ON directory_companies.id=directory_company_category_map.company_id WHERE directory_company_category_map.category_id=directory_categories.id AND directory_sub_categories.category_id=directory_categories.id AND directory_company_category_map.sub_category_id=directory_sub_categories.id AND directory_company_category_map.company_id='" . $listings[$i]['id'] . "'");
is quite a bit to process ;) It looks way better like:
<?php
$categories = $dbh->getall ("
SELECT
directory_company_category_map.company_id,
directory_categories.id AS category_id,
directory_sub_categories.id AS sub_category_id,
directory_categories.category,
directory_sub_categories.sub_category_name
FROM directory_categories
LEFT JOIN directory_sub_categories
ON directory_sub_categories.category_id=directory_categories.id
LEFT JOIN directory_company_category_map
ON directory_company_category_map.category_id=directory_categories.id
LEFT JOIN directory_companies
ON directory_companies.id=directory_company_category_map.company_id
WHERE
directory_company_category_map.category_id=directory_categories.id AND
directory_sub_categories.category_id=directory_categories.id AND
directory_company_category_map.sub_category_id=directory_sub_categories.id AND
directory_company_category_map.company_id='" . $listings[$i]['id'] . "'
");
?>
