Re: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens.

On Fri, May 6, 2016 at 8:20 PM, Andres Freund <andres@anarazel.de> wrote:
> Maybe we should add a note to the docs then?

Well, all I now about sequences comes from there, so I think they are clear.

But I've read the docs and noticed the warning:

"Important: To avoid blocking concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never rolled
back; that is, once a value has been fetched it is considered used,
even if the transaction that did the nextval later aborts. This means
that aborted transactions might leave unused "holes" in the sequence
of assigned values."

Which may lead people to think only rollbacks 'burn' numbers. So I
think some rewording indicating:

1.- once fetched a value is considered used even if the using command
does not complete, which may be caused by rollbacks, aborts, triggers,
insert_or_update and/or any other things.

2.- IIRC using cache>1 can lead to obtain sequence numbers out of
order is using the same sequence in two different sessions. I assumed
this is normal and do not have a problem with it, but people may get
surprised by it, so I think a note there could help.

I do not trust my english to suggest an exact rewording, but I think
this could lead to less reports AND make many of them solvable by
redirecting at the manual.

Francisco Olarte.