Feedback-ID: ie0a040ee:Fastmail
Message-ID: <d3f72129-95a1-4c0e-8827-e8f5a34687d6@eisentraut.org>
Date: Wed, 29 Oct 2025 17:01:12 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Two sequences associated with one identity column
To: Colin 't Hart <colinthart@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d3f72129-95a1-4c0e-8827-e8f5a34687d6%40eisentraut.org>
Precedence: bulk

On 29.10.25 12:27, Colin 't Hart wrote:
> One of my clients has a database in which a single identity column
> (called "id" in that table) has two sequences associated with it(!)
> 
> Both sequences display
> 
> Sequence for identity column: <schema>.<table>.id
> 
> when described with \d in psql.
> 
> 
> Inserting fails with "ERROR:  more than one owned sequence found", as
> does trying to alter the table to drop the identity on that column.
> 
> 
> Trying to drop either sequence results in
> 
> ERROR:  cannot drop sequence <name> because column id of table <name>
> requires it
> HINT:  You can drop column id of table <name> instead.
> 
> while trying to alter either sequence "owned by none" results in
> 
> ERROR:  cannot change ownership of identity sequence
> DETAIL:  Sequence "<name>" is linked to table "<name>".
> 
> 
> How do we fix this? I presume we need to update the catalog directly
> to dissociate one of the sequences and after that drop the orphaned
> sequence.

I don't know how one would get into this situation, but I can fake it 
like this:

create table t1 (a int, b int generated always as identity);

select * from pg_depend where refclassid = 'pg_class'::regclass and 
refobjid = 't1'::regclass;
  classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
     1247 | 16388 |        0 |       1259 |    16386 |           0 | i
     1259 | 16384 |        0 |       1259 |    16386 |           2 | i
(2 rows)

The second entry is the dependency between the sequence and the table. 
1259 is pg_class, the numbers 16384 and 16386 are the OIDs of the 
sequence and the table, and 2 is the column number.

Now create another sequence and manually insert a dependency record:

create sequence sx;

insert into pg_depend values (1259, 'sx'::regclass, 0, 1259, 16386, 2, 'i');

Now you have the same breakage:

insert into t1 (a) values (1);
ERROR:  more than one owned sequence found


To fix this, remove the extra dependency record:

delete from pg_depend where (classid, objid, objsubid) = 
('pg_class'::regclass, 'sx'::regclass, 0) and (refclassid, refobjid, 
refobjsubid) = ('pg_class'::regclass, 't1'::regclass, 2) and deptype = 'i';