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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
