Hi, I'm forwarding mail describing odd behaviour of pgagent in wheezy. -- Best regards, Grzegorz Woźniak ---------- Forwarded Message ----------
Subject: [pgagent] Behaviour change between squeeze and wheezy Date: Monday, 3 of November 2014, 12:02:05 From: Grzegorz Woźniak <[email protected]> To: Gerfried Fuchs <[email protected]> Hi, I noticed that pgagent( 3.2.1-1) in wheezy treats successfully ended sql steps in another way than pgagent in squeeze(3.0.1-1). When I downgraded pgagent on wheezy to 3.0.1 all my jobs started to work fine, as I exepected them to work :) Example, my first step is: INSERT INTO scheduled_move_old_ids (SELECT id FROM msg_in WHERE create_date < '2014-10-21'::DATE AND processed IS TRUE AND blocked IS FALSE); When executed by 3.2.1 and 0 rows is inserted, step ends with success and next step is processed, but when it insersts some rows to scheduled_move_old_ids, step ends with failure and whole job is interrupted(on error set to fail). For me it is a bug. The same step executed by 3.0.1 ends with success depsite number of rows altered by sql query. Above query inserts 12 rows or nothing if change date to '2014-10-20'. Log for 3.2.1: 2014-11-03T08:43:37 PgAgent Connection stats: total - 1, free - 0, deleted - 0 2014-11-03T08:43:37 PgAgent Checking for jobs to run 2014-11-03T08:43:37 PgAgent Creating job thread for job 1 2014-11-03T08:43:37 PgAgent Creating DB connection: user=postgres dbname=postgres 2014-11-03T08:43:37 PgAgent Allocating new connection to database postgres 2014-11-03T08:43:37 PgAgent Starting job: 1 2014-11-03T08:43:37 PgAgent Sleeping... 2014-11-03T08:43:37 PgAgent Creating DB connection: user=postgres dbname=postgres dbname=smpp 2014-11-03T08:43:37 PgAgent Allocating new connection to database smpp 2014-11-03T08:43:37 PgAgent Executing SQL step 1 (part of job 1) 2014-11-03T08:43:37 PgAgent Returning connection to database smpp --here first step seems to be failed, but ids are inserted into scheduled_move_old_ids 2014-11-03T08:43:37 PgAgent Returning connection to database postgres 2014-11-03T08:43:37 PgAgent Completed job: 1 2014-11-03T08:43:37 PgAgent Destroying job thread for job 1 2014-11-03T08:43:42 PgAgent Checking for jobs to run Log for 3.0.1: 2014-11-03T08:57:53 PgAgent Checking for jobs to run 2014-11-03T08:57:53 PgAgent Creating job thread for job 1 2014-11-03T08:57:53 PgAgent Creating DB connection: user=postgres dbname=postgres 2014-11-03T08:57:53 PgAgent Allocating new connection to database postgres 2014-11-03T08:57:53 PgAgent Starting job: 1 2014-11-03T08:57:53 PgAgent Sleeping... 2014-11-03T08:57:53 PgAgent Creating DB connection: user=postgres dbname=postgres dbname=smpp 2014-11-03T08:57:53 PgAgent Allocating new connection to database smpp 2014-11-03T08:57:53 PgAgent Executing SQL step 1 (part of job 1) 2014-11-03T08:57:53 PgAgent Returning connection to database smpp --here the same query ends with success 2014-11-03T08:57:53 PgAgent Allocating existing connection to database smpp 2014-11-03T08:57:53 PgAgent Executing SQL step 3 (part of job 1) --procced to next step 2014-11-03T08:57:53 PgAgent Query error: ERROR: duplicate key value violates unique constraint "pk_msg_in_old" -- my fault I executed this step in sql editor to check it runs ok, and did not truncate data from msg_in_old table before restarting job 2014-11-03T08:57:53 PgAgent Key (id)=(1423) already exists. 2014-11-03T08:57:53 PgAgent Returning connection to database smpp 2014-11-03T08:57:53 PgAgent Returning connection to database postgres 2014-11-03T08:57:53 PgAgent Completed job: 1 2014-11-03T08:57:53 PgAgent Destroying job thread for job 1 2014-11-03T08:57:58 PgAgent Checking for jobs to run And values from table pgagent.pga_jobsteplog for both agents: '21';11;1;'s';0;'2014-11-03 08:04:45.821279+01';'00:00:00.009522';'''' -- 3.2.1, jslresult is 0,jslstatus is 's' - success, 0 rows altered, job is completed '35';19;1;'f';12;'2014-11-03 08:43:36.832157+01';'00:00:00.008204';'''' --3.2.1, jslresult is 12, but jslstatus is 'f' - failed,12 rows are inserted, job is interrupted '36';20;1;'s';12;'2014-11-03 08:57:53.025567+01';'00:00:00.008527';'''' --3.0.1, jslresult is 12 and jslstatus is 's' and job is completed My machine is: Debian 7.7, Linux smpprouter 3.2.0-4-686-pae #1 SMP Debian 3.2.60-1+deb7u3 i686 GNU/Linux Postgresql: ii postgresql-9.1 9.1.14-0+deb7u1 i386 PgAgent currently installed: hi pgagent 3.0.1-1 i386 PgAgent installed before: ii pgagent 3.2.1-1 i386 As there is no problem with dependencies, I'll stay with pgagent from squeeze because it works for me. Any help ? -- Best regards, Grzegorz Woźniak -----------------------------------------
_______________________________________________ Pkg-postgresql-public mailing list [email protected] http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-postgresql-public
