PostgreSQL Library

[drjdpowell]

Googling suggests PQ should be threadsafe: https://www.postgresql.org/docs/9.1/libpq-threading.html

[ShaunR]

14 minutes ago, drjdpowell said:

Googling suggests PQ should be threadsafe: https://www.postgresql.org/docs/9.1/libpq-threading.html

It depends how it is compiled.

There seems to be a function to determine whether the binary is thread safe, yielding 1 if it is and zero if it isn't.

int PQisthreadsafe();

Source

[Antoine Chalons]

I've just pushed new branch to Dr Powell's repo, and built a new version of this package.

I used LabVIEW 2017 SP1.

What's new :

• added support for Linux Rt targets (possibly Linux in general but not tested) (issue #1)
• added support for boolean parameter (issue #2)
• fix a weakness in parameter detection (issue #3)

VIP 0.2.2-b16 can be downloaded from here.

[Antoine Chalons]

On 1/21/2021 at 1:44 PM, ShaunR said:

It depends how it is compiled.

There seems to be a function to determine whether the binary is thread safe, yielding 1 if it is and zero if it isn't.

int PQisthreadsafe();

Source

Running on Linux RT with the libpq.so compiled by NI : so they did build the library as thread safe

and I get the same on Windows with the libpq dll deployed with the VIP.

Edited February 25, 2021 by Antoine Chalons

[Antoine Chalons]

I've opened a support request with NI because on NI Linux RT, calling libpq.so with a threadsafe CLFN crashes LabVIEW while calling it with a non-threadsafe CLFN works fine.

Funny enough, the only exception I've found to this is using a threadsafe CLFN calling "PQisthreadsafe" with a threadsafe CLFN, calling any other function triggers a crash.

[Rolf Kalbermatter]

2 hours ago, Antoine Chalons said:

I've opened a support request with NI because on NI Linux RT, calling libpq.so with a threadsafe CLFN crashes LabVIEW while calling it with a non-threadsafe CLFN works fine.

Funny enough, the only exception I've found to this is using a threadsafe CLFN calling "PQisthreadsafe" with a threadsafe CLFN, calling any other function triggers a crash.

What do you mean with "threadsafe CLN"? It is a rather bogus terminology in this context.

What you have is "reentrant" which requires the library to be multithreading safe and "UI Thread", which will allow the library to do all kind of non multithreading safe things. That trying to call PGisthreadsafe() from any context is not crashing is to be expected. This function simply accesses a readonly information that was created at compile time and put in the library. There is absolutely nothing that could potentially cause threading issues in that function. That every other function simply crashes even if you observe proper data flow dependency so that functions never can attempt to access the same information at the same time, would be utterly strange. That would not be just the reentrant setting causing multithreading unsafe issues but something much more serious and basic.

I at least assume that you tried this also in single stepping highlighting mode? Does it still crash then?

Edited March 3, 2021 by Rolf Kalbermatter

[ShaunR]

3 hours ago, Antoine Chalons said:

Funny enough, the only exception I've found to this is using a threadsafe CLFN calling "PQisthreadsafe" with a threadsafe CLFN, calling any other function triggers a crash.

That's because it's hard-coded to return a simple Boolean when compiled.

int
PQisthreadsafe(void)
{
#ifdef ENABLE_THREAD_SAFETY
    return true;
#else
    return false;
#endif
}

 

[Antoine Chalons]

23 hours ago, Rolf Kalbermatter said:

What do you mean with "threadsafe CLN"? It is a rather bogus terminology in this context.

What you have is "reentrant" which requires the library to be multithreading safe and "UI Thread", which will allow the library to do all kind of non multithreading safe things. That trying to call PGisthreadsafe() from any context is not crashing is to be expected. This function simply accesses a readonly information that was created at compile time and put in the library. There is absolutely nothing that could potentially cause threading issues in that function. That every other function simply crashes even if you observe proper data flow dependency so that functions never can attempt to access the same information at the same time, would be utterly strange. That would not be just the reentrant setting causing multithreading unsafe issues but something much more serious and basic.

I at least assume that you tried this also in single stepping highlighting mode? Does it still crash then?

Sorry for the lack of clarity, what I meant by threadsafe CLFN is this : therefore : reentrant

As opposed to non-threadsafe CLFN : therefore : not reentrant.

In my applications I don't need reentrancy for my libpq calls, but as I was trying to make this package as generic as possible.

Yes I've tested step by step and running the same simple thing : PQconnect / PQfinish crashes when CLFNs are reentrant, not at the PQconnect though, at the PQfinish.

The data base that I try to access does exist and the settings in the string are correct.

The result is the same wether this VI is set as reentrant or not.

22 hours ago, ShaunR said:

That's because it's hard-coded to return a simple Boolean when compiled.

int
PQisthreadsafe(void)
{
#ifdef ENABLE_THREAD_SAFETY
    return true;
#else
    return false;
#endif
}

 

Ok, I'm fealing cheated. If I understand correctly it means NI's libpq.so pretends to be threadsafe but actually isn't.

[Rolf Kalbermatter]

43 minutes ago, Antoine Chalons said:

Ok, I'm fealing cheated. If I understand correctly it means NI's libpq.so pretends to be threadsafe but actually isn't.

Actually not exactly. NI set this compile define to make the shared library multi-threading safe, trusting the library developers to have done everything correctly to get this work like it should but somehow it doesn't. Still there is something seriously odd.

I could understand that things get nasty if you had other code running in the background also accessing this library at the time you do this test but if this is the only code accessing this shared library something is definitely odd. There is still only one call to the shared library at the time your PQfinish() executes so the actual protection from multiple threads accessing this library is really irrelevant.

So how did you happen to configure the PGconn "handle"? Is this a pointer sized integer variable? You are executing on an IC-7173 which is a Linux x64 target, so these "handles" are 64-bit big on your target but 32-bit if you execute the code on LabVIEW for Windows 32-bit! I'm just throwing out ideas here, but the crash from just calling one single function of a library in a reentrant CLN really doesn't make to much sense. The only other thing that could be relevant is if this library would use thread-local storage, but that would be brain damaged considering that it uses "handles" for its connections and can therefore store everything relevant in there instead.

And a warning anyways: While I doubt that you would find PG libraries that are not compiled as multithreading safe (this only really makes sense on targets that provide no proper threading support such as Windows threading or the Unix pthread system) there obviously is a chance that it could happen. You can choose to implement everything reentrant and on creation of a new connection, call the function that Shaun showed you. But what then? If that function returns false, all you can do is abort and return an error as you can not dynamically reconfigure the CLNs to use UI threading instead (well you can by using scripting but I doubt you want to do that on every connection establishment and scripting is also not available in a built application). So it does make your library potentially unusable if someone uses a binary shared library compiled to be not multithreading safe.

Edited March 4, 2021 by Rolf Kalbermatter

[Antoine Chalons]

@drjdpowell on both Windows and Linux (Ubuntu) with LV20 we have crashes (not 100% of the time though) when we send SQL command that break the constraints. After a lot of testing my colleague @kdevelle found the issue and fixed it, we will soon publish the fix in the repo (dev branch)

[Jordan Kuehn]

I couldn't find the code repository entry for this library, but I am getting an error: 

PQ Connection.lvclass:Connect.vitsdb CONNECTION_BAD: authentication method 10 not supported

with postgres versions 14.7 on linux, and 15.2 on pc, running the toolkit in LV2021 on windows.

Is there an issue with the authentication and SHA-256 on the newer versions of postgres?

[drjdpowell]

17 hours ago, Jordan Kuehn said:

I couldn't find the code repository entry for this library,

Here is the Issues lost: https://bitbucket.org/drjdpowell/pq-labview/issues?status=new&status=open

I am not actively working on this project, but it might be the case that the PQ dll used by the library needs updating.

 

[ciozi137]

Hi @Jordan Kuehn I recently had the same issue with not being able to use SHA-256 passwords. I had to change them in my database to md5. I think James is right that the library needs to be updated to support SHA-256. See also here: https://www.vipm.io/posts/6b996996-5212-4191-aef4-ccaf68c93ae7/ (Perhaps someone can recruit Tom to help us out?)

@drjdpowell Can you give me pull or commit access to the repository and I can help coordinate fixing this and other issues?

 

Patrick

 

 

[ciozi137]

@drjdpowell bumping this topic to see how best I can contribute to the postgres repo

 

[drjdpowell]

On 11/6/2023 at 1:31 PM, ciozi137 said:

@drjdpowell bumping this topic to see how best I can contribute to the postgres repo

 

Can you try forking the repo?  Then making a pull request?   I only have a free Bitbucket account and there is a limit on how many people I can add to my repos.

[ciozi137]

21 hours ago, drjdpowell said:

Can you try forking the repo?  Then making a pull request?   I only have a free Bitbucket account and there is a limit on how many people I can add to my repos.

Sounds good. I will make pull requests as I work through the open issues. I'll stay with LV 2017 for now per updates from @Antoine Chalons

[qiuhonglin]

On 11/18/2022 at 4:00 AM, Antoine Chalons said:

@drjdpowell on both Windows and Linux (Ubuntu) with LV20 we have crashes (not 100% of the time though) when we send SQL command that break the constraints. After a lot of testing my colleague @kdevelle found the issue and fixed it, we will soon publish the fix in the repo (dev branch)

Thanks for your effort. And how it goings?  The date of latest commit in Bitbucket is 2021-06-14. Have any new update? I am looking forward it.

 

[drjdpowell]

On 11/10/2023 at 2:09 PM, ciozi137 said:

Sounds good. I will make pull requests as I work through the open issues. I'll stay with LV 2017 for now per updates from @Antoine Chalons

I have published a 0.3.1 package on VIPM.io with Antoine's changes (LabVIEW 2017).  Then I've accepted your Pull Requests and published a 0.4.0 version as well (LabVIEW 2019):

https://www.vipm.io/package/jdp_science_postgresql/

[ciozi137]

thanks @drjdpowell 

[drjdpowell]

0.6.0 version now on VIPM: https://www.vipm.io/package/jdp_science_postgresql/

This involves significant improvements, as well as Examples that work with a public postgres server (and thus work without needing Postgres installed).  I am hoping this is close to a 1.0 version.

[drjdpowell]

Has anyone had a chance to use teh new version?  At least as far as trying the new examples.

[LogMAN]

I just went through the examples and everything appears to be working.

Edit: Running on Windows 11, using LabVIEW 2019 (32-bit)

As someone who is familiar with your SQLite library, it feels very familiar

Thanks for sharing! 

Edited November 25 by LogMAN