ATLAS/WebAPI - SQL for PostgreSQL sometimes renders with "dbo" as schema name

Implementers
1

I have run across some instances where the SQL rendered for PostgresSQL appends the “dbo.” text to the table names. I believe that earlier versions used this as a hack to use the default schema for MSSQL. I couldn’t find if this has been addressed in any of the versions. I am using the Broadsea container (with version 2.2.0 of ATLAS).

For example, in ATLAS, I clicked on the Jobs menu item from the left, and no job history showed up. When I turn on developer tools in Chrome, the error message on the console is:

GET http://localhost:8080/WebAPI/job/execution?comprehensivePage=true 500 (Internal Server Error)
send @ jquery-1.11.2.min.js:4
ajax @ jquery-1.11.2.min.js:4
self.updateJobs @ job-manager.js:10
jobManager @ job-manager.js:41
(anonymous) @ knockout.min.js:73
c @ knockout.min.js:78
(anonymous) @ knockout.min.js:79
(anonymous) @ knockout.min.js:72
setTimeout (async)
get @ knockout.min.js:72
a.w.q @ knockout.min.js:79
l @ knockout.min.js:44
h @ knockout.min.js:43
notifySubscribers @ knockout.min.js:33
d.W @ knockout.min.js:36
d @ knockout.min.js:36
(anonymous) @ app.js:173
execCb @ require.js:29
check @ require.js:18
enable @ require.js:23
init @ require.js:17
(anonymous) @ require.js:26
setTimeout (async)
g.nextTick @ require.js:32
j @ require.js:26
requirejs @ require.js:32
/jobs @ app.js:169
e @ director.min.js:7
f @ director.min.js:7
e.invoke @ director.min.js:7
h @ director.min.js:7
e.dispatch @ director.min.js:7
handler @ director.min.js:7
d @ director.min.js:7

In my PostgreSQL log is the following:

2018-04-03 14:28:03 PDT [7674-1] ohdsi_admin_user@ohdsi ERROR:  relation "dbo.batch_job_execution" does not exist at character 262
2018-04-03 14:28:03 PDT [7674-2] ohdsi_admin_user@ohdsi STATEMENT:  
select E.JOB_EXECUTION_ID, E.START_TIME, E.END_TIME, E.STATUS, E.EXIT_CODE, E.EXIT_MESSAGE, E.CREATE_TIME, E.LAST_UPDATED, E.VERSION,
 I.JOB_INSTANCE_ID, I.JOB_NAME, P.KEY_NAME, P.TYPE_CD, P.STRING_VAL, P.DATE_VAL, P.LONG_VAL, P.DOUBLE_VAL, P.IDENTIFYING 
	 from dbo.BATCH_JOB_EXECUTION E
	 join dbo.BATCH_JOB_INSTANCE I on I.JOB_INSTANCE_ID = E.JOB_INSTANCE_ID
	 left outer join dbo.BATCH_JOB_EXECUTION_PARAMS P on P.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID
	 order by E.START_TIME DESC
2

@ssrobertson Broadsea uses environment vars in the docker compose file to set things like the SQL dialect and database/schema prefix.

It may be helpful to compare your docker compose file with the below example:

postgresql docker-compose example

3

Lee, I double-checked again and my docker-compose.yml is identical to the example, except for the variables specific to my installation. There is no reference to MSSQL, and “dbo” does not show up anywhere. The configuration works for most operations, just not this one (I might have had another instance, but cannot remember where).
Would this issue be for the WebAPI or SQLRender folks?

4

Another error I get regarding Jobs is the following:

2018-04-03 16:20:46 PDT [9116-1] ohdsi_admin_user@ohdsi ERROR:  function rowcount() does not exist at character 8
2018-04-03 16:20:46 PDT [9116-2] ohdsi_admin_user@ohdsi HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2018-04-03 16:20:46 PDT [9116-3] ohdsi_admin_user@ohdsi STATEMENT:  select ROWCOUNT() OVER (ORDER BY tmp.analysis_id) as rn , *
	FROM ( select *
	        from ohdsi.heracles_results_dist
	        where cohort_definition_id = 29 ) tmp
	Where rn <= 100

The SQL that works properly is as follows:

	select ROW_NUMBER() OVER (ORDER BY tmp.analysis_id) as rn , *
	FROM ( select *
	        from ohdsi.heracles_results_dist
	        where cohort_definition_id = 29 ) tmp
	LIMIT 100;

I came across more recent source code that might help this out a bit, but it still used ROWCOUNT() which PostgreSQL does not support. I think the replacementPatterns.csv (below) should have a replacement for this, just like for netezza.

5

@lee_evans: It appears that the OHDSI ATLAS instance is not experiencing the problems I am having, and it also uses PostgreSQL. Could it be that these issues were fixed in the latest versions of the webtools? Do you have a scheduled date for updating Broadsea with the latest version of the tools?

For this proof of concept that I am building, I don’t want to go through the effort of installing components individually as it is so convenient to use the Broadsea containers.

6

@ssrobertson you can try using the Broadsea webtools version tagged as 2.3.1 here:

https://hub.docker.com/r/ltscomputingllc/broadsea-webtools/

7

Thank @lee_evans. I brought the container down, pulled version 2.3.1:

 docker pull ltscomputingllc/broadsea-webtools:2.3.1

then brought the container up, and the ATLAS version is still 2.2.0 and the WebAPI is still 2.2.0. When I click on Jobs, I still get the following error in the PostgreSQL (version 9.5) log:

2018-04-05 10:01:56 PDT [4374-1] ohdsi_admin_user@ohdsi ERROR:  relation "dbo.batch_job_execution" does not exist at character 262
2018-04-05 10:01:56 PDT [4374-2] ohdsi_admin_user@ohdsi STATEMENT:  select E.JOB_EXECUTION_ID, E.START_TIME, E.END_TIME, E.STATUS, E.EXIT_CODE, E.EXIT_MESSAGE, 
E.CREATE_TIME, E.LAST_UPDATED, E.VERSION, I.JOB_INSTANCE_ID, I.JOB_NAME, P.KEY_NAME, P.TYPE_CD, P.STRING_VAL, P.DATE_VAL, P.LONG_VAL, P.DOUBLE_VAL, P.IDENTIFYING 
	 from dbo.BATCH_JOB_EXECUTION E
	 join dbo.BATCH_JOB_INSTANCE I on I.JOB_INSTANCE_ID = E.JOB_INSTANCE_ID
	 left outer join dbo.BATCH_JOB_EXECUTION_PARAMS P on P.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID
	 order by E.START_TIME DESC

Is version 2.3.0 for ATLAS and WebPAI expected to be in the webtools container version 2.3.1? Or is there something I have done incorrectly?

8

@ssrobertson I looked into this issue and I have a solution for it.

FYI. I can confirm that ltscomputingllc/broadsea-webtools:2.3.1 contains ATLAS / WebAPI 2.3.0 (not sure why I tagged it 2.3.1). One thing to check is that as well pulling that docker image, you also updated the image reference in your docker-compose.yml file to:

image: ltscomputingllc/broadsea-webtools:2.3.1

The original issue you encountered was due to a regression in the postgresql docker-compose.yml introduced back in November 29th - my bad.

I’ve now corrected the postgresql docker-compose.yml file to add back the three missing environment vars. Please reference the corrected version is here:

https://github.com/OHDSI/Broadsea/blob/master/postgresql/docker-compose.yml

The three environment vars that I added back are:

- env=webapi-postgresql

- datasource.cdm.schema=cdm

- datasource.ohdsi.schema=ohdsi