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
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.
SELECT *,date <= NOW() AS type ... or SELECT * ... WHERE date <= NOW() should probably do it. If you really do need a periodic update, use a cron job as JackManey suggested.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).
