Re: Time-based trigger

[Robert Sosinski <rsosinski@xxxxxxxxxxxxxxxxxxx>]

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:

http://www.postgresql.org/mailpref/pgsql-general