postgres ERROR: duplicate key value violates unique constraint "email_hash_pkey"

[ntlspck]

• Gitea version (or commit ref): v1.13.2 built with GNU Make 4.3, go1.15.7
• Git version: 2.26.2
• Operating system: Raspbian GNU/Linux 10, docker kunde21/gitea-arm v1.13.2-armv7
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

Description

Using gitea 1.13.2 and postgres 13.0 I still have the following reoccuring postgres errors:

2021-02-16 21:31:14.345 UTC [20446] ERROR: duplicate key value violates unique constraint "email_hash_pkey"
2021-02-16 21:31:14.345 UTC [20446] DETAIL: Key (hash)=(xxxxxxxxxx) already exists.
2021-02-16 21:31:14.345 UTC [20446] STATEMENT: INSERT INTO "email_hash" ("hash","email") VALUES ($1,$2)

I understand that this should be fixed in 1.13.0 via #12577
...

Screenshots

[zeripath]

Your sequences have somehow become out of sync. If I had to guess this could be because the sequences are not in the same schema as the rest of Gitea.

There have been multiple recent issues (closed) on this issue - can you look at those for how to figure out how to update them.

It would be helpful to know if you're running Gitea in a schema in order to help us figure out how this happened. Or if you used a backup and restore method, or something else

[joseluisq]

I'm using the latest Gitea 1.13.4 and Postgres 13 and I got something similar but for topics.
I can add existing topics but not new ones.

In my case the problem happened after an upgrade via my Docker service. I'm just using a Gitea database with the default public schema.

When I try to add a new topic Gitea complains with a status 500 for https://server.com/my-org/my-repo/topics and a {"message":"Save topics failed."} response.

The log with trace enabled:

2021/03/12 22:03:59 Started POST /my-org/my-repo/topics for 10.255.0.2
2021/03/12 22:03:59 ...s/context/context.go:332:func1() [D] Session ID: 1ab234cvy10c157f
2021/03/12 22:03:59 ...s/context/context.go:333:func1() [D] CSRF Token: oabCDEFGHIjkHlmn0PQ1234Z3D06MVYxNTU4MzUoCzU9WPWzNiyXMh
2021/03/12 22:03:59 models/repo.go:464:getUnits() [T] repo.Units: [0xc008973110 0xc008973170 0xc0089731d0 0xc008973230 0xc008973290 0xc0089732f0]
2021/03/12 22:03:59 ...s/repo_permission.go:154:func1() [T] Permission Loaded for 1:joseluisq in 227:my-org/my-repo:
	Permissions: AccessMode: 4:owner, 6 Units, 0 UnitsMode(s): [ 
	Units[0]: ID: 1183 RepoID: 227 Type: 1:UnitTypeCode Config: {}
	Units[1]: ID: 1184 RepoID: 227 Type: 2:UnitTypeIssues Config: {"EnableTimetracker":true,"AllowOnlyContributorsToTrackTime":true,"EnableDependencies":true}
	Units[2]: ID: 1185 RepoID: 227 Type: 3:UnitTypePullRequests Config: {"IgnoreWhitespaceConflicts":false,"AllowMerge":true,"AllowRebase":true,"AllowRebaseMerge":true,"AllowSquash":true}
	Units[3]: ID: 1186 RepoID: 227 Type: 4:UnitTypeReleases Config: {}
	Units[4]: ID: 1187 RepoID: 227 Type: 5:UnitTypeWiki Config: {}
	Units[5]: ID: 1188 RepoID: 227 Type: 8:UnitTypeProjects Config: {} ]
2021/03/12 22:04:00 ...outers/repo/topic.go:50:TopicsPost() [E] SaveTopics failed: pq: duplicate key value violates unique constraint "topic_pkey"
2021/03/12 22:04:00 Completed POST /my-org/my-repo/topics 500 Internal Server Error in 20.337057ms

2021/03/12 22:04:00 Started GET /api/v1/topics/search?q=&_=1615586618383 for 10.255.0.2
2021/03/12 22:04:00 ...s/context/context.go:332:func1() [D] Session ID: 1ab234cvy10c157f
2021/03/12 22:04:00 ...s/context/context.go:333:func1() [D] CSRF Token: oabCDEFGHIjkHlmn0PQ1234Z3D06MVYxNTU4MzUoCzU9WPWzNiyXMh
2021/03/12 22:04:00 Completed GET /api/v1/topics/search?q=&_=1615586618383 200 OK in 5.413019ms

[zeripath]

Your postgres sequences are out of kilter.

Are you using a schema? Were the sequences in the public schema?

You need to update your sequences:

#14692 (comment)

[joseluisq]

Are you using a schema?

Yes, I have a public schema in my gitea database.

Were the sequences in the public schema?

Inside the same public schema.

You need to update your sequences:

#14692 (comment)

I did it but doesn't help since when I execute the setintval queries I get an error on the last one:

