[SOLVED] Postgres with cron job doesn't work on get request

Issue

I am working on a cron job to return results for every minute.

select
  cron.schedule(
    'webhook-every-minutex', -- name of the cron job
    '* * * * *', -- every minute
    $$
    select content::json->'results'
from http_get('https://swapi.dev/api/people');
    $$
  );

Unfortunately it doesn't return results but when I manully run the select query, it returns json results. (The code was tested on supabase)

My goal is to run the above cron job for every minute.

Solution

A test case:

SELECT cron.schedule('test', '* * * * *', $$SELECT 1$$);
 schedule 
----------
        4
--Where 4 is job number

--In Postgres log
2022-03-14 11:18:00.001 PDT [63241] LOG:  cron job 4 starting: SELECT 1
2022-03-14 11:18:00.007 PDT [63241] LOG:  cron job 4 completed: 1 row
2022-03-14 11:19:00.002 PDT [63241] LOG:  cron job 4 starting: SELECT 1
2022-03-14 11:19:00.008 PDT [63241] LOG:  cron job 4 completed: 1 row
2022-03-14 11:20:00.002 PDT [63241] LOG:  cron job 4 starting: SELECT 1
2022-03-14 11:20:00.010 PDT [63241] LOG:  cron job 4 completed: 1 row

Nothing shows up in psql, which I assume is the console you are referring to.

So:

create table cron_test(fld_1 int, ts timestamptz  default now());

SELECT cron.schedule('test', '* * * * *', $$insert into cron_test(fld_1) select 1$$);
 schedule 
----------
        4

--Postgres log
2022-03-14 11:22:00.001 PDT [63241] LOG:  cron job 4 starting: insert into cron_test(fld_1) select 1
2022-03-14 11:22:00.008 PDT [63241] LOG:  cron job 4 COMMAND completed: INSERT 0 1 1
2022-03-14 11:23:00.002 PDT [63241] LOG:  cron job 4 starting: insert into cron_test(fld_1) select 1
2022-03-14 11:23:00.012 PDT [63241] LOG:  cron job 4 COMMAND completed: INSERT 0 1 1
2022-03-14 11:24:00.001 PDT [63241] LOG:  cron job 4 starting: insert into cron_test(fld_1) select 1
2022-03-14 11:24:00.006 PDT [63241] LOG:  cron job 4 COMMAND completed: INSERT 0 1 1
2022-03-14 11:25:00.003 PDT [63241] LOG:  cron job 4 starting: insert into cron_test(fld_1) select 1
2022-03-14 11:25:00.010 PDT [63241] LOG:  cron job 4 COMMAND completed: INSERT 0 1 1


select * from cron_test ;
 fld_1 |              ts               
-------+-------------------------------
     1 | 2022-03-14 11:22:00.007153-07
     1 | 2022-03-14 11:23:00.010206-07
     1 | 2022-03-14 11:24:00.004967-07
     1 | 2022-03-14 11:25:00.008177-07

If you want to see the results of a command you will need to put them somewhere you can retrieve from. The issue being that pg_cron opens a separate libpq connection to run the job. This means you will not see the results in the console you started the job in.

Answered By - Adrian Klaver

Answer Checked By - Mary Flores (WPSolving Volunteer)