Thread: pg_agent jobs

pg_agent jobs

From
Giovanni Biscontini
Date:
Hello all members, here's my question: db version 14.5
we have to create a pg_agent job with 2 steps:
1_step execute query: if query result is true or find > 0 records then process next step, else it stops executing job
2_step execute batch command
we tried to seta as 1_step the query:
SELECT EXISTS (SELECT * FROM mytable WHERE someconditions)
but even if it returns false 2_step is always executed.
Need some help, thanks in advance, Giovanni
--

best regards



Re: pg_agent jobs

From
Adrian Klaver
Date:
On 9/29/23 08:55, Giovanni Biscontini wrote:
> Hello all members, here's my question: db version 14.5
> we have to create a pg_agent job with 2 steps:
> 1_step execute query: if query result is true or find > 0 records then 
> process next step, else it stops executing job
> 2_step execute batch command
> we tried to seta as 1_step the query:
> SELECT EXISTS (SELECT * FROM mytable WHERE someconditions)
> but even if it returns false 2_step is always executed.
> Need some help, thanks in advance, Giovanni

The only thing I could see here:

https://www.pgadmin.org/docs/pgadmin4/development/pgagent_jobs.html

that might work is:

"
Use the On error drop-down to specify the behavior of pgAgent if it 
encounters an error while executing the step. Select from:

     Fail - Stop the job if you encounter an error while processing this 
step.

     Success - Mark the step as completing successfully, and continue.

     Ignore - Ignore the error, and continue.
"

So in the first step induce an error if the conditions are not met.


> -- 
> 
> best regards
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: pg_agent jobs

From
Giovanni Biscontini
Date:
Hi, Yes I know I've to use the "on error" property, but the cue is to raise an error if query has a false result: for my knowledges the only way to do it is to "create or replace" a PL/pgSQL procedure that than uses a "RAISE ERROR" inside, but isn't there a more "SQL only way to do it?
thanks in advance, G

Il giorno ven 29 set 2023 alle ore 18:15 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:
On 9/29/23 08:55, Giovanni Biscontini wrote:
> Hello all members, here's my question: db version 14.5
> we have to create a pg_agent job with 2 steps:
> 1_step execute query: if query result is true or find > 0 records then
> process next step, else it stops executing job
> 2_step execute batch command
> we tried to seta as 1_step the query:
> SELECT EXISTS (SELECT * FROM mytable WHERE someconditions)
> but even if it returns false 2_step is always executed.
> Need some help, thanks in advance, Giovanni

The only thing I could see here:

https://www.pgadmin.org/docs/pgadmin4/development/pgagent_jobs.html

that might work is:

"
Use the On error drop-down to specify the behavior of pgAgent if it
encounters an error while executing the step. Select from:

     Fail - Stop the job if you encounter an error while processing this
step.

     Success - Mark the step as completing successfully, and continue.

     Ignore - Ignore the error, and continue.
"

So in the first step induce an error if the conditions are not met.


> --
>
> best regards
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com



--

Cordiali saluti


Dott. Giovanni Biscontini

Software Developer & Database Architect


Strada Ponte Alto Sud, 74
41123 Modena (MO)
Tel.:   059.452094  
Cell:   3938282174                
Mail:
  biscontini.g@es2000.it 


_________________________________________________________________
Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD). Chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendo poi l'eventuale copia cartacea e la copia in formato elettronico.


Re: pg_agent jobs

From
Adrian Klaver
Date:
On 10/2/23 00:50, Giovanni Biscontini wrote:
> Hi, Yes I know I've to use the "on error" property, but the cue is to 
> raise an error if query has a false result: for my knowledges the only 
> way to do it is to "create or replace" a PL/pgSQL procedure that than 
> uses a "RAISE ERROR" inside, but isn't there a more "SQL only way to do it?
> thanks in advance, G
> 

1) select 1/count(*) from table where ...

2) Use anonymous function DO:

https://www.postgresql.org/docs/current/sql-do.html


FYI, there are other programs that do roughly the same thing. You might 
want to check them out to see if they work better for your tasks:

pg_cron
https://github.com/citusdata/pg_cron

pg_timetable
https://pg-timetable.readthedocs.io/en/master/


-- 
Adrian Klaver
adrian.klaver@aklaver.com