Cannot refresh materialized view with npgsql

[rutkowskii]

Cockroach version: v21.2.7 (x86_64-unknown-linux-gnu, built 2022/03/14 16:37:26, go1.16.6)
Npgsql version: 7.0.4

Each attempt to refresh a materialized view via Npgsql:

 var cs =_dbConnectionStringProvider.ConnectionString("StateDb") +";POOLING=FALSE";

        await using (NpgsqlConnection conn = new NpgsqlConnection(cs))
        {
            await conn.OpenAsync();

            // Ensure that the connection is not in a transaction
            using (var cmd = new NpgsqlCommand(@"refresh materialized view ""V_view""", conn))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

Causes an exception:

Npgsql.PostgresException (0x80004005): 25000: cannot refresh view in an explicit transaction
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at FleetManager.StateDb.Model.Commands.MaterializedViewsRefresher.ExecuteRefreshSql(FormattableString fs) in /home/ec2-user/buildAgent/work/a60a8c1d7e1070c6/src/FleetManager.StateDb.Model/Commands/MaterializedViewsRefresher.cs:line 77
   at FleetManager.StateDb.Model.Commands.MaterializedViewsRefresher.ExecuteRefreshSql(FormattableString fs) in /home/ec2-user/buildAgent/work/a60a8c1d7e1070c6/src/FleetManager.StateDb.Model/Commands/MaterializedViewsRefresher.cs:line 79
   at FleetManager.StateDb.Model.Commands.MaterializedViewsRefresher.RunCore(FormattableString cmd) in /home/ec2-user/buildAgent/work/a60a8c1d7e1070c6/src/FleetManager.StateDb.Model/Commands/MaterializedViewsRefresher.cs:line 55
  Exception data:
    Severity: ERROR
    SqlState: 25000
    MessageText: cannot refresh view in an explicit transaction
    File: refresh_materialized_view.go
    Line: 34
    Routine: RefreshMaterializedView

Running the same query: refresh materialized view ""V_view" via command line client works just fine.
I cannot easily update Cockroach server to the latest version here, maybe there are workarounds possible?

Jira issue: CRDB-32118

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[yuzefovich]

The error message says cannot refresh view in an explicit transaction, so perhaps you need to specify some config option to not use the "explicit" transactions (i.e. avoid the tool to send BEGIN; and COMMIT;).

[rafiss]

Also, I saw that you're using v21.2.7 of CockroachDB. That version is no longer supported. Does the issue still happen with a newer version? v23.1.11 is the newest. https://www.cockroachlabs.com/docs/releases/

[rafiss]

#87271 fixed this behavior, but the fix is only in v22.1 or later. You will need to upgrade to avoid the error.