Unable to update failed jobs

[uphv]

Hi,

Queued jobs according to Vapor documentation accept a maxRetryCount parameter.

In one of the jobs, an error is thrown.

This error causes the job to fail and apparently the queueing system tries to update the "attempts" field. Instead of doing an update it tries to insert the record again and of course it's faling due to PK violation.

The error is:

[ ERROR ] Job run failed [error: PSQLError(code: server, serverInfo: [sqlState: 23505, detail: Key (id)=(4B7BBB80-82BE-4592-8691-22FA57410059) already exists., file: nbtinsert.c, line: 666, message: duplicate key value violates unique constraint "_jobs_meta_pkey", routine: _bt_check_unique, localizedSeverity: ERROR, severity: ERROR, constraintName: _jobs_meta_pkey, schemaName: public, tableName: _jobs_meta], triggeredFromRequestInFile: PostgresKit/PostgresDatabase+SQL.swift, line: 81, query: PostgresQuery(sql: INSERT INTO "_jobs_meta" ("id", "queue_name", "job_name", "queued_at", "delay_until", "state", "max_retry_count", "attempts", "payload", "updated_at") VALUES ($1, $2, $3, $4, $5, 'initial', $6, $7, $8, now()), binds: [(; TEXT; format: binary), (; TEXT; format: binary), (; TEXT; format: binary), (; TIMESTAMPTZ; format: binary), (; TIMESTAMPTZ; format: binary), (; BIGINT; format: binary), (; BIGINT; format: binary), (; BYTEA; format: binary)]))]

In addition to the above, it looks that the system fails to release the DB connections that it's using for this insert / update. As a result after a while the DB connection pool is exhausted and the only option is to restart (this can happen a lot of times in the day).

Configuration:

.package(url: "https://github.com/vapor/vapor.git", from: "4.115.0"),
// 🗄 An ORM for SQL and NoSQL databases.
.package(url: "https://github.com/vapor/fluent.git", from: "4.9.0"),
// 🪶 Fluent driver for SQLite.
.package(url: "https://github.com/vapor/fluent-postgres-driver.git", from: "2.10.0"),
// 🍃 An expressive, performant, and extensible templating language built for Swift.
.package(url: "https://github.com/vapor/leaf.git", from: "4.3.0"),
// 🔵 Non-blocking, event-driven networking for Swift. Used for custom executors
.package(url: "https://github.com/apple/swift-nio.git", from: "2.65.0"),
// Queues Fluent
.package(url: "https://github.com/vapor-community/vapor-queues-fluent-driver.git", from: "3.0.0"),

PostgreSQL: 16

Thanks for looking into that!

Let me know if further details are needed.

[uphv]