BUG #16870: ADD COLUMN IF NOT EXISTS with GENERATED ALWAYS AS IDENTITY can cause duplicate sequence

The following bug has been logged on the website:

Bug reference:      16870
Logged by:          Sean Mackedie
Email address:      sean@respax.com
PostgreSQL version: 12.6
Operating system:   Ubuntu 20.04.1
Description:

Hello, I'm getting unexpected behaviour with IDENTITY columns when running
ALTER TABLE ADD COLUMN IF NOT EXISTS on them when they already exist.

I have a scenario when creating deployment scripts that potentially need to
run multiple times on the same or different databases where schema doesn't
necessarily match what it should - the purpose is to try and standardise
schema across multiple systems where previous deployment procedures have
resulted in things getting out of sync, as well as initialising fresh
databases for new clients. The script contains a number of CREATE TABLE IF
NOT EXISTS statements for each table that should be in the schema (with the
correct layout), followed by a series of ALTER TABLE ADD COLUMN IF NOT
EXISTS statements to add missing columns in the case the table does exist.

My problem happens when doing this with IDENTITY columns. If the column
doesn't exist, the ALTER TABLE statement works as expected and adds a new
IDENTITY column. If it DOES exist already as an IDENTITY column, it puts the
table in a state where data can't be INSERTed into it anymore, failing with
the error "[XX000]: ERROR: more than one owned sequence found". Searching on
Google suggested a problem where this happened when converting an existing
"serial" column into an IDENTITY column, however this is different as it
doesn't involve any serial columns, and is using an IF NOT EXISTS to add the
column and (in my mind) shouldn't even be trying to create a sequence when
the column already exists (being different from an "ADD GENERATED ALWAYS AS
IDENTITY which should add this to an existing column).

Here's a script to reproduce the issue:
-------------------------------------------------------------------------------------------------------------
-- Create test table
CREATE TABLE IF NOT EXISTS derp
    (
        primarykey integer NOT NULL
            CONSTRAINT pk_derp_primarykey PRIMARY KEY GENERATED ALWAYS AS
IDENTITY,
        data varchar NOT NULL
    );

-- Insert record to confirm working, this should succeed
INSERT INTO
    derp
VALUES ( DEFAULT, 'derp' );

-- Should show single record just inserted
SELECT *
FROM
    derp;

-- Add new column if it doesn't exist, since it does exist this SHOULD do
nothing at all
ALTER TABLE derp
    ADD COLUMN IF NOT EXISTS primarykey integer NOT NULL GENERATED ALWAYS AS
IDENTITY;

-- Attempt to insert record, this SHOULD work but now fails with "XX000:
more than one owned sequence found"
INSERT INTO
    derp
VALUES ( DEFAULT, 'derp' );

-- Still only shows first record inserted
SELECT *
FROM
    derp;
-------------------------------------------------------------------------------------------------------------


And here's a snippet from the log with "SET LOG_ERROR_VERBOSITY TO
VERBOSE":
-------------------------------------------------------------------------------------------------------------
2021-02-17 09:40:15.349 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.095 ms  execute <unnamed>: set LOG_ERROR_VERBOSITY to verbose
2021-02-17 09:40:15.349 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.081 ms  parse <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.033 ms  bind <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.013 ms  execute <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:15.355 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.058 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.011 ms  bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.023 ms  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.584 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.019 ms  bind S_1: 
2021-02-17 09:40:34.584 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.586 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.014 ms  bind S_1: 
2021-02-17 09:40:34.586 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.588 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.009 ms  bind S_1: 
2021-02-17 09:40:34.588 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.036 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.006 ms  bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.015 ms  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.601 AEST [28138] postgres@respax_test ERROR:  XX000:
more than one owned sequence found
2021-02-17 09:40:34.601 AEST [28138] postgres@respax_test LOCATION:
getOwnedSequence, pg_depend.c:816
2021-02-17 09:40:34.601 AEST [28138] postgres@respax_test STATEMENT:  INSERT
INTO derp VALUES (DEFAULT, 'derp')
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.073 ms  parse <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.030 ms  bind <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.012 ms  execute <unnamed>: select current_database() as a,
current_schemas(false) as b
2021-02-17 09:40:34.609 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.626 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.066 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.626 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.627 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.009 ms  bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.627 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.627 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.020 ms  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.627 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:42:05.504 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.018 ms  bind S_1: 
2021-02-17 09:42:05.504 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.506 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.018 ms  bind S_1: 
2021-02-17 09:42:05.506 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.507 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.012 ms  bind S_1: 
2021-02-17 09:42:05.507 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.037 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.006 ms  bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOG:  00000:
duration: 0.015 ms  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres@respax_test LOCATION:
exec_execute_message, postgres.c:2143
-------------------------------------------------------------------------------------------------------------


System info:
Output of SELECT version(): PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04)
9.3.0, 64-bit
Output of uname -a: Linux SeanDev-Linux 5.4.0-65-generic #73-Ubuntu SMP Mon
Jan 18 17:25:17 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL config: https://pastebin.com/bTMySfMk

I hope that covers everything you need. Please let me know if you need
anything else.