Re: [HACKERS] Extended customizing, SQL functions,
pgsql == pgsql [EMAIL PROTECTED] writes: pgsql The have a database of information that is coming in at a pgsql high speed regular basis. One bit of information is a pgsql value. To get this value they must perform SELECT pgsql sum(field) FROM table. Well, this simply does not pgsql scale. They've used a trigger system with a small summary pgsql table where they update, the number in the sumary pgsql field. That works fine, except, that after a few thousand pgsql updates, the SELECT time takes a while. Then they have to pgsql vacuum constanty. It just seems like an ugly and wastefull pgsql process. Sounds like something that TelegraphCQ can do well .. http://telegraph.cs.berkeley.edu -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(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: [HACKERS] Extended customizing, SQL functions,
On Saturday 29 May 2004 18:10, [EMAIL PROTECTED] wrote: Having internal PostgreSQL variables that are not present on disk, or maybe, variables that are mirrored on disk may be good. Yes. I agree. I can see why you proposed no transactions few posts ago. Take an example of a count variable. It may not have transactions but it is expected not to be very accurate anyways. If I can declare variables which can be changed/read in locked fashion and visible to all the backends would be a real great use. It shouldn't have transactions because it is not data but a state. It is in database so that other connections and stored procedures could see it. Coupled with the fact that postgresql has custom data types, there is no end how this could be put to use. Lot more things that sit in application layer will be inside postgresql, I can image. The whole reason why I made this post was to see if other people have had similar issues and looked for a similar solution, and to think about if there is a solution that fits within PostgreSQL and how it would work. AFAIK, there is no way of doing it in postgresql. But I would love to see it happen. (I wish I could work on it...:-( ) Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Extended customizing, SQL functions,
On Saturday 29 May 2004 04:38, [EMAIL PROTECTED] wrote: Now, I could roll my own system pretty easily, and probably will do so. It won't take too much, however, it would be neat if this was in PostgreSQL. I fully expect that people would worry about this, and I don't blame them. It is a *bad* idea. Like I said, I could roll my own, but I'm curious if anyone else sees any benefit to this feature. If it is a feature that people want, it would best be done from within PostgreSQL. If it is not something generally wanted, then I'll keep it here or try to get it on gborg or pgfoundary. I agree that it could be a nice feature. But it reminds me a quote from a C++ FAQ I read once. -- *. Should I use exception for error handling? Ans. The real question is can I afford stack unwinding here... -- The situation is similar here. When you want something in database, one question is to ask is do I need MVCC here? Of course depending upon the application context the answer well could be yes. But at a lot of places, this could be easily be managed in application and probably better be done so. Personally I do not think managing such information in application is an hack. Just a thought... Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Extended customizing, SQL functions,
On Saturday 29 May 2004 04:38, [EMAIL PROTECTED] wrote: Now, I could roll my own system pretty easily, and probably will do so. It won't take too much, however, it would be neat if this was in PostgreSQL. I fully expect that people would worry about this, and I don't blame them. It is a *bad* idea. Like I said, I could roll my own, but I'm curious if anyone else sees any benefit to this feature. If it is a feature that people want, it would best be done from within PostgreSQL. If it is not something generally wanted, then I'll keep it here or try to get it on gborg or pgfoundary. I agree that it could be a nice feature. But it reminds me a quote from a C++ FAQ I read once. -- *. Should I use exception for error handling? Ans. The real question is can I afford stack unwinding here... -- The situation is similar here. When you want something in database, one question is to ask is do I need MVCC here? I am a HUGE C/C++ guy. A lot of people dump on C++ because it lets you shoot yourself in the foot with both barrels without even knowing how ... if you are not careful. Oddly enough, this very flexability is what makes it a very powerful and useful language. Similarly, sometimes, it is very difficult to make PostgreSQL do some of the things that you need too. Of course depending upon the application context the answer well could be yes. But at a lot of places, this could be easily be managed in application and probably better be done so. Personally I do not think managing such information in application is an hack. Just a thought... I may have a unique view of PostgreSQL, but I don't think of it as just a database. I think of it as a great applications platform. Most applications today are data centric, and postgresql fits this bill perfectly. As a data layer it is hard to beat. The fact that it is a great SQL database in its own right, is a huge bonus. That being said, every now and then it lacks this small little feature that working around takes a bit of work. Like functions returning multiple results, that was huge. Functions returning rows, that was huge too. Having internal PostgreSQL variables that are not present on disk, or maybe, variables that are mirrored on disk may be good. The whole reason why I made this post was to see if other people have had similar issues and looked for a similar solution, and to think about if there is a solution that fits within PostgreSQL and how it would work. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: Having internal PostgreSQL variables that are not present on disk, or maybe, variables that are mirrored on disk may be good. I don't think there's anything wrong with your idea, and there are numerous good solutions that implement it already. But what makes you think this belongs in Postgres? There are plenty of memory and disk based shared databases that are non-transactional and non-relational and meant for storing just this kind of non-relational data. Some are much faster than postgres for simple non-concurrent one-record lookups and updates like this. Use the right tool for the job. Don't try to make one tool do everything, especially something that's anathema to its basic design. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Extended customizing, SQL functions,
On Saturday 29 May 2004 18:10, [EMAIL PROTECTED] wrote: Having internal PostgreSQL variables that are not present on disk, or maybe, variables that are mirrored on disk may be good. Yes. I agree. I can see why you proposed no transactions few posts ago. Take an example of a count variable. It may not have transactions but it is expected not to be very accurate anyways. If I can declare variables which can be changed/read in locked fashion and visible to all the backends would be a real great use. It shouldn't have transactions because it is not data but a state. It is in database so that other connections and stored procedures could see it. Coupled with the fact that postgresql has custom data types, there is no end how this could be put to use. Lot more things that sit in application layer will be inside postgresql, I can image. The whole reason why I made this post was to see if other people have had similar issues and looked for a similar solution, and to think about if there is a solution that fits within PostgreSQL and how it would work. AFAIK, there is no way of doing it in postgresql. But I would love to see it happen. (I wish I could work on it...:-( ) I was thinking that it could be done as a contrib/pgfoundary function. I think, but am not sure, that a function can be pre-loaded into the main postgresql backend (postmaster) prior to starting other connections. I'll have to check that out, but I think it is true, if it isn't it should be possible to modify postmaster to do so. The function module, when loaded, looks for its shared memory block, if none is found, then it procededs to create the block, mutex, etc. Then, it is a fairly simple task of managing a name/value table protected by mutex. Make sense? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: Having internal PostgreSQL variables that are not present on disk, or maybe, variables that are mirrored on disk may be good. I don't think there's anything wrong with your idea, and there are numerous good solutions that implement it already. But what makes you think this belongs in Postgres? There are plenty of memory and disk based shared databases that are non-transactional and non-relational and meant for storing just this kind of non-relational data. Some are much faster than postgres for simple non-concurrent one-record lookups and updates like this. Use the right tool for the job. Don't try to make one tool do everything, especially something that's anathema to its basic design. I agree completely with one caveat, when the best tool for the job lacks a feature what do you do? -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: I agree completely with one caveat, when the best tool for the job lacks a feature what do you do? You're missing the point. The feature you want has nothing to do with relational databases. It has everything to do with in-memory non-transactional non-relational databases. These things exist but they're not postgres. Postgres just isn't the best tool for what you want to do. Try memcached or any of the other very fast non-persistent non-transactional in-memory databases. If you try to use postgres to do this you'll find -- as you just did -- that you've bought a lot of overhead for things you don't want. Because it's not the appropriate tool. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: I agree completely with one caveat, when the best tool for the job lacks a feature what do you do? You're missing the point. The feature you want has nothing to do with relational databases. It has everything to do with in-memory non-transactional non-relational databases. These things exist but they're not postgres. Postgres just isn't the best tool for what you want to do. Try memcached or any of the other very fast non-persistent non-transactional in-memory databases. If you try to use postgres to do this you'll find -- as you just did -- that you've bought a lot of overhead for things you don't want. Because it's not the appropriate tool. That's the problem. It easy to say, in effect, this isn't the job of the database. Yet, the information is based on what's in the database. It is one of those ambiguous things that life is so anoyingly full of. It's really data related, so it should be in the database, it's really the application's place to do this, so it should be in the application. When all is said and done, I would say it is too data related to be so separated from the database. Remember, PostgreSQL was chosen for the vast number of advantages, this is just one small issues. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Extended customizing, SQL functions, internal variables, API
On Fri, May 28, 2004 at 12:46:29 -0400, [EMAIL PROTECTED] wrote: It occurs to me that there is a need for internal state variables that can be accessed either by functions or something similar. But there still needs to be multiple copies to take into account that different transactions may need to see different values of the same variable. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extended customizing, SQL functions,
On Fri, May 28, 2004 at 12:46:29 -0400, [EMAIL PROTECTED] wrote: It occurs to me that there is a need for internal state variables that can be accessed either by functions or something similar. But there still needs to be multiple copies to take into account that different transactions may need to see different values of the same variable. Yea, what I'm about to say will cause a lot of people to disagree with me, and I don't even like the idea for some very small set of examples, but No transactions. I know this is a very bad thing, and I hate even thinking about it, but there is a real need for this sort of function in some very limited cases. Let me exaplin, and this really isn't a SQL issue, so much as flexability to break some rules issue. My client is sold on PostgreSQL, it works for them perfectly with one exception. (I have to be careful about NDA stuff here) The have a database of information that is coming in at a high speed regular basis. One bit of information is a value. To get this value they must perform SELECT sum(field) FROM table. Well, this simply does not scale. They've used a trigger system with a small summary table where they update, the number in the sumary field. That works fine, except, that after a few thousand updates, the SELECT time takes a while. Then they have to vacuum constanty. It just seems like an ugly and wastefull process. There is a quick solution, create an internal variable in shared memory that can be seen by all back-end processes. It is protected by a mutex. Now, I could roll my own system pretty easily, and probably will do so. It won't take too much, however, it would be neat if this was in PostgreSQL. I fully expect that people would worry about this, and I don't blame them. It is a *bad* idea. Like I said, I could roll my own, but I'm curious if anyone else sees any benefit to this feature. If it is a feature that people want, it would best be done from within PostgreSQL. If it is not something generally wanted, then I'll keep it here or try to get it on gborg or pgfoundary. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extended customizing, SQL functions,
[EMAIL PROTECTED] writes: On Fri, May 28, 2004 at 12:46:29 -0400, [EMAIL PROTECTED] wrote: It occurs to me that there is a need for internal state variables that can be accessed either by functions or something similar. What you're describing is called materialized views and indeed a full featured implementation capable of caching things like sum() and count() would be a very nice thing to have. But it's also a lot of work. A partial solution handling just your setup could be done using triggers like you describe, but it has the major disadvantage of serializing all insert/delete/updates on the source table. They all become serialized around the record with the cache of the sum/count. But there still needs to be multiple copies to take into account that different transactions may need to see different values of the same variable. Yea, what I'm about to say will cause a lot of people to disagree with me, and I don't even like the idea for some very small set of examples, but No transactions. Well sure you can do that, in which case there are other systems that are more appropriate than postgres. I would suggest you look at memcached or perhaps mysql. Then they have to vacuum constantly. It just seems like an ugly and wasteful process. If you were using other databases the equivalent work would have to happen in the middle of the critical path of the transaction, which is even uglier and more wasteful. If you're using 7.4 the new pg_autovacuum daemon will handle this for you, you can pretend it isn't happening. The only alternative is giving up transactions, like you say, in which case you may as well use a tool that doesn't spend so much effort providing them. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
