Re: Extended customizing, SQL functions,

pgsql(at)mohawksoft(dot)com writes:

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

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