ERROR: more than one owned sequence found in Postgres

Question

I'm setting up a identity column to my existing columns for the Patient table.
Here I would like to use GENERATED ALWAYS AS IDENTITY.

So I setup the identity column by using the following statement (previously it was serial):

ALTER TABLE Patient ALTER PatientId
   ADD GENERATED ALWAYS AS IDENTITY (START WITH 1);

For the existing patient table I have a total of 5 records. (patientId 1 to 5)
When I insert a new record after the identity setup, it will throw an error like:

more than one owned sequence found

Even after resetting the identity column, I still get the same error.

ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

Let me know if you have any solutions.

Rapture

Was patientid defined as serial before you changed it? – Lowermost

yes It was previously defined as serial – Rapture

Answer 1

Update: This bug has been fixed in PostgreSQL v12 with commit 19781729f78.
The rest of the answer is relevant for older versions.

A serial column has a sequence that is owned by the column and a DEFAULT value that gets the net sequence value.

If you try to change that column into an identity column, you'll get an error that there is already a default value for the column.

Now you must have dropped the default value, but not the sequence that belongs to the serial column. Then when you converted the column into an identity column, a second sequence owned by the column was created.

Now when you try to insert a row, PostgreSQL tries to find and use the sequence owned by the column, but there are two, hence the error message.

I'd argue that this is a bug in PostgreSQL: in my opinion, it should either have repurposed the existing sequence for the identity column or given you an error that there is already a sequence owned by the column, and you should drop it. I'll try to get this bug fixed.

Meanwhile, you should manually drop the sequence left behind from the serial column. Run the following query:

SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

That should give you the name of the sequence left behind from the serial column. Drop it, and the identity column should behave as desired.

Glowworm

As someone totally new to postgres, I was more than a little distressed to see the PK field listed twice (!!!!) when scripting a table to screen in pgAdmin. Then to discover at the same time Uber's blog pointing out a bug involving dead tuples... I have to ask... How many bugs are there ahead of me? I'm excited to use Postgres going forward, but today's experience has certainly dampened my enthusiasm. That said, THANK YOU for explaining the cause and solution. I'm now able to see my PK field listed (how crazy is that?) just... once. – Tessy

Well, bugs happen everywhere, and this here is merely an inconvenience (which is the reason why it was only fixed in v12 and not backpatched). The Uber story is a different affair. They badmouthed PostgreSQL without disclosing details about their workload. Markus wrote an interesting analysis where he tried to guess what was going on, and it seems to be a combination of a) abusing a relational database as a key-value store and b) running lots of updates, which is PostgreSQL's weakest point. – Glowworm

Yeah, I watched this and by no means did I mean to legitimize all the things Uber had said. It was just that the combination of finding a bug on my own and running across reference to the Uber bug all in the period of 30 minutes did give me some shivers. I'm still very excited to press on with Postgres. – Tessy

Well, you found a bug that has been fixed. That tends to give me a good feeling. – Glowworm

In humor I'd respond that my customers feel even better when they don't find lots of bugs of recent provenance, fixed or otherwise. But in seriousness I'd apologize, as I've probably come across as fault-finding in this exchange. I don't ever want to appear ungrateful for this awesome OSS tool. Just started watching this and am realizing you're one of the many I owe this gratitude to, most especially during this time of learning. Thank you for your contributions. – Tessy

I thought it's pgAdmin4 just showing some garbage. Glad I searched the symptom before any issues in production :) – Ubald

Thanks Laurenz, that's exactly that happens to me. You can complete your answer putting a last line: DROP SEQUENCE index_name; – Parody

@Parody I didn't add the DROP SEQUENCE statement because the name of the sequence will vary. But the last sentence of my answer tells you to drop the sequence, doesn't it? – Glowworm

I still think it should have been backpatched. Also, in pgadmin there is the suggestion you can get rid of one, which is a lot easier than running a convoluted query. But pgadmin will not let you save after dropping one of the 2 (apparent) identity columns. And PR also has value to consider. (running 11.12, so not that out of date) – Bombycid

Continuing from above: The query induced me to drop the oldest sequence. Then I tried to insert a row omitting the id, which, obviously, should be added from the sequence. I increased the start to the first avalailable id value. I got an error saying id 1 was already used, then id 2, and so all the way to get to the correct id. Obviously the cached value was not set to a useful value. This says again, as far as I'm concerned: backport the fix! – Bombycid

