OHDSI Home | Forums | Wiki | Github

ERROR: relation "results.achilles_results" does not exist

Implementers
#1

This is my last step (hopefully) of the work in the last two weeks - I did setup WebAPI, Atlas and backend CDM tables, and now I am loading Achilles in R, and encounter this error message:

DBMS:

postgresql

Error:

org.postgresql.util.PSQLException: ERROR: relation "results.achilles_results" does not exist

SQL:

create index idx_ar_aid on results.achilles_results (analysis_id)

R version:

R version 4.0.4 (2021-02-15)

Platform:

x86_64-apple-darwin17.0

Attached base packages:

- stats

- graphics

- grDevices

- utils

- datasets

- methods

- base

Other attached packages:

- Achilles (1.6.7)

- DatabaseConnector (3.0.0)

- devtools (2.3.2)

- usethis (2.0.1)

- rJava (0.9-13)

And here is what I have:

my CDM database backend:
– cdm schema which contains the cdm and vocab tables
– results schema, which is completely separate from cdm schema

then, I used this URL:

http://localhost:8089/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

to generate a long DDL script, and then I ran this script manually and created lots of tables under results schema (this is the step I am not sure about).

After all these, I followed the steps described here to install Achilles using R, and here are my two main steps:

connectionDetails <- createConnectionDetails(
dbms=“postgresql”,
server=“localhost/synpuf_CDM”,
user=“postgres”,
password=“xxxx”,
port=“5432”)

achilles(connectionDetails,
cdmDatabaseSchema = “cdm”,
resultsDatabaseSchema=“results”,
vocabDatabaseSchema = “cmd”,
numThreads = 1,
sourceName = “synpuf_CDM”,
cdmVersion = “5”,
runHeel = FALSE,
runCostAnalysis = FALSE)

and then I got this error:

Error:

org.postgresql.util.PSQLException: ERROR: relation "results.achilles_results" does not exist

and I do have some other error reports, for example,

-rw-r--r--    1 yul09  staff   888 Mar  1 15:11 achillesError_1425.txt
-rw-r--r--    1 yul09  staff  1042 Mar  1 16:03 achillesError_900.txt
-rw-r--r--    1 yul09  staff  1033 Mar  1 16:03 achillesError_901.txt
-rw-r--r--    1 yul09  staff  1339 Mar  1 16:03 achillesError_902.txt
-rw-r--r--    1 yul09  staff  2251 Mar  1 16:03 achillesError_903.txt
-rw-r--r--    1 yul09  staff  1515 Mar  1 16:03 achillesError_904.txt
-rw-r--r--    1 yul09  staff  1073 Mar  1 16:03 achillesError_906.txt
-rw-r--r--    1 yul09  staff  2607 Mar  1 16:03 achillesError_907.txt
-rw-r--r--    1 yul09  staff   990 Mar  1 16:03 achillesError_909.txt
-rw-r--r--    1 yul09  staff   812 Mar  1 16:03 achillesError_910.txt
-rw-r--r--    1 yul09  staff  1262 Mar  1 16:03 achillesError_920.txt
-rw-r--r--    1 yul09  staff  1075 Mar  1 16:03 achillesError_1000.txt
-rw-r--r--    1 yul09  staff  1066 Mar  1 16:03 achillesError_1001.txt
-rw-r--r--    1 yul09  staff  1392 Mar  1 16:03 achillesError_1002.txt
-rw-r--r--    1 yul09  staff  2280 Mar  1 16:03 achillesError_1003.txt
-rw-r--r--    1 yul09  staff  1568 Mar  1 16:03 achillesError_1004.txt
-rw-r--r--    1 yul09  staff  1126 Mar  1 16:03 achillesError_1006.txt
-rw-r--r--    1 yul09  staff  2646 Mar  1 16:03 achillesError_1007.txt
-rw-r--r--    1 yul09  staff  1012 Mar  1 16:03 achillesError_1009.txt
-rw-r--r--    1 yul09  staff   834 Mar  1 16:03 achillesError_1010.txt
-rw-r--r--    1 yul09  staff  1305 Mar  1 16:03 achillesError_1020.txt
-rw-r--r--    1 yul09  staff  7602 Mar  1 16:03 achillesError_1900.txt
-rw-r--r--    1 yul09  staff   838 Mar  1 16:03 achillesError_2200.txt
-rw-r--r--    1 yul09  staff   832 Mar  1 16:03 achillesError_2201.txt
-rw-r--r--    1 yul09  staff   484 Mar  1 16:03 errorReportSql.txt
drwxr-xr-x    4 yul09  staff   128 Mar  1 16:03 output

