Re: Queues Problem

This would work except for one thing, the building may affect another
buildings, Consider this:

the user starts one construction that will finish in 10 minutes and the
building will give a bonus of +5 gold each seconds for the user. This has to
be available in the seconds that the build is done and not one hour late
because the user will lose the bonus for one hour.

2010/6/8 Andy Colson <andy(at)squeakycode(dot)net>

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