[Pkg-postgresql-public] Fwd: [pgagent] Behaviour change between squeeze and wheezy

[Grzegorz Woźniak]

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 <wozniakg at gmail.com>
To: Gerfried Fuchs <rhonda at debian.org>

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

-----------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20141105/f0b3320e/attachment-0001.html>