How to implement expiration in PostgreSQL?

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 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 within 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 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 automatically update 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 slow down 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 really 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 5803 1687 | 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 you must 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 Manifest Voting 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 5803 1687 | 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 you must 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 Manifest Voting 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 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 5803 1687 | 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 you must 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 Manifest Voting 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 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.

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

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 5803 1687 | 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 you must 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 Manifest Voting 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 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 maintain membership 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 'issue number' 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 for very 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