PostgreSQL 14.7, 11.19 incompatibility "CREATE INDEX CONCURRENTLY cannot be executed within a pipeline"

[shokurov]

Environment

• DbUp Version: 6.0.4
• Database Vendor & Version: PostgreSQL 14.7, 11.19
• Operating System Type & Version: Debian 10, Windows 11, MacOS 16.1

Description

After upgrade of PostgreSQL to the latest minor versions: 14.7 and 11.19 (we support and test on these two branches 14 & 11) any migration with "CREATE INDEX CONCURRENTLY" failing with:

DB exception has occured in script: '1.56.0#0005/changes/1.56.0/MLRSSL-732 - jrnl_ff_transaction.sql'
Script block number: 0; Message: 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
Npgsql.PostgresException (0x80004005): 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_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()
   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 Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at DbUp.Support.ScriptExecutor.ExecuteNonQuery(IDbCommand command)
   at DbUp.Postgresql.PostgresqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
  Exception data:
    Severity: ERROR
    SqlState: 25001
    MessageText: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
    File: xact.c
    Line: 3433
    Routine: PreventInTransactionBlock

Migration example:

drop index idx_ff_transaction_partner_ext_id2;
create index concurrently idx_ff_transaction_partner_ext_id2
    on jrnl_ff_transaction (partner_id, transaction_ext_id);

Table affected (minimal example to reproduce):

create table public.jrnl_ff_transaction
(
    transaction_ext_id        varchar(150),
    partner_id                integer
);

Steps To Reproduce

Create SQL migration, like in the example above, using clean PostgreSQL docker image (postgres:14.7-bullseye, postgres:11.19-bullseye).

Actual Behavior

Exception in migration log. See above.

Expected/Desired Behavior

Execution without exception.

Additional Context

Downgrading to 14.6, 11.18 respectively fixes the issue, and we used it as makeshift workaround.

Similar issue has been reported in Luquibase#3806 which also mentions that version 15.7 is also affected.

Also see PostgreSQL expert answer here.

[rpsft]

It seems there is a workaround for the issue, like following:

create index concurrently if not exists idx_exchange_rate_from_currency_id
  on ref_exchange_rate (from_currency_id)
;
create index concurrently if not exists idx_exchange_rate_to_currency_id
  on ref_exchange_rate (to_currency_id)
;
...

The issue is that according to a comment in npgsql repo npgsql/npgsql#462 (comment) driver executes in pipeline mode if DbCommand text contains semicolons.
DbUp splits commands by semicolon only on a separate line.

So, if each of the commands affected by the problem will be fully separated, they won't execute in pipeline and won't cause PostgreSQL protection firing.

[mjauernig]

@shokurov Need this to be fixed or is the workaround of @rpsft an acceptable solution?

Note: Add this to documentation.

[shokurov]

@mjauernig I think the workaround is acceptable as a makeshift and is really worth documenting, however ultimately the splitting algorythm in PostgresqlConnectionManager is wrong, and needs to be fixed.

I looked at two implementations of the splitting algorythm: in Grate and in Npgsql. Thought Npgsql might expose it as public methods for reuse, but no, it is in internals.

I think the easiest way forward would be to borrow splitting algorythm from Npgsql's SqlQueryParser.

I could do a PR, however I am on Mac and I do not have proper infrastructure for unit tests. Would you accept a PR with only PostgreSQL tests passing?

[mjauernig]

@shokurov
Thanks for the feedback. Yes, we would accept a PR with only PostgreSQL test passed. If github actions works. Then all tests will theire executed. I can also check out the PR and do the unit tests.

[shokurov]

@mjauernig all right, PR is in review. I could not repro BDD style tests, my tests are plain xUnit. If BDD style is mandatory, please give me an example with Theory

[mjauernig]

Thanks @shokurov for the contribution. I will merge it into the v6 release in the next few weeks.

At the moments no BDD test is required.