Skip to content

migration fails with relation "public.template_versions" does not exist #12619

Closed
@95gabor

Description

@95gabor

Hi!

I just updated my coder to v2.9.0, and during the startup, I got the following migration error:

Started HTTP listener at http://0.0.0.0:8080
Started TLS/HTTPS listener at https://0.0.0.0:8443
View the Web UI: https://coder.myhost
2024-03-16 12:02:26.842 [erro]  connect to postgres failed ...
    error= migrate up:
               github.com/coder/coder/v2/cli.ConnectToPostgres
                   /home/runner/actions-runner/_work/coder/coder/cli/server.go:2121
             - up:
               github.com/coder/coder/v2/coderd/database/migrations.Up
                   /home/runner/actions-runner/_work/coder/coder/coderd/database/migrations/migrate.go:74
             - 2 errors occurred:
           	* run statement: migration failed: relation "public.template_versions" does not exist in line 0: -- We cannot alter the column type while a view depends on it, so we drop it and recreate it.
           DROP VIEW template_version_with_user;
           -- Turns the list of provider names into JSONB with the type `Array<{ id: string; optional?: boolean }>`
           -- eg. `'{github,gitlab}'::text[]` would become `'[{"id": "github"}, {"id": "gitlab"}]'::jsonb`
           CREATE OR REPLACE FUNCTION migrate_external_auth_providers_to_jsonb(text[])
             RETURNS jsonb
             LANGUAGE plpgsql
             AS $$
           DECLARE
             result jsonb;
           BEGIN
             SELECT
               jsonb_agg(jsonb_build_object('id', value::text)) INTO result
             FROM
               unnest($1) AS value;
             RETURN result;
           END;
           $$;
           -- Update the column type and migrate the values
           ALTER TABLE template_versions
             ALTER COLUMN external_auth_providers TYPE jsonb
             USING migrate_external_auth_providers_to_jsonb(external_auth_providers);
           -- Make the column non-nullable to make the types nicer on the Go side
           UPDATE template_versions
             SET external_auth_providers = '[]'::jsonb
             WHERE external_auth_providers IS NULL;
           ALTER TABLE template_versions
             ALTER COLUMN external_auth_providers SET DEFAULT '[]'::jsonb;
           ALTER TABLE template_versions
             ALTER COLUMN external_auth_providers SET NOT NULL;
           -- Recreate `template_version_with_user` as described in dump.sql
           CREATE VIEW template_version_with_user AS
           SELECT
             template_versions.id,
             template_versions.template_id,
             template_versions.organization_id,
             template_versions.created_at,
             template_versions.updated_at,
             template_versions.name,
             template_versions.readme,
             template_versions.job_id,
             template_versions.created_by,
             template_versions.external_auth_providers,
             template_versions.message,
             template_versions.archived,
             COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
             COALESCE(visible_users.username, ''::text) AS created_by_username
           FROM (public.template_versions
             LEFT JOIN visible_users ON (template_versions.created_by = visible_users.id));
           COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';
           -- Cleanup
           DROP FUNCTION migrate_external_auth_providers_to_jsonb;
            (details: pq: relation "public.template_versions" does not exist)
           	* commit tx on unlock: pq: Could not complete operation in a failed transaction
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x18 pc=0x1ef2fa8]
goroutine 1 [running]:
github.com/coder/coder/v2/cli.traceError({0x8eac220, 0x40008945a0})
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1249 +0x68
github.com/coder/coder/v2/cli.cliHumanFormatError({0x400082a600, 0x1267}, {0x8eac220?, 0x40008945a0}, 0x7f727bca70?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0xf4
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40008aca80, 0x98c}, {0x8eac220?, 0x4000894558}, 0x21d02c0?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40009166e0, 0x48}, {0x8ea9de0?, 0x4000a1a340}, 0x4000b456d8?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40009165f0, 0x44}, {0x8ea9d40?, 0x40007180c0}, 0x4000b457b8?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x40005ceb00, 0x38}, {0x8ea9d40?, 0x4000718100}, 0x4000b45898?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x4000715500, 0x23}, {0x8ea9d40?, 0x4000718200}, 0x0?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea9d40?, 0x4000718280}, 0x1?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1131 +0x13c
github.com/coder/coder/v2/cli.formatRunCommandError(0x4000894378, 0x8ea9da0?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1199 +0x1bc
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea9da0?, 0x4000894378}, 0x8ea9100?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1126 +0xbc
github.com/coder/coder/v2/cli.cliHumanFormatError({0x0, 0x0}, {0x8ea91c0?, 0x4000894390}, 0x0?)
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1112 +0x31c
github.com/coder/coder/v2/cli.(*prettyErrorFormatter).format(0x4000b45d78, {0x8ea91c0?, 0x4000894390?})
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:1073 +0x48
github.com/coder/coder/v2/cli.(*RootCmd).RunMain(0x400074dea0, {0x40007a3400, 0x2c, 0x50})
	/home/runner/actions-runner/_work/coder/coder/cli/root.go:162 +0x25c
main.main()
	/home/runner/actions-runner/_work/coder/coder/enterprise/cmd/coder/main.go:11 +0x40

I don't know the codebase, but the I think the migration uses the hardcoded public schema instead of the provided one (coder).

Migration: https://github.com/coder/coder/blob/v2.9.0/coderd/database/migrations/000196_external_auth_providers_jsonb.up.sql

my database env: CODER_PG_CONNECTION_URL=postgresql://user:password@postgres.database.svc.cluster.local/coder

Metadata

Metadata

Assignees

No one assigned

    Labels

    need-helpAssign this label prompts an engineer to check the issue. Only humans may set this.s1Bugs that break core workflows. Only humans may set this.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions