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.green/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.