gitea on postgres resets user_id_seq and login_source_it_seq to 2 and 1 respectively #610

New Issue

GiteaMirror · 2025-11-02T03:29:54-06:00

GiteaMirror commented

2025-11-02 03:29:54 -06:00

Originally created by @kubatyszko on GitHub (Apr 4, 2017).

Gitea version (or commit ref): master
Git version:
Operating system:
Database (use [x]):
- PostgreSQL
- MySQL
- MSSQL
- SQLite
Can you reproduce the bug at https://try.gitea.io:
- Yes (provide example URL)
- No
- Not relevant
Log gist:

My users reported error 500 happening on some actions (login of a newly added user), and after repeating it a few times the problem went away.

It turns out that at least the user_id_seq gets reset to a value that's smaller than the maximum value of id in the "user" table, so adding user to the "user" table fails with primary key violation, but once the sequence advances far enough it runs successfully.

you can clearly see it here:

maximum user_id - next value would have been 20.

gitea=# select id from "user" order by id desc limit 5;
 id
----
 19
 18
 17
 16
 15
(5 rows)

somehow the sequence thinks the current value should be 12 (with 13 being nextval)

gitea=# select last_value from user_id_seq;
 last_value
------------
         12
(1 row)

Initially I suspected some issue with postgresql's database consistency after restarting, but I traced it more closely today, and it seems to be fine for most sequences (I checked a few) - with the exception of user_id_seq and login_source_id_seq.

As a test, I ran nextval('user_id_seq') a few times to advance it to 19 (thus having the upcoming nextval be 20). Now checking last_value returns 19 - that is correct.

After restarting database, this is what I get:

gitea=# select last_value from user_id_seq;
 last_value
------------
          2
(1 row)

^^^ This got reset to 2 (and it always resets to 2).

gitea=# select max(id) from "user";
 max
-----
  19
(1 row)

NOW, with more debugging on database side (I started logging statements):

LOCATION:  elog_node_display, print.c:84
STATEMENT:  CREATE SEQUENCE user_id_seq
            START WITH 1
            INCREMENT BY 1
            NO MINVALUE
            NO MAXVALUE
            CACHE 1;
ERROR:  42P07: relation "user_id_seq" already exists
LOCATION:  heap_create_with_catalog, heap.c:1067
STATEMENT:  CREATE SEQUENCE user_id_seq
            START WITH 1
            INCREMENT BY 1
            NO MINVALUE
            NO MAXVALUE
            CACHE 1;
LOG:  00000: statement: ALTER TABLE user_id_seq OWNER TO gitea;

Clearly, gitea at startup tries to recreate all the tables and objects, it fails with "already exists" error - that's expected.
But then, there's something that resets the user_id_seq to 2 (as I see after restart):

LOCATION:  elog_node_display, print.c:84
STATEMENT:  ALTER TABLE ONLY "user" ALTER COLUMN id SET DEFAULT nextval('user_id_seq'::regclass);
LOG:  00000: statement: SELECT pg_catalog.setval('user_id_seq', 2, true);

I see the same behavior on the login_source_seq_id sequence (that one resets to 1)...

My setup always restarts postgres and gitea together, to isolate the problem, I managed to keep the postgres running (have the user_id_seq be a large value) and restart only gitea - the sequence got reset back to 2...

Originally created by @kubatyszko on GitHub (Apr 4, 2017). - Gitea version (or commit ref): master - Git version: - Operating system: - Database (use `[x]`): - [x] PostgreSQL - [ ] MySQL - [ ] MSSQL - [ ] SQLite - Can you reproduce the bug at https://try.gitea.io: - [ ] Yes (provide example URL) - [ ] No - [x] Not relevant - Log gist: My users reported error 500 happening on some actions (login of a newly added user), and after repeating it a few times the problem went away. It turns out that at least the user_id_seq gets reset to a value that's smaller than the maximum value of id in the "user" table, so adding user to the "user" table fails with primary key violation, but once the sequence advances far enough it runs successfully. you can clearly see it here: maximum user_id - next value would have been 20. ``` gitea=# select id from "user" order by id desc limit 5; id ---- 19 18 17 16 15 (5 rows) ``` somehow the sequence thinks the current value should be 12 (with 13 being nextval) ``` gitea=# select last_value from user_id_seq; last_value ------------ 12 (1 row) ``` Initially I suspected some issue with postgresql's database consistency after restarting, but I traced it more closely today, and it seems to be fine for most sequences (I checked a few) - with the exception of user_id_seq and login_source_id_seq. As a test, I ran nextval('user_id_seq') a few times to advance it to 19 (thus having the upcoming nextval be 20). Now checking last_value returns 19 - that is correct. After restarting database, this is what I get: ``` gitea=# select last_value from user_id_seq; last_value ------------ 2 (1 row) ``` ^^^ This got reset to 2 (and it always resets to 2). ``` gitea=# select max(id) from "user"; max ----- 19 (1 row) ``` NOW, with more debugging on database side (I started logging statements): ``` LOCATION: elog_node_display, print.c:84 STATEMENT: CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ERROR: 42P07: relation "user_id_seq" already exists LOCATION: heap_create_with_catalog, heap.c:1067 STATEMENT: CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; LOG: 00000: statement: ALTER TABLE user_id_seq OWNER TO gitea; ``` Clearly, gitea at startup tries to recreate all the tables and objects, it fails with "already exists" error - that's expected. But then, there's something that resets the user_id_seq to 2 (as I see after restart): ``` LOCATION: elog_node_display, print.c:84 STATEMENT: ALTER TABLE ONLY "user" ALTER COLUMN id SET DEFAULT nextval('user_id_seq'::regclass); LOG: 00000: statement: SELECT pg_catalog.setval('user_id_seq', 2, true); ``` I see the same behavior on the login_source_seq_id sequence (that one resets to 1)... My setup always restarts postgres and gitea together, to isolate the problem, I managed to keep the postgres running (have the user_id_seq be a large value) and restart only gitea - the sequence got reset back to 2...

GiteaMirror added the issue/not-a-bug label 2025-11-02 03:29:54 -06:00

GiteaMirror closed this issue

2025-11-02 03:29:54 -06:00

GiteaMirror commented

2025-11-02 03:29:55 -06:00

@lunny commented on GitHub (Apr 4, 2017):

What's the problem is?

@lunny commented on GitHub (Apr 4, 2017): What's the problem is?

GiteaMirror commented

2025-11-02 03:29:55 -06:00

@kubatyszko commented on GitHub (Apr 4, 2017):

ignore it, it wasnt gitea related