Recent release breaks migrations that use 'create index concurrently'

[Daniel-Ash]

Describe the bug

This occurred in a github action that uses the latest version of the CLI by default. Reverting to an older version (I picked 1.220.0 for no good reason) resolves the issue.

To Reproduce
Steps to reproduce the behavior:

1. Install the latest version of the CLI (v1.223.10 as I write this).
2. Add a migration with CREATE INDEX CONCURRENTLY
3. Run supabase start
4. Receive the following error:
   Applying migration [redacted].sql... Stopping containers... ERROR: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline (SQLSTATE 25001)

Expected behavior
Migration should run without fail.

System information

• Version of CLI: v1.223.10

[sweatybridge]

This might be due to a version bump to postgres from 15.1 to 15.6. Could you check the contents of supabase/.temp/postgres-version, if it exists?

[Daniel-Ash]

I've only observed this in a github action so far + haven't attempted to reproduce elsewhere - any thoughts as to how I could validate that there?

[sweatybridge]

Ic, could you share the postgres version of your hosted project then? https://supabase.com/dashboard/project/_/settings/infrastructure

This error is reported by the server afaik, so it's a bit strange that downgrading cli had any impact at all.

Have you also tried to reproduce this locally with npx supabase@latest db start? My migration file containing create index concurrently ran without errors on 15.6. So it's making me suspect that the issue might be ephemeral, depending on the internal state of your hosted db.

[Daniel-Ash]

I'm not sure it has anything to do with a linked project, because it is a CI action that spins up a fresh supabase on the runner, runs the migrations, lints, tests, etc. I haven't experienced this issue against an actual project - it happened when supabase start applied an old migration to the fresh db.

But for clarity, our staging and production dbs are on 15.1.1.44

Here is the github action:

name: Supabase CI

on:
  pull_request:
  push:
    branches:
      - main
      - dev
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-latest
    defaults:
      run:
        working-directory: ./supabase-db/supabase

    steps:
      - uses: actions/checkout@v3

      - uses: supabase/setup-cli@v1
        with:
          version: latest 
      - name: Start database
        run: supabase db start

      - name: Lint database
        run: supabase db lint

      - name: Run database tests
        run: |
          supabase db test 

[matthewmorek]

I can confirm this started happening recently in our repos where we use the CLI and run the same kind of migration:

ERROR: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline (SQLSTATE 25001)              
At statement 1: CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS 

Pinning CLI version to an older one (v1.219.2 in our case) resolved the issue. This version runs Postgres v15.1.1.78.

[devstein]

This started happening to us today too. Only in CI is impacted. We are on Postgres v15

[sweatybridge]

I managed to reproduce this locally. The problem is with adding create index concurrently ... in the same migration file where the referenced table is created.

Since we are running each migration in its own transaction, I'd suggest moving create index statement to a separate file. For eg. supabase/supabase-action-example@397c630. You can see that db start passed with this change.

After moving the create index statement, you can use the migration repair command to update your history table. For example, assuming your new migration file is 20241120053534_add_index.sql

supabase migration repair --status applied 20241120053534

[Daniel-Ash]

Hey @sweatybridge - do you have any more context on the underlying issue?

In fixing our migrations, we've found an example where this breaks but the migration in question doesn't have any other statements related to the referenced table.

Update: To keep it simple, we moved all instances of create index concurrently to a single new migration file (with nothing else in it), but this file now causes the error to occur.

Update 2: Seems you cannot create multiple concurrent indexes in the same transaction now. Our solution was to remove concurrently as this is a ghost migration anyway..

Would love to get a clear view on the correct way to use create index concurrently in supabase migrations going forward.

[activenode]

Here's more information about it:

It's quite interesting to see that if you simply create indexes with CONCURRENTLY, then there cannot be more than one concurrent index creation in the file EVEN if it's on different tables. Here's a sample:

CREATE index concurrently if not exists task_received_at ON task(received_a);

CREATE index concurrently if not exists recuring_idx ON recurring(user_id, received_at);

will lead to the above issue of pipeline failure. Anybody knows why?

//edit:

Basically we're confirm what @Daniel-Ash said

Update 2: Seems you cannot create multiple concurrent indexes in the same transaction now. Our solution was to remove concurrently as this is a ghost migration anyway..

[sweatybridge]

To provide more context on the underlying issue:

We are running each migration file in its own transaction to ensure all SQL statements are executed atomically.

The main use case for creating indexes concurrently is to avoid locking out writes to the table being indexed. Postgres implements this using multiple transactions under the hood.

In a concurrent index build, the index is actually entered as an “invalid” index into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate.

This means create index concurrently has a few limitations, including

• Only one concurrent index build can occur on a table at a time.
• Schema modification of the table is not allowed while the index is being built.
• Regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.

Given the complexities above, the only guaranteed way to create an index concurrently is to execute it in its own migration file.

This problem did not surface in older versions of CLI because we were running Postgres 15.1 by default. There was bug in postgres server that failed to block such operations when the transaction is executed using the extended query protocol.

Since CLI 1.223.1, we have updated the default image to 15.6 which introduced fixes to the server side bug. Therefore, individual create index concurrently statements must now be placed into its own standalone migration file.

To repair your migration history table, you can refactor all migration files in one go, and run supabase migration repair without any args to update all entries to match your local supabase/migrations directory.

[activenode]

Great insights @sweatybridge , will bookmark them!