citusdata / citusdata/pg_cron

executing multiple statements in one job but logging only first statement's output

Open
#349 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

why do multiple statements in one job only log the first statement's output in `cron.job_run_details` in `return_message`?

example:

```
postgres=> create table a (b timestamp with time zone);
CREATE TABLE

postgres=> SELECT cron.schedule('* * * * *', $$select count(*) from a;insert into a select now();insert into a select now();$$);
schedule
----------
24
(1 row)

postgres=> select * from a;
b
-------------------------------
2024-08-20 14:10:00.068271+00
2024-08-20 14:10:00.068271+00
(2 rows)

select * from cron.job_run_details order by end_time desc limit 1;
jobid | runid | job_pid | database | username | command | status | return_message | start_time
| end_time
-------+-------+---------+-------------+-------------+---------------------------------------------------------------------------------+-----------+----------------+------------------------
-------+-------------------------------
25 | 664 | 11186 | postgres | postgres | select count(*) from a; insert into a select now(); insert into a select now(); | succeeded | INSERT 0 1 | 2024-08-20 14:10:00.010
36+00 | 2024-08-20 14:10:00.084913+00
```

we only see `INSERT 0 1` here. this is restrictive when we're running a job that executes `DELETE`s that have child dependencies - in order, for example.

thanks.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reproducing the SQL example with multiple statements scheduled through cron.schedule, then inspect how cron.job_run_details populates return_message. Done means the job history records output from all statements rather than only the final INSERT result, including cases such as ordered DELETEs with child dependencies.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.