PostgreSQL 在宣告 serial 遇到的雷

Posted on by Clarence

來談談這幾天遇到的問題，我們原本有一個系統最近發現好像有點卡卡的，查詢了系統 Log 發現會有 out of range 的錯誤訊息

尋找問題

發現事情好像不大妙就開始著手查看，首先觀察到這個 log 發出的位置是資料庫模組，就查詢了發出問題的 table 欄位發現這個欄位是用 serial 宣告的。

這時候其實覺得很詭異理論上 serial 應該跟 out of range 沒什麼關係怎麼會有這個問題呢? 就查了一下這個欄位的 SEQUENCE 發現大事不妙 Maximum 竟然是 9223372036854775807 又仔細看了 SEQUENCE Type 是 bigint，table 欄位宣告的 Type 是 integer，就發現案情不單純

PostgreSQL Numeric Types

查了一下 PostgreSQL Numeric Types 的文件，可以發現目前定義的資料 Size

名稱資料長度
integer4 bytes
bigint8 bytes
serial4 bytes
bigserial8 bytes

發現 PostgresSQL 把 serial 的 SEQUENCE 設定錯誤了，這個問題其實滿嚴重的，今天假設我們這個資料是 id 欄位型態是 integer 如上表可以看到是 4 bytes，代表最大資料長度是 2147483647，而今天 sequence 如果超過 2147483647 還會繼續增加，這時候就會發生 out of range 的問題

竟然發現問題的原因就來討論修復的方法，目前想到的方法有

  1. id type 改成 bigint
  2. SEQUENCE type 改成 int

使用 PostgreSQL SQL 修改型態

有方法了就來執行它吧！

修改 Column Type

方法一：修改 id type 從 int -> bigint，執行 SQL 指令 ALTER TABLE users ALTER COLUMN id SET DATA TYPE bigint;

postgres=# ALTER TABLE users ALTER COLUMN id SET DATA TYPE bigint;
ALTER TABLE
postgres=# \d users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | bigint            |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |

SEQUENCE type 改成 int

方法二：修改 SEQUENCE type 從 int -> bigint，執行 SQL 指令 ALTER SEQUENCE users_id_seq AS bigint;

postgres=#  ALTER SEQUENCE users_id_seq AS bigint;
ALTER SEQUENCE
postgres=# \d users_id_seq
                        Sequence "public.users_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.users.id

測試不同 PostgreSQL SQL 版本

如上兩種方法都可以解決問題，問題解決了就來討論一下新版本 PostgreSQL 是否有修正這個問題吧！

使用 Docker 啟動 PostgreSQL 資料庫

為了快速啟動一個 PostgreSQL DB 與連線資料庫這邊使用 Docker + psql 來講解，首先我們用 Docker 啟動一個 PostgreSQL 12 的資料庫

  • 資料庫名稱：postgres
  • 資料庫帳號：postgres
  • 資料庫密碼：password
  • 資料庫 Port：5432
docker run -d \
    -p 5432:5432 \
    --name postgres \
    -e POSTGRES_PASSWORD=password \
    postgres:12

MAC 安裝 psql

brew install libpq

使用 psql 連線 

clarence~: psql -h 127.0.0.1 -U postgres -W
Password:
psql (12.3, server 12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

postgres=#

出現以上就代表成功連線拉！

創建表含有 SERIAL

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

使用 \d users 可以查看資料表是否有正常創建

                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |

測試插入資料

postgres=# insert into users(name) values('abc');
INSERT 0 1

查詢資料表的內容

postgres=# select * from users;
 id | name
----+------
  1 | abc
(1 row)

查詢 Sequence 詳細資料

postgres=# \d users_id_seq
                    Sequence "public.users_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.users.id

使用 PostgreSQL 12

這邊把整個在意的資料做一個連續的操作，這邊發現在 PostgreSQL 12 並沒有一開始說明的問題，那我們再來使用舊一點的版本試試看

psql -h 127.0.0.1 -U postgres -W
Password:
psql (12.3, server 12.4 (Debian 12.4-1.pgdg100+1))

postgres=# CREATE TABLE users(
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name VARCHAR NOT NULL
postgres(# );
CREATE TABLE
postgres=# \d users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

postgres=# \d users_id_seq
                    Sequence "public.users_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.users.id

使用 PostgreSQL 8

再來我們拿一個比較舊版的 PostgreSQL 8 來試試看，會發現問題出現了，明明使用的指令跟剛剛 12 一樣，不過卻會出現 Maximum 等於 9223372036854775807 

psql -h 127.0.0.1 -U postgres -W
Password:
psql (12.3, server 8.4.22)
Type "help" for help.

postgres=# CREATE TABLE users(
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name VARCHAR NOT NULL
postgres(# );
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

postgres=# \d users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

postgres=# \d users_id_seq
                        Sequence "public.users_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.users.id

使用 PostgreSQL 9

使用 PostgreSQL 9 再做一次發現，還是有這個問題

psql -h 127.0.0.1 -U postgres -W
Password:
psql (12.3, server 9.6.19)
Type "help" for help.

postgres=# CREATE TABLE users(
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name VARCHAR NOT NULL
postgres(# );
CREATE TABLE
postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

postgres=# \d users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

postgres=# \d users_id_seq
                        Sequence "public.users_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.users.id

使用 PostgreSQL 10

再來使用 PostgreSQL 10 發現問題已經修正了，如此可以知道這個問題在 PostgreSQL 10 有了修正

psql -h 127.0.0.1 -U postgres -W
Password:
psql (12.3, server 10.13 (Debian 10.13-1.pgdg90+1))
Type "help" for help.

postgres=# CREATE TABLE users(
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name VARCHAR NOT NULL
postgres(# );
CREATE TABLE
postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

postgres=# \d users
                                 Table "public.users"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

postgres=# \d users_id_seq
                    Sequence "public.users_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.users.id

結論

這個問題的發生不會因為 PostgreSQL 版本升級就修復，所以在舊版 PostgreSQL 創建表的話就要手動去修復問題，不然值長到超過 2147483647 就會發生問題了！

參考資料

Comments (2)