Обсуждение: Queues Problem

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;

Oh Yeah! A BRMS might be just what I’m look for! I did not know that such thing existed, but, there is always a but!! I don’t think we have the buget to use one, will investigate further.

Thanks