CODEWITHSUNDEEP
postgresqlstored-proceduresloopsplpgsql
Nurjan
Nurjan

Reputation: 6073

Postgresql plpgsql stored procedure update statement in loop

I need to write a plpgsql function which executes update statement in an infinite loop:

create function change_type() returns void as $$
begin
  loop  
    update table a set type = 1 where date < now(); 
  end loop;
end;
$$ LANGUAGE plpgsql;

When I call this function the update statement is not executed, although I can see that the loop is running. I ran the update statement as a single query and it works. How can I solve this problem?

Thanks

Upvotes: 1

Views: 3695

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659197

I guess what you are looking for is a cronjob to schedule a task at certain times (repeatedly). Try man crontab on a on UNIX / LINUX system. Preferably as system user postgres.

To run a query every 5 minutes, enter a line like this in your cron table. I use crontab -e under LINUX to edit my cron jobs:

* 5 * * * psql mydb -c 'UPDATE TABLE a SET type = 1 WHERE date < now()'

If you have more complex jobs, create a shell script with multiple SQL commands and call it like:

* 5 * * * psql mydb -f '/path/to/my_script.sh'

Or create a plpgsql function and call it:

* 5 * * * psql mydb -c 'SELECT myfunc()'

Set your system up so that the superuser postgres can log in without password (is default).

Or have a look at pgAgent that is shipped with pgAdmin.

Upvotes: 3

Jonathan Hall
Jonathan Hall

Reputation: 79784

The problem you are encountering is that a stored procedure in PostgreSQL automatically runs in its own transaction. So the updates are taking place, they just aren't visible until they are committed--which happens when the stored procedure exits (which it never does in this case).

The practical effect this will have is that your server will eventually run out of disk space or memory, or both (or some other built-in safety limit), once the active transaction becomes too big.

The solution is, as @JackManey suggested, to completely rethink your strategy. If you can explain what you're trying to accomplish, I'll bet my next pay check, there's a better way to accomplish it.

Upvotes: 4

Related Questions