for example, 1425.txt says this,

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: column "payer_source_concept_id" does not exist
  Position: 116

SQL:
--HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TEMP TABLE s_tmpach_1425 
  
AS
SELECT
1425 as analysis_id,
       cast(payer_source_concept_id AS varchar(255)) AS stratum_1,
       cast(null AS varchar(255)) AS stratum_2,
       cast(null as varchar(255)) as stratum_3,
       cast(null as varchar(255)) as stratum_4,
       cast(null as varchar(255)) as stratum_5,
       COUNT(*) AS count_value
  
FROM
cdm.payer_plan_period
 group by payer_source_concept_id

R version:
R version 4.0.4 (2021-02-15)

Can someone please kindly let me know which part I did wrong? really appreciated any help I can get.

Thanks!

#2

Check the database configuration,Achilles can only connect to the cdm database instead of the webapi database ,and make sure that the cdm table has available data.If all goes well,You can clear the results database first,then execute Achilles and finally execute the sql script last.

Please follow the instructions below, maybe you will find the missing step.

#3

The DDL from WebAPI is a different set of tables than the tables created in the Achilles package. The only connection to WebAPi and Achilles is that if you write the Achilles tables to WebAPI’s results schema, WebAPI can read those tables for the ‘Data Sources’ section of Atlas.

Achilles should build the necessary tables, so it is strange that you get a ‘table does not exist’ when it’s tryign to build the index.

#4

Hi @Chris_Knoll,

Thank you for the help and explanation, as always.

I solved the problem, and let me share what I did here, so if anyone has the same issue, at least this is one thing she/he can try.

This error message,

org.postgresql.util.PSQLException: ERROR: relation "results.achilles_results" does not exist

is actually due to some other issues, which prevent it from being created successfully, and once we fix those other issues, the problem will go away and results.achilles_results table will be created.

How do you know what are the other issues? There are some txt documents created and there is also a log file, which can all be used as a clue about what these other issues are. For example, one txt file tells me the following issue:

DBMS:

postgresql

Error:

org.postgresql.util.PSQLException: ERROR: relation "cdm.specimen" does not exist

Position: 5180

SQL:

--HINT DISTRIBUTE_ON_KEY(stratum_1)

CREATE TEMP TABLE s_tmpach_1900

so I know I am missing this cdm.specimen table, which I can add it right away. Once I did that, this particular issue is fixed. By the way, the reason I did not have that table at the first place was because I was using the synpuf test dataset and I thought this specimen table was not needed.

Again, we just need to clear all the issues, one by one. Once all the issues are gone, the results table will be create and you can see the Data Source in Atlas. Since we are on this topic, I had one issue like this,

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: column de.drug_era_start_date does not exist
  Position: 454

SQL:
--HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TEMP TABLE s_tmpach_901

AS
SELECT
901 AS analysis_id,
        CAST(de.drug_concept_id AS VARCHAR(255)) AS stratum_1,
        CAST(NULL AS VARCHAR(255)) AS stratum_2,
        CAST(NULL AS VARCHAR(255)) AS stratum_3,
        CAST(NULL AS VARCHAR(255)) AS stratum_4,
        CAST(NULL AS VARCHAR(255)) AS stratum_5,
        COUNT(de.person_id) AS count_value

FROM
cdm.drug_era de

Note de is alias of cmd.drug_era. So is this some kind of version error? apparently, in my cdm.drug_era table, I had a field called column de.drug_era_start_datetime, but it was de.drug_era_start_date that Achilles tool is looking for. And I did specified cdmVersion = “5” … so this is still a big mystery to me.

