Re: sequences and "addval('myseq', value)" - Mailing list pgsql-hackers

> pgsql@mohawksoft.com writes:
>> 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).
>
> It produces a series of numbers that are guaranteed to be *different*.
> The always-increasing property is valid as far as the numbers delivered
> in a single session go, but it doesn't hold across backends, so I think
> the extent to which you can rely on it is pretty limited.

Yup.

>
>> 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.
>
> This strikes me as a complete nonstarter.

Tom, I have to chuckle here. You HATE every suggestion I ever make. I
can't think of one thing I've suggested over the years that was ever met
with enthusiasm. Never change. :-)


> How would a failed
> transaction fix anything, or even tell anyone else that there was now
> something needing to be fixed?  You'd have to rely on the client to
> start a fresh transaction and do the fixup.  In practice the total would
> get out of sync in no time.

This is very true, and "expected." In some uses, the "client" is quite in
control of the database. Imagine this:

begin
...
...
...
...
commit

if(no error)   select addval('mysum', val);

In this case it is updated after the commit. It is known NOT to be a
reliable number, but it is good enough for a summary, and in practice


>
> Also, I don't see how you would actually use this without needing addval
> to accept negative increments (for decrementing totals on delete, etc).
> That seems to create a whole new set of semantic issues, because now you
> no longer have the "it's the same as N consecutive nextval's" rule to
> define the behavior for you.

actually, the code does accept negative numbers. Also, I removed the
caching code.

This is *NOT* a perfect or elegant solution. There is, however, an
important problem. How do you maintain a value that is visable to the
database, but does not incure the cost of a huge number of updates or a
full table scan? I'm talking about systems that need to take thousands of
inserts per minute?

Given a controlled environment, "addval" could do this. In fact, I can't
see anything BUT a controlled environment needing something like this.