[SQL] Selecting a non-locked row.
I'm in the process of writing an application, and I'm not sure how to properly solve it. I have a table with records in, and they either still need to be processed or not. Several people will be using the table at the same time. I was thinking about using a select for update limit 1, but the other persons will of course try to select the same record and wait until that one is released. The locks are held for a long period of time. Is there a way to select a row that is not locked yet? Kurt ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Selecting a non-locked row.
Kurt, > I'm in the process of writing an application, and I'm not sure > how to properly solve it. > > I have a table with records in, and they either still need to be > processed or not. Several people will be using the table at the > same time. > > I was thinking about using a select for update limit 1, but > the other persons will of course try to select the same record > and wait until that one is released. The locks are held for a > long period of time. > > Is there a way to select a row that is not locked yet? I'm a bit confused by your question. Could you try explaining it another way, possibly with SQL code examples? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Selecting a non-locked row.
On Sun, Jan 19, 2003 at 01:26:10PM -0800, Josh Berkus wrote: > Kurt, > > > I have a table with records in, and they either still need to be > > processed or not. Several people will be using the table at the > > same time. > > > > I was thinking about using a select for update limit 1, but > > the other persons will of course try to select the same record > > and wait until that one is released. The locks are held for a > > long period of time. > > > > Is there a way to select a row that is not locked yet? > > I'm a bit confused by your question. Could you try explaining it another way, > possibly with SQL code examples? I have data in the table that should only be used once. Several people will be using the table at the same time to get a new record out of it. After they're done with it the record gets marked as done. I have a query that looks something like: begin; select id, data from table where used IS NULL order by id for update limit 1; And after some time: update table set used = 1 where id = id; commit; Of course a second person doing the same thing will just wait for my commit. What I want is that he just gets the next non-locked record. Kurt ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Selecting a non-locked row.
Kurt, > Of course a second person doing the same thing will just wait for > my commit. > > What I want is that he just gets the next non-locked record. Well, there's two ways you can do this: 1) hack the system tables to find out which incomplete rows in the table are currently locked, and select the lowest ID from those that aren't. You can do this in 7.3 fairly easily throught the "pg_locks" table, but in 7.2.3 it involves a rather annoying hack of the "hidden" tuple fields (which I don't know very well, so don't ask). 2) (my preference) modify your done/not done field to accept 3 values: not done, in progress, done. Then add this step between select ... for update and the final update that updates the row as "in progress".Then you can more easily select the first "not done" row. (actually, you would have to set a tuple lock with something longer lasting than select for update, and arrange to drop it if the connection dies. but it's still my preferred solution) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Group By Error Text
Hi with the following query: select jobno, count(jobno) from drawing_register; I get the following error: ERROR: Attribute drawing_register.jobno must be GROUPed or used in an aggregate function Is this correct? Getting rid of the error is easy: select jobno, count(jobno) from drawing_register group by jobno; I believe that jobno is being used in an aggregate function. Should a change be made to the error text? Regards Joe Healy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Group By Error Text
Joe, > ERROR: Attribute drawing_register.jobno must be GROUPed or used in an > aggregate function > > Is this correct? Getting rid of the error is easy: > > select jobno, count(jobno) from drawing_register group by jobno; > > I believe that jobno is being used in an aggregate function. Should a > change be made to the error text? Nope. The error message is exactly correct. If you didn't want to GROUP BY jobno, then what you actually wanted to query was: SELECT count(jobno) FROM drawing_register; -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Selecting a non-locked row.
On Sunday 19 January 2003 16:26, Josh Berkus wrote: > > I was thinking about using a select for update limit 1, but > > the other persons will of course try to select the same record > > and wait until that one is released. The locks are held for a > > long period of time. > > > > Is there a way to select a row that is not locked yet? > > I'm a bit confused by your question. Could you try explaining it another > way, possibly with SQL code examples? I suspect that he is looking for something like this: SELECT * FROM foo WHERE [yada yada yada] AND NOT LOCKED; I don't think we have anything like that. It's not a bad idea though. I have used this facility in Progress RDBMS and it can be useful. My application of it was to create my own sequences that were guaranteed to be contiguous. They could be out of order within reason but no numbers were allowed to be skipped. I wound up creating a special table for sequences that had multiple entries for each sequence and you would simply get the lowest unlocked number in your sequence and bump it by the count of numbers, 10 in this case. If someone else had a number locked in a transaction and after you took the next one they released it with an ABORT, the number simply became available again to the next process. I'm not sure how to do something like that without the ability to exclude locked records from the query or else with an atomic compare and set function. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
