[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