Postgres flyway create concurrently index error

Question

I want do something like:

CREATE OR REPLACE FUNCTION __column_exists(TEXT, TEXT, TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.columns WHERE (table_schema, table_name, column_name) = ($1, $2, $3));
$$ language sql STRICT;

DO $$ BEGIN IF __column_exists('public', 'table_name', 'column_name') THEN
CREATE INDEX CONCURRENTLY IF NOT EXISTS column_idx ON table_name USING btree (column_name);
END IF; END; $$;

But there is

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

It works fine if I write only

CREATE INDEX CONCURRENTLY IF NOT EXISTS column_idx ON table_name USING btree (column_name);

How can I preserve the condition for the existence of a column?

Flyway 4.2.0;

IIRC, you need to use two separate scripts (that is CREATE INDEX CONCURRENTLY must be on its own in a script, otherwise Flyway can't run it without transaction) — Mark Rotteveel
– Mark Rotteveel, Commented Jun 25, 2021 at 16:45

Cyrille MODIANO · Accepted Answer · 2021-06-25 15:43:45Z

2

Why are you using such an old version of flyway ? You should first upgrade your version of flyway, and then:

Create a configuration file for your script like V01_001__my_script.sql.conf where V01_001__my_script.sql is the name of your sql file.

and put this line inside:

executeInTransaction=false

That will disable the execution in a transaction for this script.

edited Jun 25, 2021 at 15:43

answered Jun 22, 2021 at 15:51

Cyrille MODIANO

2,4163 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NewSheriff Over a year ago

There is a lot of old legacy code) When i started updating the version it caused a cascade of errors. Tomorrow i will try your advice)

NewSheriff Over a year ago

There is new problem) Postgres cant run functions and procedure in non transactional mode

Collectives™ on Stack Overflow

Postgres flyway create concurrently index error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related