Postgres Persistence Provider problems

Question

Hi all,
 
I am trying to set up a Persistence Provider that uses a PostgresDB (on another server which is not the TWX server). Steps that I took:
 
1) created a new Persistence Provider using the PostgresPersistenceProviderPackage (Name: "MyPostgresPersistenceProvider", I'm sure the Postgres JDBC connection string and credentials are correct)
2) created a new Value Stream using MyPostgresPersistenceProvider
3) created a new Thing using the new Value Stream
4) activated "Logging" for a handful of properties of that Thing.
 
So far so good, no error messages or anything.
 
Only when I try to access historical values of any property using, e.g., the service QueryNumberPropertyHistory(), I get the following error message:
 
 Unable to Invoke Service QueryNumberPropertyHistory on <Thing>: com.thingworx.common.exceptions.DataAccessException: [1.018] Data store unknown error: [Error occurred while accessing the data provider.]
 
Moreover, I can see in the Postgres DB that no table or anything has been created.
(I would expect TWX to create the necessary tables etc. in the Persistence Provider database automatically, right?)
 
So I guess the DB connection of the Persistence Provider is somehow not working (although it doesn't give me any negative feedback!).
Questions:
- Is there any way to test the DB connectoin of the Persistence Provider? (I have seen a service called "TestConnectivity", but that doesn't seem to do anything expect issueing the connection string again...)
- anything I missed or did wrong in the above steps?
 
By the way:
I have successfully connected to that same PostgresDB from a "normal Thing" (using the Database Thing template). So it's neither a credentials nor connection string nor firewall or similar issue. For some reason it just doesn't work with the Persistence Provider... Any ideas?

VladimirRosu_116627 · Accepted Answer

What exactly happens when you try to connect to the configured database through the new persistence provider? I saw a mention of "not working as I would expect", but I am not sure what that means.

Background explanation:

What you are trying to do is clear. You must however configure the DB with those SQL scripts, and if you're successfull, you should seel all the tables created in the DB. Not just the value_stream table, but all. Note that you just need to run the script, that does everything for you (albeit passing parameters is sometimes a pain, like paths with spaces etc.).

This is the only supported way to achieve what you're trying to do. If you insist on having a single table, you would need to remove the relevant lines from the Schema Setup sql file, but to be frank, I don't see any advantage in doing this, it just takes more time.

I have tested now this approach to be 100% sure and it works normally, I have two persistence providers and can setup my value stream to use the second.

Let us know what's the actual error.

VladimirRosu_116627 · Answer

The issue is relatively simple, and it is this assumption:

"(I would expect TWX to create the necessary tables etc. in the Persistence Provider database automatically, right?)"

If you remember, when ThingWorx is installed manually, there are a bunch of SQL scripts that you must run on your DB. I think the installer does that automatically (not sure though, I'm a manual install person).

But for sure, those SQL scripts are not embedded in the platform at all.

The persistence provider only connects to a "configured" database.

Hope it helps 😉

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded