Re: Logging for sequences

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 17:39 29/11/00 -0800, Mikheev, Vadim wrote:
>> BTW, why SETVAL is called in pg_dump output instead of
>> if (called) NEXTVAL? SETVAL is disallowed for sequences
>> with cache_value > 1 - ie we can't dump such sequences now.

> Can someone explain this to me? It's just a little over my head...

He's talking about the error check at the head of do_setval:

if (seq->cache_value != 1)
elog(ERROR, "%s.setval: can't set value of sequence %s, cache != 1",
seqname, seqname);

Because of this, pg_dump's script will fail to set the sequence value
correctly if the sequence was created with a cache setting larger than 1.

Vadim, Philip changed that part of pg_dump on my advice. The idea was
to try to do the right thing for sequences when loading schema only or
data only. Analogously to loading data into a pre-existing table, we
felt that a data dump ought to be able to restore the current state of
an already-existing sequence object. Hence it should use setval().
But I overlooked the cache issue.

Philip, the reasoning behind that error check is that if cache_value >
1, then the behavior of the setval() may not be what the user expects.
In particular, other backends may have pre-cached sequence values, which
their nextval() calls will continue to dole out even after the setval()
caller thinks he's changed the sequence's value.

This error check is probably good in the general case, but I think it's
irrelevant for typical uses of pg_dump: there won't *be* any other
backends with cached values of the sequence object. Also, the behavior
that the error check is trying to prevent isn't exactly catastrophic,
it's just potentially confusing to the user. So I don't want to let
the check stand in the way of making pg_dump do something reasonable
with sequences.

My inclination is to leave pg_dump as it stands, and change do_setval's
error check. We could rip out the check entirely, or we could modify
the code so that a setval() is allowed for a sequence with cache > 1
only if it's the new three-parameter form of setval(). That would allow
pg_dump to do its thing without changing the behavior for existing
applications. Also, we can certainly make setval() flush any cached
nextval assignments that the current backend is holding, even though we
have no easy way to clean out cached values in other backends.

Comments?

regards, tom lane