Alter entries of Primary key in table to match number of rows from COUNT Postgresql

We have the definition of the table below

CREATE TABLE Log
(
    LogId             serial      not null,
    JobId             integer     not null,
    Time              timestamp   without time zone,
    LogText           text        not null,
    primary key (LogId)
);
create index log_name_idx on Log (JobId);

The LogId had reached its max value for serial (2bilion) We had to manually delete older entries and vacuum the table because we were getting the following error from the PostgreSQL logs

##ERROR:  integer out of range
##STATEMENT:  INSERT INTO Log (JobId, Time, LogText) VALUES .....

The integer is referring the LogId as it had reached max positive integer/serial4 value of 2,147,483,647.

Now we have the following problem: The latest entry of the logid column, which also is the primary key is at max (2,147,483,647). We would like to recreate the entries of the primary key column so that is matches the number of rows of this table. That number is 953,811,856. Still big, but we could automate the process of deleting and vacuuming the table if this is resolved.

As after the delete and vacuum, almost half of the entries were removed along with their matching primary key, we would like to "re-align" if you will, the primary key with the rest of the remaining values of the table, meaning that we want the oldest remaining entry on the table to have the number 1 on their LogID field and increment that to the latest row entry, reaching 953811856.

Note that the LogId is serial datatype, and I don't know why the error we get from the logs is referencing an integer. Best guest because integer and serial4 have same max value.

We tried to alter the serial sequence to 1 on our test environment, and even if this works and new entries were made to the table, that will eventually create duplicate entries in the primary key, as we haven't completely deleted all prior entries from the LogId column.

 ** bacula=> \d log Table "public.log" 
Column | Type | Modifiers logid | integer | not null default nextval('log_logid_seq'::regclass) jobid | integer | not null time | timestamp without time zone | logtext | text | not null Indexes: "log_pkey" PRIMARY KEY, btree (logid) "log_name_idx" btree (jobid) **

i dont see FK's

Note that the postgres version is 9.2 –

Solution

Switch to bigserial/bigint - that'll get your limit up to 9223372036854775807. Demo:

alter table log alter column LogId type bigint;
select pg_get_serial_sequence('public.log','logid');
alter sequence public.log_logid_seq as bigint;

Better yet, switch to bigint generated by default as identity: demo2

alter table log alter column LogId type bigint;
alter table log alter column LogId drop default;
alter table log alter column LogId 
  add generated by default as identity;

In contrast to changing the type entirely or adding another column to build a multi-column primary key, nice thing about this here is that all foreign key columns pointing at this table remain intact. You can look them up and switch to bigint in another step.

In version 9.2 (detail added to context later) alter sequence...set wasn't implemented, but since sequences were all bigint by default, it's enough to just alter the column type. Demo in 9.3:

alter table log alter column LogId type bigint;
select setval(pg_get_serial_sequence('public.log','logid'),
              (select max(LogId)+1 from Log));

And that's it. The sequence will just continue providing new id's without any further intervention.

Best guess because integer and serial4 have same max value.

Specifying type serial in table definition just means int that you want Postgres to create and maintain a sequence for - it's not really a different type, so your guess was exactly right. It's a similar situation for bigserial: same thing but with bigint.

we would like to "re-align"

The idea to periodically clean up, vacuum, compact, re-number and rewind the sequence sounds risky because it'll have to cascade over references everywhere. Plus, it's a lot of work and a lot of stress on the db, none of which you really need. Any outside references or internal weak links (columns used to hold foreign keys but without a references constraint in place) would break after the re-numbering. Switching to bigint, you can keep all your identifiers and all references to them, intact.

Technically, you can actually double your mileage with both int and bigint (and smallint) if you instruct the underlying sequence to start from the real bottom of the range, -2147483648 instead of 1. Serial-type columns don't offer any sequence-configuring clauses in create table to allow that on setup, identity columns do:

create table test(
    id bigint primary key 
        generated by default as identity (minvalue -9223372036854775807
                                          start with -9223372036854775807)
);

And both indentity and serial allow the sequence to be altered to do that later, after creating. Expect to raise some eyebrows if you try to show it to someone or tell anyone it's the new normal they have to work with.