Cannot Insert IDictionary<string,string> into Hstore column

ServiceStack.OrmLite PostgreSQL
1

In my model, I have a prop with type IDictionary<string,string> as indicated in npgsql type mapping. However when I Db.Insert(myModel) or Db.Save I get errors.

To me it looks like a possible mismatch between OrmLite and npgsql, but it is late in the evening.

With either IDictionary<string,string> or Dictionary<string,string> type prop

public IDictionary<string, string> MyColumnHs { get; set; }

I get the following error.

{Npgsql.PostgresException (0x80004005): 42804: column "my_column_hs" is of type hstore but expression is of type text
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Action`1 commandFilter, Boolean selectIdentity)
   at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass5_0`1.<Insert>b__0(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
   at MyApp.MyPageService.Any(MyPage request) in C:\MyApp\...}

If I add the [PgSqlHstore] attribute on the prop, I get the following error.

{System.InvalidCastException: Can't write CLR type System.String with handler type HstoreHandler
   at lambda_method(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
   at Npgsql.TypeHandling.NpgsqlTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) in C:\projects\npgsql\src\Npgsql\TypeHandling\NpgsqlTypeHandler`.cs:line 198
   at Npgsql.NpgsqlParameter.ValidateAndGetLength() in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 553
   at Npgsql.NpgsqlCommand.ValidateParameters() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 793
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1141
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Action`1 commandFilter, Boolean selectIdentity)
   at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass5_0`1.<Insert>b__0(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
   at MyApp.MyPageService.Any(MyPage request) in C:\MyApp...}
2

Complex types like Dictionaries in OrmLite are blobbed as strings, yet somehow the Exception indicates the field is a hstore which I’m assuming the table wasn’t created by OrmLite as the field needs to be a string.

3

The database tables are created externally from OrmLite and already exists when code runs. In this particular table we use a specific column order for minimizing table space by 10% to 20+%.

The table column in create is defined as my_column_hs hstore,.

4

OrmLite doesn’t natively support PostgreSQL’s hstore data type, you would need to use custom SQL if you want to access it.

5

Well that’s a bummer. Alright, we’ll try something different.

6

Just want to say it would be nice if PostgreSQL would support Hstore (via npgsql) when using IDictionary<string,string> type and/or possibly a specific attribute.

While this particular table isn’t completely a logging/audit table it’s functionality is the same as the postgres audit trigger (code) in it’s use of hstore in which we can easily determine differences between two or more hstores (key values) simply in SQL. Which isn’t possible as a OrmLite blob.

Simple SQL for determining “changed fields” in an updated row:

audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;`

The situation where “use custom sql” would be fine in SELECT ... FROM ..., but isn’t as simple when you need to parse and insert. When doing complex parsing it is considerably easier and probably more efficient to do the parsing in .NET than SQL.

Anyways, I’m not sure if there is a particular reason not to support hstore, especially when npgsql supports it, but we’re definitely in favor of it (UserVoice Feature) if anyone is up to adding the functionality. We would take the time, but at the moment we don’t have the time. Maybe in the future we’ll take the time.

The main point of this reply was to indicate the usefulness of hstore in certain situations (especially audit/logging like tables) and to link to the user voice feature.

7

@mythz hey you probably want a patch for OrmLite.PostgreSQL, but since the fix to get hstore working ended up being way more simple than expected I’ll post it below. Hopefully this can be included in the upcoming release.

public static class PostgreSqlDialectWithHstore
    {
        public static IOrmLiteDialectProvider Provider => PostgreSQLDialectProviderWithHStore.Instance;
    }

    public class PostgreSQLDialectProviderWithHStore : ServiceStack.OrmLite.PostgreSQL.PostgreSqlDialectProvider
    {
        public static PostgreSqlDialectProvider Instance = new PostgreSQLDialectProviderWithHStore();

        public PostgreSQLDialectProviderWithHStore() : base()
        {
            RegisterConverter<IDictionary<string, string>>(new PostgreSqlHstoreConverter());
            RegisterConverter<Dictionary<string, string>>(new PostgreSqlHstoreConverter());
        }

    }

    public class PostgreSqlHstoreConverter : ReferenceTypeConverter
    {
        public override string ColumnDefinition => "hstore";

        public override DbType DbType => DbType.Object;

        public override object FromDbValue(Type fieldType, object value)
        {
            return (IDictionary<string, string>)value;
        }

        public override object ToDbValue(Type fieldType, object value)
        {
            return (IDictionary<string, string>)value;
        }
    }

The main thing to take note of is the 2 RegisterConverter<> in the ctor. One for the interface and the second for the concrete type.

RegisterConverter<IDictionary<string, string>>(new PostgreSqlHstoreConverter());
RegisterConverter<Dictionary<string, string>>(new PostgreSqlHstoreConverter());
8

I’ve included this support for Hstore data types in this commit. It’s not enabled by default as it needs the hstore extension enabled in the postgresql database:

CREATE EXTENSION hstore;

You can then enable it in OrmLite with:

PostgreSqlDialectProvider.Instance.UseHstore = true;

Then you’ll be able to Insert/Save and Query Dictionary<string,string> or IDictionary<string,string> fields using hstore query syntax, e.g:

var results = db.Select(db.From<Table>().Where("column -> 'Key' = 'Value'"));

This change is available in the latest v5.4.1 that’s now available on MyGet.

1 Like
9

You’re AWESOME!!!

We’re good with UseHstore = true. Completely understandable.

PostgreSqlDialectProvider.Instance.UseHstore = true;
1 Like