Question about changing transaction isolation levels in PostgreSQL and the interplay with Procrastinate?

[paulzakin]

Hey @medihack and @ewjoachim!

1. Super excited to try out v3 in the first week of March - looks like you guys and the rest of team did a bunch of really awesome work.

2. I was playing around with the default PostgreSQL transaction isolation levels and tried to change from read committed to repeatable read. Procrastinate did not seem to like it, but it also could have just been a quirk with our production system or a task we run. Our error looked something like this. No idea if Procrastinate was built with non-default transaction isolation levels in mind, and I did not see anything in the documentation, but thought I would ask. Totally not urgent, either way. Thanks so much!

2025-02-11T18:31:00.670Z | Main coroutine error, initiating remaining coroutines stop. Cause: ConnectorException('\nDatabase error.\n')
-- | --
  | 2025-02-11T18:31:02.784Z | periodic_deferrer error: ConnectorException('\nDatabase error.\n')
  | 2025-02-11T18:31:02.784Z | NoneType: None
  | 2025-02-11T18:31:02.825Z | could not serialize access due to concurrent update
  | 2025-02-11T18:31:02.825Z | CONTEXT: SQL statement "INSERT
  | 2025-02-11T18:31:02.825Z | INTO procrastinate_periodic_defers (task_name, periodic_id, defer_timestamp)
  | 2025-02-11T18:31:02.825Z | VALUES (_task_name, _periodic_id, _defer_timestamp)
  | 2025-02-11T18:31:02.825Z | ON CONFLICT DO NOTHING
  | 2025-02-11T18:31:02.825Z | RETURNING id"
  | 2025-02-11T18:31:02.825Z | PL/pgSQL function procrastinate_defer_periodic_job(character varying,character varying,character varying,character varying,integer,character varying,bigint,jsonb) line 7 at SQL statement
  | 2025-02-11T18:31:02.825Z | Database error.
  | 2025-02-11T18:31:02.825Z | One of the specified coroutines ended with an exception

[medihack]

Hi @paulzakin. Unfortunately, we are not testing against other transaction isolation levels and only use the default one. And I must confess that I wasn't even aware of those other levels 🫣. So, I can't estimate how much work it would be to make Procrastinate play nicely with those different levels. But if you find a solution, I would happily integrate it and run a test in the matrix.

[paulzakin]

Not a problem at all - genuinely just found it interesting! If I ever do use a higher transaction level and find a solution for it, I’ll be sure to let you guys know! But consider this discussion answered :)

[onlyann]

This specific error probably happens because multiple worker instances try to insert that periodic row concurrently.

Interested to know if you run into other errors if you limit your test to a single worker.

[onlyann]

@paulzakin running a single instance is most certainly not a solution.

However, you could try a different default transaction level while running a single process to see if other issues arise.

As for this specific error, I think it could be solved by adapting the query.

[paulzakin]

Fair enough! I can run it and see what happens tomorrow - will report back

[paulzakin]

Ok! I messed around using only one process and a couple of periodic tasks.

On serializable and repeatable read, I get no error. So I think you were right @onlyann.

[onlyann]

Two things than can be done to prevent the issue with procrastinate_defer_periodic_job

1. introduce a select for update statement prior to the INSERT statement
2. wrap the query with an explicit READ COMMITTED transaction level

Are you ok trying this change , run concurrent worker instances and report back?

[paulzakin]

I’m happy to do the testing but not super familiar with the internals - probably @ewjoachim or @medihack would be better suited for the actual code