Relation not found in Postgres DB

[bauerjs1]

Hi everybody,

I am not yet sure if this is a issue in the helm chart or in the FHIR server itself. When deploying the server via helm and using an external PostgreSQL as database, the FHIR server is not able to operate and reports "relation xyz not found" SQL errors, where xyz are the table names in the fhirdata schema.

As a workaround, I manually execute

ALTER DATABASE ibm_fhir SET SEARCH_PATH TO fhirdata,"$user",public;

so that the server's database user finds all the tables from this schema. Is this something that should be considered by the schema migration job?

However, this looks to me as if the FHIR server does not prepend the schema name to the tables when accessing the DB. Is this the expected behavior?

[lmsurpre]

You are right that the fhir server doesn't prepend the schema name to the tables when it constructs SQL. However, its supposed to set the schema name globally (for that datasource) based on the config.

Can you confirm whether your fhir-server-config.json persistence section contains a currentSchema field like the following? https://github.com/LinuxForHealth/FHIR/blob/main/demo/basic/fhir/config/default/fhir-server-config.json#L295

[bauerjs1]

Yes I can confirm. The persistence section looks like this:

"datasources": {
    "default": {
        "type": "postgresql",
        "currentSchema": "fhirdata",
        "searchOptimizerOptions": {
            "from_collapse_limit": 12,
            "join_collapse_limit": 12
        }
    }
}

I am using chart version 0.7.1 with the following persistence parameters:

postgresql:
  enabled: false
db:
  type: postgresql
  host: pg-cluster.databases
  port: 5432
  enableTls: false
  name: ibm_fhir
  user: ibm-fhir
  dbSecret: ibm-fhir-db-credentials
  passwordSecretKey: pgPassword
  schema: fhirdata
schemaMigration:
  enabled: true
  ttlSecondsAfterFinished:

[lmsurpre]

hmm, could be a fhir server bug then, do you happen to have the stack trace of the "relation xyz not found" SQL errors?

[bauerjs1]

Sorry for the long delay here.

Today we stumbled upon a database issue somewhere else which led me back here. It is now clear to me what the problem is – we have a HA PostgreSQL cluster backing this FHIR server, among other apps. This cluster uses pgbouncer for connection pooling, which in turn cannot implement some startup parameters, such as extra_float_digits, or in this case the problematic search_path: https://github.com/pgbouncer/pgbouncer/issues/89#issuecomment-155717714

As a consequence, we configured pgbouncer to ignore these parameters (afaik that is the only way to make it work with connection pooling). Which obviously leads us to this issue. I am not a DB expert but it looks to me like a "cleaner" way would be explicit usage of the desired schema in each SQL statement, but this is clearly not an issue in this helm chart.

Can this problem be worked around by setting

db:
  schema: public

in the helm chart?

[lmsurpre]

To be clear, the helm chart takes that particular value and adds that to the currentSchema property of both the datasource configuration and also to the corresponding fhir-server-config section. The value in that fhir-server-config persistence currentSchema property is used by the application in a call to Connection.setSchema(String) for each connection it gets. OpenLiberty is doing its own connection pooling and I can't say that I'm familiar enough with pgbouncer to understand why that wouldn't work.

Do your tables exist in fhirdata or did they somehow end up in public?

[bauerjs1]

Thanks! My tables are located in fhirdata. If I move them to public or use that schema initially for fresh installs (so that the FHIR server initializes its tables in public), this should work, shouldn't it?

FYI, this issue explains some details about the pgbouncer issue: https://github.com/pgbouncer/pgbouncer/pull/73#issuecomment-143570901 If i get it right, the pooler would need to start analyzing statements which would introduce a lot of complexity and performance issues, because setting a search_path session-wide, and being able to change that, seems to be non-trivial if you are pooling connections. But again, I am far from being a database expert... 😉

[bauerjs1]

I've set up a new FHIR server with

db:
  schema: public

and it's working fine now without the need to alter the search_path manually within the db.

EDIT: Since usually all users can read tables in the public schema, this is also a workaround and should probably not be used in production environments. The better workaround (imho) is still setting the search_path on the database level like in the first comment, although it requires manual interaction with the database.