Experimental Postgres support

[torgeirl]

Is your feature request related to a problem? Please describe. I would like to use PostgreSQL instead of MariaDB for my Inception deployment since it has better support in my organization, and are therefore wondering if it would be possible to add experimental Postgres support to Inception?

Describe the solution you'd like There is a mention of testing Postgres dating back to October 2021 (#2681), and since then there have been quite a few updates with the goal of improve database compatibility (#3400).

Adding the hacks mentioned in the #3400 to a local Snapshot build I'm able to start the application just fine, but trying to either create a new project or importing the example projects I run into error such as:

INFO [SYSTEM] DocumentRepositoryExporter - Imported [0] document repositories for project [Example: Concept Linking]
WARN [SYSTEM] SqlExceptionHelper - SQL Error: 0, SQLState: 42804
ERROR [SYSTEM] SqlExceptionHelper - ERROR: column "createTag" is of type bit but expression is of type boolean\n  Hint: You will need to rewrite or cast the expression.\n  Position: 91
(...)
Caused by: org.postgresql.util.PSQLException: ERROR: column "createTag" is of type bit but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 91

Would it be possible to:

• address these cast issues
• perhaps also add settings such as database.globally_quoted_identifiers, database.ddl-auto and others if needed to allow this to be tested beyond local snapshots

(Not sure how much work this would entail so please say no / not now if this isn't possible / possible now!)

Additional context Changes to inception/inception-app-webapp/src/main/resources/application.yml (as suggested in #3400):

     properties:
       hibernate:
+        globally_quoted_identifiers: true
         dialect: ${database.dialect:${INCEPTION_DB_DIALECT:}}
         # Enable SQL statements formatting.
         # format_sql: true
         # jmx:
         #   enabled: true
     hibernate:
-      ddl-auto: validate
+      ddl-auto: none

Database settings in settings.properties:

database.url=jdbc:postgresql://DATABASE-URL
database.username=USERNAME
database.password=PASSWORD
database.dialect=org.hibernate.dialect.PostgreSQLDialect
database.driver=org.postgresql.Driver

[reckart]

I have been able to start INCEpTION with postgres some time back, but I believe there were still various places in the database schema where INCEpTION is not quite compatible with postgres. Feel free to experiment if you like, but know that for me postgres is not a priority atm.

[torgeirl]

Noted! I'll see what I can do myself.

[reckart]

To fix these issues, it would be necessary to change the db-changelog.xml files in the project. However, those changes would need to be made very careful such that they not only work with postgres but also with hsqldb (embedded) and mariadb/mysql and that they would not break existing user's setups. Thus, they would need to be made in such a way that they migrate the existing database setup to a version that would be compatible with Postgres without losing data.

[torgeirl]

Closed this issue since I'm unable to fix it myself, but now realizing you had put it in the maintenance backlog milestone I'll re-open it as I do really want to see this in the future if possible. :crossed_fingers: :crossed_fingers: