Re: [BUGS] Bug #613: Sequence values fall back to previously chec

> | > Placing a restriction on an application that says it must treat the
values
> | > returned from a sequence as if they might not be committed is absurd.
> |
> | Why? The fact that you are not able to rollback sequences does not
> | necessary mean that you are not required to perform commit to ensure
> | permanent storage of changes made to database.
>
> I use sequences to generate message identifiers for a simple
> external-to-database message passing system. I also use
> them for file upload identifiers. In both cases, if the
> external action (message or file upload) succeeds, I commit;
> otherwise I roll-back. I assume that the datbase won't give
> me a duplicate sequence... otherwise I'd have to find some

So can you do "select nextval()" in *separate* (committed)
transaction *before* external action and "real" transaction where
you store information (with sequence number) about external
action in database?

BEGIN;
SELECT NEXTVAL();
COMMIT;
BEGIN;
-- Do external actions and store info in DB --
COMMIT/ROLLBACK;

Is this totally unacceptable? Is it really *required* to call nextval()
in *the same* transaction where you store info in DB? Why?

> other way go get sequences or I'd have duplicate messages
> or non-unique file identifiers.
>
> With these changes is this assumption no longer valid? If

1. It's not valid to assume that sequences will not return duplicate
numbers if there was no commit after nextval.
2. It doesn't matter when sequence numbers are stored in
database objects only.
3. But if you're going to use sequence numbers in external objects
you must (pre)fetch those numbers in separate committed
transaction.

(Can we have this in FAQ?)

> so, this change will break alot of user programs.
>
> | And why? Just for convenience of << 1% applications which need
> | to use sequences in their own, non-database, external objects?
>
> I think you may be underestimating the amount of "external resources"
> which may be associated with a datbase object. Regardless, may of the
> database features in PostgreSQL are there for 1% or less of the
> user base...

Please note that I was talking about some *inconvenience*, not about
*inability* of using sequence numbers externally (seems my words were
too short). Above is how to do this. And though I agreed that it's not
very convenient/handy/cosy to *take care* and fetch numbers in
separate committed transaction, but it's required only in those special
cases and I think it's better than do fsync() per each nextval() call what
would affect other users/applications where storing sequence numbers
outside of database is not required.

Vadim