NET 8: `EnsureDeleted` fails with Npgsql.PostgresException : 42601: syntax error at or near "WITH" on PostgreSQL 12

[JonPSmith]

I have updated from Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0-rc.2 to 8.0.0 and I now have a error when I use the EnsureDeleted method. NOTE: The the 8.0.0-rc.2 version does not have this problem.

Here is the stacktrace

Message: 
Npgsql.PostgresException : 42601: syntax error at or near "WITH"

POSITION: 77

  Stack Trace: 
NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
IValueTaskSource<TResult>.GetResult(Int16 token)
NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
NpgsqlDataReader.NextResult()
NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteNonQuery()
RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
NpgsqlDatabaseCreator.Delete()
RelationalDatabaseCreator.EnsureDeleted()
DatabaseFacade.EnsureDeleted()
TestEnsureCleanPostgreSql.TestWipeDataDatabase1Ok() line 106
RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

Is this because I am using the old PostgreSQL 12 version (which I see is still supported)? I didn't find anything in the NET 8 breaking changes section. Please advise.

[haas-daniel]

Hi, compatibility mode has been bumped to postgresql 14. Maybe this helps: https://www.npgsql.org/efcore/release-notes/8.0.html#default-postgresql-compatibility-mode-has-been-bumped-from-12-to-14

[JonPSmith]

Thanks @haas-daniel. I kinda expected that the it was something to do the postgresql version.

I suggest that you add an entry for this change to the EF Core's NET 8 breaking changes section maybe with a link to the release note you provided above.

[roji]

@JonPSmith can I see some code?

[JonPSmith]

[Fact]
public void Test()
{
    const string connectionString =
        "host=127.0.0.1;Database=Test-TestPostgesEnsureDelete;Username=xxx;Password=xxx";
    var builder = new DbContextOptionsBuilder<DbContext1>();
    builder.UseNpgsql(connectionString);
    var options = builder.Options;
    using var context = new DbContext1(options);
    context.Database.EnsureCreated();
    context.Database.EnsureDeleted();  //throws exception here
}

NOTES:

• the Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0-rc.2 did not have this issue, but 8.0.0 does.
• Also adding builder.UseNpgsql(connectionString, o => o.SetPostgresVersion(12, 0))); to the builder as shown in your .NET 8 release notes stops the exception.
• The EnsureDeleted doesn't throw if no database exists.

[roji]

Ah yes, that's this breaking change - this is PostgreSQL-specific, which is why it doesn't appear in EF release notes but rather in the Npgsql ones.

[phebing]

Had the same error in our Tests, but we are using TestContainers 3.7.0. Adding builder.UseNpgsql(connectionString, o => o.SetPostgresVersion(12, 0))); also fixed the problem.

[roji]

Closing this as everything seems by-design.