On Wed, Sep 15, 2010 at 5:15 AM, Dann Corbit <DCorbit@xxxxxxxxx> wrote: >> -----Original Message----- >> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- >> owner@xxxxxxxxxxxxxx] On Behalf Of John R Pierce >> Sent: Tuesday, September 14, 2010 8:41 PM >> To: Michael Hull >> Cc: pgsql-general@xxxxxxxxxxxxxx >> Subject: Re: Search then Delete Performance >> >> On 09/14/10 5:55 PM, Michael Hull wrote: >> > So fairly simply, I have a daemon running on a machine, which >> accesses >> > this DB. Clients connect and request the details for say 1000 >> > simulations, at which point the daemon takes 1000 entries from the >> > unassigned table and moves them to the assigned table. The once the >> > client is finished with those jobs, it signals this to the daemon, >> > which then move those jobs from 'assigned' to 'complete'. >> > >> > So this is fairly simple to implement, but my problem is that it is >> very slow. >> > >> > >> >> instead of moving data from one table to another, it might be better to >> just have a table of simulations, then another table which just >> contains >> the PK of each simulation, and a flag that says its assigned or >> unassigned (and maybe the client its assigned to? and anything else >> thats related to this assignment?)... so instead of moving your big >> table rows, which involves deleting them from one table and inserting >> them into another, you just update the row of this small table. if >> you >> create this small table with a fillfactor like 75%, the updates likely >> will easily be handled by HOT > > Or just a status integer in the main table along the lines of: > 1 = unassigned > 2 = assigned > 3 = running > 4 = completed > Etc. > > And then update the status as appropriate and check the status as needed. > > If you want until a batch is done, you would also be able to update like this: > > UPDATE jobs SET status = 4 WHERE status = 3 > > As you like, with a single statement. > > There are lots of job schedulers on SOURCEFORGE. > http://sourceforge.net/search/?words=scheduler+workflow&type_of_search=soft&sort=latest_file_date&sortdir=desc&limit=100 > > Thanks everyone for the input - I will have an investigate Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
