Thread: BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update
BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15891 Logged by: Maarten Jacobs Email address: m.jacobs@defacto.nl PostgreSQL version: 11.4 Operating system: MacOS and Ubuntu Description: Since the update of my PostgreSQL server to 11.4 some of my automated migrations of a web app (Phoenix on Elixir) started to fail. I’ve narrowed it down to not being able to do the following: GIVEN THESE TABLES: ==================== Table "public.users" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('users_id_seq'::regclass) Indexes: "users_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "likes" CONSTRAINT "likes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ------------------------ Table "public.posts" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('posts_id_seq'::regclass) Indexes: "posts_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "likes" CONSTRAINT "likes_post_id_fkey" FOREIGN KEY (post_id) REFERENCES posts(id) ------------------------ Table "public.likes" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('likes_id_seq'::regclass) user_id | bigint | | | post_id | bigint | | | inserted_at | timestamp without time zone | | not null | updated_at | timestamp without time zone | | not null | Indexes: "likes_pkey" PRIMARY KEY, btree (id) "unique_user_post_index" UNIQUE, btree (user_id, post_id) "likes_post_id_index" btree (post_id) "likes_user_id_index" btree (user_id) Foreign-key constraints: "likes_post_id_fkey" FOREIGN KEY (post_id) REFERENCES posts(id) "likes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ==================== I am not able to run the (generated) alter statement: ALTER TABLE "likes" DROP CONSTRAINT "likes_user_id_fkey", ALTER COLUMN "user_id" TYPE bigint, ADD CONSTRAINT "likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE, DROP CONSTRAINT "likes_post_id_fkey", ALTER COLUMN "post_id" TYPE bigint, ADD CONSTRAINT "likes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "posts"("id") ON DELETE CASCADE But the following statement does work: ALTER TABLE "likes" DROP CONSTRAINT "likes_user_id_fkey", ADD CONSTRAINT "likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE, DROP CONSTRAINT "likes_post_id_fkey", ADD CONSTRAINT "likes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "posts"("id") ON DELETE CASCADE Is this how it is supposed to work or is it a bug? --- Thanks Maarten
Re: BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > Since the update of my PostgreSQL server to 11.4 some of my automated > migrations of a web app (Phoenix on Elixir) started to fail. I’ve narrowed > it down to not being able to do the following: Yeah, this is an embarrassing bug in the fix for bug #15835. A fix is already committed, https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=afaf48afb and it will be in the next minor releases. regards, tom lane