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@postgresql.org > (mailto:pgsql-general@postgresql.org)) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >
