postgresql update after 1 hour

Question

CREATE TABLE em_user
(
  user_id character(10),
  last_attempt integer,
  last_unsuccessfull_login timestamp with time zone,
  is_locked boolean DEFAULT false);

UPDATE em_user
SET is_locked = TRUE
           WHERE last_attempt >3 AND last_unsuccessfull_login=last_unsuccessfull_login+'2 minutes';     

I want to update my table after 1 hour in postgresql.

Answer 1

pgAgent is what you need. Take a look here: http://www.pgadmin.org/docs/1.8/pgagent.html

You can create jobs, which performs every hour, every 15 minutes etc.

EDIT:

In your specific case it would be better to lock user immediately by a trigger after some count of login failures.

The next step would be to run a pgAgent job every 15 min. With this job you can unlock this accounts again, which are locked more then 15 minutes. So you can prevent DoS attacks, which want to lock all your users.