Re: Extended customizing, SQL functions, - Mailing list pgsql-hackers

> On Fri, May 28, 2004 at 12:46:29 -0400,
>   pgsql@mohawksoft.com 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.