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(at)respax(dot)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(at)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(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:15.355 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:15.355 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:15.355 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.058 ms parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.011 ms bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.023 ms execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:15.367 AEST [28138] postgres(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.584 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.019 ms bind S_1:
2021-02-17 09:40:34.584 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.586 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.014 ms bind S_1:
2021-02-17 09:40:34.586 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.588 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.009 ms bind S_1:
2021-02-17 09:40:34.588 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.036 ms parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.006 ms bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.015 ms execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.596 AEST [28138] postgres(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.601 AEST [28138] postgres(at)respax_test ERROR: XX000:
more than one owned sequence found
2021-02-17 09:40:34.601 AEST [28138] postgres(at)respax_test LOCATION:
getOwnedSequence, pg_depend.c:816
2021-02-17 09:40:34.601 AEST [28138] postgres(at)respax_test STATEMENT: INSERT
INTO derp VALUES (DEFAULT, 'derp')
2021-02-17 09:40:34.609 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.609 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.609 AEST [28138] postgres(at)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(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:40:34.626 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.066 ms parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.626 AEST [28138] postgres(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:40:34.627 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.009 ms bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.627 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:40:34.627 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.020 ms execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:40:34.627 AEST [28138] postgres(at)respax_test LOCATION:
exec_execute_message, postgres.c:2143
2021-02-17 09:42:05.504 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.018 ms bind S_1:
2021-02-17 09:42:05.504 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.506 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.018 ms bind S_1:
2021-02-17 09:42:05.506 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.507 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.012 ms bind S_1:
2021-02-17 09:42:05.507 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.510 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.037 ms parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres(at)respax_test LOCATION:
exec_parse_message, postgres.c:1555
2021-02-17 09:42:05.510 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.006 ms bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres(at)respax_test LOCATION:
exec_bind_message, postgres.c:1922
2021-02-17 09:42:05.510 AEST [28138] postgres(at)respax_test LOG: 00000:
duration: 0.015 ms execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
2021-02-17 09:42:05.510 AEST [28138] postgres(at)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.