PostgreSQL dialect design

Supported database versions

SQL persistence supports PostgreSQL 10 and above, as well as AWS Aurora PostgreSQL.

Usage

Use the Npgsql NuGet Package.

var connection = "Server=localhost;Port=5432;Database=dbname;User Id=user;Password=pass;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new NpgsqlConnection(connection);
    });

Token Authentication

To connect using token credentials, a User ID must be supplied in the connection string with the password supplied from the access token. Given that the token is only short-lived, a data source builder must be utilized to handle password refreshes. The following example uses Microsoft Entra ID.

var connection = "Server=test.postgres.database.azure.com;Database=postgres;Port=5432;User Id=<entra user id>;Ssl Mode=Require;";
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connection);
if (string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
{
    dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var credentials = new DefaultAzureCredential();
        var token = await credentials.GetTokenAsync(new TokenRequestContext(["https://ossrdbms-aad.database.windows.net/.default"]), ct);
        return token.Token;
    }, TimeSpan.FromHours(24), TimeSpan.FromSeconds(10));
}

var builder = dataSourceBuilder.Build();
persistence.ConnectionBuilder(connectionBuilder: () => builder.CreateConnection());

Passing Jsonb as NpgsqlDbType

Npgsql requires parameters that pass JSONB data to have NpgsqlParameter.NpgsqlDbType explicitly set to Npgsql​Db​Type.Jsonb. Npgsql does not infer this based on the DB column type. It is not possible for the Sql Persistence to control this setting while still avoiding a reference to Npgsql.

As such it is necessary to explicitly set NpgsqlParameter.NpgsqlDbType to NpgsqlDbType.Jsonb:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });

Json.Net TypeNameHandling

When using Json.Net $type feature via TypeNameHandling, MetadataPropertyHandling should be set to ReadAhead.

var settings = new JsonSerializerSettings
{
    TypeNameHandling = TypeNameHandling.Auto,
    MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead
};
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.JsonSettings(settings);

This is because Json.Net normally expects the $type metadata to be the first property of each object for best efficiency in deserialization. If the $type does not appear first, then Json.Net assumes it isn't there. When using the PostgreSQL Jsonb the JSON stored does not preserve the order of object keys. This can result in storing the $type in a non-first position.

Unicode support

SQL persistence itself supports Unicode characters, however data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine. In particular set the correct character set and collation for databases storing persistence data.

Schema support

The PostgreSQL dialect supports multiple schemas. By default, when schema is not specified, it uses public schema when referring to database objects.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.Schema("MySchema");

Case sensitivity

Unless explicitly specified, PostgreSQL automatically lower-cases all identifier names (e.g. column or table names, etc.). To enforce case sensitivity, it is necessary to quote all names.

SQL persistence internally honors the UpperCamelCase (also called PascalCase) convention, which is the standard default in other popular database engines, e.g. MS SQL Server. SQL persistence uses quoted identifier names in stored procedures, queries, etc.

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length.

The SQL Persistence provides autonomy between endpoints by using separate tables for every endpoint based on the endpoint name. By default PostgreSQL limits object names to 63 characters.

By default, SQL persistence uses an endpoint's name as a table prefix, the maximum length of the table prefix is 20 characters. The table prefix can be customized.

Using pg_temp. schema in installation scripts

The table creation for the SQL Persistence requires some dynamic SQL script execution. To achieve this in PostgreSql it is necessary to create temporary functions. These functions are created in the PostgreSql temporary-table schema, commonly referred to as pg_temp.

As per Client Connection Defaults pg_temp is:

The current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp