npgsql: PostgreSQL types missing in old PG versions (and Redshift)

The issue

Using Npgsql Version 6.0.0-rc.1 and opening a connection to PostgreSQL Server version 9.3.25 throws an ArgumentException: A PostgreSQL type with the name jsonb was not found in the database.

There is no issue if I use preview7 or connect to a newer server version. I have only tried v13, but assume any version after 9.5 or 9.6 (which ever version jsonb was first released) would work.

Unfortunately, I’m stuck with 9.3 for now.

Additionally, In a long-shot attempt I have tried to Enable Legacy Timestamp Behavior with no luck AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

This also affects EF Core rc.1.

Unhandled exception. System.ArgumentException: A PostgreSQL type with the name jsonb was not found in the database
   at Npgsql.Internal.NpgsqlDatabaseInfo.GetPostgresTypeByName(String pgName)
   at Npgsql.TypeMapping.BuiltInTypeHandlerResolver.PgType(String pgTypeName)
   at Npgsql.TypeMapping.BuiltInTypeHandlerResolver..ctor(NpgsqlConnector connector)
   at Npgsql.TypeMapping.BuiltInTypeHandlerResolverFactory.Create(NpgsqlConnector connector)
   at Npgsql.TypeMapping.ConnectorTypeMapper.Reset()
   at Npgsql.TypeMapping.ConnectorTypeMapper.set_DatabaseInfo(NpgsqlDatabaseInfo value)
   at Npgsql.Internal.NpgsqlConnector.LoadDatabaseInfo(Boolean forceReload, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.<Get>g__RentAsync|29_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.Open()
   at Program.<Main>$(String[] args) in ...\Program.cs:line 12

Further technical details

Npgsql version: 6.0.0-rc.1 PostgreSQL version: 9.3.25 Operating system: Windows 1909 Dotnet Version: 6.0.100-rc.1.21458.32

Steps to reproduce

Create and open a connection to a Postgres Server version 9.3

var conn = new NpgsqlConnection(connectionString);
conn.Open();

Edit: Setting Server Compatibility Mode=NoTypeLoading does seem to resolve it

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 17 (7 by maintainers)

Commits related to this issue

Resolve jsonb lazily To make the driver work in databases where it's absent (e.g. Redshift). Fixes #3996 — committed to roji/Npgsql by roji 3 years ago
Resolve jsonb lazily (#4074) To make the driver work in databases where it's absent (e.g. Redshift). Fixes #3996 — committed to npgsql/npgsql by roji 3 years ago
Resolve uuid lazily To make the driver work in databases where it's absent (e.g. Redshift). Fixes #3996 — committed to roji/Npgsql by roji 3 years ago
Resolve uuid lazily (#4075) To make the driver work in databases where it's absent (e.g. Redshift). Fixes #3996 — committed to npgsql/npgsql by roji 3 years ago

Most upvoted comments

Getting the following exception connecting to my PostgreSQL DB (which is upstream of my Redshift connection) Not sure if this is related but it seems to be in the same routine that was modified here. We are running PostgreSQL 13.4 on RDS

Will try and recreate this outside my app tomorrow.

The type initializer for ‘Npgsql.TypeMapping.BuiltInTypeHandlerResolver’ threw an exception. An item with the same key has already been added.

at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) at Npgsql.TypeMapping.BuiltInTypeHandlerResolver…ctor(NpgsqlConnector connector) at Npgsql.TypeMapping.BuiltInTypeHandlerResolverFactory.Create(NpgsqlConnector connector) at Npgsql.TypeMapping.ConnectorTypeMapper.Reset() at Npgsql.Internal.NpgsqlConnector.<LoadDatabaseInfo>d__194.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.Internal.NpgsqlConnector.<Open>d__193.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Npgsql.ConnectorPool.<OpenNewConnector>d__34.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.ConnectorPool.<<Get>g__RentAsync|31_0>d.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.NpgsqlConnection.<<Open>g__OpenAsync|45_0>d.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.NpgsqlConnection.Open()

johnfedak on Oct 27, 2021

I can confirm that Package version 6.0.0-rtm-ci.20211026T220029 resolve my issue with jsonb.

Thank you!

WarrenCrabb on Oct 26, 2021