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

[arno01]

• 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)

[lunny]

So this should be fixed after v1.12.2

[SuperSandro2000]

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

[arno01]

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

same here.

[zeripath]

So in the avatar linking code we do:

gitea/models/avatar.go

Line 44 in e6baa65

_, _ = x.Insert(emailHash)

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]

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)