Re: 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(at)minerva(dot)info<mailto:admin(at)minerva(dot)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.