Oftentimes dealing with Database logic is like pulling teeth. Other times, it’s like being knocked unconscious by having a fruit bat thrown at your head. Each Database technology has it’s own advantages and disadvantages and PostGres is no exception.

Using and accessing a PostGres Database from a .NET Application is simple enough. In fact, the standard PostGres setup even includes the developer tools- including the .NET PostGres database connector, which work in the same fashion as other ADO.NET compatible types.

BLOB fields, or “Binary Large OBjects” are essentially when you have a column which simply contains a blob of binary data. This is common for storing various forms of arbitrary data such as images or other binary formats. There are some special considerations that need to be taken, however, when dealing with these fields in PostGres.

Make sure to perform uploading and downloading off of the UI thread

Because the size of the data can be non-trivial, you don’t want to block your UI thread while that data is being retrieved. eg, don’t do something like this:

The issue here is that the “ExecuteNonQuery” is going to cause problems if the data to send is large, or takes a long time to squirt over the network. In my testing, symptoms ranged from an Exception being thrown regarding the Connection being “broken”, to the CLR itself having an internal Exception regarding Thread Context switches, since no Message Pumps were being run for the duration. The solution is reasonably simple: spin it off into a separate thread. If you are using C# 5, you could even use Tasks or Asynchronous methods. Taking the above snippet, we can threadify it thus:

This will spin off the logic to a separate thread. Of course this particular snippet ignores the fact that errors can and will eventually occur that need user intervention. If you wanted to use a Task-based approach, you could use C# 5.0 features and simply make the method an async method. This would have the advantage in that it would let you run more than one task at once (in the threaded example there was only one thread- it had to be kept locally because otherwise the Thread variable would go out of scope when the method exited, putting the Thread itself up for Garbage Collection and disposal which would of course terminate the thread. Downloading has similar rules. One thing that bit me is that the common understanding of CommandBehaviour.SequentialAccess seem to no longer apply. Normally with SequentialAccess set, yo ucan access the blob column and read bytes from it sequentially. With PostGres, however, it seems that you simply cannot access that same column more that once- so once you perform that first read, the column is lost to you forever. Thus you need to read the entire column of Blob Data at once. Since it’s typically pretty big (depending on what is being stored) you should either spin off another thread or otherwise get that processing off your UI thread. Note also that like the Upload procedure it may be desirable to crank up your commandTimeout parameter to an otherwise unreasonable value.

I have yet to find a good way to provide reasonable UI feedback about the upload or download process, and have had to resort to marquee that simply shows that something is happening, but not how soon it will be complete, how fast it’s going, etc. This data is simply not within our grasp and is bottled away within the actual query execution, and we have no way to hook into that (certainly nothing that isn’t expensive in terms of implementation). For the purpose I was using it for, this has suited us well, since it opened up the program to remove a myriad of other problems involving UAC and Network Share authentication. It is likely that I simply wasn’t able to figure out how it works; the fact that my attempts used code based on code that does work on MySQL makes me think it may be a incorrect implementation of the SequentialAccess scheme used by PostGres for bytea fields.