issues when running openmrs with postgresql

[Ivo Ulrich]

Hello all!

I am trying to run openmrs with postgresql and would love your feedback!

My setup: ubuntu 14.04.1 64bit, java 1.7.0_71, postgresql 9.3.5, postgresql-9.3-1102.jdbc41.jar

First I created a db called "openmrs", deployed openmrs.war (version 1.10.1) and followed the wizard (create tables and demo data).

The wizard tells me that he has completed the tasks

- Created database tables and added core data
- Added demo data

But I get an error during an insert into the db (see full message at the end).

Inserts in liquibase-update-to-latest.xml into table 'order_type' refer to column 'retired' as type valueNumeric instead of valueBoolean (column is of type boolean):

    <changeSet id="201403070132-TRUNK-4286" author="andras-szell">

        <preConditions onFail="MARK_RAN">

            <sqlCheck expectedResult="0">select count(*) from order_type where java_class_name = 'org.openmrs.TestOrder'</sqlCheck>

        </preConditions>

        <comment>Insert order type for test orders</comment>

        <insert tableName="order_type">

            <column name="name" value="Test Order" />

            <column name="description" value="Order type for test orders" />

            <column name="java_class_name" value="org.openmrs.TestOrder" />

            <column name="creator" valueNumeric="1" />

            <column name="date_created" valueDate="2014-03-09" />

            <column name="retired" valueNumeric="0" />

            <column name="uuid" value="52a447d3-a64a-11e3-9aeb-50e549534c5e" />

        </insert>

    </changeSet>

see https://github.com/openmrs/openmrs-core/blob/master/api/src/main/resources/liquibase-update-to-latest.xml

Postgresql doesnt simply convert that to a boolean.

I guess I will encounter more of this as I go and will have to adapt resource files, ...

My question is are there people out there running OpenMRS with postgresql? In production?

And how much effort is it do do so? Is it easy (few changes to resource files) to get to run openmrs-core with postgresql.

And what is your experience with modules? Is it more of a challenge there.

Does anybody know if OpenMRS plans on supporting postgresql aside with mysql in the near future?

Thank you very much for your help!

Cheers

Ivo

Full error message shown by openmrs wizard:

There was an error while updating the database to the latest. file: liquibase-update-to-latest.xml. 

Error: Migration failed for change set liquibase-update-to-latest.xml::201403070132-TRUNK-4286::andras-szell: Reason: liquibase.exception.DatabaseException: Error executing SQL INSERT INTO order_type (creator, date_created, description, java_class_name, name, retired, uuid) VALUES (1, '2014-03-09', 'Order type for test orders', 'org.openmrs.TestOrder', 'Test Order', 0, '52a447d3-a64a-11e3-9aeb-50e549534c5e'): ERROR: column "retired" is of type boolean but expression is of type integer Hint: You will need to rewrite or cast the expression. Position: 193: Caused By: Error executing SQL INSERT INTO order_type (creator, date_created, description, java_class_name, name, retired, uuid) VALUES (1, '2014-03-09', 'Order type for test orders', 'org.openmrs.TestOrder', 'Test Order', 0, '52a447d3-a64a-11e3-9aeb-50e549534c5e'): ERROR: column "retired" is of type boolean but expression is of type integer Hint: You will need to rewrite or cast the expression. Position: 193: Caused By: ERROR: column "retired" is of type boolean but expression is of type integer Hint: You will need to rewrite or cast the expression. Position: 193 Error while trying to update to the latest database version

[Darius Jazayeri]

Hi Ivo,

As I understand it:

• As of OpenMRS 1.9.0, OpenMRS core would run fine with postgresql.
• Since then we've surely had some small regressions, since we're not testing this in CI, and AFAIK nobody active on any lists is using postgresql and telling us about issues.
• Modules have not been made to work with postgresql. (Maybe a handful have, but there hasn't been any concerted effort.)

Personally I would love for us to support postgresql, but for this to happen we'd need someone to volunteer to lead/direct the effort.

Things involved, off the top of my head:

• finding everywhere in openmrs-core since 1.9.0 that isn't postgresql-compatible
• pushing to get database setup included in our CI testing, so we don't regress
• documenting a few simple fixes that many modules should make, and creating tickets for this
• analyzing some trickier things to come up with a plan (e.g. the reporting module probably has some mysql-specific queries that can't trivially be rewritten as standard sql because of the lack of standard sql date arithmetic functions)

So...there are no short-term plans for this, unless someone steps up.

-Darius

--
OpenMRS Implementers: http://om.rs/implist
Post: implem...@openmrs.org | Unsubscribe: implementers...@openmrs.org
Manage your OpenMRS subscriptions at http://om.rs/id
 
Register today for our Maputo 2015 Implementers Meeting: http://om.rs/moz15

To unsubscribe from this group and stop receiving emails from it, send an email to implementers...@openmrs.org.