PostgreSQL citext field issue

Posts   
 
    
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 08-Aug-2016 12:03:01   

Hi,

I'm not able to fetch a row using predicate expression that is comparing a citext field with string value. So I have the following code that should work case-insensitive filter:

bucket.PredicateExpression.Add(MyEntitiyFields.Field == phrase);

which results to

WHERE  ("public"."MyEntity"."Field" = :p1) 

Params (using ORM Profiler): Name DbType Value

:p1         String  active

Field DbValue is "Active"

It seems to me that the problem is with parameter DbType string, which results in the following query plan:

Filter: (("Field")::text = 'active'::text

Which filters out the row with field value Active because case-sensitive filter is performed.

Filter: (("Field")::citext = 'active'::citext

or when used without casting works as expected.

Any ideas how to fix this or any workarounds ?

I'm using LLBLGen 4.2.15.721

Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Aug-2016 13:03:04   

what npgsql version are you using? Please first try the latest build of npgsql and our runtime.

Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 08-Aug-2016 14:29:13   

I have tried the 4.2.16.725 and NpgSQL 2.2.7/3.1.7 and all of them produce the same result.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Aug-2016 16:19:30   

Ah I think what's wrong. Please use the following: http://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringpredicateclasses.htm#examples-5 (works in v4.2 too)

The tip at the bottom of the section explains what to do:

IPredicate p = CustomerFields.CompanyName.Equal("FOO INC").CaseInsensitive();

will produce an UPPER(field) = "FOO INC" comparison.

If that doesn't work, the only way to do it I think is to use a DbFunctionCall, to embed the citext directive. Do you happen to know how to tell npgsql to use citext:: instead of text:: ?

Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 08-Aug-2016 16:41:57   

It seems to me that this should be working out of the box in NpgSQL, I don't know how to force it to use citext cast but I'm poking around and will let you know if I find anything.

Thanks

khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 09-Aug-2016 07:32:36   

Hi,

for now I have found that you can force the driver to use citext by using NpgsqlDbType.Citext instead of DbType.String on parameter, but now I can see that DBSpecific project potentially holds wrong information inside the PersistenceInfoProvider.cs, where for citext field you output Varchar so I don't know how will you know when to use citext.

Regards

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2016 10:13:53   

The 'Varchar' is the value of NpgsqlDbType to which the parameter's type property is set (we don't use DbType, we generate a method in IL which sets the provider specific type, based on the value in the mapping data). So in theory, to fix this the value in the persistenceinfo.cs should be 'Citext' instead of 'Varchar' for that field.

But that's tricky, as the 'Varchar' is the type read from the DB meta data. So a driver change with support for 'Citext' won't help, as it can't detect such a field and will see 'varchar' next time you refresh.

I also think it's not really the right place to state the field is a 'Citext' field, as the Citext directive is query specific. What if you need to query the field later on with case sensitive data?

the best way to solve this IMHO is to use what's available for that: set the CaseInsensitive flag on the predicate and use an uppercased value to compare with.

Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 09-Aug-2016 14:41:25   

I have manually put citext in the persistenceinfo and everything is working properly, so I think that citext is the proper value for this if the filed type in the database is citext.

What do you mean by read from DB meta data ? I think DB should return citext as field type ?

_I also think it's not really the right place to state the field is a 'Citext' field, as the Citext directive is query specific. What if you need to query the field later on with case sensitive data? _ In my opinion if the field is citext then default behavior should be to operate on the field with case-insensitivity and if one want to do a case-sensitive search I'll do a cast or apply your suggestion.

_the best way to solve this IMHO is to use what's available for that: set the CaseInsensitive flag on the predicate and use an uppercased value to compare with. _ This can be used as an alternative, but has two drawback in my opinion, one is that I need to change / handle the case-insensitivity manually and the second, index on citext field won't be used (not 100% sure this is correct I'll try to find a source of this info).

I see you have text option in dropdown on edit field dialog in the designer so is there a chance to read the meta data from the db and get the citext in there ?

BTW it seems to me that ORM profiler doesn't know how to handle citext when I try to execute the query and plan. My application is returning the result when I manually set citext in persistence but ORM Profile returns zero rows.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2016 15:56:37   

khorvat wrote:

I have manually put citext in the persistenceinfo and everything is working properly, so I think that citext is the proper value for this if the filed type in the database is citext.

What do you mean by read from DB meta data ? I think DB should return citext as field type ?

Reverse engineering the schema will make the driver see 'text' fields and not 'citext' fields, as the driver currently doesn't know anything about citext. I think they currently fall back to the varchar type as the type is unknown.

I see it's an extension type, which we don't support by default.

I also think it's not really the right place to state the field is a 'Citext' field, as the Citext directive is query specific. What if you need to query the field later on with case sensitive data?

In my opinion if the field is citext then default behavior should be to operate on the field with case-insensitivity and if one want to do a case-sensitive search I'll do a cast or apply your suggestion.

That's true, but as currently we don't support citext, there's not a lot left to do.

the best way to solve this IMHO is to use what's available for that: set the CaseInsensitive flag on the predicate and use an uppercased value to compare with.

This can be used as an alternative, but has two drawback in my opinion, one is that I need to change / handle the case-insensitivity manually and the second, index on citext field won't be used (not 100% sure this is correct I'll try to find a source of this info).

The postgresql docs on citext indeed state the upper() (or in their case 'lower()') usage is less efficient, as it doesn't use an index, unless you define the index with the function. See https://www.postgresql.org/docs/9.1/static/citext.html

I see you have text option in dropdown on edit field dialog in the designer so is there a chance to read the meta data from the db and get the citext in there ?

Yes, but you need to alter the driver for that. The driver contains tables to convert from a DB type (e.g. 'text') to .NET type and the NpgsqlDbType enum value and to string for display purposes. Adding a line to these tables for citext is enough to support it.

I'll see if it's easy to install citext on a postgresql server, if not, then you unfortunately have to use the workaround.

BTW it seems to me that ORM profiler doesn't know how to handle citext when I try to execute the query and plan. My application is returning the result when I manually set citext in persistence but ORM Profile returns zero rows.

That's because orm profiler uses DbType, as that's all it has. (ado.net provider specific types convert to dbtype under the hood. orm profiler works with DbCommand/DbConnection objects only, so doesn't know of any ado.net provider specific type and can't set it to citext)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2016 16:22:09   

I've created a citext field on our test postgresql server. The meta data says it's a 'user defined' type. You're in luck, we already added code for supporting userdefined types to the driver, namely for the spatial types.

I'll add citext to the set of types supported in the driver. Just a sec.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2016 16:43:43   

Could you please test the attached driver? It's for v4.2. To use, is simply copy the dll as administrator over the existing one in <installation folder>\Drivers\Postgresql.

Then, start the designer and load your project. Refresh the catalog and you should see all your citext fields as 'citext' fields in the catalog explorer. You can then generate code and all should work as expected from then on. Refresh is important to make the new driver pick up the right types.

It's a debug build. Tests here work, so I don't expect any problems on your side but just in case.

Attachments
Filename File size Added on Approval
HOTFIX_PostgreSqlDBDriver_42_09082016.zip 22,308 09-Aug-2016 16:43.49 Approved
Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 09-Aug-2016 17:21:50   

Great, I'll test this and get back to you. I suppose just simple dll drop to designer folder will work ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2016 17:34:44   

khorvat wrote:

Great, I'll test this and get back to you. I suppose just simple dll drop to designer folder will work ?

Yes, copy as administrator. It might be windows wants you to 'unlock' the dll first, so unpack first, then check whether you need to unblock it by right-clicking it in explorer -> properties. Then copy it as administrator in <installation folder>\Drivers\Postgresql

Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 10-Aug-2016 08:54:16   

I can confirm that this patch is working, it has properly detected citext fields, made proper output to persistence info and now query gives proper results.

Do you plan to push this patch to new 4.2 build or ?

Few notes:

  • ORM Profiler has the similar issue, it doesn't detect the citext and when you try to execute Query Plan Stats you get that ::text to ::text case information because of the DbType.String. Same happens when you try to get Result Set, you don't get any results because case-sensitive query has been made while my app is successfully getting results. So this is misleading and if you are going to fix this let me know so I can install new version.

  • I hope this patch will find its way to 5.0.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Aug-2016 10:25:26   

khorvat wrote:

I can confirm that this patch is working, it has properly detected citext fields, made proper output to persistence info and now query gives proper results.

Do you plan to push this patch to new 4.2 build or ?

Yes, later today.

Few notes:

  • ORM Profiler has the similar issue, it doesn't detect the citext and when you try to execute Query Plan Stats you get that ::text to ::text case information because of the DbType.String. Same happens when you try to get Result Set, you don't get any results because case-sensitive query has been made while my app is successfully getting results. So this is misleading and if you are going to fix this let me know so I can install new version.

I think you should read my posts better wink I already explained why this fails above: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=135941&ThreadID=23945 ->

That's because orm profiler uses DbType, as that's all it has. (ado.net provider specific types convert to dbtype under the hood. orm profiler works with DbCommand/DbConnection objects only, so doesn't know of any ado.net provider specific type and can't set it to citext)

so unfortunately, not fixable. The profiler wraps the factory, and intercepts when things are executed that way as it wraps commands, connections etc. It doesn't wrap parameters. So any ado.net provider specific property that's set isn't intercepted (as that would be impossible). When the command is intercepted, the parameters are read, but at the DbParameter level. It doesnt read ado.net provider specific properties, and even if it did, it has no notion of how to set them at the client level: it has no ado.net provider specific code, so this won't work in the profiler.

  • I hope this patch will find its way to 5.0.

v5.0.5 hotfix available on the website was refreshed with the fix for this yesterday simple_smile

Frans Bouma | Lead developer LLBLGen Pro
khorvat avatar
khorvat
User
Posts: 65
Joined: 17-Feb-2011
# Posted on: 10-Aug-2016 12:09:45   

Ok I see why ORM can't do this properly. Thanks for everything I'll download the 4.2 build tomorrow and give it a try.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Aug-2016 21:38:12   

The new v4.2 build is now available simple_smile

Frans Bouma | Lead developer LLBLGen Pro