pg_agent jobs

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

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 <verrini.e@es2000.it>
Web: https://www.es2000.it <http://www.es2000.it/&gt;

_________________________________________________________________
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.

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