Converting from int to bigint and from serial to identity

Leave a Comment / By /

Yesterday, a Facebook memory reminded me of an old issue with FreshPorts: serial values which are about to overflow.

This is where you may be thinking? serial? In this day?

You might also think, why serial? Why not UUID.

Keep thinking about that. Some of the tables we’re talking about were designed 20+ years ago.

In this post:

  1. FreeBSD 14.2
  2. PostgreSQL 18beta1 – for testing the initial changes
  3. PostgreSQL 16.9 – on test, stage, and prod

The original post went something like this:

Database geek stuff. Please move along.
I noticed the integer nextval(‘page_load_detail_id_seq’::regclass) of a table has a current value of:
452,687,639

2,147,483,647 is the max value possible.

How long did it take me to get to that value?

The max number of rows for any given date is 1,358,680, but the next max is 631,517.

At 1.4 millions rows a day, I will hit maxint in about 1,694,796,008/1,400,000 = 1,210 days.

Or about Sept 12 2023

That expected date was about 2 years ago as I write this. Let’s go back into the database for current values.

freshports.org=> select nextval(‘page_load_detail_id_seq’::regclass);
nextval
———–
665,298,285
(1 row)

I added the commas to the output. At this rate, I might be good for a few more years at least.

However, it’s time to make the change.

I searched, I read, and I found this function from https://stackoverflow.com/questions/77907016/migration-to-identity-columns-on-postgresql-but-without-privileges-to-modify-pg

I modify this function later, see below.

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(table_name text, column_name text)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    -- will hold the max value found in the sequence
    max_id integer;
    
    -- sequence name
    seqence_name text := table_name || '_' || column_name || '_seq';
BEGIN
  
    EXECUTE format('LOCK %I;', table_name);
    
    -- get current maximum id
    EXECUTE format (
            'SELECT COALESCE(MAX(%I), 0) FROM %I;',
             column_name,
             table_name
    ) INTO max_id;
    
    -- drop the sequence
    EXECUTE format('DROP SEQUENCE IF EXISTS %I CASCADE;', seqence_name);
    
    -- alter table so it now uses an identity, instead of the previously seqence
    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)',
            table_name,
            column_name,
            max_id + 1
    );
    
END;
$$;

A bit of caution

I carried these changes out on isolated systems. You may need to consider the affect on a active host if you do what I did. I had the luxury of knowing no other users were accessing the system while I did this.

The first change

In this section, I convert the column from serial to identity.

freshports.page_load_detail=# begin;
BEGIN
freshports.page_load_detail=*# select upgrade_serial_to_identity('page_load_detail', 'id');
NOTICE:  drop cascades to default value for column id of table page_load_detail
 upgrade_serial_to_identity 
----------------------------
 
(1 row)

freshports.page_load_detail=*# \d page_load_detail
                                     Table "public.page_load_detail"
     Column     |          Type          | Collation | Nullable |                Default                 
----------------+------------------------+-----------+----------+----------------------------------------
 id             | integer                |           | not null | generated always as identity
 date           | date                   |           | not null | 'now'::text::date
 time           | time without time zone |           | not null | 'now'::text::time(6) without time zone
 page_name      | text                   |           | not null | 
 user_id        | integer                |           |          | 
 ip_address     | inet                   |           | not null | 
 full_url       | text                   |           | not null | 
 rendering_time | interval               |           | not null | 
Indexes:
    "page_load_detail_test_pkey" PRIMARY KEY, btree (id)
    "page_load_detail_date" btree (date)
    "page_load_ip_address" btree (ip_address)

freshports.page_load_detail=*# commit;
COMMIT

Then I noticed it was still integer. Serves me right for doing this while the grandchildren were here instead of thinking it through first. I’m only halfway done.

Changing the type:

freshports.page_load_detail=*# alter table page_load_detail alter column id type bigint;
ALTER TABLE
freshports.page_load_detail=*# \d page_load_detail
                                     Table "public.page_load_detail"
     Column     |          Type          | Collation | Nullable |                Default                 
