Re: Queues Problem

On 6/8/2010 11:53 AM, uaca man wrote:
> Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite
> some time now, but most of my experience is consuming database, and for
> the current project we are without a proper DBA and they have to bear
> with me and so I must seek advice.
>
> I have a list of building and a queue and the user can start the
> construction of one or more building that will take some time to build.
> The problems lies with the fact this is a web browser game and the user
> can log in, star the construction and log off, to further aggravate the
> problem another user may take a look at the buildings, to add a little
> bit more, when a building is done it may have effect on the user
> population, gold and whatever the “imagination team” can come up with.
>
> Bottom line is: the construction has to be “concluded” with second’s
> precision.
>
> Lets say for a 20 thousand users server, it may have at most 20 thousand
> constructions started at the same time.
>
> To accomplish such behavior so far I could come up with two options:
>
> 1. Make a never ending function that will look at the BuildingQueue
> table every second and finish the construction.
>
> 2. Every time the user start a construction add a cron job for that
> construction to run 1 seconds after the construction is finished and
> call a function the will finish.
>
> For some reason I can not believe that a never ending function is a good
> idea and I don’t think cron jobs are meant to have 20 thousand jobs.
>
> Anyone care to share?
>
> Tables:
>
> Create table "Building"
>
> (
>
> "idBuilding" Serial NOT NULL,
>
> "description" Varchar(200),
>
> "time" Integer,
>
> primary key ("idBuilding")
>
> ) Without Oids;
>
> Create table "BuildingQueue"
>
> (
>
> "idBuilding" Integer NOT NULL,
>
> "start" Timestamp,
>
> "end" Timestamp,
>
> primary key ("idBuilding")
>
> ) Without Oids;
>
> Alter table "BuildingQueue" add foreign key ("idBuilding") references
> "Building" ("idBuilding") on update restrict on delete restrict;
>

How about you figure out when "it should be finished", and if now() is
after "should be" then mark the building as completed with a finish time
of "should be".

Then you can run it whenever (every hour, on login, etc), and it'll
catch up, and mark buildings as complete with the appropriate time.

-Andy