Re: [SQL] About Div
On 7/25/06, Otniel Michael <[EMAIL PROTECTED]> wrote: Mr. Aaron. I am sorry, your solution didn't match in my case. Example for your solution :A = 1B = 1C = 1D = 1E = 1F = 1G = 4G have 4 candy. Its too much for G.In my case, the solution is : A = 1B = 1C = 1D = 1E = 2F = 2G = 2The extra candy is given to three child.Do you have the other solution? I need function in postgresql for my case.Because my loop is too slow. Btw thanks for your solution. I think you misunderstood how to use the MOD value. 10 % 7 = 3 so the last 3 people get 1 extra, NOT the last person getting the extra 3.But it looks like Ross got you the code to fix the problem. Check what he provided. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Storing an ordered list
On 7/25/06, Michael Artz <[EMAIL PROTECTED]> wrote: What is the best way to store and ordered list that can be updatedOLTP-style? A simplified problem is that I have an event, and theevent has an ordered list of predicates and I need to preserve theorder of the predicates. All of the data is entered via a web application, and I would like to support the new flashy ajaxdrag-droppy thingies, meaning that there could be a significant amountof updates if the user is dragging things all over the place.I figure that one choice is to explicitly code the order as an integer column in the predicate table which has the advantage of being veryeasy and fast to query/order but *very* slow to reorder as all of thepredicates need to be updated. This would seem to be a postgres/MVCC weak spot as well. Example:create table event (event_id integer);create table predicate (event_id integer not null referencesevent(event_id), name varchar, order integer);insert into event (event_id) values (1); insert into predicate (1, 'first event', 1);insert into predicate (1, 'second predicate', 2);select * from predicate p where p.event_id = 1 order by p.order;I'm also thinking about a linked list, i.e. create table event (event_id integer);create table predicate (predicate_id integer, event_id integer notnull references event(event_id), name varchar, next_predicate integerreferences predicate (predicate_id)); insert into predicate (101, 1, 'second predicate', NULL);insert into predicate (102, 1, 'first predicate', 101);The downside is that I'm not quite sure how to efficiently query thelinked list. Any suggestions? Are there any known best practices for storing ordered lists inrelational databases? Are there any tricks that I can use withpostgres? Even the linked list will require a lot of updates if there are is a lot of reshuffling - perhaps less though in certain circumstances, especially if the list is large and there is very little reshuffling. If you use the linked list, remember this: to reduce the updates you are going to need more code in the application as it will have to keep track of what to update and what to not update. It will also be more difficult to order the items using SQL so your application may have to take on that burden. As a result, your application will become more complicated and writing reports that use the ordering will become difficult. Another thing to think about with a linked list: What if two people are reordering the items at the same time - they load the items at the same time, then reorder at the same time (with their own separate cache of the data) and finally save. If you update everything, the last man to save wins but if you only update only what they change, you could end up with a mess: Example: Start with: 1 -> 2 -> 3 -> 4 -> 5 Person 1 reorders to: 1 -> 5 -> 2 -> 3 -> 4 (only update 1 -> 5, 5 -> 2 and 4 -> null) Person 2 reorders to: 1 -> 2 -> 5 -> 3 -> 4 (only update 2 -> 5, 5 -> 3 and 4 -> null) If they then both save (assume person 1 saves and then person 2 saves) you get: 1 -> 5 2 -> 5 This is going to be a big problem.When I need something like this I go with your first approach, a simple order field. Unless the user is reordering a small number of items in a very large list and doing it frequently, is there really a need to worry about the number of updates? Are you worrying about a performance problem you will never have? == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] SQL generator
On 7/25/06, Chris Browne <[EMAIL PROTECTED]> wrote: "Bit Byter" <[EMAIL PROTECTED]> writes:> I would like to know what the underlying SQL statement will look> like, so that I can use this knowlege to build a generic parser that > creates SQL statements from the "English like" text, using the> syntax I described above.I seem to recall that Lotus tried doing this once upon a time withtrying to build an "english-like" interface for doing "business intelligence-like" queries against spreadsheets.Nobody likely remembers HAL; it was underwhelmingly useful.In effect, anyone that was skilful enough at composing "English-like"queries to get HAL to work was likely to find the "native" methods more convenient. There was a company near where I live who recently tried to do natural language parsing for search engines - like Ask Jeeves. It was an utter failure but before they got far enough to see that, they did a study of users to see how they search. They found that people adapted to learn how to "talk in the search engine's language" fairly easily and it has actually become unnatural for people to talk to search engines with a natural language. I have had so many problems with users who know a little SQL causing database crashes and slow downs. I don't think I would recommend allowing someone who can't even do SQL access to a database.If you still think it is a good idea to do this, good luck and I hope you prove us wrong. But just keep in mind you are going to be attempting something that has been tried before with little success. Maybe you will figure out what your predecessors were missing... Bottom line though, just how difficult is SQL to read and write anyway? It is a hell of a lot easier to read and write than any other computer language I have seen.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] SQL generator
On Wed, Jul 26, 2006 at 10:21:01AM -0500, Aaron Bono wrote: > Bottom line though, just how difficult is SQL to read and write anyway? It > is a hell of a lot easier to read and write than any other computer language > I have seen. It's not SQL. It's the notion of relations. If the language were QUEL or ENGLISH (a PICK variant) or whatever you have, but relations underneath, the same problem would be happening. Remember, even people who ought to understand this stuff, like developers who program against databases for a living, frequently get it wrong. I'm not surprised that casual users can accidentally do a 5-way Cartesian product. But it would make no difference if the commands were specified in natural language, because the conceptual confusion is the problem. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Rows with exclusive lock
Martin Marques wrote: > On Sun, 23 Jul 2006, Alvaro Herrera wrote: > > > Martin Marques escribió: > >> > >> After the SELECT FOR UPDATE other transactions can still see the locked > >> rows. I want a read/write lock, so no one can access does rows. > > > > SELECT FOR UPDATE acquires an exclusive lock, but other transactions > > must try to acquire a lock on the rows as well, or they won't be locked. > > You can try using SELECT FOR SHARE (new as of 8.1) if you want some > > transactions to hold shared (read) locks. > > Sorry for not getting it clear the first time. > > What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but > at row level. Well... you could change your other selects to use FOR UPDATE as well, even if they're not going to be updating (but this block concurrent reads). You're probably better to take Alvaro's suggestion above and use SELECT FOR SHARE. Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with privilages please
7.4.1 is quite old and has a number of serious known bugs. I'd suggest you either upgrade to 8.1.4 (current) or, if you can't do that, at least upgrade to 7.4.13 (latest 7.4) immediately. Hilary Forbes wrote: > Tom > > Thank you - I think that the underlying problem is that I was trying out > > REVOKE ALL FROM TABLE suppliers FOR public; > > then connect as hilary and I can still see the table rows. > > I appear to have to revoke each type eg > REVOKE SELECT FROM TABLE suppliers FOR public; > etc and then the code works. > > Is this a known bug in this version (7.4.1)? > > Thanks > Hilary > > > At 18:08 20/07/2006 -0400, Tom Lane wrote: > > Hilary Forbes <[EMAIL PROTECTED]> writes: > > I have an existing table suppliers and I have created a new user > > 'hilary' > > REVOKE ALL on TABLE suppliers FROM hilary; > > now login as hilary > > SELECT * from suppliers; > > and I get all the records!!! > > Most likely there's been a grant of (at least) select privilege to PUBLIC. > You'll need to revoke that if you don't want every user to have that > privilege implicitly. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > Hilary Forbes > DMR Limited (UK registration 01134804) > A DMR Information and Technology Group company (_www.dmr.co.uk_) > Direct tel 01689 889950 Fax 01689 860330 > DMR is a UK registered trade mark of DMR Limited > ** ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Storing an ordered list
On Tue, Jul 25, 2006 at 22:58:47 -0400, Michael Artz <[EMAIL PROTECTED]> wrote: > > I figure that one choice is to explicitly code the order as an integer > column in the predicate table which has the advantage of being very > easy and fast to query/order but *very* slow to reorder as all of the > predicates need to be updated. This would seem to be a postgres/MVCC > weak spot as well. Example: If you use numeric instead of int, then it is easy to insert new values. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] DBD::Pg ... how would I format this prepare?
I need to do: NOW() + '2 day'::interval where 2 is a variable ... if I do: NOW() + '? day'::interval it, of course, takes the ? as a literal ... so is there some way I can do this such that I can do the placeholder? Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DBD::Pg ... how would I format this prepare?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > if I do: > NOW() + '? day'::interval > it, of course, takes the ? as a literal ... so is there some way I can do > this such that I can do the placeholder? NOW() + n * '1 day'::interval n can be any numeric value ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DBD::Pg ... how would I format this prepare?
try now() + (? || ' day')::interval -- Original Message --- From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT) Subject: [SQL] DBD::Pg ... how would I format this prepare? > I need to do: > > NOW() + '2 day'::interval > > where 2 is a variable ... > > if I do: > > NOW() + '? day'::interval > > it, of course, takes the ? as a literal ... so is there some way I can do > this such that I can do the placeholder? > > Thx > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] > Yahoo . yscrappy Skype: hub.orgICQ . 7615664 > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] DBD::Pg ... how would I format this prepare?
On Wed, 26 Jul 2006, Jim Buttafuoco wrote: try now() + (? || ' day')::interval Perfect ... had thought of that, but put my ) after ::interval instead of before ;( Thx ... -- Original Message --- From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT) Subject: [SQL] DBD::Pg ... how would I format this prepare? I need to do: NOW() + '2 day'::interval where 2 is a variable ... if I do: NOW() + '? day'::interval it, of course, takes the ? as a literal ... so is there some way I can do this such that I can do the placeholder? Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq --- End of Original Message --- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 1: 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] Storing an ordered list
On 7/26/06, Aaron Bono <[EMAIL PROTECTED]> wrote: If you use the linked list, remember this: to reduce the updates you are going to need more code in the application as it will have to keep track of what to update and what to not update. It will also be more difficult to order the items using SQL so your application may have to take on that burden. As a result, your application will become more complicated and writing reports that use the ordering will become difficult. Yeah, that was what I wanted to avoid. I wasn't sure if there was a common approach to this sort of problem. When I need something like this I go with your first approach, a simple order field. Unless the user is reordering a small number of items in a very large list and doing it frequently, is there really a need to worry about the number of updates? Are you worrying about a performance problem you will never have? Perhaps it is unnecessary, however I wanted to start out with a decent design that could scale if need be. Also, I'd like to support "real-time" reordering, i.e. the user won't have to click "save" ... as soon as they drag the item to a new position the list is updated. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Storing an ordered list
On 7/26/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote: If you use numeric instead of int, then it is easy to insert new values. Hmm, hadn't thought about that. How would you normally implement it? I'm thinking that, if I wanted to insert between A and B, I could take (A.order + B.order)/2, which would be pretty simple. Is there a better way? ---(end of broadcast)--- TIP 1: 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
