Re: BUG #8059: sequence crash recovery is not working properly

On 2013-04-12 10:47:52 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > Thats caused by the fact that you didn't do anything that forces a WAL
> > flush since you didn't do any inserts or such. If you would do any
> > DML in the nextval() calling transaction this shouldn't happen (unless
> > you use synchronous_commit=off).
> > Now, there's a legitimate argument to be made that returning the
> > sequence value to the user kinda persists it. On the other hand, at
> > least in the first example you haven't even committed the transaction so
> > there's nothing that could flush the transaction unless we we would
> > *always* flush nextval() immediately if needs to get new values which
> > doesn't seem reasonable.
>
> I believe we've gone around on this before and decided to leave it
> as-is. The thinking is that any realistic use-case would involve
> storing the nextval result into some table somewhere, so that additional
> WAL and a commit record would be generated.

Hm. I couldn't find anything about that discussions after searching some
minutes. I am not really sure if I can follow that argument, its not
uncommon to use the database sequences to prepare data thats then
inserted lateron. In a separate transaction and possibly even in a
separate connection. In the latter case you wouldn't even notice the
database has restarted.
I don't think flushing the WAL immediately in nextval_internal is a
realistic thing to do, but making it generate an xid if its doing WAL
logging sounds like a compromise with acceptable overhead. In the normal
usage where you use to directly insert it won't have any additional
overhead since the xid will have to be generated anyway and if you use
it in a separate transaction/connection you want the flush at commit.
Sure, if somebody loops around SELECT nextval(...); without using a
transaction that will increase the overhead, but that doesn't sound like
a terribly good idea anyway.

On a tangent: I'd still really like to have nextval(...,
how_many_values) or such...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services