Postgres with cron job doesn't work on get request

Question

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.

Return results where? Right now I see you getting results from an API. I don't see them being sent anywhere. What is it you are trying to achieve? — Adrian Klaver, Mar 13 '22 at 18:12
it returns results on console. The code above doesn't send anywhere, I will do that part as soon as if this works correctly :) My goal is run the above API using cron job(backed by postgres) every minute and return results on console. — user4150758, Mar 14 '22 at 03:11
I don't think you are going to see anything returned to the console. I don't have time to test right now, but I'm guessing the `$$ ... $$` becomes an anonymous function which do not return anything. Pretty sure if you want to see results you will need to store the result of the query somewhere. — Adrian Klaver, Mar 14 '22 at 16:32

Adrian Klaver · Accepted Answer · 2022-03-14T19:34:59.067

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.

>If you want to see the results of a command you will need to put them somewhere you can retrieve from ----like how? — user4150758, Mar 16 '22 at 01:56

Postgres with cron job doesn't work on get request

1 Answers1