CREATE INDEX CONCURRENTLY cannot be executed within a pipeline

[thamerlan]

Environment

Liquibase Version: 4.19.0, 4.1.1, 3.5.3

Liquibase Integration & Version: CLI

Liquibase Extension(s) & Version: N/A

Database Vendor & Version: PostgreSQL 14.7

Operating System Type & Version: Ubuntu 18.04.6 LTS

Infrastructure Type/Provider: Ubuntu 18.04.6 LTS Docker Image

Description

After upgrade of PostgreSQL to the latest minor version: 14.7 migration with "CREATE INDEX CONCURRENTLY" failing with:
Starting Liquibase at 14:20:53 (version 4.1.1 DbUp/DbUp#10 built at 2020-10-12 19:24+0000) Unexpected error running Liquibase: Migration failed for change set .../changelog.sql::2021-12-10-... Reason: liquibase.exception.DatabaseException: ERROR: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline [Failed SQL: (0)

Migration example:

--changeset author:2021-12-10-add_new_index_support endDelimiter:# stripComments:false runInTransaction:false
--comment New functionality ......
SET search_path = schema;

CREATE OR REPLACE
   FUNCTION sw_cast_to_ts_utc (VARCHAR)
RETURNS TIMESTAMP WITHOUT TIME ZONE
LANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT AS
$$
/*
~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ > ~ >

   Purpose    : .....
   History    :
~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ < ~ <
*/
   SELECT timezone('UTC', $1::TIMESTAMP WITH TIME ZONE)
$$;

GRANT EXECUTE ON
   FUNCTION sw_cast_to_ts_utc (VARCHAR)
TO user1, user2;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name1 on table1 ( sw_cast_to_ts_utc (jsonb_fields1->>'created_at') );

RESET search_path;

Steps To Reproduce

Create SQL migration, like in the example above. With listed headers, function and index build with concurrently.
Try to run on PostgreSQL 14.7.

Actual Behavior

Exception. See above.

Expected/Desired Behavior

Execution without exception.

Additional Context

See PostgreSQL expert answer here.

[nvoxland]

Thanks for the issue report and the question to the postgresql mailing list.

I've not looked much into postgresql's pipeline mode, it's not something we're explicitly enabling. We normally run in a transaction but it looks like you are already explicitly disabling it which would have been my first question. So thanks for thinking of that.

If you run those series of commands through some other tool, they all run successfully I assume?

[thamerlan]

If you run those series of commands through some other tool, they all run successfully I assume?

yes, if run it manually in the psql -> all good.
if separate it to the two independent changesets (function in one changeset, index in other) -> also all good (this is what I did now as a hotfix for my migrations)

[shokurov]

@thamerlan @nvoxland a bit of information from the other project: we have exactly same problem with DbUp#680 after upgrading PostgreSQL to 14.7 and 11.19 in the last few days (versions below 14.6 and 11.18 are unaffected). Whatever is causing the problem seems to be introduced in last updates.

So @thamerlan you may want to go over again to postgrespro for consultations, let me know if you need any specifics from DbUp. For your reference, error and call Stack are below. Can be reproduced with any create index concurrently.

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

[satheeshj98]

We too started to face similar issue with latest pg versions (13.10, 14.7, 15.7) . As of now downgraded pg to previous version to move ahead.

[dverite]

I've not looked much into postgresql's pipeline mode, it's not something we're explicitly enabling

If the code uses Statement.executeBatch() then it uses a pipeline: that's the same thing. At the level of the Postgres extended query protocol, it's a series of statements that are not separated by SYNC messages, and thus they are executed together in an implicit transaction. Statements like CREATE INDEX CONCURRENTLY that cannot be run in an explicit transaction (that is, inside a BEGIN/COMMIT pair) also cannot be run in an implicit transaction. The latter case being detected and reported as an error by the server is what's new in the recent Postgres bugfix updates.
So in short, CREATE INDEX CONCURRENTLY cannot be run in a JDBC batch.

[mplain]

Problem also appears in postgres version 13.10

Might also be caused by a compound sql statement, e.g. set lock_timeout to 1000; update ..., break this into separate statements.

[tati-qalified]

Hello @thamerlan, I've tested this with liquibase version 4.23.0 and I was able to replicate the issue.
As @dverite said, the way liquibase executes and commits changesets is what's causing the exception, as the transaction will be prematurely closed.

This PR is a workaround for this issue, as is putting the create index concurrently statement in an independent changeset.

I'll be closing this ticket, though be sure to comment if the issue persists.
Thank you!
Tatiana