PostgreSQL Library

[drjdpowell]

A while back I posted a beta version of a client library to access Postgresql.  The project driving that work was put on hold, and I have not done significant work since.  But at least some people have used or have considered using it, so I wanted to see how many people that is.  There has also been a desire expressed to me about using it on RT, so I wanted to know if anyone has developed it to do that.

[drjdpowell]

Here is the latest version of the package.  To run the examples you will need a Postgres server/database and must modify the connection string to point to it (and have the right user and password, of course).  I just tried it with a fresh install of Postgres 10 and it reasonably painless.  This is LabVIEW 2015 (but if I developed this library further I would probably go the 2018-9 to access the power of malleable VIs).

jdp_science_postgresql-0.1.1.9.vip

Edited November 8, 2019 by drjdpowell

[smithd]

On 11/8/2019 at 5:24 AM, drjdpowell said:

A while back I posted a beta version of a client library to access Postgresql.  The project driving that work was put on hold, and I have not done significant work since.  But at least some people have used or have considered using it, so I wanted to see how many people that is.  There has also been a desire expressed to me about using it on RT, so I wanted to know if anyone has developed it to do that.

I've definitely used it, although we ended up going with a different db so the code never got used for real. I think I had tried it on lvrt but I can't really remember. I tend to think that talking to a central db from an rt target tends to break the nicely distributed nature of the systems and so I'm more likely to use your sqlite on rt, but I get that for test systems and the like (now that PXI RT Linux is a thing) might be more likely to talk directly to a postgres database.

[Dpeter]

Thank you for this.  Started playing with it a few days ago.  It is a bit clunky to migrate SQLite code because you've changed how you approach it.  That being said,  now that I look at the screenshot at the top of this thread, some of this makes a lot more sense than just concatenating an SQL statement and running it through the execute.  Hopefully at some point you get a chance to make this a bit more full featured and release it.

[drjdpowell]

On 5/8/2020 at 2:29 PM, Dpeter said:

It is a bit clunky to migrate SQLite code because you've changed how you approach it. 

That's because SQLite and PQ have different approaches, and I'm closely following how they work.  One could think of coming up with a combined API, but that might involve compromises.  

[MzazM]

Thank you for this library. We are using it for a few weeks now. Mainly for inserts and some reads. We basically followed the example available in the installation folder. No RT at the moment. 

[Antoine Chalons]

Any chance this package will ever support Linux RT targets? I would love if it could

 

I guess it means integrating libPQ.so, right?

[drjdpowell]

Theoretically, there are no roadblocks to developing it to work on RT.  See the SQLite Library package, which has conditional disables to point to the .so on Linux RT, as an example.

[Antoine Chalons]

cool, I'm on it.

 

I'll clone your bitbucket repo and create a branch to add RT support. Also, I only have LV2019 SP1, I guess once I'm done someone else can save back to an earlier version.

[Antoine Chalons]

On 1/6/2021 at 1:45 PM, drjdpowell said:

Theoretically, there are no roadblocks to developing it to work on RT.

Actually - for me - Linux is always a big roadblock 😢

I've taken a similar approach to what you did with SQLite - finding the lib path once at connection, store it in the class private data and setting all the CLFN with a library path input.

Of course running that on Windows goes fine, but when trying to run it on a Linux RT target (I have an IC-3173), as soon as the running code gets to a VI that calls the libpq.so.5.7 the execution stops and I get a message that the target was disconnected.

I checked the access right for the file /usr/lib/libpq.so.5.7 and they look fine for the account "lvuser"

 

I'm now thinking the issue in libPQ version because I followed this tutorial to install PostgreSQL client on NI Linux RT, it installs version 9.4.17 (latest available on http://download.ni.com/ni-linux-rt/feeds/2019/x64/core2-64/) and the version used in the package is 9.6

 

How can I install PostgreSQL client 9.6 on NI Linux RT ?

[Neil Pate]

2 hours ago, Antoine Chalons said:

Actually - for me - Linux is always a big roadblock 😢

I've taken a similar approach to what you did with SQLite - finding the lib path once at connection, store it in the class private data and setting all the CLFN with a library path input.

Of course running that on Windows goes fine, but when trying to run it on a Linux RT target (I have an IC-3173), as soon as the running code gets to a VI that calls the libpq.so.5.7 the execution stops and I get a message that the target was disconnected.

I checked the access right for the file /usr/lib/libpq.so.5.7 and they look fine for the account "lvuser"

 

I'm now thinking the issue in libPQ version because I followed this tutorial to install PostgreSQL client on NI Linux RT, it installs version 9.4.17 (latest available on http://download.ni.com/ni-linux-rt/feeds/2019/x64/core2-64/) and the version used in the package is 9.6

 

How can I install PostgreSQL client 9.6 on NI Linux RT ?

Are you sure you burned the correct amount of sage during the incantation?

[Antoine Chalons]

yeah... well I've tried various different amounts and waited for the full moon but it's still stuck.

 

After doing some digging 9.6 and 9.4 seem to have the same function names and the same arguments for the functions, so now I'm looking at the dependencies.

