Обсуждение: Cannot alter columns and add constraints in one alter statement since11.4 update

Поиск
Список
Период
Сортировка

Cannot alter columns and add constraints in one alter statement since11.4 update

От
Maarten Jacobs
Дата:
Since the update of my PostgreSQL server to 11.4 some of my automated migrations of a web app (Phoenix on Elixir)
startedto 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?