Thread: How to implement expiration in PostgreSQL?
Hi,
I guess this question has been asked a million times, but all solutions I can find online don’t really work well for my
case.I’ll list them here and hope someone can shed some light.
My use case is to implement joining clubs that require entrance fee:
1. Each clubs only allows maximum number of members.
2. When a person decides to join a club, the seat is reserved for a limited amount of time. If that person fails to pay
withinthat period, the seat will be open again
I want to write a query that can quickly list all clubs that still have open seats and #2 is where I want expiration to
happen.
The solutions I find so far:
1. Exclude closed clubs in queries and periodically delete expired members
I can’t come up with a query that can accomplish this in an efficient way.
WITH seated_member AS (
SELECT
club_id,
count(member_id) AS num_seated_member
FROM member
WHERE paid OR join_time > now() - ‘1h’::interval
GROUP BY club_id
),
open_member AS (
SELECT
club_id,
max_num_member - coalesce(num_seated_member, 0) AS num_open_member
FROM club LEFT JOIN seated_member USING(club_id)
)
SELECT club_id AS open_club
FROM open_member
WHERE num_open_member > 0
This requires going through all seated members, which can potentially be large and takes a long time.
I can of course add an num_open_member column to the club table and index it, but the problem becomes how to
automaticallyupdate it when a member expires, which take us back to square one.
All following solutions assume I add this column and seek to find a way to update it automatically.
2. Run a cron job
This won’t work because the number is updated only after the cron job is run, which only happens at intervals.
3. Update the column before running any related queries
This requires I execute DELETE and UPDATE queries before all seat related queries. It’s hard to manage and seems to
slowdown all such queries.
4. pg_cron
My environment wouldn’t allow me to install 3rd-party extensions, but even if I could, it seems pg_cron run cron jobs
sequentially.I’m not sure it works well when I need to add a cron job for each newly joined member.
—
I’m not aware any other solutions. But the problem seems banal, and I believe it has been solved for a long time. Would
reallyappreciate it if someone could at least point me in the right direction.
Regards,
Glen
On 01/04/2021 02:51, Glen Huang wrote: > Hi, > > I guess this question has been asked a million times, but all solutions I can find online don’t really work well for mycase. I’ll list them here and hope someone can shed some light. > > My use case is to implement joining clubs that require entrance fee: > > 1. Each clubs only allows maximum number of members. > 2. When a person decides to join a club, the seat is reserved for a limited amount of time. If that person fails to paywithin that period, the seat will be open again > > I want to write a query that can quickly list all clubs that still have open seats and #2 is where I want expiration tohappen. > > The solutions I find so far: > > 1. Exclude closed clubs in queries and periodically delete expired members > > I can’t come up with a query that can accomplish this in an efficient way. > > WITH seated_member AS ( > SELECT > club_id, > count(member_id) AS num_seated_member > FROM member > WHERE paid OR join_time > now() - ‘1h’::interval > GROUP BY club_id > ), > open_member AS ( > SELECT > club_id, > max_num_member - coalesce(num_seated_member, 0) AS num_open_member > FROM club LEFT JOIN seated_member USING(club_id) > ) > SELECT club_id AS open_club > FROM open_member > WHERE num_open_member > 0 > > This requires going through all seated members, which can potentially be large and takes a long time. > > I can of course add an num_open_member column to the club table and index it, but the problem becomes how to automaticallyupdate it when a member expires, which take us back to square one. > > All following solutions assume I add this column and seek to find a way to update it automatically. > > 2. Run a cron job > > This won’t work because the number is updated only after the cron job is run, which only happens at intervals. > > 3. Update the column before running any related queries > > This requires I execute DELETE and UPDATE queries before all seat related queries. It’s hard to manage and seems to slowdown all such queries. > > 4. pg_cron > > My environment wouldn’t allow me to install 3rd-party extensions, but even if I could, it seems pg_cron run cron jobs sequentially.I’m not sure it works well when I need to add a cron job for each newly joined member. > > — > > I’m not aware any other solutions. But the problem seems banal, and I believe it has been solved for a long time. Wouldreally appreciate it if someone could at least point me in the right direction. > > Regards, > Glen > Possibly keep your count of members updated via a trigger? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
> Possibly keep your count of members updated via a trigger? But how to evoke the trigger when the count of members should be updated by a timeout, i.e., the person’s pay is due?
On 01/04/2021 14:28, Glen Huang wrote: >> Possibly keep your count of members updated via a trigger? > But how to evoke the trigger when the count of members should be updated by a timeout, i.e., the person’s pay is due? I'd run a cron job that triggers a function call which would make the necessary expiry tests and set the status accordingly. Maybe run the cron once an hour or once a day depending on the granularity of your needs? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
Good suggestion, thanks. I ended up running the cron jobs in my app, one for each newly joined member, to get the smallest granularity possible. > On Apr 1, 2021, at 9:43 PM, Tim Clarke <tim.clarke@minerva.info> wrote: > > On 01/04/2021 14:28, Glen Huang wrote: >>> Possibly keep your count of members updated via a trigger? >> But how to evoke the trigger when the count of members should be updated by a timeout, i.e., the person’s pay is due? > > > I'd run a cron job that triggers a function call which would make the > necessary expiry tests and set the status accordingly. Maybe run the > cron once an hour or once a day depending on the granularity of your needs? > > Tim Clarke MBCS > IT Director > Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 > > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 > Web: https://www.manifest.co.uk/ > > > > Minerva Analytics Ltd - A Solactive Company > 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom > > ________________________________ > > Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> > Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
On 01/04/2021 14:47, Glen Huang wrote: > Good suggestion, thanks. > > I ended up running the cron jobs in my app, one for each newly joined member, to get the smallest granularity possible. (This list bottom-posts by convention) I'd say that was onerous and you could get the same effect with a well-crafted query that targetted only those that might possibly expire. Then you'd have only one cron job to manage. Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
> I'd say that was onerous and you could get the same effect with a well-crafted query that targetted only those that might possibly expire. I wish one cron job could rule them all, but since a person can decide to join at any time, her expiration (e.g., after 1hour) can also happen at any time. So one cron job won’t cut it if a member’s expiration has to bee accurate.
On 01/04/2021 15:23, Glen Huang wrote: > I wish one cron job could rule them all, but since a person can decide to join at any time, her expiration (e.g., after1 hour) can also happen at any time. So one cron job won’t cut it if a member’s expiration has to bee accurate. One cron job running every 5 minutes should do? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
Hmm, maybe letting people linger on for a couple more minutes isn’t unacceptable. And it simplifies the code a lot. Good idea, thanks. > On Apr 1, 2021, at 10:31 PM, Tim Clarke <tim.clarke@minerva.info> wrote: > > > On 01/04/2021 15:23, Glen Huang wrote: >> I wish one cron job could rule them all, but since a person can decide to join at any time, her expiration (e.g., after1 hour) can also happen at any time. So one cron job won’t cut it if a member’s expiration has to bee accurate. > > > One cron job running every 5 minutes should do? > > > Tim Clarke MBCS > IT Director > Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 > > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 > Web: https://www.manifest.co.uk/ > > > > Minerva Analytics Ltd - A Solactive Company > 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom > > ________________________________ > > Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> > Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
On Thu, Apr 1, 2021 at 7:23 AM Glen Huang <heyhgl@gmail.com> wrote:
> I'd say that was onerous and you could get the same effect with a
well-crafted query that targetted only those that might possibly expire.
I wish one cron job could rule them all, but since a person can decide to join at any time, her expiration (e.g., after 1 hour) can also happen at any time. So one cron job won’t cut it if a member’s expiration has to bee accurate.
There are indeed a number of moving parts here but I would suggest that setting up a more static data model would be a well advised starting position. Write queries that take the expiration timestamp into account, noting whether the time it represents is in the past or future. The reduced update burden will be a boon for both complexity of the updates themselves as well as for your I/O subsystem that has to handle vacuuming all of those dead tuples.
David J.
On Thu, 1 Apr 2021 09:51:38 +0800
Glen Huang <heyhgl@gmail.com> wrote:
Hi,
If you are in a position to modify your design, I believe your problem comes from this part :
> WHERE paid OR join_time > now() - ‘1h’::interval
which suggests that there is a 'paid' column being updated. I learned that the proper way to structure a database to
maintainmembership and save a lot of grief is the following :
create table seated_member(
name text not null,
...,
join_time date not null default now,
validity integer not null default 1
);
where validity is a number of whatever unit is appropriate; typically 'year' for a club membership (newspapers use
'issuenumber' instead of join_time to account for strikes, when no paper is issued). In your case, 'hour' I suppose.
All you need to do when the member pays is to update the 'validity' field with the proper amount of units. This makes
forvery simple and efficient queries to retrieve the data, and you only need to write :
WHERE now() < join_time + 'validity hours'::interval
to retrieve valid accounts.
Accounts expire automatically, deleting them can wait; it also makes it easier to send reminders before the expiration
date
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance