Some interesting PostgreSQL-related links which have come my way this week:
- Wikipedia has a overview of different relational database systems in table form. It's not particularly detailed but provides a useful "at-a-glance" comparision:
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems - "Datamining Apache Logs with PostgreSQL": Article by Robert Bernier at OnLAMP.com:
http://www.onlamp.com/pub/a/onlamp/2005/03/03/pg_datamining.html - Benchw Results for PostgreSQL, Firebird and MySQL:
http://benchw.sourceforge.net/benchw_results_open3.html
The latest version of DBD::Pg, the PostgreSQL DBI driver, has been
released and provides some useful new functionality,
including support for server-side prepares,
SQLSTATE
code retrieval, and a
last_insert_id()
function. See below for
a brief overview.
PostgreSQL's command line utility psql
provides a
settings toggle \timing
which displays the time it takes
to run a statement, right down to thousandths of a millisecond.
This is very useful for debugging speed-critical statements,
especially for web-based applications where a few milliseconds either
way can make a noticeable cumulative difference.
Recently I was struggling with a particular statement which
normally executed in less than 10ms, but with the addition of a
single additional condition in the WHERE
clause,
execution time went up by a factor of five to around 50ms.
(In human terms that might not sound a lot, "only" 1/20th of a second,
but the app should be able to deliver the entire dynamic web page within
250ms - in that case 40ms is suddenly a very long time).
The usual suspects in this kind of situation - differing query plans,
lack of recent VACUUM ANALYZE
etc. - were evidently
not guilty, and no amount of SQL voodoo would make any difference.
Due to a potential security hole recently identified with the
LOAD
command, security updates for currently supported
PostgreSQL versions (7.2 to 8.0) have been released.
- Announcement
- Mirrors: http://wwwmaster.postgresql.org/download/mirrors-ftp
- Bittorrent: http://bt.postgresql.org
Privilege-checking weaknesses with the LOAD
command
may permit local privilege escalations in all current versions.
Updates are expected on Friday.
Reference:
PostgreSQL 8.0 has been released and is available for download.
SUSE is now making RPMs available for current PostgreSQL versions (7.2, 7.3, 7.4 and 8.0rc builds):
Well, the previous prediction doesn't seem to have been quite accurate. A number of last-minute bug-fixes, including the solution for a hard-to-reproduce table definition problem (more info) have been added, bringing the Release Candidate total up to 5.
An initdb
is not required.
- Release announcement: https://www.postgresql.org/message-id/20050107115212.V63822@ganymede.hub.org
- Mirrors: http://wwwmaster.postgresql.org/download/mirrors-ftp
- Bittorrent: http://bt.postgresql.org