BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: m(dot)jacobs(at)defacto(dot)nl
Subject: BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update
Date: 2019-07-03 14:04:04
Message-ID: 15891-70ce88ee7428e0b1@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15891
Logged by: Maarten Jacobs
Email address: m(dot)jacobs(at)defacto(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-07-03 14:49:48 Re: BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update
Previous Message Alexey Ermakov 2019-07-03 13:49:37 Re: BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"