OrmLite 4.5.6 & Npgsql 3 & Postgres Arrays -- Issue

There is a StackOverflow question on this located here (we upgraded from 4.0.39 to 4.5.6):

Issue is probably similar to Postgres String Arrays Broken

Gist of code showing issue using NUnit

However the main difference is that none of our models contain or use the array fields and the models are not populated.

We use SELECT *, array_to_json(my_array) AS my_json. In otherwords our tables and views contain the array fields and they are returned because of the SELECT * however they are not contained in Models as we use the json version my_json.

It would be a significant undertaking to go thru all the queries and explicitly name each field that we want when there is typcially only 1 or 2 fields that would contain an array field which is not used in the model.

So is it possible if the array field isn’t in the model to skip the array field(s) and populate the other fields by default? Because right now all fields are populated with null or default values, unless the array is NULL, then it populates the model.

For the curious, the reason why we use arrays instead of json was when we were evaluating Postgres 9.3 (when json was first available) json performance was terrible and queries took longer. A simple switch to arrays alleviated that issue and increased performance. I’m unsure if 9.6+ has fixed that issue or if jsonb would be better.

The Exception raised in the 2nd test is the use of array_agg() which Npgsql doesn’t support and throws The field 'color_array' has a type currently unknown to Npgsql (OID 101487) when trying to read it from the data reader in a batch with GetValues() which is the optimal way to fetch a dataset from an ADO.NET Data Reader.

I’ve added a fallback in this commit where if GetValues() fails it will fallback to read from the DataReader individually which will resolve the issue in the tests. It logs a warning to indicate that there was an Exception trying to call the batched GetValues() API and instead tries to falling back to individual column fetches.

You can avoid using the optimized GetValues() altogether by configuring it to use the Deoptimized code path with:

OrmLiteConfig.DeoptimizeReader = true;

Which will avoid attempting to use GetValues() and avoid its Exception.

The fallback is available from v4.5.7 that’s now available on MyGet.

Thanks! We haven’t finished testing, but so far so good. On a side note, when I was researching about the issue this morning I came across this Npgsql Issue 1324. Your committed fix should also avoid the issue of postgres infinite dates in DateTime values as that would have been the next issue we found.

I quoted below and bolded the interesting information from that Npgsql issue that GetValue() and GetValues() should be avoided. It is possible that he is talking in a specific context of having to get an unknown type and not in general usage. Thought I should point that out if it does matter.

If your application needs values not covered by DateTime (e.g.
infinity), just use NpgsqlDateTime by calling
GetProviderSpecificValue(), or even better, GetFieldValue. This is a
much better option than having an inconsistent API that sometimes
returns DateTime and sometimes NpgsqlDateTime. True, you can’t use
GetValues() to get all values at once, but you have all the alternatives
you need.
On a side note, GetValues(), GetValue() and GetProviderSpecificValue() should be avoided in many cases anyway, since they box your values (as opposed to GetFieldValue).

We’ve found GetValues() to provide better performance which is why we’ve switched to using it by default.