.Net: Bug: Postgres upsert fails

[dluc]

The code below fails on UpsertAsync. Manual insert with SQL works fine instead.

This works:

INSERT INTO public."tests" ("id", "Count", "Vec")
VALUES ('1', 5, '[0.15, 0.25, 0.35]')
ON CONFLICT ("id")
DO UPDATE SET "Count"=5, "Vec"='[0.15, 0.25, 0.35]';

This fails:

public static async Task Run(string connString)
{
    var dataSource = NpgsqlDataSource.Create(connString);
    var db = new PostgresVectorStore(dataSource);
    var collection = db.GetCollection<string, Model>("tests") as PostgresVectorStoreRecordCollection<string, Model>;
    await collection.DeleteCollectionAsync().ConfigureAwait(false);
    await collection.CreateCollectionAsync().ConfigureAwait(false);

    // exception
    await collection.UpsertAsync(
        new Model { Id = "1", Count = 5, Vec = new ReadOnlyMemory<float>([0.15f, 0.25f, 0.35f]) });
}

public class Model
{
    [VectorStoreRecordKey(StoragePropertyName = "id")]
    public string Id { get; set; }

    [VectorStoreRecordVector(3)]
    public ReadOnlyMemory<float> Vec { get; set; }

    [VectorStoreRecordData(IsFilterable = true)]
    public int Count { get; set; }
}

Postgres: docker.io/pgvector/pgvector:pg17

Error:

Unhandled exception. Microsoft.Extensions.VectorData.VectorStoreOperationException: Call to vector store failed.
---> System.InvalidCastException: Writing values of 'Pgvector.Vector' is not supported for parameters having no NpgsqlDbType or DataTypeName. Try setting one of these values to the expected database type..
at Npgsql.Internal.AdoSerializerHelpers.g__ThrowWritingNotSupported|1_0(Type type, PgSerializerOptions options, Nullable1 pgTypeId, Nullable1 npgsqlDbType, Exception inner)
at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForWriting(Type type, Nullable1 pgTypeId, PgSerializerOptions options, Nullable1 npgsqlDbType)
at Npgsql.NpgsqlParameter.ResolveTypeInfo(PgSerializerOptions options)
at Npgsql.NpgsqlParameterCollection.ProcessParameters(PgSerializerOptions options, Boolean validateValues, CommandType commandType)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreDbClient.ExecuteNonQueryAsync(PostgresSqlCommandInfo commandInfo, CancellationToken cancellationToken) in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreDbClient.cs:line 266
at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreDbClient.ExecuteNonQueryAsync(PostgresSqlCommandInfo commandInfo, CancellationToken cancellationToken) in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreDbClient.cs:line 267
at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreDbClient.UpsertAsync(String tableName, Dictionary2 row, String keyColumn, CancellationToken cancellationToken) in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreDbClient.cs:line 122 at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreRecordCollection2.<>c__DisplayClass14_0.<b__1>d.MoveNext() in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreRecordCollection.cs:line 162
--- End of stack trace from previous location ---
at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreRecordCollection2.RunOperationAsync[T](String operationName, Func1 operation) in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreRecordCollection.cs:line 346
--- End of inner exception stack trace ---
at Microsoft.SemanticKernel.Connectors.Postgres.PostgresVectorStoreRecordCollection2.RunOperationAsync[T](String operationName, Func1 operation) in ~/dotnet/src/Connectors/Connectors.Memory.Postgres/PostgresVectorStoreRecordCollection.cs:line 350

[dluc]

Workarounds:

Option 1 (deprecated):

call NpgsqlConnection.GlobalTypeMapper.UseVector();

Option 2:

var builder = new NpgsqlDataSourceBuilder(connString);
builder.UseVector(); // <--
var db = new PostgresVectorStore(builder.Build());

[roji]

@dluc I can't reproduce this - can you post a runnable, minimal console program please? And which exact version of the MEVD connector are you running against (1.44.0-preview nuget, feature-vector-data-prev2 branch?)

[westey-m]

@dluc, note that UseVector is required. Since the connector is taking a NpgsqlDataSource as input, the caller needs to set this before passing it in.
This is set automatically when using the DI helpers that construct NpgsqlDataSource.
See https://learn.microsoft.com/en-us/semantic-kernel/concepts/vector-store-connectors/out-of-the-box-connectors/postgres-connector?pivots=programming-language-csharp#getting-started

I think there are two actions here:

1. See if there is a way to check if UseVector has been set, and throw if not on construction.
2. Update the docs page further to call this out in a limitations section similar to some of the other connectors.

[roji]

Good catch @westey-m.

See if there is a way to check if UseVector has been set, and throw if not on construction.

I'll confirm, but OTOH there's no way to currently introspect an NpgsqlDataSource to find out whether UseVector has been set.