Sequence error on creating patient in PostgreSQL

Development
, ,
1

Hello everyone. I have just completed initialization of OpenMRS core on PostgreSQL. All changesets in core are executing after some changes. I have added the legacy UI module and was trying to create a patient where I got the error Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist

Here is the link to the logs: https://pastebin.com/Mp0GjKyi I went through the Patient Hibernate config file and found that there is no sequence set on patient_id https://github.com/openmrs/openmrs-core/blob/d16c00515d31f47bf1b852f763597f0254c7398e/api/src/main/resources/org/openmrs/api/db/hibernate/Patient.hbm.xml#L20-L23

Looking at postgresql database, it appears that there is a sequence with name patient_identifier_patient_identifier_id_seq but it has not been linked to patient_id.

Is there a reason for this or am I missing something?

Any help is appreciated.

2

Hi @aman,

Thanks for your dedication on this! :muscle:

Just Googling quickly from my phone, but can this kind of stuff be added in a part of some setup changeset that is specific to Postgres?

3

I will give it a shot and see what happens. It might be some issue with PostgreSQL dialect that it is not getting created by default.

4

We don’t let Hibernate create tables, etc. by default. We use Liquibase to setup the database, etc. It maybe be necessary to add a Liquibase changeset to setup the Hibernate sequence.

5

Thanks for the info @ibacher . I added the required changeset and the sequence problem resolved but now there are some other issues I am bumping into. Let me see if can resolve it or else I’ll be back with some more questions. :slightly_smiling_face:

2 Likes
6

Thanks @ibacher and @mksd . I am now able to add patients successfully to PostgreSQL. I will now be heading to the admininstration section and verify that the different functionalities are working correctly.

If there is anything I should keep in mind while testing openmrs-core on PostgreSQL, please do suggest. :slightly_smiling_face:

7

@ibacher @mksd , creating hibernate_sequence with start values as 1 leads to primary key violation error when you add patient for first time because there is already a person with id = 1 (superuser) . You move 1 page back and again try to add patient, and patient gets added successfully since the sequence autoincremented. There appears to be a conflict between sequence defined for person and default fallback sequence hibernate_squence that hibernate uses.

Making the hibernate_sequence start from 2 solves the issue. But is this the right way to get over this problem ?

8

I would think so because we can assume that this person ID’d 1 will always be there, baked in OpenMRS. @ibacher?

9

I completely agree. Person 1 should always be the admin user.

@aman: Does Hibernate use a single sequence to generate ids for all objects or is it a sequence per-table?

10

If you have not created a sequence specifically for the object, then hibernate looks for its default sequence which is hibernate_sequence and uses it even if the objects may be different. In OpenMRS there are sequences that have been created specifically for objects( example Person ) but its not the case with Patient.

11

Thanks for that! That alleviates my worry there. Do we need to do anything to ensure that person id is always the same as patient id?

12

We can make both Patient and Person use the same sequence which is the existing sequence being used by the Person.

13

Working on this problem for quite sometime, I have observed that although the different sequences for different entities were being created but none of them were being utilized, all the different entities were making use of hibernate_sequence for persistance which is the default hibernate fallback sequence.

Searching for this problem I have finally found that starting from hibernate 5+ we need to change all the mapping of sequence generators in hibernate files to

  	<generator class="native">
	     <param name="sequence_name">person_person_id_seq</param>
	</generator>

That is change ‘sequence’ to ‘sequence_name’ .

Doing this for Person need not required the use of hibernate_sequence for Patient since it was just extending the Person class and Person Id was automatically made equal to Patient Id when persisting making use of person_person_id_seq.

I found this solution here : java - HIbernate 5: generator class="sequence" not working - Stack Overflow

And now finally the sequence problem is resolved while saving Patient in PostgreSQL. I am not able to figure out why this was not a problem in MySQL though it just makes use of autoincrement.

cc. @mksd @ibacher @dkayiwa

2 Likes
14

The reason it isn’t an issue for MySQL, is that MySQL allows columns to be defined as have an AUTO_INCREMENT attribute. Years ago, PostgreSQL did not support this functionality and when they did add this (in the form of the SERIAL datatype) it was just as a thin wrapper around sequences. The upshot is that Hibernate has always been able to rely on MySQL’s AUTO_INCREMENT functionality, but hasn’t for PostgreSQL.

Incidentally, you may want to experiment with just making the primary keys “SERIAL” types so that we don’t have to mess too much with identity generation.

2 Likes