On Windows, Dr Powell includes libeay32.dll, libintl.dll & ssleay32.dll, so now I've got to rabbits to chase :

- my Linux RT is 64bit, so could that be an issue

- I can't find equivalent *.so file for these 3 dll

When I run

# opkg depends libpq*

I get this

And all 3 are installed with correct permissions

If anyone has any idea...

[ShaunR]

6 minutes ago, Antoine Chalons said:

If anyone has any idea...

If yo think it's a linking problem then ldd will give you the dependencies.

[Antoine Chalons]

It look like ldd should always work...

😢

 

[ShaunR]

1 hour ago, Antoine Chalons said:

It look like ldd should always work...

😢

 

/usr/bin/ldd or /usr/sbin/ldd depending on the distro.

But the fact that it isn't in your environment path maybe a clue to the problem.

[Antoine Chalons]

It just wasn't installed by default...

https://forums.ni.com/t5/NI-Linux-Real-Time-Discussions/LDD-on-NI-Linux-RT/m-p/4111499/thread-id/3368#M3369

 

[Antoine Chalons]

Ok, it took me some time but in the end, all I had to do to make it work is to set all the CLFNs to run in the UI thread.

I'm not a regular user of CLFNs so I'm not sure why and if it was the obvious thing to do but there we are.

[JKSH]

4 hours ago, Antoine Chalons said:

Ok, it took me some time but in the end, all I had to do to make it work is to set all the CLFNs to run in the UI thread.

I'm not a regular user of CLFNs so I'm not sure why and if it was the obvious thing to do but there we are.

I'm guessing that the library's functions are not thread-safe. Without forcing the CLFN to use the UI thread, it could use different threads to call the library functions... thus causing a crash.

[Antoine Chalons]

- I have no idea if what I'm going to say makes sense or not - on Windows the CLFN were all "any thread" and it worked fine.

[JKSH]

10 hours ago, Antoine Chalons said:

- I have no idea if what I'm going to say makes sense or not - on Windows the CLFN were all "any thread" and it worked fine.

Hmm, we don't know which thread(s) are chosen by the CLFN. If it happens to pick the same thread every call, then there will be no ill effects.

[kdevelle]

Hello
I noticed that inside the vi Single Command with parameters.vi boolean are not support. Would it be possible to add this support? It does not look complicated but I don't understant the Oids values

[drjdpowell]

On 1/11/2021 at 2:18 PM, JKSH said:

I'm guessing that the library's functions are not thread-safe. Without forcing the CLFN to use the UI thread, it could use different threads to call the library functions... thus causing a crash.

SQLite is thread safe unless it is changed from the default "Serialized" mode: https://www.sqlite.org/threadsafe.html

[Antoine Chalons]

I'm confused... what is the connection between SQLite and PostgreSQL?

[Rolf Kalbermatter]

50 minutes ago, Antoine Chalons said:

I'm confused... what is the connection between SQLite and PostgreSQL?

Most likely a cerebral shortcut with his more involved SQLLite library he has worked on so relentlessly over the years. 😀

There is a good chance that the Windows version of the PostgreSQL library is compiled in a way that supports multithreading calls while the Linux version may not. With the CLN set to UI thread, LabVIEW will always switch to the UI thread before invoking the library call, resulting that all such CLNs are always called in the same thread. With "Call in any thread" enabled, LabVIEW will call the shared library function in whatever thread the VI is executing itself. A VI is assigned a so called execution system and each executation system is assigned at startup of LabVIEW a number of threads (typically 8 but this does depend on the number of cores your CPU has and can also be reconfigured in your LabVIEW ini file). Each VI is typically executed in the thread of its caller from inside that thread collection for the execution system unless LabVIEW decides that parallel VIs can be executed in parallel in which case one of the VIs will be called in one of the other threads. LabVIEW does try to avoid unnecessary thread switches since the context switching between threads does incur some overhead.

In fact there are at least two potential problems from calling a library with multithreading:

1) If the library uses Thread Local Storage (TLS) to store things between calls, then calls occuring in different threads will access different information and that may cause problems. This should be not the problem here as each connection has its own "handle" and all the things across calls that are important should be stored in that handle and never in TLS.

2) If the library is not protected against simultaneous access to the contents inside the handle (or worse yet global variables in the library), things can go wrong as soon as your LabVIEW program has two different code paths that may run in parallel. It should not be a problem if your LabVIEW program guarantees sequential execution of functions through data flow dependency such as a consequently wired through handle wire and/or error cluster.

The fact that the VIs seem to hang immediately on first call of any function, could indicate that it is a Thread Local Storage problem. The loading of the shared library is most likely occurring in the UI thread to make sure that it does run in the so called root loop of LabVIEW and if the library then initializes TLS for some of its stuff, things would go wrong in the "Call in any thread" case as soon as the first function is executed, as it tries to access non initialized TLS values there. 

[drjdpowell]

1 hour ago, Antoine Chalons said:

I'm confused... what is the connection between SQLite and PostgreSQL?

My mistake.  There is an SQLite on Linux RT conversation also going on right now and I mixed the two up.