No correct sequences after re-importing database from SQLite3 to PostgreSQL

[blackandred]

• Gitea version (or commit ref): 1.12.3
• Git version: 2.24.3
• Operating system: Alpine (Docker)
• 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:

2020/08/17 19:20:27 ...ion/action/action.go:180:NotifyCreateRepository() [E] notify watchers '1/106': insert new actioner: pq: duplicate key value violates unique constraint "action_pkey"
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password"
, "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admi
n", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_membe
rs", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 1.110404ms
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT "id", "owner_id", "owner_name", "lower_name", "name", "description", "website", "original_service_type", "original_url", "default_branch", "num_watches", "num
_stars", "num_forks", "num_issues", "num_closed_issues", "num_pulls", "num_closed_pulls", "num_milestones", "num_closed_milestones", "is_private", "is_empty", "is_archived", "is_mirror", "status", "is_fork", "fork_id", "is_template", "tem
plate_id", "size", "is_fsck_enabled", "close_issues_via_commit_in_any_branch", "topics", "avatar", "created_unix", "updated_unix" FROM "repository" WHERE "owner_id"=$1 AND "lower_name"=$2 LIMIT 1 [1 testowe] - 1.134095ms
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT "id", "repo_id", "user_id", "mode" FROM "collaboration" WHERE "repo_id"=$1 AND "user_id"=$2 LIMIT 1 [106 1] - 644.65µs
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT "id", "repo_id", "type", "config", "created_unix" FROM "repo_unit" WHERE (repo_id = $1) [106] - 608.982µs
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT count(*) FROM "release" WHERE repo_id=$1 AND is_draft=$2 [106 false] - 640.102µs
2020/08/17 19:20:27 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM "user" INNER JOIN "team_user" ON "team_user".org_id="user".id INNER JOIN "team" ON "team".id="team_user".team_id WHERE ("team_user".uid=$1) AND ("team"
2020/08/17 19:20:34 ...o/xorm/session_tx.go:46:Rollback() [I] [SQL] ROLL BACK [] - 250.102µs
2020/08/17 19:20:34 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password"
, "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admi
n", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_membe
rs", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 1.110657ms
2020/08/17 19:20:34 ...m.io/xorm/core/tx.go:36:BeginTx() [I] [SQL] BEGIN TRANSACTION [] - 246.075µs
2020/08/17 19:20:34 ...m.io/xorm/core/tx.go:157:QueryContext() [I] [SQL] SELECT "id", "owner_id", "owner_name", "lower_name", "name", "description", "website", "original_service_type", "original_url", "default_branch", "num_watches", "num
_stars", "num_forks", "num_issues", "num_closed_issues", "num_pulls", "num_closed_pulls", "num_milestones", "num_closed_milestones", "is_private", "is_empty", "is_archived", "is_mirror", "status", "is_fork", "fork_id", "is_template", "tem
plate_id", "size", "is_fsck_enabled", "close_issues_via_commit_in_any_branch", "topics", "avatar", "created_unix", "updated_unix" FROM "repository" WHERE "id"=$1 AND "owner_id"=$2 LIMIT 1 [107 1] - 987.512µs
2020/08/17 19:20:34 ...o/xorm/session_tx.go:46:Rollback() [I] [SQL] ROLL BACK [] - 302.06µs
2020/08/17 19:20:34 ...sitory/repository.go:23:CreateRepository() [E] Rollback deleteRepository: repository does not exist [id: 107, uid: 1, owner_name: , name: ]
2020/08/17 19:20:34 routers/repo/repo.go:176:handleCreateError() [E] CreatePost: watchRepo: pq: duplicate key value violates unique constraint "watch_pkey"

Description

I upgraded from Gogs 0.9.x to Gitea 1.12.3 by creating schema in Gitea 1.0.0, then copying only data from Gogs. Then I run through upgrade process minor release-by-minor-release from Gitea 1.0.0 to 1.12.3

Now whatever I do I have 500 error caused by incorrectly set up sequences. It seems that the sequences are even not correctly set up in the schema, probably not linked to the columns as resetting sequences does not work.

Tried already:

• resetdb (REINDEX table)
• https://wiki.postgresql.org/wiki/Fixing_Sequences

No luck.

[blackandred]

Finally resolved by resetting sequences with help of #740 (comment)
The query from official PostgreSQL wiki didn't work. I went through tables one-by-one and reset the sequences.

[zeripath]

I recall another problem with Postgres Sequences in the past and had to add the below to our test_fixtures code to get postgres to sort out the sequences:

gitea/models/test_fixtures.go

Lines 70 to 86 in 61a1729

	if x.Dialect().URI().DBType == schemas.POSTGRES {
	results, err := x.QueryString(`SELECT 'SELECT SETVAL(' ||
	quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
	', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
	quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
	FROM pg_class AS S,
	pg_depend AS D,
	pg_class AS T,
	pg_attribute AS C,
	pg_tables AS PGT
	WHERE S.relkind = 'S'
	AND S.oid = D.objid
	AND D.refobjid = T.oid
	AND D.refobjid = C.attrelid
	AND D.refobjsubid = C.attnum
	AND T.relname = PGT.tablename
	ORDER BY S.relname;`)

(mentioned here in case others find this issue.)