Thread: Time-based trigger

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".

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

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:

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