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 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
Re: How to implement expiration in PostgreSQL?
> 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?
Re: How to implement expiration in PostgreSQL?
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 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 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 > ad...@minerva.info<mailto:ad...@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.
Re: How to implement expiration in PostgreSQL?
> 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.
Re: How to implement expiration in PostgreSQL?
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 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., 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 > ad...@minerva.info<mailto:ad...@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.
Is replacing transactions with CTE a good idea?
Hi all, From application’s standpoint, it seems using CTE saves a lot work. You no longer need to parse values out only to pass them back in, and only one round-trip to the db server. If I’m not wrong, CTE is equivalent to serializable transactions? So I guess the downsize is that quarries can’t be run in parallel? If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road?
Re: Is replacing transactions with CTE a good idea?
> When you deal with updates/deletes, things can be trickier Care to expand why they are tricker? I presume they run the risk of being referenced more than once? > On Apr 1, 2021, at 10:58 PM, Brian Dunavant wrote: > > >> On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: >> If I decide to replace all my transaction code with CTE, will I shoot myself >> in the foot down the road? > > I do this all the time and makes code way cleaner. It's very > straightforward with inserts queries. When you deal with updates/deletes, > things can be trickier. I usually leave these in a transaction if there is > any concern. > > They can also be hard for future programmers that may not understand SQL. > Make sure you comment your queries for maintainability long term. > > I have yet to regret replacing a transaction with a CTE over the past decade. > YMMV >
Re: Is replacing transactions with CTE a good idea?
No, but are they equivalent to serializable transactions? > On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: > > > > > >> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >> Hi all, >> >> From application’s standpoint, it seems using CTE saves a lot work. You no >> longer need to parse values out only to pass them back in, and only one >> round-trip to the db server. >> >> If I’m not wrong, CTE is equivalent to serializable transactions? So I guess >> the downsize is that quarries can’t be run in parallel? > > I do not think a CTE changes the isolation level. >> >> If I decide to replace all my transaction code with CTE, will I shoot myself >> in the foot down the road? > > > Dave Cramer > www.postgres.rocks
Re: Is replacing transactions with CTE a good idea?
Sorry, my mistake. I misunderstood serializable. Are queries in a CTE equivalent to those in a repeatable read transaction? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > > > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >> No, but are they equivalent to serializable transactions? > > No, they are not. > > > > Dave Cramer > www.postgres.rocks >> >>>> On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: >>>> >>> >>> >>> >>> >>>> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >>>> Hi all, >>>> >>>> From application’s standpoint, it seems using CTE saves a lot work. You no >>>> longer need to parse values out only to pass them back in, and only one >>>> round-trip to the db server. >>>> >>>> If I’m not wrong, CTE is equivalent to serializable transactions? So I >>>> guess the downsize is that quarries can’t be run in parallel? >>> >>> I do not think a CTE changes the isolation level. >>>> >>>> If I decide to replace all my transaction code with CTE, will I shoot >>>> myself in the foot down the road? >>> >>> >>> Dave Cramer >>> www.postgres.rocks
Re: Is replacing transactions with CTE a good idea?
Ah, I see what you mean. You still have to wrap a CTE inside a transaction to specify the isolation level? By default, queries in a CTE run with the read committed isolation level? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > > > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >> No, but are they equivalent to serializable transactions? > > No, they are not. > > > > Dave Cramer > www.postgres.rocks >> >>>> On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: >>>> >>> >>> >>> >>> >>>> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >>>> Hi all, >>>> >>>> From application’s standpoint, it seems using CTE saves a lot work. You no >>>> longer need to parse values out only to pass them back in, and only one >>>> round-trip to the db server. >>>> >>>> If I’m not wrong, CTE is equivalent to serializable transactions? So I >>>> guess the downsize is that quarries can’t be run in parallel? >>> >>> I do not think a CTE changes the isolation level. >>>> >>>> If I decide to replace all my transaction code with CTE, will I shoot >>>> myself in the foot down the road? >>> >>> >>> Dave Cramer >>> www.postgres.rocks
Re: Is replacing transactions with CTE a good idea?
I had the impression that since they are chained together, somehow they run “tighter” 😂. Thanks for pointing out that mistake. > On Apr 1, 2021, at 11:25 PM, Dave Cramer wrote: > > > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? > > Dave Cramer > www.postgres.rocks > > >> On Thu, 1 Apr 2021 at 11:20, Glen Huang wrote: >> Sorry, my mistake. I misunderstood serializable. Are queries in a CTE >> equivalent to those in a repeatable read transaction? >> >>>> On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: >>>> >>> >>> >>> >>>> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >>>> No, but are they equivalent to serializable transactions? >>> >>> No, they are not. >>> >>> >>> >>> Dave Cramer >>> www.postgres.rocks >>>> >>>>>> On Apr 1, 2021, at 11:04 PM, Dave Cramer >>>>>> wrote: >>>>>> >>>>> >>>>> >>>>> >>>>> >>>>>> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >>>>>> Hi all, >>>>>> >>>>>> From application’s standpoint, it seems using CTE saves a lot work. You >>>>>> no longer need to parse values out only to pass them back in, and only >>>>>> one round-trip to the db server. >>>>>> >>>>>> If I’m not wrong, CTE is equivalent to serializable transactions? So I >>>>>> guess the downsize is that quarries can’t be run in parallel? >>>>> >>>>> I do not think a CTE changes the isolation level. >>>>>> >>>>>> If I decide to replace all my transaction code with CTE, will I shoot >>>>>> myself in the foot down the road? >>>>> >>>>> >>>>> Dave Cramer >>>>> www.postgres.rocks
Re: Is replacing transactions with CTE a good idea?
This discussion really questioned my understanding of concurrency in PostgreSQL, thanks a lot. I gave the corresponding part of the doc some more read, and I’m now in the option that insolation level has no effect on CTEs, but please correct me if I’m wrong. If notionally all queries execute at the same time, even if they are executed in read committed, they behave like repeatable read. This should also be true for serializable, since the anomalies that isolation level tries to address won’t occur in a CTE. @Bruce The gotchas you mentions are really interesting, I have a follow up question if you don’t mind: CREATE foo(n int); CREATE bar(n int REFERENCES foo(n)); WITH t AS ( INSERT INTO foo(n) VALUES(1) ) INSERT INTO bar(n) VALUES(1); Is the CTE guaranteed to success or it’s actually unspecified? I ran it a couple times without issues, but I can’t be sure. If it’s unspecified any idea how should I correct it? > On Apr 6, 2021, at 2:41 AM, Bruce Momjian wrote: > > On Mon, Apr 5, 2021 at 02:32:36PM -0400, Dave Cramer wrote: >> On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: >> I think we are in agreement. My point was that WITH queries don't change the >> isolation semantics. > > My point is that when you combine individual queries in a single WITH > query, those queries run together with snaphot behavior as if they were > in a repeatable-read multi-statement transaction. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. >
