Table of Contents

Npgsql 6.0 Release Notes

Npgsql version 6.0 has been released and is available on nuget.

Npgsql 6.0 brings some major breaking changes and is not a simple in-place upgrade. Carefully read the breaking change notes below and upgrade with care.

New features

The full list of issues for this release is available here.

Multiple hosts, load balancing and failover

Npgsql now includes first-class support for managing connections to multiple PostgreSQL servers, unlocking both load balancing for better performance and failover for better reliability (#732). This long-awaited feature is an important part of using PostgreSQL in scalable and mission-critical environments.

For example, consider the following connection string:

Host=server1,server2,server3,server4,server5;Username=test;Password=test;Load Balance Hosts=true;Target Session Attributes=prefer-standby

This will make Npgsql load balance connections across 5 different servers, in round-robbin fashion; connections to read-only standby servers is preferred in order to offload as much read-only load from the primary server, but if only the primary server is up it will still be used.

The multiple hosts feature is highly configurable, see the full documentation for more details.

Tracing with OpenTelemetry

Npgsql can now report tracing data via OpenTelemetry; this can provide invaluable data on the queries your application is running, how long they're taking, and situate them in the larger context of your application. For example, you can visualize what percentage of your HTTP requests are spent waiting for your database query.

The following is a sample timeline visualization of Npgsql-reported trace data, using Zipkin:

Zipkin UI Sample

See the full documentation in the diagnostics page.

Timestamp rationalization and improvements

Support for timestamp with time zone and timestamp without time zone has been rationalized and simplified, and aligned with PostgreSQL best practices. In particular, the "UTC everywhere" pattern is much better supported via the PostgreSQL timestamp with time zone type, which is the recommended way to handle timestamps. A detailed explanation is available in this blog post, below is a summary of the main improvements.

  • UTC timestamps have been cleanly separated from non-UTC timestamps, aligning with the PostgreSQL types. The former are represented by timestamp with time zone and DateTime with Kind UTC, the latter by timestamp without time zone and DateTime with Kind Local or Unspecified. It is recommended to use UTC timestamps where possible.
  • Npgsql no longer performs any implicit timezone conversions when reading or writing any timestamp value - the value in the database is what you get, and the machine timezone no longer plays any role when reading/writing values.
  • Npgsql no longer supports date/time representations which cannot be fully round-tripped to the database. If it can't be fully stored as-is, you can't write it.
  • A compatibility switch enables opting out of the new behavior, to maintain backwards compatibility.

This change introduces significant breaking changes (see below), although a compatibility flag can be used to opt out and revert to the previous behavior.

Other date/time improvements include:

  • Support for the new .NET DateOnly and TimeOnly types (#3616).
  • PostgreSQL tstzrange is now mapped to NodaTime Interval, and PostgreSQL daterange is now mapped to NodaTime DateInterval. Most methods on these types are translated (#3973, #4070).
  • DateTime.MinValue and MaxValue are now mapped to PostgreSQL -infinity and infinity by default.

Raw SQL mode and new batching API

It is now possible to use PostgreSQL positional parameters, allowing Npgsql to skip rewriting your SQL and send it directly to PostgreSQL (#1042):

SELECT * FROM blogs WHERE b.name = @p; -- Before Npgsql 6.0
SELECT * FROM blogs WHERE b.name = $1; -- Npgsql 6.0

This is more efficient, is safer (since Npgsql doesn't have to parse SQL), and aligns your SQL with the actual PostgreSQL SQL. Note that named parameters are still supported for backwards compatibility.

In addition, Npgsql now supports the new ADO.NET batching API introduced in .NET 6.0 as an alternative to packing multiple statements into a single NpgsqlCommand, delimited by semicolons (#3860):

// Before Npgsql 6.0
var command = new NpgsqlCommand("SELECT 1; SELECT 2", connection);
var reader = await command.ExecuteReaderAsync();

// Npgsql 6.0
var batch = new NpgsqlBatch(connection)
{
    BatchCommands = { new("SELECT 1"), new("SELECT 2") }
};
var reader = await batch.ExecuteReaderAsync();

As with positional parameters, this allow Npgsql to avoid parsing and splitting your SQL which is safer and more efficient.

To learn more about positional parameters and batching, see this blog post.

SSL/TLS improvements

Npgsql 6.0 contains some considerable improvements to encryption support, here are some highlights (#4006):

  • The Ssl Mode connection string parameter has been aligned with the standard PostgreSQL ssl_mode setting, and allows for more fine-grained choice in what to validate (see breaking change note below for SSL Mode=Require).
  • Support has been added for PEM client certificates when running on .NET 5.0 and above.
  • Support for specifying a root CA certificate that isn't installed in the machine store.

See the updated security and encryption docs for more details.

Improved logical replication support

The recently-released PostgreSQL 14 made significant improvements to logical replication, and Npgsql 6.0 already supports them! Here's a summary of the main changes (#4050, thanks @Brar):

  • PG14 binary logical replication - you can now read the standard .NET types from replication data; only text was possible previously (#4049).
  • PG14 streaming replication - incremental updates for large in-progress transactions (#4047)
  • The replication API now fully streams all data, including even columns; the previous API buffered rows, leading to lots of heap allocations for big rows (#4068).

Unfortunately, some of the above required an API redesign, leading to some breaking changes.

Other new features

  • Support for the new .NET DateOnly and TimeOnly types (#3616).
  • Full support for the PostgreSQL 14 multirange type, mapped to arrays or lists of NpgsqlRange<T> (#3868).
  • PostgreSQL decimal can now be mapped to .NET BigInteger (#3665).
  • Bulk import/export now includes a fully asynchronous API (#3309).
  • Improved array nullability via a new connection string parameter (#3386).
  • Composite types and records can now be read by getting a nested DbDataReader through NpgsqlDataReader.GetData() (#3558, thanks @Emill). This allows for efficient access without having to map composite types to .NET POCOs.
  • Considerable work has gone in to make Npgsql more friendly to trimming/AOT, removing reflection (#3300). Npgsql still isn't fully trimming-friendly, but it's getting close.

Breaking changes

Changes to SSL configuration (SSL Mode=Require)

  • To validate server certificates, use SSL Mode=VerifyFull (or alternatively SSL VerifyCA) instead of Require.
  • To not validate server certificates (e.g. self-signed certificates), use SSL Mode=Require and Trust Server Certificate=true.

In previous versions, specifying SSL Mode=Require made Npgsql validate the server certificate, but the standard PostgreSQL ssl_mode setting does not. As part of aligning Npgsql with other PostgreSQL drivers and tools, 6.0 now has SSL Mode=VerifyFull which corresponds to the previous Require behavior. Stopping validation for Require would align Npgsql with the PostgreSQL behavior, but would silently turn off validation for current users and expose them to potential security issues. As a result, we now require Trust Server Certificate=true to be specified with Require: this forces users to explicitly opt out of validation. In a future version, we'll remove the requirement to specify Trust Server Certificate for Require (and possibly the parameter itself).

Major changes to timestamp mapping

Note

It is possible to opt out of these changes to maintain backwards compatibility, see below.

Quick summary

  • In many cases, is makes sense to store UTC timestamps in the database. To do this, migrate your timestamp without time zone columns to timestamp with time zone (see migration notes below), and always use either DateTime with Kind=Utc or DateTimeOffset with offset 0.
  • To store non-UTC timestamps, use DateTime with Kind=Unspecified.
    • If using NodaTime (recommended), use LocalDateTime (no explicit column configuration is required).
  • If you're using Dapper, use version 2.0.123 or above. Earlier versions will fail when trying to send a UTC DateTime.

Detailed notes

The below notes will use the PostgreSQL aliases timestamptz to refer to timestamp with time zone, and timestamp to refer to timestamp without time zone. Note that timestamp with time zone represents a UTC timestamp and does not store a timezone in the database.

  • UTC DateTime is now strictly mapped to timestamptz, while Local/Unspecified DateTime is now strictly mapped to timestamp.
    • DateTime with Kind=UTC are now written as PostgreSQL timestamptz; previously, DateTime was always written as timestamp.
    • It is no longer possible to write UTC DateTime as timestamp, or Local/Unspecified DateTime as timestamptz. This was previously allowed, with Npgsql performing implicit timezone conversions.
    • Note that if you write a UTC DateTime to a PostgreSQL timestamp column, PostgreSQL will implicitly convert the timestamptz value sent by Npgsql, performing a timezone conversion based on the TimeZone parameter.
  • timestamptz values are now read back as DateTime with Kind=UTC, without any conversions; these were previously returned as local DateTime, converted to the local machine's timezone. When reading timestamptz values as DateTimeOffset, UTC values (offset 0) are always returned.
  • DbType.DateTime now maps to timestamptz, not timestamp. DbType.DateTime2 continues to map to timestamp, and DbType.DateTimeOffset continues to map to timestamptz, as before. Unless you're writing cross-database applications, consider using NpgsqlDbType instead of DbType to specify precise PostgreSQL types, or simply let Npgsql infer the types by not setting either.
  • It is no longer possible to write DateTimeOffset with offsets other than 0 (UTC), since these cannot be represented in PostgreSQL. These were previously implicitly converted to UTC before sending.
  • It is no longer possible to read or write timetz as DateTime or TimeSpan, as these don't have a timezone. This was previously allowed, with the offset being stripped.

See the EF Core provider docs for additional changes at the EF level.

NodaTime changes

  • Instant is now sent as a timestamptz value, and not as a timestamp, since they represent a universally agreed-upon point in time. To send a timestamp, use LocalDateTime.
  • When reading timestamptz as ZonedDateTime or OffsetDateTime, UTC values are always returned. Previously, local values based on the PostgreSQL TimeZone parameter were returned.
Note

In most cases, storing UTC timestamps is the recommended practice. If this is what you're doing, it's strongly recommended to migrate all relevant columns from timestamp columns to timestamptz. See below for how to do this.

Migrating columns from timestamp to timestamptz

Migrating timestamp columns to timestamptz is a simple procedure, but care must be taken, depending on the current contents of your column.

As a starting point, let's assume your existing timestamp column has the timestamp 2020-01-01 12:00:00:

SELECT "created_on", pg_typeof("created_on") AS type FROM "Blogs";

Results in:

      created_on     |            type
---------------------+-----------------------------
 2020-01-01 12:00:00 | timestamp without time zone

The following SQL will change the column's type to timestamptz:

ALTER TABLE blogs ALTER COLUMN created_on TYPE timestamp with time zone;

When converting the timestamp without time zone column to timestamp with time zone, PostgreSQL will assume that existing values are local timestamps, and will convert them to UTC based on the TimeZone parameter. Performing the above query will result in something like:

        CreatedOn       |           type
------------------------+--------------------------
 2020-01-01 12:00:00+02 | timestamp with time zone

This means that your new timestamptz column now contains 10:00 UTC, which is probably not what you want: if the original values were in fact UTC values, you need them to be preserved as-is, changing only the column type. To do this, set TimeZone to UTC before executing the ALTER TABLE:

SET TimeZone='UTC';
ALTER TABLE blogs ALTER COLUMN created_on TYPE timestamp with time zone;

This will ensure that no time zone conversions will be applied when converting the columns:

        CreatedOn       |           type
------------------------+--------------------------
 2020-01-01 14:00:00+02 | timestamp with time zone

Opting out of the new timestamp mapping logic

The changes described above are far-reaching, and may break applications in various ways. You can upgrade to version 6.0 but opt out of the new mapping by enabling the Npgsql.EnableLegacyTimestampBehavior AppContext switch. To do this and revert to the legacy timestamp behavior, add the following at the start of your application, before any Npgsql operations are invoked:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

NodaTime: tstzrange and daterange are mapped to Interval and DateInterval by default

When using NodaTime, reading a PostgreSQL tstzrange returns Interval instead of NpgsqlRange<Instant> (#4070), and reading a PostgreSQL daterange returns DateInterval instead of NpgsqlRange<LocalDate> (#3973). It is still possible to read NpgsqlRange by via reader.GetFieldValue<NpgsqlRange<Instant>> and reader.GetFieldValue<NpgsqlRange<LocalDate>>.

Date/time min/max values are now converted to PostgreSQL infinity values by default

PostgreSQL has special infinity and -infinity values for timestamps and dates, which are later and earlier than other value. Npgsql has supported mapping DateTime.MaxValue and MinValue to these infinity values via an Convert Infinity DateTime connection string parameter, which was disabled by default. This behavior is now on by default, since DateTime.MaxValue and MinValue are very rarely used as actual timestamps/dates, and the Convert Infinity DateTime parameter has been removed. To disable infinity conversions, add the following at the start of your application:

AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

See the date/time documentation for more details.

PG intervals with months/years can no longer be read as TimeSpan

The PostgreSQL interval type can contain months and years, which are time units without a fixed, absolute duration (different months have different numbers of days). In contrast, .NET TimeSpan is always an absolute duration, and does not support months or years. Previously, when reading intervals as TimeSpan, Npgsql read months as 30-day units, which was incorrect and could yield wrong results.

To read intervals with month/year components, consider using NodaTime's Period type, which is perfectly suited for representing intervals and is supported by Npgsql. Otherwise, you can read intervals as NpgsqlInterval, provides direct, raw access to the PostgreSQL data which Npgsql receives. Finally, if you want to continue treating months as 30-day units, consider changing your interval data in the database, so that e.g. '1 month 20 days' becomes '50 days'.

NpgsqlStatement and PostgresException.Statement have been removed

Npgsql versions before 6.0 exposed an NpgsqlStatement type, which contained information on individual statements within an NpgsqlCommand batch. Npgsql introduces support for the new standardized ADO.NET DbBatch type, which has numerous advantages compared to the previous support. While executing multiple statements in a single NpgsqlCommand is still supported for backwards compatibility, NpgsqlCommand no longer exposes per-statement information via NpgsqlStatement as before. Consider using the new NpgsqlBatch for all batched executions instead: it exposes the BatchCommands property which is similar to the previous Statements property. Accordingly, PostgresException.Statement has been removed, and replaced by BatchCommand which is only populated when executing via NpgsqlBatch.

The default SSL Mode is now Prefer

When SSL Mode isn't specified on the connection string, it used to default to Disable; it is now Prefer. This means that SSL will be used when the server supports it, and if a custom certificate validation callback is set, failure to validate would cause the connection to fail.

See the Security and encryption page for more details.

The logical replication API has been redone

As part of the redesign around logical replication support, some major changes to the API had to be done. Applications will have to be changed to use the new API.

Arrays/lists over ranges are mapped to PG14 multiranges

PostgreSQL 14 introduced a new multirange type, which is very similar to an array of ranges but supports various range-related operations efficiently. The provider now maps arrays and lists of NpgsqlRange to these new types by default. You can still read old-style arrays over ranges by explicitly specifying the type:

var arrayOverRange = reader.GetFieldValue<NpgsqlRange<int>[]>(0);

You can also write arrays over ranges by explicitly specifying the NpgsqlDbType:

var parameter = new NpgsqlParameter
{
    Value = new NpgsqlRange<int>[] { ... },
    NpgsqlDbType = NpgsqlDbType.IntegerRange | NpgsqlDbType.Array
};

DBNull no longer permitted in arrays

Previously, it was possible to write an object[] containing DBNull as a way of indicating null; this is no longer supported. Instead, use C# null.

Parameter name matching changed around case-sensitivity

When using named parameter placeholders, Npgsql now only does a single pass to match placeholders to parameters, taking the first parameter whose name passes a case-insensitive match. Npgsql previously did two passes - a case-insensitive pass, followed by a case-sensitive one if the first pass failed to match; this was detrimental to performance in some scenarios. This behavioral change is only visible when using two parameters with names that are identical except for case; this is generally discouraged.

To revert to the legacy two-pass behavior, add the following at the start of your application, before any Npgsql operations are invoked:

AppContext.SetSwitch("Npgsql.EnableLegacyCaseInsensitiveDbParameters", true);

Note: version 6.0.0 and 6.0.1 changed parameter matching to be case-sensitive. This change was rolled back in 6.0.2 since it could lead to data loss in some scenarios.

The provider-specific date/time types have been obsoleted

Npgsql contains provider-specific NpgsqlDateTime, NpgsqlDate and NpgsqlTimeSpan types, which were designed to provide the same APIs as the corresponding built-in BCL types, but to support the full range of the PostgreSQL types. These types were buggy and inefficient in many ways, and have been obsoleted; they will be removed in Npgsql 7.0.

Instead of the obsoleted types, use the following techniques:

  • NodaTime can be used to interact with values which are out-of-range for the BCL types.
  • To support values which are out-of-range for NodaTime, PostgreSQL timestamps can now be read/written as long, and dates can be read/written as int. These are the raw PostgreSQL representations, with no operations - they simply provide an "escape hatch" in case users need to interact with out-of-range values.
  • For interval, a new NpgsqlInterval type has been introduced, which again contains the raw PostgreSQL data (months, days, time).

Npgsql.LegacyPostgis has been removed

The Npgsql.LegacyPostgis plugin, which mapped legacy Npgsql types to PostGIS, has been removed and no longer ships (#3962). Use Npgsql.NetTopologySuite to work with PostGIS.

Contributors

Thank you very much to the following people who have contributed to the individual 6.0.x. releases.

Milestone 6.0.8

Contributor Assigned issues
@roji 1
@vonzshik 1

Milestone 6.0.7

Contributor Assigned issues
@vonzshik 4
@roji 2
@Brar 1

Milestone 6.0.6

Contributor Assigned issues
@vonzshik 3

Milestone 6.0.5

Contributor Assigned issues
@roji 4
@alitas 1
@davidhunt135 1
@vonzshik 1

Milestone 6.0.4

Contributor Assigned issues
@roji 13
@vonzshik 10

Milestone 6.0.3

Contributor Assigned issues
@roji 8
@vonzshik 3
@Brar 2
@NinoFloris 2

Milestone 6.0.2

Contributor Assigned issues
@roji 3
@NinoFloris 1

Milestone 6.0.1

Contributor Assigned issues
@vonzshik 5
@roji 4
@NinoFloris 1
@zitmen 1

Milestone 6.0.0

Contributor Assigned issues
@roji 42
@vonzshik 34
@Brar 7
@NinoFloris 3
@Emill 2
@manandre 2
@mdalepiane 1
@mintsoft 1
@Seltzer 1
@TwentyFourMinutes 1
Edit this page