Pensierinmusica
Pensierinmusica

Reputation: 6958

Is there a way to set an "expiry" time, after which a data entry is automatically deleted in PostgreSQL?

Is there any way to set some sort of "expiry" time on data entries in PostgreSQL? I'm thinking about something equivalent to EXPIRE in Redis.

I'm not looking to store a timestamp and then manually code some sort of cron job to check what entries have expired.

I'm trying to find out if there's any native feature in PostgreSQL that would provide this kind of functionality, or if it would make sense to request such feature for future releases.

Upvotes: 219

Views: 186455

Answers (4)

Jose V
Jose V

Reputation: 1864

Sad that this isn't a feature. So I am thinking of a quick solution (no cron job lol).

Add a "expiresAt" column in your table. Then write two queries in your application code (not the trigger, yikes).

  1. SELECT rows WHERE the data has not expired.
  2. DELETE rows WHERE the data has expired.

Then in your app whenever you need to query the table, you call query #1 synchronously and return the result quickly. However, right after the first query returns, you call query #2 asynchronously, without waiting for its result to carry on with your program.

This will be much more consistent behavior than the answer with the trigger, you will never read an expired row. This also keeps cleaning up the table at intervals that are allegedly good enough for most scenarios.

Upvotes: 0

user6880399
user6880399

Reputation: 161

Nope, PG does not. But you do get these with Google Cloud Spanner. It does have an SQL interface and currently support postgres dialect too.

Upvotes: -2

Brett DiDonato
Brett DiDonato

Reputation: 1952

There is no built in expiration feature but if your goal is to automatically expire fields and have the logic contained within your database (and thus no outside dependency like a cron job) then you can always write a trigger. Below is an example of a trigger that deletes rows from a table that have a timestamp of older than 1 minute. It is executed whenever a new row is inserted into that same table. You can obviously set the trigger to execute on other conditions and for various expiration dates as need be. I used the following website as a basis for this: http://www.the-art-of-web.com/sql/trigger-delete-old/

CREATE TABLE expire_table (
    timestamp timestamp NOT NULL DEFAULT NOW(),
    name TEXT NOT NULL
);

INSERT INTO expire_table (name) VALUES ('a');
INSERT INTO expire_table (name) VALUES ('b');
INSERT INTO expire_table (name) VALUES ('c');

select * from expire_table;
         timestamp          | name 
----------------------------+------
 2014-09-26 15:33:43.243356 | a
 2014-09-26 15:33:45.222202 | b
 2014-09-26 15:33:47.347131 | c
(3 rows)

CREATE FUNCTION expire_table_delete_old_rows() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  DELETE FROM expire_table WHERE timestamp < NOW() - INTERVAL '1 minute';
  RETURN NEW;
END;
$$;

CREATE TRIGGER expire_table_delete_old_rows_trigger
    AFTER INSERT ON expire_table
    EXECUTE PROCEDURE expire_table_delete_old_rows();

INSERT INTO expire_table (name) VALUES ('d');

select * from expire_table;
         timestamp          | name 
----------------------------+------
 2014-09-26 15:36:56.132596 | d
(1 row)

Upvotes: 173

Richard Huxton
Richard Huxton

Reputation: 22943

No. There is no such feature.

I can't see what it does more than either (1) just an "expired" timestamp does or (2) timestamp + cron-job/pgAgent.

It doesn't sound like a general feature that would be added to the core. You could quite simply code an extension to handle this sort of thing, with either a tick called from a cron-job or perhaps a background-worker process.

I don't see anything on pgxn, so presumably there's not been much demand for it yet.

Upvotes: 13

Related Questions