@Bombycid I hear you. But you'll have to write to the pgsql-hacker list to get Peter Eisentraut or another committer involved. It would be best to wait until 14.2 or so, to give people confidence that a backpatch is not too risky. – Glowworm

@LaurenzAlbe Thanks for your advice and lead! – Bombycid

Answer 2

This is not an answer -- apologies, but this allows me to show, with a vivid image, the crazy behavior that I (unintentionally) uncovered this morning...

All I had to do was this:

alter TABLE db.generic_items alter column generic_item_id drop default;
alter TABLE db.generic_items alter column generic_item_id add generated by default as identity;

and now when scripting the table to SQL I get (abbreviated):

CREATE TABLE db.generic_items
(
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_generic_items PRIMARY KEY (generic_item_id),
)

I am thankful for the answer posted above, by Laurenz Albe! As he explains, just delete the sequence that was used for the serial default, and this craziness goes away and the table looks normal again.

Tessy

Same here, thanks for writing it's the same problem, this was not obvious at first :) – Coachwhip

Answer 3

Again, this is NOT AN ANSWER, but commenting did not let me add enough text. Apology. Continues from my earlier comment(s). This is what I executed and it shows, imo, that the manual fix is not sufficient, and with large tables, the repetitive trick I used (see below) would be impractical and potentially wrong because adopting an id belonging to a deleted row.

-- pls disregard the absence of 2 id rows, this is the final situation    
\d vaste_data.studie_type
                                  Table "vaste_data.studie_type"
     Column |         Type          | Collation | Nullable |             Default
    --------+-----------------------+-----------+----------+----------------------------------
     id     | integer               |           | not null | generated by default as identity
     naam   | character varying(25) |           | not null |
    Indexes:
        "pk_tstudytype_tstudytype_id" PRIMARY KEY, btree (id)
    Referenced by:
        TABLE "stuwadoors" CONSTRAINT "fk_t_stuwadoors_t_studytype" FOREIGN KEY (study_type_id) REFERENCES vaste_data.studie_type(id)
        TABLE "psux" CONSTRAINT "study_studytype_fk" FOREIGN KEY (studie_type_id) FOREIGN KEY (studie_type_id) REFERENCES vaste_data.studie_type(id)
    
    alter table vaste_data.studie_type alter column id drop default;
    ALTER TABLE
    alter table vaste_data.studie_type alter column id add generated by default as identity;
    ALTER TABLE
    -- I chose to show both sequences so I could try to drop either one.
    SELECT d.objid::regclass
    FROM pg_depend AS d
       JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                                 d.refobjsubid = a.attnum
    WHERE d.classid = 'pg_class'::regclass
      AND d.refclassid = 'pg_class'::regclass
      AND a.attname = 'id'
      AND d.refobjid = 'vaste_data.studie_type'::regclass;
                      objid
    -----------------------------------------
     vaste_data.studie_type_id_seq
     vaste_data.tstudytype_tstudytype_id_seq
    (2 rows)
    
    drop sequence vaste_data.studie_type_id_seq;
    ERROR:  cannot drop sequence vaste_data.studie_type_id_seq because column id of table vaste_data.studie_type requires it
    HINT:  You can drop column id of table vaste_data.studie_type instead.
    
    \d vaste_data.studie_type_id_seq
                   Sequence "vaste_data.studie_type_id_seq"
      Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
    ---------+-------+---------+------------+-----------+---------+-------
     integer |     1 |       1 | 2147483647 |         1 | no      |     1
    Sequence for identity column: vaste_data.studie_type.id
    
    alter sequence vaste_data.studie_type_id_seq start 6;
    ALTER SEQUENCE
    drop sequence vaste_data.tstudytype_tstudytype_id_seq;
    DROP SEQUENCE
    insert into vaste_data.studie_type (naam) values('Overige leiding');
    ERROR:  duplicate key value violates unique constraint "pk_tstudytype_tstudytype_id"
    DETAIL:  Key (id)=(1) already exists.
    ...
    ERROR:  duplicate key value violates unique constraint "pk_tstudytype_tstudytype_id"
    DETAIL:  Key (id)=(5) already exists.
    insert into vaste_data.studie_type (naam) values('Overige leiding');
    INSERT 0 1

Bombycid