Duplicate key value violates unique constraint "pk_workflow_statistics"

[karkill]

Describe the bug
I get some error when use n8n with postgres.

2023-09-26 06:45:23.963 GMT [23560] ERROR:  duplicate key value violates unique constraint "pk_workflow_statistics"
2023-09-26 06:45:23.963 GMT [23560] DETAIL:  Key ("workflowId", name)=(pZXCxX8W0iLdLUON, data_loaded) already exists.
2023-09-26 06:45:23.963 GMT [23560] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)

I review code and I think we need to change from insertWorkflowStatistics to upsertWorkflowStatistics in function nodeFetchedData to fix it.
https://github.com/n8n-io/n8n/blob/9accf3f0d937d68ff25c3e430136cb2c6c18cf3c/packages/cli/src/services/events.service.ts#L71C1-L72C1
Change from

async nodeFetchedData(workflowId: string | undefined | null, node: INode): Promise<void> {
		if (!workflowId) return;

		const insertResult = await this.repository.insertWorkflowStatistics(
			StatisticsNames.dataLoaded,
			workflowId,
		);
....		

to

async nodeFetchedData(workflowId: string | undefined | null, node: INode): Promise<void> {
		if (!workflowId) return;

		const insertResult = await this.repository.upsertWorkflowStatistics(
			StatisticsNames.dataLoaded,
			workflowId,
		);

Environment (please complete the following information):

• Enviroment: EKS v1.23.17
• n8n Version 1.1.1
• Database system: postgresdb
• Operation mode: queue

Additional context

[Joffcom]

Hey @karkill,

Thanks for the report, It looks like this is already being tracked internally as PAY-749. I will let you know when we have resolved this.

[mateusztylec]

Hi. Have you managed to fix that issue?

[Joffcom]

Hey @mateusztylec,

Not yet, at the moment this issue doesn't really break anything so the priority isn't that high.

If this is breaking something for you though let me know and I can get the ticket updated.

[janbrb]

Hey @Joffcom,

I witness the same error as above in my self-hosted environment. Additionally I see workflows that never end and therefore staying in status 'running' forever.
When I restart all services (workers, redis, ...) this error resolves and everything runs fine. The error appears after several executions and then again very frequent.

Any chance this is related to the above issue? Is there a fix in sight?

[luizeof]

@Joffcom same here ... v1.16.0

2023-11-23 11:39:48 UTC:5.x.x.x(45990):postgres@n8n_queue:[5407]:ERROR:  duplicate key value violates unique constraint "pk_workflow_statistics"
2023-11-23 11:39:48 UTC:5.x.x.x(45990):postgres@n8n_queue:[5407]:DETAIL:  Key ("workflowId", name)=(p9TDAAJP5QwhMPEB, data_loaded) already exists.
2023-11-23 11:39:48 UTC:5.x.x.x(45990):postgres@n8n_queue:[5407]:STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-11-23 11:39:49 UTC:5.x.x.x(38068):postgres@n8n_queue:[5683]:ERROR:  duplicate key value violates unique constraint "pk_workflow_statistics"
2023-11-23 11:39:49 UTC:5.x.x.x(38068):postgres@n8n_queue:[5683]:DETAIL:  Key ("workflowId", name)=(oGVIsNmGjk3BA7a8, data_loaded) already exists.
2023-11-23 11:39:49 UTC:5.x.x.x(38068):postgres@n8n_queue:[5683]:STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-11-23 11:39:49 UTC:5.x.x.x(44190):postgres@n8n_queue:[5686]:ERROR:  duplicate key value violates unique constraint "pk_workflow_statistics"
2023-11-23 11:39:49 UTC:5.x.x.x(44190):postgres@n8n_queue:[5686]:DETAIL:  Key ("workflowId", name)=(skKjdq6vAq2R8HdC, data_loaded) already exists.

[janober]

Fix got released with n8n@1.18.1 in the PR #7824