PostGre SQL Data fetching issue

kishoren0003,

Error Message you shared doesn't have enough information to debug. Check your Tomcat Server Log for more information and share here?

A possible reason could be your data store entity is not published properly.

Here the Log.... i could see below error 

2021-09-14 12:07:30,865 [http-nio-8080-exec-18] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:07:33,527 [http-nio-8080-exec-6] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:18:25,250 [http-nio-8080-exec-18] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:20:08,730 [http-nio-8080-exec-57] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:34:50,416 [http-nio-8080-exec-42] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 13:04:43,227 [Timer-4] INFO com.appiancorp.cache.DatabaseAuxiliaryCache - Removing expired entries from db cache: uiState-auxiliary
2021-09-14 13:09:18,034 [http-nio-8080-exec-50] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639

Here the Log.... i could see below error 

2021-09-14 12:07:30,865 [http-nio-8080-exec-18] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:07:33,527 [http-nio-8080-exec-6] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:18:25,250 [http-nio-8080-exec-18] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:20:08,730 [http-nio-8080-exec-57] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 12:34:50,416 [http-nio-8080-exec-42] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639
2021-09-14 13:04:43,227 [Timer-4] INFO com.appiancorp.cache.DatabaseAuxiliaryCache - Removing expired entries from db cache: uiState-auxiliary
2021-09-14 13:09:18,034 [http-nio-8080-exec-50] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: relation "AAF_Asset" does not exist
Position: 639

Can you check the below link to see if that helps? 

stackoverflow.com/.../cannot-simply-use-postgresql-table-name-relation-does-not-exist

I gone through the link which you shared and followed few steps which is given. but, still getting same issue.

even i have given my schema as default  search path in Postgre SQL for searching data from table. this is also did not worked. 

Kishore,

Is this fixed? if not, can you ensure the table name you have in CDT XSD has the same case like the one you have in the database?

Vimalkumar,

Yes, issue got fixed. 

but, while using Postgre SQL DB i found 2 issues.

1 Issue:- In Postgre SQL by default schema is Public whatever we created tables under public schema those table's records are fetching in Applian expression rule with out any issues.

if we created manually our own schema(Test-Schema) that is not working. not sure, whether this is postgre DB issue or something else. during the trail and errors i found this.

2 Issue:-  Actually, we created CDT using Appain cloud DB and later we changed to PostGreSQL. the datatyp's are changed as per DB column datatype DSE entity mapped verified successfully. 

post DSE verification, we were trying to read data from in expression rule getting unexpected error which i mentioned above chain conversation.

Resolution:- post data source test connection successful, i created one more CDT using existing DB for same table and  copied new XSD file content and pasted existing CDT XSD file. after it was working fine.

i am not sure why this did not worked before which we initially created CDT.

Change the default search path for the user which you are using to connect to the PostgreSQL database from public to your_schema using the following command:

ALTER ROLE <user_name> IN DATABASE <db_name> SET search_path TO "$user",your_schema_name;

After this, refresh your db once and then re-open your query entity rule and test. It will work for schemas other than public schema as well.


Hi, 

Can you please tell us how you migrated from Appian Cloud DB(Mysql) to Postgre?

Did you rewrite all the xsd's using python scripts?

Please reply back!

Thanks in advance!!