Jabber server + PostgreSQL == good

Recently I needed to get Jabber 1.4.4 to store rosters and users and presence and so forth to a PostgreSQL database.

• I modified the SQL scripts to create tables in PostgreSQL; script is here.  Note that there are some extra sequences and id columns in there so that ActiveRecord can its object-relational mapping magic
• I'm not too familiar with PostgreSQL date formats, so I just put storetime, delivertime, and lastmodified columns in TEXT format

I modified the sample jabber.xml so now the queries use PostgreSQL CASE and COALESCE statements vs the MySQL IF statement; sample jabber.xml is here.  Just scroll down to the xdb element to see the new queries.

For what it's worth, this setup works fine with the Ruby client library for Jabber.  Not surprising, but, cool to know anyway.  We might end up adding some load testing cases to the Jabber4R project.

Was this helpful?  If so, please buy my completely unrelated book!

Comments

[Daniel Schierbeck]

In jabberd_tables_for_postgresql.sql, in the CREATE TABLE commands, why do you manually create the sequences? Why not just set the type of the id columns to SERIAL?

CREATE TABLE users (id SERIAL PRIMARY KEY, ...);

[tomcopeland]

Hi Daniel -
Hm, that's a good question... that does seem much nicer. I guess I've just always explicitly named and referenced the sequences, but if I'm always using the same naming scheme, what's the point?
Thanks! I'll update this once I've changed and verified my scripts.
Yours,
Tom