Re: Time-based trigger

Good deduction Steve.

Looks like we are going to use the timestamp idea. This way, the ticket will be open for sale again the second it's hold_until time lapses. The cronjob was a close second, but there could be a lag-time between runs.

Thanks everyone for all the help.

--
Robert Sosinski

On Tuesday, September 18, 2012 at 4:04 PM, Steve Crawford wrote:

> 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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org (mailto:pgsql-general(at)postgresql(dot)org))
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>