----------------+------------------------+-----------+----------+----------------------------------------
 id             | bigint                 |           | not null | generated always as identity
 date           | date                   |           | not null | 'now'::text::date
 time           | time without time zone |           | not null | 'now'::text::time(6) without time zone
 page_name      | text                   |           | not null | 
 user_id        | integer                |           |          | 
 ip_address     | inet                   |           | not null | 
 full_url       | text                   |           | not null | 
 rendering_time | interval               |           | not null | 
Indexes:
    "page_load_detail_test_pkey" PRIMARY KEY, btree (id)
    "page_load_detail_date" btree (date)
    "page_load_ip_address" btree (ip_address)

freshports.page_load_detail=*# commit;
COMMIT

The modified function

Let’s add a change to BIGINT below. Let’s try this function instead. Notice this function has a different name to the one shown above.

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity_bigint(table_name text, column_name text)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    -- will hold the max value found in the sequence
    max_id integer;
    
    -- sequence name
    seqence_name text := table_name || '_' || column_name || '_seq';
BEGIN
  
    EXECUTE format('LOCK %I;', table_name);
    
    -- get current maximum id
    EXECUTE format (
            'SELECT COALESCE(MAX(%I), 0) FROM %I;',
             column_name,
             table_name
    ) INTO max_id;
    
    -- drop the sequence
    EXECUTE format('DROP SEQUENCE IF EXISTS %I CASCADE;', seqence_name);
    
    -- alter table so it now uses an identity, instead of the previously seqence
    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)',
            table_name,
            column_name,
            max_id + 1
    );

    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I type bigint',
            table_name,
            column_name
    );
    
END;
$$;

Let’s try it on this table, which is rather static and has not changed in many years. It doesn’t need bigint, but let’s try anyway.

freshports.page_load_detail=# \d system
                               Table "public.system"
   Column    |   Type   | Collation | Nullable |              Default               
-------------+----------+-----------+----------+------------------------------------
 id          | integer  |           | not null | nextval('system_id_seq'::regclass)
 name        | text     |           | not null | 
 time_adjust | interval |           | not null | '00:00:00'::interval
Indexes:
    "system_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "system_branch" CONSTRAINT "$1" FOREIGN KEY (system_id) REFERENCES system(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log" CONSTRAINT "$1" FOREIGN KEY (system_id) REFERENCES system(id) ON UPDATE CASCADE ON DELETE CASCADE

Let’s try it:

freshports.page_load_detail=# begin;
BEGIN
freshports.page_load_detail=*# select upgrade_serial_to_identity_bigint('system', 'id');
NOTICE:  drop cascades to default value for column id of table system
 upgrade_serial_to_identity_bigint 
-----------------------------------
 
(1 row)

freshports.page_load_detail=*# \d system
                            Table "public.system"
   Column    |   Type   | Collation | Nullable |           Default            
-------------+----------+-----------+----------+------------------------------
 id          | bigint   |           | not null | generated always as identity
 name        | text     |           | not null | 
 time_adjust | interval |           | not null | '00:00:00'::interval
Indexes:
    "system_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "system_branch" CONSTRAINT "$1" FOREIGN KEY (system_id) REFERENCES system(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log" CONSTRAINT "$1" FOREIGN KEY (system_id) REFERENCES system(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.page_load_detail=*# commit;
COMMIT
freshports.page_load_detail=#

That looks good.

Testing the results

Let’s add another value to that table:

freshports.page_load_detail=# begin;
BEGIN
freshports.page_load_detail=*# select * from system;
 id |  name   | time_adjust 
----+---------+-------------
  1 | FreeBSD | 00:00:00
(1 row)

freshports.page_load_detail=*# insert into system (name) values ('testing');
INSERT 0 1
freshports.page_load_detail=*# select * from system;
 id |  name   | time_adjust 
----+---------+-------------
  1 | FreeBSD | 00:00:00
  2 | testing | 00:00:00
(2 rows)

freshports.page_load_detail=*# rollback;
ROLLBACK
freshports.page_load_detail=#

Yes, that seems to work.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Related Posts

Leave a Comment

Scroll to Top