Running atlas commands against cockroachdb returns error with postgres wire protocol

[theoriginalstove]

Running the commands atlas migrate or atlas schema when trying to create either declarative or versioned migration workflows will result in an error: Error: sql/migrate: taking database snapshot: query triggers: pq: unknown function: regexp_match()

Steps to reproduce:

1. Setup a single node of Cockroachdb or cluster with docker compose (cluster setup with compose file below):

# basic docker-compose.yaml to setup cockroachdb cluster locally.
# run docker compose up -d
# NOTE: once the cluster is started run the command:
# docker exec -it roach1 ./cockroach --host=roach1:26357 init --insecure
services:
  roach1:
    container_name: roach1
    image: cockroachdb/cockroach:latest-v23.2
    command: start --insecure --advertise-addr=roach1:26357 --http-addr=roach1:8080 --listen-addr=roach1:26357 --sql-addr=roach1:26257 --join=roach1:26357,roach2:26357,roach3:26357
    hostname: roach1
    networks:
      roachnet:
        aliases:
          - roach1
    ports:
      - "26257:26257"
      - "8080:8080"
    volumes:
      - docker_data_cockroach1:/cockroach/cockroach-data
  roach2:
    container_name: roach2
    image: cockroachdb/cockroach:latest-v23.2
    command: start --advertise-addr=roach2:26357 --http-addr=roach2:8081 --listen-addr=roach2:26357 --sql-addr=roach2:26258 --insecure --join=roach1:26357,roach2:26357,roach3:26357   
    hostname: roach2
    networks:
      roachnet:
        aliases:
          - roach2
    ports:
      - "26258:26258"
      - "8081:8081"
    volumes:
      - docker_data_cockroach2:/cockroach/cockroach-data
  roach3:
    container_name: roach3
    image: cockroachdb/cockroach:latest-v23.2
    command: start --advertise-addr=roach3:26357 --http-addr=roach3:8082 --listen-addr=roach3:26357 --sql-addr=roach3:26259 --insecure --join=roach1:26357,roach2:26357,roach3:26357
    hostname: roach3
    networks:
      roachnet:
        aliases:
          - roach3
    ports:
      - "26259:26259"
      - "8082:8082"
    volumes:
      - docker_data_cockroach3:/cockroach/cockroach-data
networks:
  roachnet: 
    name: roachnet
volumes:
  docker_data_cockroach1: { }
  docker_data_cockroach2: { }
  docker_data_cockroach3: { }

2. Setup schema file schemas.sql:

   CREATE TABLE IF NOT EXISTS "users" (
   id UUID NOT NULL DEFAULT gen_random_uuid(),
   PRIMARY KEY("id")
   );

3. Run either command atlas schema or atlas migrate

   atlas schema diff \
   --from "postgres://root@localhost:26257/example1?sslmode=disable" \
   --to "file://schemas.sql"
   --dev-url "postgres://root@localhost:26257/defaultdb?sslmode=disable"

atlas migrate diff initial \
--to "file://schemas.sql" \
--dev-url "postgresql://root@localhost:26257?sslmode=disable" \
--format '{{ sql . " " }}'

[theoriginalstove]

@a8m from the discord discussion regarding triggers in cockroachdb https://discord.com/channels/930720389120794674/933360123357839360/1270638827454730373

[theoriginalstove]

Was digging around the sql/postgres driver and found the error is propagating up from this block in sql/postgres/driver.go when calling d.InspectSchema(ctx, d.schema, nil):

// Snapshot implements migrate.Snapshoter.
func (d *Driver) Snapshot(ctx context.Context) (migrate.RestoreFunc, error) {
    // Postgres will only then be considered bound to a schema if the `search_path` was given.
    // In all other cases, the connection is considered bound to the realm.
    if d.schema != "" {
        s, err := d.InspectSchema(ctx, d.schema, nil)
        if err != nil {
            return nil, err
        }
        if len(s.Tables) > 0 {
            return nil, &migrate.NotCleanError{
                State:  schema.NewRealm(s),
                Reason: fmt.Sprintf("found table %q in connected schema", s.Tables[0].Name),
            }
        }
        return d.SchemaRestoreFunc(s), nil
    }

Which led me to test logging out of the atlas cli and running the command. Running it while logged out of atlas cloud, the command will run successfully (or in my case give another error because I'm trying to create migrations with multiple .sql files in a declarative manner)

My guess is that the query being ran against cockroachdb are trying to execute a Postgres function that hasn't been ported over to cockroach. Running the commands while logged in, but against earlier versions of cockroach would error out with Error: sql/migrate: taking database snapshot: query functions: pq: unknown function: pg_get_function_arg_default(): function undefined which pg_get_function_arg_default() was added in cockroach v23.2 based off their documentation.