Обсуждение: EXPIRE as a statement

Hello,

This is my first time posting on the list. Also, I was trying to find
something on the list's history on this topic but without results.

My idea is new statement with roughly the following format (similar to update):

EXPIRE FROM my_table   AT my_timestamp   WHERE my_condition

or

EXPIRE FROM my_table   AFTER my_interval   WHERE my_condition

The rows that match the `my_condition` will be deleted when the
current timestamp reaches my_timestamp or, in the second case, exactly
my_interval time after the execution.

One concern would of course be the FK integrity, but the regular
DELETE takes into account the RESTRICT / SET NULL / SET DEFAULT /
CASCADE specification on the FK, so this statement would take those
into account as well.

As a consequence, a row function ttl(), i.e. time-to-live, would be
appropriate (not quite clear about this, though). Basically, would
return an interval until the deletion of the row takes place, or none
if the there's no expiration scheduled.

I know for example that redis has this feature, the EXPIRE / EXPIREAT
/ TTL commands.
http://redis.io/commands/expire


Kind regards,
Blagoj Petrushev

Blagoj,

* Blagoj Petrushev (b.petrushev@gmail.com) wrote:
> The rows that match the `my_condition` will be deleted when the
> current timestamp reaches my_timestamp or, in the second case, exactly
> my_interval time after the execution.

An in-PG version of cron has been discussed before and seems like it
would meet your need here.  It's certainly something which I would like
to see happen, most likely using the background worker infrastructure.
I don't know of anyone actively working on it though.

Your proposal does have the interesting property that it could be
implemented without a cron-like process by keeping track of the
expiration time and then just ignoring the records even if they're
there.  That could be done with a view, of course, but as you point out,
it'd need backend/core support if it's going to work for FK
relationships or similar.

> As a consequence, a row function ttl(), i.e. time-to-live, would be
> appropriate (not quite clear about this, though). Basically, would
> return an interval until the deletion of the row takes place, or none
> if the there's no expiration scheduled.

For a view-based approcah, this function could be trivially written to
go against the underlying table to return the answer.
Thanks,
    Stephen

Blagoj Petrushev wrote
> I know for example that redis has this feature, the EXPIRE / EXPIREAT
> / TTL commands.
> http://redis.io/commands/expire

Redis seems to have decided that limiting the extent to which EXPIRE works
is necessary in order to maintain performance; I'd be very worried about a
similar situation here - in addition to solving the master-slave replication
issue.

One random thought I had would that this would make an excellent addition
for an UNLOGGED table; if even just for an initial implementation phase.  It
would seem that both features would share similar use cases and the main
current limitation of UNLOGGED tables is the same one that make implementing
EXPIRE difficult - replication - and eating some of the savings from not
WAL-logging would still result is reasonably good performance; as long as
only UNLOGGED tables are impacted.

One question is how much actual speed benefit can be had by implementing
this in core instead of advising users to put an "expires_at" field on those
tables that need them and to make sure usage of the table involves limiting
that field to "<= now()"?  If it would be possible to only look for the
field on tables defined to have it, and not all tables, this becomes less of
an issue and the advantage of having it built-in when used becomes dominate
- though someone would still need to code it; which doesn't seem trivial for
normal tables; and the option of an explicitly user-managed interface seems
quite viable.

David J.


David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/EXPIRE-as-a-statement-tp5802378p5802386.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> Blagoj Petrushev wrote
>> I know for example that redis has this feature, the EXPIRE / EXPIREAT
>> / TTL commands.
>> http://redis.io/commands/expire

One thought here is that recent versions of the SQL standard contain some
temporal-data features, which might well be usable for the purposes
envisioned here.  I'd much rather see us implementing SQL-spec features
than randomly invented ones, so please take a look into the spec before
going too far with EXPIRE.

Having said that, I rather doubt that any such feature is likely to be
worth the implementation complexity and overhead.  The previous suggestion
of a cron-like daemon is probably far more likely to be acceptable;
it'd be less work, have a lot more use-cases, and be less likely to add
overhead that's useless to users who don't need the feature.
        regards, tom lane