Re: Time-based trigger

On 09/18/2012 08:59 AM, Robert Sosinski wrote:
> We have a table, which has items that can be put on hold of 5 minutes
> (this is for an online store) once they are placed into a cart. What
> we need is for this hold to automatically expire after 5 minutes.
> Right now, we put a time stamp into the row (called hold_until) at 5
> minutes into the future, and select items where hold_until is less
> then now().
>
> Would it be possible to change this to using a boolean that is set to
> true when item is put on hold, and have something like a time-based
> trigger automatically update the held boolean to false after 5 minutes
> pass.
>
I'm surmise by your domain that the items in question are not inventory
that you need to check against (reserved one of 15 lamps) but unique
individual items like event seats. While there aren't specifically
time-based triggers there are plenty of other options depending on the
nature of your queries.

There is a good possibility that the time column won't be used in
queries. If the items table is tickets for many events then an index on
the event will likely be used with the time column as a filter on the
index results. You may even be able to create a multi-column index that
will better restrict the results. Something like event/seat-category or
whatever fits your use-case. I'm sure that once an item is purchased it
is either removed or flagged in which case the event/available might be
a good index.

My first inclination would be to make the hold-till column "not-null
default now()" (or now() - '1 second'::interval if you prefer) which
would make your query work fine without additional null checking, would
work well as an indexed column if you need to see *all* reserved or
non-reserved items, and would not require any external cron-job cleaning
support.

Cheers,
Steve