René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"\"Relation does not exist\" error when using SQLC with goose migrations ","articleBody":"### Version\n\n1.26.0\n\n### What happened?\n\nI'm integrating sqlc into an existing golang project using Goose. When I run sqlc generate I'm getting the following error:\r\n\r\nmigrations/0036_source_table_embeddings.sql:1:1: relation \"global_metric\" does not exist\r\n\r\nThe table does exist and is in an earlier migration file. Strangely if remove the lines referencing that table in the migration file referenced in the error, sqlc generate runs successfully. \r\n\r\nThe table in question is created in migration 5 and if I use it in any migration before migration 9 sqlc generate runs successfully, if I include it in any migration after migration 9 I get the above error of the reference not being found. The table is not deleted or modified in any way in migration 9.\n\n### Relevant log output\n\n```shell\nmigrations/0036_source_table_embeddings.sql:1:1: relation \"global_metric\" does not exist\n```\n\n\n### Database schema\n\n```sql\n-- Migration 5 (table is introduced)\r\n-- +goose Up\r\nCREATE TABLE IF NOT EXISTS client_portal.public.global_metric (\r\n    id SERIAL primary key,\r\n    metric varchar NOT NULL,\r\n    user_id uuid NOT NULL,\r\n    org_id uuid NOT NULL,\r\n    formula varchar NOT NULL,\r\n    required_metrics varchar,\r\n    \"description\" varchar,\r\n    tags varchar,\r\n    tags_query tsquery,\r\n    created_date timestamp DEFAULT (now() at time zone 'utc'),\r\n    updated_date timestamp DEFAULT (now() at time zone 'utc'),\r\n    UNIQUE(org_id, user_id, metric)\r\n);\r\n\r\n-- +goose Down\r\nDROP TABLE IF EXISTS client_portal.public.global_metric;\r\n\r\n-- Migration 9 (Last migration where I can include the table and it runs successfully)\r\n-- +goose Up\r\nCREATE TABLE IF NOT EXISTS client_portal.public.global_metric (\r\n    id SERIAL primary key,\r\n    metric varchar NOT NULL,\r\n    user_id uuid NOT NULL,\r\n    org_id uuid NOT NULL,\r\n    formula varchar NOT NULL,\r\n    required_metrics varchar,\r\n    \"description\" varchar,\r\n    tags varchar,\r\n    tags_query tsquery,\r\n    created_date timestamp DEFAULT (now() at time zone 'utc'),\r\n    updated_date timestamp DEFAULT (now() at time zone 'utc'),\r\n    UNIQUE(org_id, user_id, metric)\r\n);\r\n\r\n-- +goose Down\r\nDROP TABLE IF EXISTS client_portal.public.global_metric;\r\n\r\n-- Migration 10 (starts failing here if I include the table)\r\n-- +goose Up\r\nCREATE TABLE IF NOT EXISTS client_portal.public.global_concept (\r\n    id SERIAL primary key,\r\n    user_id uuid NOT NULL,\r\n    org_id uuid NOT NULL,\r\n    concept varchar NOT NULL,\r\n    tags varchar,\r\n    tags_query tsquery,\r\n    context varchar,\r\n    description varchar,\r\n    created_date timestamp DEFAULT (now() at time zone 'utc'),\r\n    updated_date timestamp DEFAULT (now() at time zone 'utc')\r\n);\r\n\r\nCREATE INDEX IF NOT EXISTS global_concept_org_id_user_id_tags_idx ON global_concept (\"org_id\", \"user_id\", \"tags\");\r\n\r\n-- +goose Down\r\nDROP TABLE IF EXISTS client_portal.public.global_concept;\r\nDROP INDEX IF EXISTS global_concept_org_id_user_id_tags_idx;\r\n\r\n-- Migration 36 (first migration after migration 10 that references the table, this is where it's failing)\r\n-- +goose Up\r\nALTER TABLE client_portal.public.glossary ADD COLUMN IF NOT EXISTS term_embedding vector(1024);\r\nCREATE INDEX IF NOT EXISTS glossary_term_embedding_idx ON client_portal.public.glossary USING hnsw (\"term_embedding\" vector_cosine_ops);\r\n\r\nALTER TABLE client_portal.public.global_metric ADD COLUMN IF NOT EXISTS metric_embedding vector(1024);\r\nCREATE INDEX IF NOT EXISTS global_metric_embedding_idx ON client_portal.public.global_metric USING hnsw (\"metric_embedding\" vector_cosine_ops);\r\n\r\nALTER TABLE client_portal.public.global_concept ADD COLUMN IF NOT EXISTS concept_embedding vector(1024);\r\nCREATE INDEX IF NOT EXISTS global_concept_embedding_idx ON client_portal.public.global_concept USING hnsw (\"concept_embedding\" vector_cosine_ops);\r\n\r\n-- +goose Down\r\nDROP INDEX IF EXISTS glossary_term_embedding_idx;\r\nALTER TABLE client_portal.public.glossary DROP COLUMN IF EXISTS term_embedding;\r\n\r\nDROP INDEX IF EXISTS global_metric_embedding_idx;\r\nALTER TABLE client_portal.public.global_metric DROP COLUMN IF EXISTS metric_embedding;\r\n\r\nDROP INDEX IF EXISTS global_concept_embedding_idx;\r\nALTER TABLE client_portal.public.global_concept DROP COLUMN IF EXISTS concept_embedding;\n```\n\n\n### SQL queries\n\n```sql\nN/A as the issue is the schema\n```\n\n\n### Configuration\n\n```yaml\nversion: \"2\"\r\nsql:\r\n  - engine: \"postgresql\"\r\n    queries: \"/pkg/dal/queries\"\r\n    schema: \"migrations/\"\r\n    gen:\r\n      go:\r\n        package: \"dal\"\r\n        out: \"/pkg/dal/sqlc\"\r\n        sql_package: \"pgx/v5\"\n```\n\n\n### Playground URL\n\n_No response_\n\n### What operating system are you using?\n\nLinux\n\n### What database engines are you using?\n\nPostgreSQL\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/rob2244","@type":"Person","name":"rob2244"},"datePublished":"2024-06-13T22:43:53.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":7},"url":"https://github.com/3444/sqlc/issues/3444"}