Log cron job results in a table

[marcocitus]

Currently, pg_cron does not give much feedback on the outcome of cron jobs other than what is available from the PostgreSQL log. Add logic to update the cron.result table as tasks progress. Optionally, add a cron job to clean up the table.

[onderkalaci]

+1 for this.

[MichaelDBA]

+1 again!

[keithf4]

Another option to avoid the overhead of a table write would be to emit a NOTICE or LOG to the postgresql logs.

[marcocitus]

Open to input on this one. Would adding a GUC to enable logging be sufficient or would a table be preferable? I would expect doing a few writes per minute wouldn't be a problem for anyone, though I realise a much desired feature is running jobs every x seconds in which case continuously writing to the results table might not be that great.

[bonesmoses]

Even just log entries that say that a job was started, when it completed, any errors reported, etc, would be nice. I've got a pg_cron installation that's not showing up in my Postgres logs anywhere, and I have literally no debugging information to track down the problem.

[marcocitus]

I've added a cron.log_statement GUC (on by default), which produces output as follows:

success case:

2016-11-10 03:25:00.003 CET LOG:  cron job 1 starting: UPDATE pit SET a = a + 1
2016-11-10 03:25:00.026 CET LOG:  cron job 1 completed: UPDATE 0 1

error case:

2016-11-10 03:27:00.003 CET LOG:  cron job 1 starting: UPDATE pit SET a = a + 1
2016-11-10 03:27:00.014 CET LOG:  cron job 1 ERROR:  relation "pit" does not exist
    LINE 1: UPDATE pit SET a = a + 1
                   ^

Will this work?

I plan to do a 1.0 release soon and build packages so any feedback is highly appreciated!

[bonesmoses]

For me that would be a huge help. Not sure about others that +1'd this. :)

[Andryj]

Hello everyone
I can't install this extension.
CentOS Linux release 7.2.1511 (Core) + PG9.4
Can you help me? What do i wrong?

`

pg_cron]# PATH=/usr/pgsql-9.4/bin/:$PATH make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Iinclude -I/usr/pgsql-9.4/include -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/job_metadata.o src/job_metadata.c
src/job_metadata.c: In function ‘cron_schedule’:
src/job_metadata.c:178:2: error: too many arguments to function ‘GetUserNameFromId’
char *userName = GetUserNameFromId(userId, false);
^
In file included from src/job_metadata.c:14:0:
/usr/pgsql-9.4/include/server/miscadmin.h:295:14: note: declared here
extern char *GetUserNameFromId(Oid roleid);
^
src/job_metadata.c: In function ‘cron_unschedule’:
src/job_metadata.c:354:2: error: too many arguments to function ‘GetUserNameFromId’
userName = GetUserNameFromId(userId, false);
^
In file included from src/job_metadata.c:14:0:
/usr/pgsql-9.4/include/server/miscadmin.h:295:14: note: declared here
extern char *GetUserNameFromId(Oid roleid);
^
src/job_metadata.c:362:3: error: unknown type name ‘AclResult’
AclResult aclResult = pg_class_aclcheck(CronJobRelationId(), GetUserId(),
^
src/job_metadata.c:362:3: error: implicit declaration of function ‘pg_class_aclcheck’ [-Werror=implicit-function-declaration]
src/job_metadata.c:364:20: error: ‘ACLCHECK_OK’ undeclared (first use in this function)
if (aclResult != ACLCHECK_OK)
^
src/job_metadata.c:364:20: note: each undeclared identifier is reported only once for each function it appears in
src/job_metadata.c:366:4: error: implicit declaration of function ‘aclcheck_error’ [-Werror=implicit-function-declaration]
aclcheck_error(aclResult, ACL_KIND_CLASS,
^
src/job_metadata.c:366:30: error: ‘ACL_KIND_CLASS’ undeclared (first use in this function)
aclcheck_error(aclResult, ACL_KIND_CLASS,
^
cc1: all warnings being treated as errors
make: *** [src/job_metadata.o] Error 1

`

[marcocitus]

@Andryj

CentOS Linux release 7.2.1511 (Core) + PG9.4

Unfortunately, only PostgreSQL 9.5 or higher is supported by pg_cron. I'll clear this up in the README.

[Andryj]

Thank you for your answer.

[termit-uanic]

If modify source you can build this extension.

diff --git a/pg_cron.sql b/pg_cron.sql
index 4c90ebc..a9f6f65 100644
--- a/pg_cron.sql
+++ b/pg_cron.sql
@@ -25,8 +25,8 @@ CREATE TABLE cron.job (
        username text not null default current_user
 );
 GRANT SELECT ON cron.job TO public;
-ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;
-CREATE POLICY cron_job_policy ON cron.job USING (username = current_user);
+--ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;
+--CREATE POLICY cron_job_policy ON cron.job USING (username = current_user);
 
 CREATE FUNCTION cron.schedule(schedule text, command text)
     RETURNS bigint
diff --git a/src/job_metadata.c b/src/job_metadata.c
index 9ef0555..f0e6e1f 100644
--- a/src/job_metadata.c
+++ b/src/job_metadata.c
@@ -39,6 +39,7 @@
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
+#include "utils/acl.h"
 #include "utils/relcache.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
@@ -175,7 +176,7 @@ cron_schedule(PG_FUNCTION_ARGS)
        bool isNulls[Natts_cron_job];
 
        Oid userId = GetUserId();
-       char *userName = GetUserNameFromId(userId, false);
+       char *userName = GetUserNameFromId(userId);
 
        parsedSchedule = parse_cron_entry(schedule);
        if (parsedSchedule == NULL)
@@ -351,7 +352,7 @@ cron_unschedule(PG_FUNCTION_ARGS)
 
        /* check if the current user owns the row */
        userId = GetUserId();
-       userName = GetUserNameFromId(userId, false);
+       userName = GetUserNameFromId(userId);
 
        ownerNameDatum = heap_getattr(heapTuple, Anum_cron_job_username,
                                                                  tupleDescriptor, &isNull);

But this patch isn't secure.

[jamessewell]

This is good - I'd still be interested in the table option though.

The log option does make it hard to get at this information for audit as you have to parse all logfiles - not the end of the world but still a pain to assert that it's been run at the correct times.

For monitoring you'd have to scrape the log file at each health check, which is really not ideal.

[lachesis]

We just abandoned pg_cron because of the lack of error reporting. Please add decent logging by default.

[marcocitus]

Fixed by #111