sql-info.de

Converting tsearch2 to 8.3

2007-11-20T07:00:00+01:00

One of the major features in the upcoming 8.3 release is the integration of the tsearch2 full text search extension as a core PostgreSQL feature . While there are no fundamental changes, there are some differences which make upgrading from an existing installation a little tricky. The following are my notes from upgrading a test version of the database which powers this website.

more...

PostgreSQL vs MySQL website backend fieldtest

2007-10-10T06:03:00+01:00

(I'm back in the wonderful world of SQL in general and PostgreSQL in particular after a long hiatus).

Just came across this article describing (briefly) someone's practical experience converting a website backend from MySQL to PostgreSQL, with PostgreSQL coming out faster. Not an exhaustive scientific test, but the comments are interesting reading.

permalink

PostgreSQL on Windows primer from Microsoft

2007-02-26T06:25:00+01:00

PostgreSQL on Windows: A Primer (via Slashdot )

permalink

Elephants and (Trench) Mice

2007-02-10T21:57:00+01:00

It's not often that you click through from {insert name of popular Web 2.0 community site here} to {insert name of Web 2.0 startup-type site here} and notice they're running the site on a decently pachydermal database engine, but this one called TrenchMice certainly is.

permalink

PostgreSQL in Thailand

2006-11-23T17:54:00+01:00

I've just got back from a few weeks in Thailand doing some consulting work for an international organisation, some of which thankfully involves open source in general and PostgreSQL in particular.

I took the opportunity to poke around the IT sections of a few bookshops and get the impression that the IT book market at least is dominated by publications dealing with proprietary software, mainly Windows in general and as far as databases go Oracle and SQL Server. Books on open source were pretty much in the minority, mainly dealing with Linux and / or PHP. I did find one book each on PostgreSQL and MySQL though, which as far as I could tell were both written in Thailand (i.e. not translations). I can only speculate that the infamous availability of proprietary software at very low prices through, erm, inofficial channels means open source software is at a comparative disadvantage. The current government seems to have issues with open source too, for whatever reason.

On the other hand, I had to source a dedicated server, and most providers offer Linux as the base package, with Windows servers available at a premium. In several cases the specs for the Linux servers detailed both MySQL and PostgreSQL as "features".

(Apologies to anyone who has sent mails to me in the last month or so and hasn't had a reply, I'm still up to my neck in things to do).

permalink

Exporting TRUE and FALSE to MySQL

2006-09-19T12:12:00+01:00

One of the many pitfalls of web-related work is that sometimes you just have to deal with MySQL, as it's the only database option some clients have. Particularly irksome is the penchant many web hosters have for older MySQL versions, which is a major PITA when trying to deal with even slightly complex data.

One of the many things lacking in MySQL is a true boolean datatype. It does possess a pseudo-type BOOL , which is silently transmuted to TINYINT(1) , and from 4.1 onwards, to quote the manual, " the constants TRUE and FALSE evaluate to 1 and 0, respectively ".

more...

5th Anniversary

2006-07-11T19:46:00+01:00

Just back from Japan, where I've been busy on various private projects (using PostgreSQL wherever a database is required, of course), and haven't had much time to pursue the mailing lists, so hadn't really noticed that the 10th anniversary celebrations were underway. Congratulations all round!

Side note: Japan is a remarkably PostgreSQL-friendly country; an unscientific survey of various major bookstores in Tokyo showed that, for the open source databases, PostgreSQL books were in the majority. I'd say compared to a certain dolphin-orientated RDBMS there's about a 5:3 ratio in favour of PostgreSQL.

No, the title isn't a mistake: it occurred to me that it's almost exactly 5 years since I first used PostgreSQL. That was back in the waning days of the dot-com boom, when I was entrusted with the development of a CRM system for my former employer, Germany's first e-learning platform . The challenge was to unite an Oracle-based system with a MySQL-based system, and at least-possible cost (because the magic money pots were slowly drying up). Oh yes, the new system also had to take care of a fairly complex user authentication and session handling system for the new website.

Fortunately I had cut my SQL teeth on Oracle (version 7), and while this wasn't an option, neither was MySQL, which at the time completely lacked basic things such as foreign keys and transaction support (I think this was the period when MySQL was feuding with its transactional engine supplier about domain names). As a DB backend for an application which was to handle financial transactions, this was a total no-go (and this was long before I'd got my teeth into the infamous gotchas list ). I had vaguely heard of PostgreSQL though, and decided to try it out, and while it had a few niggles (altering table definitions was a pain), I liked it.

more...

How to ensure you're in a transaction

2006-04-26T06:51:00+01:00

Transactions are only useful when you actually use them, otherwise it's too late when you realize you've forgotten the WHERE clause and would really, really like to be able to roll back the changes - as Robert Treat found out recently.

If you're lucky conventional backups or PITR will help you avoid total disaster, but apart from the delay in extracting the necessary data from the backups and reading it in, Murphy's Law dicates that exactly the data you need won't be in the backup. Far better is to remember to start a transaction in the first place, putting recovery a blood-pressure friendly ROLLBACK away. psql has two surprisingly little-used functions which help ensure you're inside a transaction when you need to be.

more...

Installing phpPgAdmin

2006-04-22T18:23:00+01:00

phpPgAdmin in action phpPgAdmin is PostgreSQL's answer to the ever-popular phpMyAdmin . It's a PHP-based web-frontend for administering PostgreSQL databases - a serverside equivalent of pgAdmin III if you like.

Installation is very simple. The basic requirements are a PHP-enabled webserver, typically Apache, with PHP 4.1 or later, and access to a PostgreSQL server. In the following description I'm assuming a UNIXoid environment such as Linux or OS/X.

more...

PostgreSQL Book Market "up 84%"

2006-04-21T23:18:00+01:00

According to this post by Tim O'Reilly of O'Reilly books fame, the market for PostgreSQL books is "up 84% over a year ago", at least in the US market. This is based on data for the entire market, not just O'Reilly publications (see an earlier post for more information on the methodology; sadly O'Reilly analyzed the data with MySQL). With more and more books such as Beginning PHP and PostgreSQL 8 appearing, this is not surprising, and a further sign that PostgreSQL is gaining much-deserved mind-share.

more...