Thread: Time-based trigger

Time-based trigger

From
Robert Sosinski
Date:
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.

Thanks,

-- 
Robert Sosinski

Re: Time-based trigger

From
Szymon Guz
Date:
On 18 September 2012 17:59, Robert Sosinski <rsosinski@ticketevolution.com> 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.

Thanks,

-- 
Robert Sosinski


It is not possible right now, however I'd rather create some cron job which updates the boolean value if needed and runs each minute.

- Szymon

Re: Time-based trigger

From
Rob Sargent
Date:
On 09/18/2012 10:03 AM, Szymon Guz wrote:
> On 18 September 2012 17:59, Robert Sosinski
> <rsosinski@ticketevolution.com <mailto:rsosinski@ticketevolution.com>>
> 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.
>
>     Thanks,
>
>     --
>     Robert Sosinski
>
>
> It is not possible right now, however I'd rather create some cron job
> which updates the boolean value if needed and runs each minute.
>
> - Szymon
Yeah, seems like a boolean would give no indication of when it was set.
I would rather have value "held_at" as a timestamp and the cron job
would null those "held_at" more than 5 minutes ago.  Not sure if an
index on "held_at" would help in "select where held_at is not null".


Re: Time-based trigger

From
Steve Crawford
Date:
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




Re: Time-based trigger

From
Robert Sosinski
Date:
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)
To make changes to your subscription: