Queues Problem

From: uaca man <uacaman(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Queues Problem
Date: 2010-06-08 16:51:17
Message-ID: AANLkTilX8f9HU-Hts0RluXCn2JX92W389-8sUkNLL2bT@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Browse pgsql-general by date

  From Date Subject
Next Message uaca man 2010-06-08 16:53:34 Queues Problem
Previous Message John Gage 2010-06-08 16:42:05 Re: Cognitive dissonance