Search Postgresql Archives

Re: Time-based trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux