Oleksandr Volkovskyi wrote:
> I am trying to apply migration but getting issues similar to the following:
> Reason: liquibase.exception.DatabaseException: ERROR: CREATE INDEX
> CONCURRENTLY cannot be executed within a pipeline
>
> We have a tons of script and services and its not feasible to modify all of
> them, it will take couple of days to me...
>
> Can someone please advise how can I disable pipeline mode or where can I
> find installer or binaries for postgres 13.7 ?
> Maybe there is any pages on how can I downgrade 13.10 to 13.7 ?
There was an identical report previously:
https://www.postgresql.org/message-id/56b56a3e-81c0-41eb-ad96-7e5e02fe6357@gmail.com
There's an open issue at liquibase:
https://github.com/liquibase/liquibase/issues/3806
I've just commented over there to mention that a pipeline and a JDBC batch
were
the same thing, and it looks like they're using Statement.executeBatch() to
send the SQL statements.
The root cause is that CREATE INDEX CONCURRENTLY can neither be run in an
implicit
transaction (in other words inside a batch or a pipeline, that's the same),
nor in an explicit transaction.
So in a migration, if a user has a bunch of statements that they want to be
executed
in an "all or nothing succeeds " mode, they need to extract the CREATE INDEX
CONCURRENTLY statements from the rest, and run them separately.
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite