postgres12 throws duplicate key value violates unique constraint "email_hash_pkey" messages #5764

New Issue

GiteaMirror · 2025-11-02T06:35:16-06:00

GiteaMirror commented

2025-11-02 06:35:16 -06:00

Originally created by @ghost on GitHub (Jul 21, 2020).

Gitea version (or commit ref): 1.12.2
Operating system: Ubuntu
Database (use [x]):
- PostgreSQL

Description

postgres12 throws duplicate key value violates unique constraint "email_hash_pkey" messages.

I am not sure when these messages started to appear, but it seems right after the upgrade from 1.12.1 to 1.12.2.

Logs

...
...
postgres12_1        | 2020-07-21 14:34:26.727 UTC [962] ERROR:  duplicate key value violates unique constraint "email_hash_pkey"
postgres12_1        | 2020-07-21 14:34:26.727 UTC [962] DETAIL:  Key (hash)=(494e4122a76b31b98c7f37243f9bb30c) already exists.
postgres12_1        | 2020-07-21 14:34:26.727 UTC [962] STATEMENT:  INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2)
postgres12_1        | 2020-07-21 14:34:34.354 UTC [986] ERROR:  duplicate key value violates unique constraint "email_hash_pkey"
postgres12_1        | 2020-07-21 14:34:34.354 UTC [986] DETAIL:  Key (hash)=(3d7a739ecd1fd5a0e6d638118d47c699) already exists.
postgres12_1        | 2020-07-21 14:34:34.354 UTC [986] STATEMENT:  INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2)
postgres12_1        | 2020-07-21 14:34:36.073 UTC [997] ERROR:  duplicate key value violates unique constraint "email_hash_pkey"
postgres12_1        | 2020-07-21 14:34:36.073 UTC [997] DETAIL:  Key (hash)=(7c2d0cb25b44568819e1a0e04501aa97) already exists.
postgres12_1        | 2020-07-21 14:34:36.073 UTC [997] STATEMENT:  INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2)

Originally created by @ghost on GitHub (Jul 21, 2020). - Gitea version (or commit ref): 1.12.2 - Operating system: Ubuntu - Database (use `[x]`): - [x] PostgreSQL ## Description postgres12 throws `duplicate key value violates unique constraint "email_hash_pkey"` messages. I am not sure when these messages started to appear, but it seems right after the upgrade from `1.12.1` to `1.12.2`. ## Logs ``` ... ... postgres12_1 | 2020-07-21 14:34:26.727 UTC [962] ERROR: duplicate key value violates unique constraint "email_hash_pkey" postgres12_1 | 2020-07-21 14:34:26.727 UTC [962] DETAIL: Key (hash)=(494e4122a76b31b98c7f37243f9bb30c) already exists. postgres12_1 | 2020-07-21 14:34:26.727 UTC [962] STATEMENT: INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2) postgres12_1 | 2020-07-21 14:34:34.354 UTC [986] ERROR: duplicate key value violates unique constraint "email_hash_pkey" postgres12_1 | 2020-07-21 14:34:34.354 UTC [986] DETAIL: Key (hash)=(3d7a739ecd1fd5a0e6d638118d47c699) already exists. postgres12_1 | 2020-07-21 14:34:34.354 UTC [986] STATEMENT: INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2) postgres12_1 | 2020-07-21 14:34:36.073 UTC [997] ERROR: duplicate key value violates unique constraint "email_hash_pkey" postgres12_1 | 2020-07-21 14:34:36.073 UTC [997] DETAIL: Key (hash)=(7c2d0cb25b44568819e1a0e04501aa97) already exists. postgres12_1 | 2020-07-21 14:34:36.073 UTC [997] STATEMENT: INSERT INTO "email_hash" ("hash","email") VALUES ($1, $2) ```

GiteaMirror closed this issue

2025-11-02 06:35:16 -06:00

GiteaMirror commented

2025-11-02 06:35:17 -06:00

@lunny commented on GitHub (Aug 9, 2020):

So this should be fixed after v1.12.2

@lunny commented on GitHub (Aug 9, 2020): So this should be fixed after v1.12.2

GiteaMirror commented

2025-11-02 06:35:17 -06:00

@SuperSandro2000 commented on GitHub (Aug 15, 2020):

I am on 1.12.3 and my log still gets spammed with this.

@SuperSandro2000 commented on GitHub (Aug 15, 2020): I am on 1.12.3 and my log still gets spammed with this.

GiteaMirror commented

2025-11-02 06:35:17 -06:00

@ghost commented on GitHub (Aug 21, 2020):

I am on 1.12.3 and my log still gets spammed with this.

same here.

@ghost commented on GitHub (Aug 21, 2020): > I am on 1.12.3 and my log still gets spammed with this. same here.

GiteaMirror commented

2025-11-02 06:35:17 -06:00

@zeripath commented on GitHub (Aug 22, 2020):

So in the avatar linking code we do:

e6baa656f7/models/avatar.go (L44)

On the assumption that a constraint violation just means that the insert fails so it's all fine.

Clearly postgres demands that we actually add a if not exists there.

@zeripath commented on GitHub (Aug 22, 2020): So in the avatar linking code we do: https://github.com/go-gitea/gitea/blob/e6baa656f757fd1f2f6ba20c677e0c83422a8739/models/avatar.go#L44 On the assumption that a constraint violation just means that the insert fails so it's all fine. Clearly postgres demands that we actually add a if not exists there.

GiteaMirror commented

2025-11-02 06:35:18 -06:00

@zeripath commented on GitHub (Aug 23, 2020):

I'm not certain if this would just stop postgres moaning here. We literally just want to store the precomputed avatar hash in the db if it's not there already.

I'd really rather not have to write 4 different statements to get around excessive logging practices on the DB when we're just happily ignoring the error upstream.

Could someone with a postgres setup try this to see if it stops the logging?

diff --git a/models/avatar.go b/models/avatar.go
index 311d71462..c9ba2961e 100644
--- a/models/avatar.go
+++ b/models/avatar.go
@@ -41,7 +41,18 @@ func AvatarLink(email string) string {
 			Email: lowerEmail,
 			Hash:  sum,
 		}
-		_, _ = x.Insert(emailHash)
+		// OK we're going to open a session just because I think that that might hide away any problems with postgres reporting errors
+		sess := x.NewSession()
+		defer sess.Close()
+		if err := sess.Begin(); err != nil {
+			// we don't care about any DB problem just return the lowerEmail
+			return lowerEmail, nil
+		}
+		_, _ = sess.Insert(emailHash)
+		if err := sess.Commit(); err != nil {
+			// Seriously we don't care about any DB problems just return the lowerEmail - we expect the transaction to fail most of the time
+			return lowerEmail, nil
+		}
 		return lowerEmail, nil
 	})
 	return setting.AppSubURL + "/avatar/" + url.PathEscape(sum)

@zeripath commented on GitHub (Aug 23, 2020): I'm not certain if this would just stop postgres moaning here. We literally just want to store the precomputed avatar hash in the db if it's not there already. I'd really rather not have to write 4 different statements to get around excessive logging practices on the DB when we're just happily ignoring the error upstream. Could someone with a postgres setup try this to see if it stops the logging? ```patch diff --git a/models/avatar.go b/models/avatar.go index 311d71462..c9ba2961e 100644 --- a/models/avatar.go +++ b/models/avatar.go @@ -41,7 +41,18 @@ func AvatarLink(email string) string { Email: lowerEmail, Hash: sum, } - _, _ = x.Insert(emailHash) + // OK we're going to open a session just because I think that that might hide away any problems with postgres reporting errors + sess := x.NewSession() + defer sess.Close() + if err := sess.Begin(); err != nil { + // we don't care about any DB problem just return the lowerEmail + return lowerEmail, nil + } + _, _ = sess.Insert(emailHash) + if err := sess.Commit(); err != nil { + // Seriously we don't care about any DB problems just return the lowerEmail - we expect the transaction to fail most of the time + return lowerEmail, nil + } return lowerEmail, nil }) return setting.AppSubURL + "/avatar/" + url.PathEscape(sum) ```

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#5764

postgres12 throws duplicate key value violates unique constraint "email_hash_pkey" messages #5764

Description

Logs

postgres12 throws `duplicate key value violates unique constraint "email_hash_pkey"` messages #5764