Jump to content

PostgreSQL Library


Recommended Posts

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.

PQLabVIEW.png.5a7e92b64efee9b53b92b352a02e0f45.png

Link to post
Share on other sites

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 by drjdpowell
Link to post
Share on other sites
  • 2 weeks later...
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.

Link to post
Share on other sites
  • 5 months later...

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.

Link to post
Share on other sites
  • 2 weeks later...
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.  

Link to post
Share on other sites
  • 4 weeks later...

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. 

Link to post
Share on other sites
  • 6 months later...

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.

Link to post
Share on other sites
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 ?

Link to post
Share on other sites
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?

  • Haha 1
Link to post
Share on other sites

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

image.png.050a0168494a9ee385a2985c6b2f4432.png

And all 3 are installed with correct permissions

If anyone has any idea...

Link to post
Share on other sites
1 hour ago, Antoine Chalons said:

It look like ldd should always work...

image.png.cc7dd467a949290dc3ef7b37bad048e2.png

😢

 

image.png

/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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

Link to post
Share on other sites
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.

Link to post
Share on other sites

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

Link to post
Share on other sites
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

Link to post
Share on other sites
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. 

Link to post
Share on other sites
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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.