OHDSI Home | Forums | Wiki | Github

Auto-ganerated SQL for postgres problem in the Atlas

Developers
#1

Hi, My name is hansu chung.

One of my clients asked me that some auto-generated SQL query for Postgre is not working. It is just hanged.

But It the query for MSSQL is working well.

There is only one different structure between Postgre and MSSQL.

I have 3 files. A.text is automatically generated SQL, B.text is manually modified sql and C.text is auto-generated query for MSSQL.

We firstly execute the “AS WITH primary_events” in the create qualified events then execute the “create primary_events”.

CREATE TABLE
primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id)  AS (
-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
  select E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM 
  (
  -- Begin Condition Occurrence Criteria
SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + 1*INTERVAL'1 day')) as end_date,
       C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.condition_start_date as sort_date
FROM 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
We cannot change auto-generated sql query manually whenever it is not working properly.

I would like to ask an Atlas manager to change the auto-generated option of the Postgre DB.

Could you please check the auto-generated process of Postgre DB? 

Sincerely,

Hansu Chung
#2

Hello, Hansu Chung,
I’m sorry for the difficulties you’ve having with PostgreSQL. There is a known behavior with common table expressions (CTE) prior to version 12 where the query in the CTE is materialized as a separate, un-indexed table.

However, things have improved with version 12 and 13: common table expressions are optimized together with the rest of the query such that indexes and other query optimizations can be applied. This is when the CTE is NOT MATERIALIZED. You can read about it here: https://www.postgresql.org/docs/12/queries-with.html.

Do you have the option of working on PostgreSQL 13.0?

-Chris

Subject: ATLAS System : PostgreSQL Search Query Improvement Request!
#3

Hello Chris_Knoll,
Thank you for your kind answer.
The SQL that I’ve asked for is generated by Atlas.
I can change the query executable but the problem is the Atlas is still generating the wrong query and I can not change it.
I have looked for Atlas help desk. but I couldn’t

Do you know the contact point that I can communicate with this problem?

Thank you.

#4

You can post your information to the cohort definition repository here: https://github.com/ohdsi/circe-be. Please include the query files A, B and C.txt files as that will help figure out a solution. Thank you.

t