Npgsql 4.0 Release Notes

Npgsql 4.0 is out and available at nuget.org. This is a major version with significant changes, upgrade with care, consult the breaking changes section below and test well before deploying to production.

A special thanks goes out to @YohDeadfall for his many contributions and reviews. Thanks also (alphabetically) to @austindrenski, @Brar, @kspeakman, @rwasef1830, @shortspider, @StillLearnin, @uhayat for their valuable contributions.

High performance

A concentrated effort has substantially increased Npgsql performance, especially in highly concurrent, low-latency scenarios. Improvements include:

• Rewriting of the connection pool to be lock-free, since contention started to be an issue in highly concurrent, short-lived connection scenarios (#1839).
• Significant reduction of allocations through more recycling and other techniques.
• New API for generically providing parameters, avoiding boxing of value types (#1639).
• Avoiding numerous internal async calls where they weren't needed.
• ... many others

In round 16 of the TechEmpower benchmark, .NET Core/ASP.NET Core came in 7th place running with Npgsql, making it one of the fastest mainstream web stacks available - see this blog post for more info. Please let us know how the new version works for you - both positive and negative comments are welcome.

If you're interested in Npgsql performance and haven't yet seen the performance page, it's a good opportunity to check it out (it's valid also for 3.2 users).

Improved spatial support (PostGIS)

Previous versions have allowed basic usage of PostGIS's spatial types via built-in Npgsql types, which were limited in many ways. Thanks to a new plugin infrastructure, you can now use the Npgsql.NetTopologySuite plugin, which maps PostGIS types to the NetTopologySuite spatial library's types. NetTopologySuite's types are more complete, and support a variety of spatial operations and conversions you can perform after loading your spatial data from PostgreSQL.

If you prefer to use JSON for your spatial types, the Npgsql.GeoJSON plugin maps PostGIS types to GeoJSON.NET types. GeoJSON is a standard JSON format for spatial data.

Finally, if you prefer to use the previous Npgsql types (e.g. PostgisPoint), these are available via the Npgsql.LegacyPostgis plugin.

Thanks to @YohDeadfall for implementing both the NetTopologySuite and GeoJSON plugins.

NodaTime date/time support

NodaTime is a powerful alternative to .NET's built-in date/time types, such as DateTime. The built-in types are flawed in many ways: they have problematic support for timezones, don't have a date-only or time-only types, and promote problematic programming but not making the right distinctions. If your application handles dates and times in anything but the most basic way, you should seriously consider using NodaTime. To learn more read this blog post by Jon Skeet.

You can now use the new Npgsql.NodaTime to have Npgsql map PostgreSQL date/time types to NodaTime types.

Json.NET

Another plugin, Npgsql.Json.NET, works with Newtonsoft Json.NET to automatically serialize and deserialize PostgreSQL's jsonb and json types to your objects, providing a seamless database JSON programming experience. Instead of working with strings which you have to serialize and deserialize, Npgsql does it for you.

Other improvements

• Fix the binary COPY API to make it interact better with exceptions (#1646).
• Npgsql better supports working with enums and composites, even without mapping them, and better supports new types introduced via plugins (#1792).
• Better "reflection" capabilities. Continuing work from 3.2, Npgsql now exposes more information about PostgreSQL types, allowing you to dynamically reflect on columns types returned by queries, or required as parameters (#1276, #1779).
• Derive parameters for queries. You can now also use NpgsqlCommandBuilder to dynamically understand which parameters and types are required for arbitrary queries (previously supported only for functions) (#1698, thanks @Brar!).
• Allow reading a single character from a PostgreSQL text column (#1188).
• Decimals read from PostgreSQL will now have the correct scale (#1925). Thanks @StillLearnin and @YohDeadfall.

In addition to more documentation, several blog posts are planned to explain the above in more details (to be announced on @shayrojansky).

Breaking changes from 3.2

Caution

The date/time behavior has changed in the following ways:

1. DateTime is always sent as timestamp by default, regardless of its kind. You can still specify NpgsqlDbType.TimestampTz, in which case local DateTime gets converted to UTC before sending.
2. When reading timestamptz as a DateTimeOffset, the machine local offset will be used. Previously a DateTimeOffset in UTC was returned.
3. It is no longer possible to read or write DateTimeOffset as timestamp, only as timestamptz.

Caution

The API for binary import (COPY IN) has changed substantially in a breaking way, and code from 3.2 will not work as-is on 4.0.

You must now call NpgsqlBinaryImporter.Complete() to save your imported data; not doing so will roll the operation back. NpgsqlBinaryImporter.Cancel() has been removed - simply closing/disposing the importer will implicitly cancel the import. This is similar to how TransactionScope works and is necessary to prevent accidental commit of data on exception. See #1646.

Caution

If you're using decimal/numeric numbers (not floating-point), there's a chance your data needs to be fixed (previous versions incorrectly inserted a scale larger than 28, which is the maximum allowed by .NET decimal). If you're having trouble reading data previously inserted by Npgsql, consider running this fixup code. If your data really does contain more than 28/29 fractional digits and you need to keep that precision, see the workarounds proposed in this comment for loading these values.

• .NET Standard 1.3 is no longer supported. .NET Standard 2.0 is the lowest supported version.
• Npgsql used to use its own internal TLS/SSL due to issues with some server. As these issues have been resolved, the standard .NET SslStream is now used by default (#1482), but you can still set Use SSL Stream=false to keep using the internal implementation (please report why you need this, as it's likely the internal implementation will be removed in a future release).
• The reader instances returned by NpgsqlCommand.ExecuteReader() are now recycled, to reduce memory allocations (#1649). You should not keep a reference or interact with a reader after its command has been disposed (such interaction was limited in any case).
• The Min Pool Size parameter will no longer make the pool create new connections internally - it will only have an effect on how many connections are pruned. Previously, in various points the pool would check if the current number of connections was below Min Pool Size, and if so, automatically created new ones - this no longer happens.
• Parameter types have become more strict. Previous versions allowed to you pass arbitrary value types, such as writing CLR string to int columns, or anything that implemented IConvertible. Although some implicit conversions are still supported (e.g. long -> int, short -> int), some have been removed.
• Data type names returned from NpgsqlDataReader.GetDataTypeName() and other APIs are now more standards-conforming (e.g. integer[] instead of _int4), and properly include type modifiers (e.g. character varying(10)) (#1919).
• NpgsqlParameter.EnumType and NpgsqlParameter.SpecificType have been removed. See Composites and Enums for more details.
• Parameter names are no longer trimmed, set your names to the exact parameter name specified in your SQL.
• If a parameter's name isn't set, it will no longer default to Parameter1, Parameter2, etc.
• The following APIs "connection capability" APIs have been removed from NpgsqlConnection: UseConformantStrings, SupportsEStringPrefix, UseSslStream.
• The default name translator, NpgsqlSnakeCaseNameTranslator, has been changed to handle acronyms better. Given the property name IsJSON, the old translator algorithm would output is_j_s_o_n, while the new outputs is_json. To revert back to the old algorithm, create a NpgsqlSnakeCaseNameTranslator instance with legacyMode: true and pass it when calling the MapComposite and MapEnum methods.
• If you are reading tables as composites (#990), you will have to add the new Load Table Composites to your connection string.
• NpgsqlConnection.GetSchema() will no longer return system tables (i.e. tables in schemas pg_catalog and information_schema), #1831.
• You may no longer have multiple streams or text readers open on a reader (this was previously supported with non-sequential readers). Accessing a new column closes any open stream or text reader.
• The DateTimeOffset instances returned for PostgreSQL timetz now have their date set to 0001-01-02 instead of the previous 0001-01-01 (#1924).

Contributors

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

Milestone 4.0.11

Contributor	Assigned issues
@manandre	1
@roji	1
@YohDeadfall	1

Milestone 4.0.10

Contributor	Assigned issues
@kYann	1
@roji	1

Milestone 4.0.9

Contributor	Assigned issues
@roji	2
@YohDeadfall	1

Milestone 4.0.8

Contributor	Assigned issues
@roji	2
@romanov-is-here	1
@thetranman	1
@YohDeadfall	1

Milestone 4.0.7

Contributor	Assigned issues
@roji	4
@aspaw	1

Milestone 4.0.6

Contributor	Assigned issues
@roji	2
@austindrenski	1
@zabulus	1

Milestone 4.0.5

Contributor	Assigned issues
@roji	6
@YohDeadfall	5
@austindrenski	1

Milestone 4.0.4

Contributor	Assigned issues
@roji	6
@YohDeadfall	3
@austindrenski	1

Milestone 4.0.3

Contributor	Assigned issues
@roji	6
@YohDeadfall	3

Milestone 4.0.2

Contributor	Assigned issues
@roji	2
@YohDeadfall	1

Milestone 4.0.11

Contributor	Assigned issues
@manandre	1
@roji	1
@YohDeadfall	1

Milestone 4.0.10

Contributor	Assigned issues
@kYann	1
@roji	1

Milestone 4.0.1

Contributor	Assigned issues
@roji	3
@austindrenski	2
@YohDeadfall	2

Milestone 4.0

Contributor	Assigned issues
@roji	34
@YohDeadfall	6
@Brar	1
@funny-falcon	1