SQL: PostgreSQL trigger for deleting old records
System log files can be easily managed using logrotate to archive old entries, but when logging to a database table you will typically need to write your own script.
In the following example we've created a TRIGGER that is called automatically by PostgreSQL that has the effect of deleting 'stale' records.
The obvious advantage being that it's always associated with the database and will be included in any backups. It also doesn't require any CRON script.
In this simple example we've created a log of the ip address of visitors to a certain part of a website, and the timestamp of each visit:
CREATE TABLE limiter ( ip varchar NOT NULL, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP );
Data is added using a simple INSERT statement called from PHP:
<?PHP $visitorip = pg_escape_string($_SERVER['REMOTE_ADDR']); pg_query("INSERT INTO limiter (ip) VALUES ('$visitorip')"); ?>
This logs thousands of entries each day, but for our purposes the data only needs to be kept for up to 48 hours, so at regular intervals we call a DELETE function:
<?PHP pg_query("DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days'"); ?>
Simple enough, and it works, but lets see how we can better automate this using a trigger.
Creating the TRIGGER function
The following code creates a basic trigger that deletes old rows
from the table as before:
CREATE FUNCTION delete_old_rows() RETURNS trigger
DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days';
We plan to call this using AFTER INSERT so the RETURN value is not used can can be 'NULL'.
Calling the TRIGGER
Finally, we set the trigger to be called every time a new record is inserted into the table:
CREATE TRIGGER trigger_delete_old_rows AFTER INSERT ON limiter EXECUTE PROCEDURE delete_old_rows();
And we're done. Every time a new row is inserted into this table, our trigger function is called and deletes any records older than 48 hours.
As a final step we're going to modify the trigger so that it also reports the number of rows that have been deleted:
CREATE FUNCTION delete_old_rows() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE row_count int; BEGIN DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days'; IF found THEN GET DIAGNOSTICS row_count = ROW_COUNT; RAISE NOTICE 'DELETEd % row(s) FROM limiter', row_count; END IF; RETURN NULL; END; $$;
Now when any rows are deleted a NOTICE is raised:
postgres=# INSERT INTO limiter VALUES ('18.104.22.168'); NOTICE: DELETEd 136 row(s) FROM limiter INSERT 0 1
For those paying attention, note that we're not actually making use of the NOTICE text in our PHP code, but it could be logged for debugging purposes in the PostgreSQL log file.
VIEWing TRIGGER details
From the command-line interface you can view the TABLE details as follows:
# \d limiter Table "public.limiter" Column | Type | Modifiers -----------+-----------------------------+------------------------ ip | character varying | not null timestamp | timestamp without time zone | not null default now() Triggers: trigger_delete_old_rows AFTER INSERT ON limiter FOR EACH STATEMENT EXECUTE PROCEDURE delete_old_rows()
And to see the TRIGGER definition and code:
# \x Expanded display is on. # \df+ delete_old_rows List of functions -[ RECORD 1 ]-------+------------------------------------------------------------------- Schema | public Name | delete_old_rows Result data type | trigger Argument data types | Type | trigger Security | invoker Volatility | volatile Owner | postgres Language | plpgsql Source code | | DECLARE | row_count int; | BEGIN | DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days'; | IF found THEN | GET DIAGNOSTICS row_count = ROW_COUNT; | RAISE NOTICE 'DELETEd % row(s) FROM limiter', row_count; | END IF; | RETURN NULL; | END;
Using just \df instead of \df+ you will only see the Scheme, Name, Result and Argument data types, and Type. Not particularly useful
- Variable containing the number of rows affected by previous DELETE?
- PostgreSQL: CREATE TRIGGER
- PostgreSQL: Overview of Trigger Behavior
- PostgreSQL: Obtaining the Result Status