Re: sequences and "addval('myseq', value)"

> pgsql(at)mohawksoft(dot)com writes:
>> The question is, what do you think of an "addval" function for
>> sequences.
>
> It would have to be defined as "do the same thing as N successive
> nextval calls would do", which would not be especially useful in the
> presence of caching.
>
>> The problem I, and I know many other people are having, is that large
>> sums
>> can not be obtained without a table scan. A summary table can be
>> created,
>> but if you have any sort of volume, you have to vacuum the summary table
>> constantly.
>> Using the sequence construct as sort of an accumulator just might fit
>> the
>> bill for this sort of thing.
>
> How would it work at all? Suppose your transaction rolls back after
> doing the insert or update. The table won't have changed ... but the
> sequence change doesn't roll back. You can't make such an easy end run
> around the fundamental MVCC/locking problem.
>

I know that MVCC is an issue, but for this particular class of problem, it
isn't so much. Given any rollback, one should recalculate the value. This
sort of thing is one of those "no elegant solution" problems. A table scan
can't work because it takes too long and a summary table can't work
because you have to vacuum all the time and/or suffer the cost of all the
dead tuples waiting to be vacuumed.

If I understand correctly, and I've sort of just worked on this
assumption, a sequence does not nessisarily produce a sequence of numbers.
It produces a succession of numbers that are guarenteed to increase, but
not nessisarily with a specific interval (usually one).

The "addval" is similar, you can add a value to a sequence. It should be
OK, but is not guarented to be MVCC. The admin or developer will have to
correct any rollbacks. With all its imperfections, it could offer the
software developer who basically has full control over what is being done
to the database to maintain a value which can be used in OLAP or summary
information without forcing a tablescan and without incurring the cost of
constantly updating a row in a table.

I really can't think of a more MVCC compatible way of solving this
problem. I'd love a more elegant solution, but it escapes me, and I know a
lot of developers have this same problem. In fact, when ever I have to
defend my choice of PG over something like MySQL, it is almost invariably
thrown in my face.