Postgres Persistence Provider problems

[AP_9591596]

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]

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

[EloyHz]

Is there any documentation on how to install Thingworx with PostgresDB manually?

[VladimirRosu]

Of course, it's in the online ThingWorx Help Center.

The 4th item from the top (or the 3rd if you only count the "+") is called "Installation and Configuration".

Note that the installation is also available as a PDF guide, but we strive more and more to get all the content in the online Help Center.

[AP_9591596]

Hi Vladimir,

thanks for the quick response, this definitely helps.

However, even if I fully configure the DB with the install SQL scripts it still doesn't work as I would expect.

Maybe some more background on what I want to achieve:

Actually I want to use that external Postgres DB only for historization of Value Streams. I don't want to store any historical values of Thing configurations etc. in that database. It should only serve as a historian for Streams just like an InfluxDB would - no more than that. So I don't see why I would need to create the full table structure etc. as for the regular TWX database... Very simply, one table that basically stores (time, value) pairs should be sufficient - I know it needs a bit more in a real application, but that's the core.

And that's kind of what I expected from the PostgresPersistenceProviderPackage. But obviously it doesn't work like that...? Do you get my point?

[VladimirRosu · Accepted solution]

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.

[AP_9591596]

Thanks for the support.

Turns out the problem was simply insufficient privileges in the relevant DB schema for the relevant user that TWX uses to connect. Finally, it works as expected

Since I want to use that database only for historization of value streams, I was even able to get rid of most of the tables + structures that are used by the platform for all the other config. The only structures I left in the database are basically the table value_stream and the function upsert_value_stream_entry. Works nicely.

Maybe only one wish: Some more elaborate error messages and debugging information around the setup and usage of persistence providers would be welcome.