Postgres Process manager does not persist job results

[webb-ben]

Description
When running OAProcesses with a PostgreSQL job manager on scaling containers in Cloud Run, we are noticing that we are unable to retrieve job results if the container executing the process is ephemeral. We are unable to a have persistent job manager if the job result is not tied to the same lifespan.

It seems all process managers delegate execution of the actual process to base_manager.py which only writes results to a directory. Does it make sense to store the process result in the same context as the process manager? Or is there some reason I am missing that all managers are still implementing a custom get_job_result when the location of the job result is determined by the parent class.

It could either be a second table with the job id as the PK and result bytes as acompanying column - or this could be an added column to the existing manager table.

Steps to Reproduce
Steps to reproduce the behavior:
Run pygeoapi with manager on some sort of system with ephemeral containers.

Expected behavior
Job results values stay in sync with the job manager.

Screenshots/Tracebacks
If applicable, add screenshots to help explain your problem.

Environment

• OS: ubuntu
• Python version: 3.12
• pygeoapi version: 0.22.0

Additional context
We could create a separate volume to write job results to.
cc @C-Loftus

[francescoingv]

The job result(s) generically are of different nature and size, and might be big/huge.
The base_manager.py offers a basic feature, and PostgreSQL job manager enhance it to use PostgreSQL as backend DB.
Storing the whole result in the database may be not efficient in some cases, but in others it could be the right solution, which is what I understand you suggest.
For such a scenario it is possible to make a specialized project manager derived from base_manager.py to store everything in the DB: in this case it is required to overwrite and re-write most of the original functions.

[webb-ben]

That makes sense. I would say this issue is mostly trying to understand what is the implementation responsibility of child classes of the BaseManager class. It seems duplicative to have child classes re-implement get_job

pygeoapi/pygeoapi/process/manager/tinydb_.py

Line 166 in 46eaaf5

def get_job(self, job_id: str) -> dict:

and get_job_result

pygeoapi/pygeoapi/process/manager/postgresql.py

Line 271 in 46eaaf5

def get_job_result(self, job_id: str) -> Tuple[str, Any]:

If the BaseManager implements the storage of job results, would it not make sense to implement the retrieval of job results with get_job_result in this class. That function would call get_job which would be implemented in the child classes - and the response from this function would be usable to retrieve the job result across any manager.

Separately, does it make sense to store job results separately from the job metadata? What is the purpose of persisting job run metadata if the result is too large to persist in the same context? All that results in is a manager that appears to be storing results that do not exist.