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.
The situation
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($conn, $_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
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days';
RETURN NULL;
END;
$$;
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.
Adding DIAGNOSTICS
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 ('1.2.3.4');
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
References
- Stack Overflow: Variable containing the number of rows affected by previous DELETE?
- PostgreSQL: PostgreSQL: CREATE TRIGGER
- PostgreSQL: PostgreSQL: Overview of Trigger Behavior
- PostgreSQL: PostgreSQL: Obtaining the Result Status
DimonB 25 November, 2022
add
IF random () < 0.001 THEN
DELETE FROM
END IF
to make db admin happier
YG 4 November, 2021
Very ridiculous solution!
If this is a heavy maintenance table and thousands of records are inserted every day what a huge overhead and cpu cost to fire a trigger thousands times!
I do not think any DBA would approve such approach