ERROR: index "idx_ar_aid" does not exist SQL: drop index ohdsi.idx_ar_aid

Implementers
1

Hello everybody,

I have managed to import data (OMOP CDMV5) to my cdm schema in the database, using the scripts from:

https://github.com/mustafaascha/ubuntu-synpuf-cdm

All seems to have gone fine. When running Achilles (v1.6.7) though, I can see in the error log files that something went wrong at some point. There has been an error logged:

Error:
org.postgresql.util.PSQLException: ERROR: index “idx_ar_aid” does not exist

SQL:
drop index ohdsi.idx_ar_aid

After Achilles finished, I can see on ATLAS(v2.7.2 using Broadsea Docker image), many of the data-sources reports (Dashboard up to visit and death). The rest of the reports load an error message (Error loading report) though.
After some search it seems that concept_hierarchy table has not been created.

Any idea somebody what could have gone wrong? @Ajit_Londhe maybe? I have seen through posts that you might be familiar with similar problems.

Thanks in advance

1 Like
2

@blasisd – there’s 2 issues at play here:

  1. the index error message
  2. concept_hierarchy not created

Issue 1:

Short answer: don’t worry about this error message.
Long answer: Because the syntax “drop index if exists” doesn’t work for all OHDSI SQL platforms, I have to run the “drop index” command in a try-catch. However, while the try-catch block allows the R execution to continue, the error logger still picks it up. So, when the “drop index” step fails if you don’t already have an index, it throws an error message, but all is well. I will add a commit to handle this more gracefully (https://github.com/OHDSI/Achilles/issues/461).

Issue 2:

The concept hierarchy table is no longer created by Achilles. Rather, you need to create it yourself by grabbing the results DDL script from WebAPI (replace the bolded items as appropriate):

/ddl/results?dialect=postgresql&schema=ohdsi&vocabSchema=vocab5

This will provide a script to deploy all your results tables, including the concept_hierarchy table.

1 Like
3

You were right @Ajit_Londhe

Everything seems to be working fine now!

Thank you very much!

4

Hi all and congrats for this great community!!!
I have exactly the same error.However i observe that once i run the achilles with one thread it completes the task but once i choose the option of multiple threads i get the aforementioned error.
I have used the last code from github

here are some more details

Error:
org.postgresql.util.PSQLException: ERROR: index “idx_ar_aid” does not exist

SQL:
drop index ohdsi.idx_ar_aid

R version:
R version 3.6.3 (2020-02-29)

Platform:
x86_64-pc-linux-gnu

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • Achilles (1.6.7)
  • DatabaseConnector (4.0.2)