SQL Error [42P01]: ERROR: relation "tmp_recreate__topic" does not exist

[joseluisq]

I did try to recreate the tables but doesn't help.

$ gitea doctor recreate-table
2021/03/13 20:19:22 ...dules/setting/git.go:91:newGit() [I] Git Version: 2.26.2, Wire Protocol Version 2 Enabled
2021/03/13 20:19:22 ...rm/session_schema.go:25:Ping() [I] PING DATABASE postgres
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: user for Bean: User
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: public_key for Bean: PublicKey
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: access_token for Bean: AccessToken
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: repository for Bean: Repository
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: deploy_key for Bean: DeployKey
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: collaboration for Bean: Collaboration
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: access for Bean: Access
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: upload for Bean: Upload
2021/03/13 20:19:22 ...ations/migrations.go:364:func1() [I] Recreating Table: watch for Bean: Watch
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: star for Bean: Star
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: follow for Bean: Follow
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: action for Bean: Action
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: issue for Bean: Issue
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: pull_request for Bean: PullRequest
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: comment for Bean: Comment
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: attachment for Bean: Attachment
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: label for Bean: Label
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: issue_label for Bean: IssueLabel
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: milestone for Bean: Milestone
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: mirror for Bean: Mirror
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: release for Bean: Release
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: login_source for Bean: LoginSource
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: webhook for Bean: Webhook
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: hook_task for Bean: HookTask
2021/03/13 20:19:23 ...ations/migrations.go:364:func1() [I] Recreating Table: team for Bean: Team
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: org_user for Bean: OrgUser
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: team_user for Bean: TeamUser
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: team_repo for Bean: TeamRepo
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: notice for Bean: Notice
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: email_address for Bean: EmailAddress
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: notification for Bean: Notification
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: issue_user for Bean: IssueUser
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: lfs_meta_object for Bean: LFSMetaObject
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: two_factor for Bean: TwoFactor
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: gpg_key for Bean: GPGKey
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: gpg_key_import for Bean: GPGKeyImport
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: repo_unit for Bean: RepoUnit
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: repo_redirect for Bean: RepoRedirect
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: external_login_user for Bean: ExternalLoginUser
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: protected_branch for Bean: ProtectedBranch
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: user_open_id for Bean: UserOpenID
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: issue_watch for Bean: IssueWatch
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: commit_status for Bean: CommitStatus
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: stopwatch for Bean: Stopwatch
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: tracked_time for Bean: TrackedTime
2021/03/13 20:19:24 ...ations/migrations.go:364:func1() [I] Recreating Table: deleted_branch for Bean: DeletedBranch
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: repo_indexer_status for Bean: RepoIndexerStatus
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: issue_dependency for Bean: IssueDependency
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: lfs_lock for Bean: LFSLock
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: reaction for Bean: Reaction
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: issue_assignees for Bean: IssueAssignees
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: u2f_registration for Bean: U2FRegistration
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: team_unit for Bean: TeamUnit
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: review for Bean: Review
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: oauth2_application for Bean: OAuth2Application
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: oauth2_authorization_code for Bean: OAuth2AuthorizationCode
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: oauth2_grant for Bean: OAuth2Grant
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: task for Bean: Task
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: language_stat for Bean: LanguageStat
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: email_hash for Bean: EmailHash
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: project for Bean: Project
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: project_board for Bean: ProjectBoard
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: project_issue for Bean: ProjectIssue
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: topic for Bean: Topic
2021/03/13 20:19:25 ...ations/migrations.go:364:func1() [I] Recreating Table: repo_topic for Bean: RepoTopic
2021/03/13 20:19:25 ...rm/session_schema.go:360:Sync2() [W] Table user Column max_repo_creation db default is '-1', struct default is -1

[joseluisq]

SQL Error [42P01]: ERROR: relation "tmp_recreate__topic" does not exist

Since this error is related to the topic I assume is the reason why is not working.

[zeripath]

agh! don't understand what is happening in that listbut there should not be a tmp_recreate__topic. I've adjusted that person's comment to just use topic.

Try it again!

[joseluisq]

agh! don't understand what is happening in that listbut there should not be a tmp_recreate__topic. I've adjusted that person's comment to just use topic.

Try it again!

> SELECT setval('topic_id_seq', (SELECT MAX(id) FROM topic));
-- SQL Error [42P01]: ERROR: relation "topic_id_seq" does not exist

[zeripath]

Hmm. There really should be a primary key on that table: #12639

gitea/models/topic.go

Lines 27 to 33 in 71a2adb

	type Topic struct {
	ID int64 `xorm:"pk autoincr"`
	Name string `xorm:"UNIQUE VARCHAR(25)"`
	RepoCount int
	CreatedUnix timeutil.TimeStamp `xorm:"INDEX created"`
	UpdatedUnix timeutil.TimeStamp `xorm:"INDEX updated"`
	}

and the recreate-table command should have created one too:

gitea/models/migrations/migrations.go

