Is require-concurrent-index-creation necessary straight after table creation?

[maciej]

Currently if even if an index is created directly after the table it refers to require-concurrent-index-creation will be triggered. Index creation on an empty table is virtually instantaneous and locking a table that is unlikely to receive writes until the migration script finishes seems like a non-issue to me.

Example migrate script:

create table if not exists customer
(
    email text
);

create unique index if not exists idx_custome_email on customer (email);

squawk output:

> squawk up.sql
up.sql:5:2: warning: require-concurrent-index-creation

   5 | create unique index if not exists idx_custome_email on customer (email);

  note: Creating an index blocks writes.
  help: Create the index CONCURRENTLY.

find detailed examples and solutions for each rule at https://squawkhq.com/docs/rules

I'd suggest relaxing the rule in cases where tables and their indexes are created in the same file.

[sbdchd]

Yeah if the table has just been created it won't matter since there's no data

[chdsbd]

If you wrap your migration in a transaction squawk won't warn:

begin;
create table if not exists customer
(
    email text
);

create unique index if not exists idx_custome_email on customer (email);
commit;

[hxeli]

What do you think about adding a rule to ban CREATE INDEX CONCURRENTLY in migrations? We have a long list of migrations that run when onboarding a new tenant, and using CONCURRENTLY can take forever to finish on a database with high throughput.

[sbdchd]

@hxeli does the wrapping in a database transaction not work in your case?

Couple other ideas:

• maybe we could check if the table was created in the same migration and assume it's okay?
• add a config option that lets you specify the behavior for the existing concurrent-index-creation rule, like 'never' | 'always'

[hxeli]

The issue arises when creating a new schema from scratch: the table is created in a previous migration, but that context isn't available during linting. But when migrating a populated database, you definitely should use CONCURRENTLY.

On second thought, this might not be relevant for squawk, IMO.