Custom "return message"

[mtvan]

Is it possible to set a custom return message in a function or a procedure that would then show up in the job_run_details table?

[ayuryshev]

I did not understood how to use it too.

SELECT message::text

SELECT 'Hello world!';

return_message is SELECT 1

RAISE NOTICE

SELECT * FROM cron.schedule('raise_exp' ,'* * * * *',  $$
DO $do$
BEGIN
    RAISE NOTICE 'hello';
END
$do$
$$);

Result: DO

Not very usable

[chris-gilbert-2]

I was having a look at this - the reason for the message is that on successful completion the column is just populated with a call to PQcmdStatus which is pretty limited - https://www.postgresql.org/docs/current/libpq-exec.html

One thing that might be worth considering is to cater for cases where user calls a function with a single return value, then I think PQgetvalue might do what we want.

So I think if PQntuples returns 1 and PQnfields returns 1 then use PQgetvalue else use PQcmdStatus

Then one option, if we are not calling a function with a useful scalar return value, we can wrap calls with a function that returns an explicit summary - for example

CREATE OR REPLACE FUNCTION my_procedure_wrapper()
RETURNS varchar
LANGUAGE plpgsql
AS
$$
DECLARE
    updates int;
BEGIN
    CALL my_procedure();
    select x
    into updates
    from affected_table
    where last_update > now() - '1 day';

    return updates || ' records affected'; 
END
$$;

More complex would be to cater for arbitrary select results, but I think it is not unreasonable to limit useful output to cases where the job command is in the format select function() and where function returns a scalar.

[chris-gilbert-2]

If that sounds reasonable I'll try to have a stab at it, see how it looks and raise a PR, but I'll wait for any other comments and alternatives - of course we can always dump useful messages in our own custom table during any procedure/function called via pg_cron.

Also wondering if another option would be a procedure in the cron schema that could be called from inside any procedure to add info that would go in maybe an additional column in job_run_details. That would only work if job_run details row is inserted at the start of execution, and updated with end time, status etc at the end - I'm not sure if that is the case but will check.

In that case the difficulty would be how to identify the row to update at any point during execution when this hypothetical procedure is called and wants to update the right row. If the procedure could identify the right job_id that would be easy as pg_cron doesn't allow concurrent execution of the same job so there would only be one row without an end time.

[marcocitus]

I thnk having some kind of result column in job_run_details for 1x1 query results seems reasonable, though you do need to be careful to not inadvertendly leak information so this should be configurable.

[chris-gilbert-2]

Thanks Marco, I'll try to take some time to come back to this and let you know. Agree about the configurability

[christopher-gilbert]

I can see this isn't quite so straightforward - adding new column in job_run_details, and populating it in GetTaskFeedback is straightforward, but background worker query execution is more complex, and needs some interesting trawling through the Postgres source - maybe first step is to implement with the libpq connection.

Looking at it, and trying a couple of things, I think the key to this for the background worker is the receiver = CreateDestReceiver(DestNone); on line 2235. If I change it to DestDebug then the result of the function call does get logged as expected. The question I think is how to get the result included in the data that is retrieved in res = shm_mq_receive(responseq, &nbytes, &data, nowait); on line 1932, presumably by using one of the options in dest.h but setting things up properly in order to handle the specified destination.

[sjlyoung58]

I too would like the ability to get some info on the job logged to the job_run_details table. My thinking is that just having the ability to return varchar to be stored in the result column is flexible enough - the user could build a json array to document various steps of the job (returned as text) and then use a custom query to get formatted results from the job by casting back to jsonb and using jsonb_array_elements() to unpack the job steps to a set of rows result for report viewing

[mtvan]

Wanted to check in if anyone took a stab at this. It would be incredibly useful to pass along more log data when these jobs run

[christopher-gilbert]

I did start on this, and was planning to come back to it. The problem I hit is that there are 2 ways in which execution happens - one way is easy to update, the other is more tricky to figure out how or if the information can be made available. The details are a bit hazy as it's a while since I looked but as mentioned I was going to come back to it and I can give more detail on the issue to see if anyone else has some good pointers.

[christopher-gilbert]

Okay, I had a look again, and got everything up and running. I think the notes I made above still stand. If you don't use background workers it is straightforward but if you do have cron.use_background_workers = on configured then I'm struggling to figure out how to set up the right DestReceiver and access results.

For anyone that can give me any hints or have a go themselves, the background worker query execution happens from here, getting data from message queue happens from here

I'm not quite sure about the relationship between the shared memory message queue that is set up here and the Portal in which query execution is managed, and which has a DestReceiver set up here. The fact that DestDebug causes the function result to be logged suggests that is the key to getting hold of the result, but then what is the role of the message queue - is it just to communicate changes in state?

The source around DestReceivers is well commented in https://doxygen.postgresql.org/dest_8h_source.html - in fact the Postgres code is all well documented, and that site is quite good for navigating around and looking up the functions used by pg_cron.