Обсуждение: BUG #15891: Cannot alter columns and add constraints in one alter statement since 11.4 update

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

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