MultiHostDataSource fails on connect when EnableSqlRewriting is set to false #5055
Description
Steps to reproduce
Create a simple console project with sql rewriting off and multiple hosts for failover:
using Npgsql;
AppContext.SetSwitch("Npgsql.EnableSqlRewriting", false);
var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=localhost:5432,whatever:5555;Username=user;Password=pass");
await using var source = dataSourceBuilder.
BuildMultiHost().WithTargetSession(TargetSessionAttributes.Primary);
using var connection = source.OpenConnection();
The issue
This fail on source.OpenConnection()
Exception message: 42601: cannot insert multiple commands into a prepared statement
Stack trace:
Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: cannot insert multiple commands into a prepared statement
at Npgsql.NpgsqlMultiHostDataSource.Get(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 Npgsql.NpgsqlDataSource.OpenConnection()
at Program.<Main>$(String[] args) in /Users/kyrrem/code/postgres-failover/Program.cs:line 9
at Program.<Main>$(String[] args) in /Users/kyrrem/code/postgres-failover/Program.cs:line 10
at Program.<Main>(String[] args)
Exception data:
Severity: ERROR
SqlState: 42601
MessageText: cannot insert multiple commands into a prepared statement
File: postgres.c
Line: 1434
Routine: exec_parse_message
Further technical details
From reading the code and seeing the logs, it seems like it fails in the NpgsqlConnector method QueryDatabaseState - this does a query with two statements separated by semicolon. Which will not work in raw mode:
using var cmd = CreateCommand("select pg_is_in_recovery(); SHOW default_transaction_read_only");
Not sure why this codepath only is triggered when using multi host data source, but it seems to behave that way. Maybe rewrite this to a batch command would be a proper solution?
Npgsql version: 7.0.4
PostgreSQL version: 13.3
Operating system: Mac M1