One last question - I never truly understand about the DDL scripts generated by using this URL:

http://localhost:8089/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

exactly when should we use the generated DDL scripts?

Thanks,
liyang

1 Like
Running DDL scripts in the CDM source’s results schema
Getting an error running achilles
#5

@UltraXiaoZi, thank you! I saved your document, wished I had it earlier!

#6

One last question - I never truly understand about the DDL scripts generated by using this URL:

http://localhost:8089/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

exactly when should we use the generated DDL scripts?

Once you are ready to deploy a CDM database in Atlas, you will need to run these DDL scripts in the CDM source’s results schema. This is where cohort generation information is written.

#7

@Ajit_Londhe, thank you for clearing that!! so these scripts are NOT needed by Achilles?

#8

Right, don’t worry about running those statements from the results endpoint in WebAPI until you’re ready to deploy the source to Atlas. Achilles is a self-contained package, no need to create any tables ahead of time. If it tells you that achilles_results didn’t get created, it’s because 1 or more of the underlying analysis queries failed.

#9

@Ajit_Londhe, nice!! got it, got it. This is very helpful information, and thank you for confirming that!

1 Like
#10

Hi All,

i am getting the below error for one of the analysis.
could you tell me if there is any fix for this.

DBMS:
redshift

Error:
java.sql.SQLException: Amazon Invalid operation:
This type of correlated subquery pattern is not supported due to internal error;

SQL:
–HINT DISTRIBUTE_ON_KEY(stratum_1)

CREATE TABLE #s_tmpach_11

because of this error the final table achilles_results dint get created.

#11

Hi Guys, I received the same errors because i thought some of my achilles scripts are not working correctly.
The error log is :
System has not been booted with systemd as init system (PID 1). Can't operate. Failed to create bus connection: Host is down System has not been booted with systemd as init system (PID 1). Can't operate. System has not been booted with systemd as init system (PID 1). Can't operate. Failed to create bus connection: Host is down Failed to create bus connection: Host is down System has not been booted with systemd as init system (PID 1). Can't operate. Failed to create bus connection: Host is down System has not been booted with systemd as init system (PID 1). Can't operate. Failed to create bus connection: Host is down
One of the error file is attached below as well:

postgresql

Error:
org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.666231122" to 8388608 bytes: No space left on device
  Where: parallel worker

SQL:
--HINT DISTRIBUTE_ON_KEY(count_value)
CREATE TEMP TABLE tempResults_803

AS
WITH rawData(count_value)  AS (
SELECT 
	COUNT(DISTINCT o.observation_concept_id) AS count_value
FROM 
	cdm.observation o
JOIN 
	cdm.observation_period op 
ON 
	o.person_id = op.person_id
AND 
	o.observation_date >= op.observation_period_start_date
AND 
	o.observation_date <= op.observation_period_end_date
GROUP BY 
	o.person_id
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select CAST(avg(1.0 * count_value) AS NUMERIC) as avg_value,
    CAST(STDDEV(count_value) AS NUMERIC) as stdev_value,
    min(count_value) as min_value,
    max(count_value) as max_value,
    COUNT(*) as total
  from rawData
),
statsView (count_value, total, rn) as
(
  select count_value, 
  	COUNT(*) as total, 
		row_number() over (order by count_value) as rn
  FROM rawData
  group by count_value
),
priorStats (count_value, total, accumulated) as
(
  select s.count_value, s.total, sum(p.total) as accumulated
  from statsView s
  join statsView p on p.rn <= s.rn
  group by s.count_value, s.total, s.rn
)
 SELECT
803 as analysis_id,
  o.total as count_value,
  o.min_value,
	o.max_value,
	o.avg_value,
	o.stdev_value,
	MIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
	MIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
	MIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
	MIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
	MIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value

FROM
priorStats p
CROSS JOIN overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value


R version:
R version 4.1.2 (2021-11-01)

Platform:
x86_64-pc-linux-gnu

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- snow (0.4-4)

I deployed the CDM in docker container and the container should have enough space.
And also I don’t know how to enable systemd in ubuntu containers.

Thanks in advance for your help

1 Like
t