database 有没有办法设置一个“到期”时间，之后在 PostgreSQL 中自动删除数据条目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26046816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to set an "expiry" time, after which a data entry is automatically deleted in PostgreSQL?
提问by Pensierinmusica
Is there any way to set some sort of "expiry" time on data entries in PostgreSQL? I'm thinking about something equivalent to EXPIREin Redis.
有没有办法在PostgreSQL 中的数据条目上设置某种“到期”时间？我正在考虑类似于EXPIREin Redis 的东西。
I'm not looking to store a timestamp and then manually code some sort of cronjob to check what entries have expired.
我不想存储时间戳，然后手动编写某种cron作业来检查哪些条目已过期。
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.
我试图找出 PostgreSQL 中是否有任何本机功能可以提供这种功能，或者是否有必要为将来的版本请求此类功能。
回答by Brett DiDonato
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/
没有内置的过期功能，但是如果您的目标是自动使字段过期并在您的数据库中包含逻辑（因此没有像 cron 作业这样的外部依赖项），那么您总是可以编写触发器。以下是从表中删除时间戳早于 1 分钟的行的触发器示例。每当新行插入同一个表时，它就会执行。显然，您可以根据需要将触发器设置为在其他条件和各种到期日期上执行。我使用以下网站作为此基础：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)
回答by Richard Huxton
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.
除了 (1) 只是“过期”时间戳或 (2) 时间戳 + cron-job/pgAgent 之外，我看不到它的作用。
It doesn't sound like a general feature that would be added to the core. You could quite simply code an extensionto handle this sort of thing, with either a tick called from a cron-job or perhaps a background-workerprocess.
这听起来不像是要添加到核心中的通用功能。你可以很简单地编写一个扩展来处理这类事情，或者从 cron-job 调用一个滴答声，或者一个后台工作进程。
I don't see anything on pgxn, so presumably there's not been much demand for it yet.
我在pgxn上没有看到任何东西，所以大概对它的需求还不大。