Lines 376 to 400 in 71a2adb

	func recreateTable(sess *xorm.Session, bean interface{}) error {
	// TODO: This will not work if there are foreign keys
	tableName := sess.Engine().TableName(bean)
	tempTableName := fmt.Sprintf("tmp_recreate__%s", tableName)
	// We need to move the old table away and create a new one with the correct columns
	// We will need to do this in stages to prevent data loss
	//
	// First create the temporary table
	if err := sess.Table(tempTableName).CreateTable(bean); err != nil {
	log.Error("Unable to create table %s. Error: %v", tempTableName, err)
	return err
	}
	if err := sess.Table(tempTableName).CreateUniques(bean); err != nil {
	log.Error("Unable to create uniques for table %s. Error: %v", tempTableName, err)
	return err
	}
	if err := sess.Table(tempTableName).CreateIndexes(bean); err != nil {
	log.Error("Unable to create indexes for table %s. Error: %v", tempTableName, err)
	return err
	}

Could you go into your db and check?

[joseluisq]

Right now the DDL for topic table looks like:

CREATE TABLE public.topic (
	id bigserial NOT NULL,
	"name" varchar(25) NULL,
	repo_count int4 NULL,
	created_unix int8 NULL,
	updated_unix int8 NULL,
	CONSTRAINT topic_pkey PRIMARY KEY (id)
);
CREATE INDEX "IDX_topic_created_unix" ON public.topic USING btree (created_unix);
CREATE INDEX "IDX_topic_updated_unix" ON public.topic USING btree (updated_unix);
CREATE UNIQUE INDEX "UQE_topic_name" ON public.topic USING btree (name);

Is there something wrong with it?

[zeripath]

Well there should be an associated sequence on the topic primarily key...

[joseluisq]

Now I got after push some commits

021/03/15 00:41:33 ...odules/cron/tasks.go:103:func2() [E] CreateNotice: pq: duplicate key value violates unique constraint "notice_pkey"

[joseluisq]

Can you tell me how I can repair the db?
I was using inside the gitea app container:

su git
gitea doctor recreate-table

But it doesn't fix the problem. Or am I doing the command wrong?

[zeripath]

The issue is the sequences are out of kilter.

That means you need to go in to your postgres dB and fix them.

We don't have a command to do that for you because we haven't written one because we didn't expect this to be such a problem.

My only suspicion is that recreate table is causing the sequences to disappear or be set at 0 but you're not giving me any information to do that.

Could you tell me the what the values of your sequences are and if they exist?

[joseluisq]

The issue is the sequences are out of kilter.

That means you need to go in to your postgres dB and fix them.

We don't have a command to do that for you because we haven't written one because we didn't expect this to be such a problem.

That's great

My only suspicion is that recreate table is causing the sequences to disappear or be set at 0 but you're not giving me any information to do that.

Could you tell me the what the values of your sequences are and if they exist?

I have restored the database to the previous state.
Here the sequences that the database had until the beginning of my first comment.

Sequences in gitea.public

access_id_seq
access_token_id_seq
action_id_seq
attachment_id_seq
collaboration_id_seq
comment_id_seq
commit_status_id_seq
deleted_branch_id_seq
deploy_key_id_seq
email_address_id_seq
follow_id_seq
gpg_key_id_seq
hook_task_id_seq
issue_assignees_id_seq
issue_dependency_id_seq
issue_id_seq
issue_label_id_seq
issue_user_id_seq
issue_watch_id_seq
label_id_seq
language_stat_id_seq
lfs_lock_id_seq
lfs_meta_object_id_seq
login_source_id_seq
milestone_id_seq
mirror_id_seq
notice_id_seq
notification_id_seq
oauth2_application_id_seq
oauth2_authorization_code_id_seq
oauth2_grant_id_seq
org_user_id_seq
project_board_id_seq
project_id_seq
project_issue_id_seq
protected_branch_id_seq
public_key_id_seq
pull_request_id_seq
reaction_id_seq
release_id_seq
repo_indexer_status_id_seq
repo_redirect_id_seq
repo_unit_id_seq
repository_id_seq
review_id_seq
star_id_seq
stopwatch_id_seq
task_id_seq
team_id_seq
team_repo_id_seq
team_unit_id_seq
team_user_id_seq
tmp_recreate__topic_id_seq
tracked_time_id_seq
two_factor_id_seq
u2f_registration_id_seq
upload_id_seq
user_id_seq
user_open_id_id_seq
version_id_seq
watch_id_seq
webhook_id_seq

Note that the already mentioned topic_id_seq is missing.
Also there is a tmp_recreate__topic_id_seq that I have no idea why is there for.

Below the content of the tmp_recreate__topic_id_seq:

SELECT * FROM performance_schema.events_statements_summary_global_by_event_name AS t
ORDER BY t.COUNT_STAR DESC;

So please I would like to have the list of valid sequences and their queries in order to fix them manually because looks like the "repair" tool doesn't work for that purpose.