cron.job_run_details incorrectly logs 1 row. instead of function's RETURN value

[isaack0815]

Describe the bug When a pg_cron job is scheduled to execute a SELECT my_function() statement, the return_message column in cron.job_run_details consistently logs the string 1 row. instead of the actual TEXT or VARCHAR value returned by the function. The status of the job is correctly logged as succeeded.

This behavior prevents effective monitoring and debugging of cron jobs, especially those that return status messages or identifiers (e.g., from pg_net calls).

To Reproduce Steps to reproduce the behavior:

Environment:

Supabase Project (hosted cloud environment) PostgreSQL Version: 15.1 pg_cron Version: 1.6.2 (as provided by Supabase) Create a simple test function that returns a static text value:

sql

CREATE OR REPLACE FUNCTION public.cron_test_return_value()
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN 'Hello from pg_cron! This is the expected return message.';
END;
$$;

Schedule a cron job to run this function every minute:

sql SELECT cron.schedule('test_job', '*/1 * * * *', 'SELECT cron_test_return_value()'); Wait for the job to execute and then query the run details:

sql

SELECT
  jobid,
  runid,
  status,
  return_message,
  start_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 5;

Expected behavior The return_message column should contain the actual string returned by the function.

Expected return_message: Hello from pg_cron! This is the expected return message.

Actual behavior The return_message column contains the literal string 1 row..

Actual return_message: 1 row.

Screenshots / Logs (Here you can add a screenshot of your SQL query result if you want, but the text description is very clear)

jobid	runid	status	return_message	start_time
123	456	succeeded	1 row.	2024-08-01 08:00:00.123+00
123	455	succeeded	1 row.	2024-07-31 07:59:00.123+00

Additional context This issue seems to be specific to how pg_cron captures the output of a SELECT statement that calls a function. It appears to be logging the result of the SELECT command itself (which is indeed one row) rather than the content of that row.

This was discovered while trying to log the request_id from a pg_net.http_post call within a function. The inability to capture the return value makes it impossible to trace asynchronous HTTP requests initiated by cron jobs. The simple test function above was created to isolate the problem and confirm it's not related to pg_net.

The behavior is consistent and reproducible on the Supabase cloud platform.

[marcoslot]

That's always been the behaviour. It returns postgres' query outcome rather than user data. Arbitrarily storing user data in the cron.job_run_details table is probably not a good idea.

[tvogel]

That's always been the behaviour. It returns postgres' query outcome rather than user data. Arbitrarily storing user data in the cron.job_run_details table is probably not a good idea.

That might be the case sometimes or often but is easy to control for the user, so giving the option to just return succeeded or more detailed data from the called code depending on the case (and minding security) would be really helpful. Always just getting 1 row on success really feels more like a bug than a feature.