Postgres Persistence provider [Data store unknown error]

[sabharees]

I tried to create Postgres persistent provider to store stream and value stream data. 

I am able to connect DB( checked using isConnected service) but not able to store and retrieve data, when I use the default service of add stream-entry I am not getting any errors. but I tried to access row count by calling GetStreamEntryCount then I got the error below-mentioned error message. 

I am getting errors like this : 

Unable to Invoke Service GetStreamEntryCount on Demo.Manual.STREAM : com.thingworx.common.exceptions.DataAccessException: [1,018] Data store unknown error: [Error occurred while accessing the data provider.]

[bchaudhary]

Hi @sabharees ,

This issue seems similar resolved in the given article. 

Kindly check and revert back the results Similar case .

Regards

Bhawna

[sabharees]

Hi @bchaudhary ,

Thanks for the link. But still, I am not able to resolve these issues.

Also in my application log, I found this error log: Transaction has been marked as a failure for request /Thingworx/Things/Test.PP.Stream/Services/QueryStreamEntriesWithData.

Regards,

Sab.

[bchaudhary]

Hi @sabharees , 

Can you please attach the application logs?

Regards

Bhawna

[sabharees]

[Jimwang]

@sabharees , you can find the Application log, Database log, Error log under <ThingworxStorage>/logs . It will be helpful if you can upload these log files here

[sabharees]

Hi @Jimwang & @bchaudhary,

Attached logs.

[Jimwang]

@sabharees ,

From the error log, you can see it reports that the table "stream" does not exist and function "upsert_stream_entry" doesn't exist, see the below error. You can check your Database and see if the table "stream" and function "upsert_stream_entry" exist or not. If there are there, does the DB user has enough permission to access to the database and schema?

=========

[Error occurred while accessing the data provider.] org.postgresql.util.PSQLException: ERROR: relation "stream" does not exist

=========

Batch entry 0 select * from upsert_stream_entry('Test.Stream.PP_Test','sravi','2021-08-25 18:30:00+00','{"meterName":"tedt","meterValue":1231.0,"timestamp":1629972000000}','0.0,0.0,0.0','User','[]') as result was aborted: ERROR: function upsert_stream_entry(character varying, character varying, unknown, jsonb, character varying, character varying, jsonb) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15 Call getNextException to see other errors in the batch.

=============

[Jimwang · Accepted solution]

@sabharees ,

You can refer to the article CS262935 (https://www.ptc.com/en/support/article/CS262935) to setup 2nd persistence provider.

The key steps are:

1> Create a DB User and grant the permission

2> Create a new Database

3> Grant enough permission for the new User to access to the database

4> Create Thingworx DB schema

5> In Thingworx Composer, Create a 2nd Persistence Provider and Configure the connection

[Jimwang]

@sabharees ，

1. Using the newly created DB user to login to the newly Database schema, and see if the table "stream" and function "upsert_stream_entry" exist or not in the Database.

2. If there are there, export your Persistence Provider and upload it here

[sabharees]

@Jimwang,

Issue has been found and resolved. It was from postgres DB side. 

Thanks for the help !!

Regards,

Sab.