Atlas Kubernetes Operator, getting error when migration has "CREATE INDEX CONCURRENTLY"

[vmrm]

Atlas Kubernetes Operator, getting error when migration has "CREATE INDEX CONCURRENTLY"

[a8m]

A file directive should be separated by two newlines from the first statement's comment.

Closing as the issue was resolved. You're more than welcome to join our Discord server in case you need more help with it.

[rotemtam]

Use atlas migrate hash

[a8m]

I think setting txmode to none in the create_indexes.sql file should solve the issue, because the default behavior is running each file within a transaction. See: https://atlasgo.io/versioned/apply#file-level-transaction-mode

Can you try this and report if that solves the issue?

[vmrm]

I've tried using https://atlasgo.io/versioned/apply#file-level-transaction-mode

  20230712165923_create_indexes.sql: |
    -- atlas:txmode none
    -- Create index "bookings.facid_memid" to table: "bookings"
    CREATE INDEX CONCURRENTLY "bookings.facid_memid" ON "cd"."bookings" ("facid", "memid");
    -- Create index "bookings.facid_starttime" to table: "bookings"
    CREATE INDEX CONCURRENTLY "bookings.facid_starttime" ON "cd"."bookings" ("facid", "starttime");
    -- Create index "bookings.memid_facid" to table: "bookings"
    CREATE INDEX CONCURRENTLY "bookings.memid_facid" ON "cd"."bookings" ("memid", "facid");
    -- Create index "bookings.memid_starttime" to table: "bookings"
    CREATE INDEX CONCURRENTLY "bookings.memid_starttime" ON "cd"."bookings" ("memid", "starttime");
    -- Create index "bookings.starttime" to table: "bookings"
    CREATE INDEX CONCURRENTLY "bookings.starttime" ON "cd"."bookings" ("starttime");
    -- Create index "members.joindate" to table: "members"
    CREATE INDEX CONCURRENTLY "members.joindate" ON "cd"."members" ("joindate");
    -- Create index "members.recommendedby" to table: "members"
    CREATE INDEX CONCURRENTLY "members.recommendedby" ON "cd"."members" ("recommendedby");

but still getting the:

Status:
  Conditions:
    Last Transition Time:  2023-07-17T18:15:58Z
    Message:               sql/migrate: sql/migrate: execute: executing statement "CREATE INDEX CONCURRENTLY \"bookings.facid_memid\" ON \"cd\".\"bookings\" (\"facid\", \"memid\");" from version "20230712165923": pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block: sql/migrate: execute: write revision: pq: current transaction is aborted, commands ignored until end of transaction block
    Reason:                Reconciling
    Status:                False
    Type:                  Ready

After changing file to:

  20230712165923_create_indexes.sql: |
    -- atlas:txmode none

    CREATE INDEX CONCURRENTLY "bookings.facid_memid" ON "cd"."bookings" ("facid", "memid");
    CREATE INDEX CONCURRENTLY "bookings.facid_starttime" ON "cd"."bookings" ("facid", "starttime");
    CREATE INDEX CONCURRENTLY "bookings.memid_facid" ON "cd"."bookings" ("memid", "facid");
    CREATE INDEX CONCURRENTLY "bookings.memid_starttime" ON "cd"."bookings" ("memid", "starttime");
    CREATE INDEX CONCURRENTLY "bookings.starttime" ON "cd"."bookings" ("starttime");
    CREATE INDEX CONCURRENTLY "members.joindate" ON "cd"."members" ("joindate");
    CREATE INDEX CONCURRENTLY "members.recommendedby" ON "cd"."members" ("recommendedby");

Status:
  Conditions:
    Last Transition Time:  2023-07-17T18:20:20Z
    Message:
    Reason:                Applied
    Status:                True
    Type:                  Ready
  Last Applied:            1689618018
  Last Applied Version:    20230712165923
  observed_hash:           d721437cb2fc4821a74764784a86d7feb6e4da758b40df28a7bac07151edf7ec

everything worked as expected. Thanks! Not sure if comments are breaking some parsing.

[vmrm]

Thanks a lot for your help!

[hosseindehghanpoor]

@vmrm
how did you calculate atlas.sum ?
can you explain it to me

atlas.sum: |
h1:xTbw3AxHRH3YSW7dOGteMxvcigM3f/Y7pdkwwScmbGM=
20230712165510_create_tables.sql h1:BWXssdgcYxJcYSNOaK7bYlhgB5LsumtrNXiBfkC3HiU=
20230712165923_create_indexes.sql h1:WQxGEQSGBr+QCZQB+/cRZ84Ei8Br2iJ9nbwC9/k2oW8=