Re: BUG #17428: last_value incorrect for uninitialized sequence

David, Tom,

The problem really is that the two states of not initialized and post first
usage are indistinguishable (both return 1), but the sequence next value is
different. ie it’s a reasonable expectation that the next value is
last_value + 1. IMHO violating this makes it a bug. Further, this situation
means there’s no query that can be run on the sequence that can determine
if the next value is 1 or 2.

As for existing code relying of current behaviour, given that current
behaviour can’t be relied upon to predict the next value, I don’t think
it’s possible for any usage to rely on 1 being returned when uninitialized.

I agree though that returning null is a better choice for uninitialized
sequences than returning 0.

This whole line of enquiry came about because currval('mytable_id_seq')
explodes when called on an uninitialized sequence (another surprise - I was
expecting null); how about fixing that too? If that was fixed is would
provide a work around for the lack of distinction between the two states of
next value being 1 and next value being 2.

Regards,
Glen

On Tue, 8 Mar 2022 at 1:48 am, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Mon, Mar 7, 2022 at 12:15 AM PG Bug reporting form <
> > noreply(at)postgresql(dot)org> wrote:
> >> For a freshly defined sequence, the following:
> >> select last_value from mytable_id_seq
> >> should return 0, but returns 1.
>
> > One seems as good a choice as zero if a non-null value is to be returned.
>
> The larger point here is that any change is much more likely to
> break applications expecting the historical behavior than it is
> to make anyone's life better. In a green field I'd tend to
> agree that returning NULL (and dispensing with is_called) would
> be a better design, but that opportunity was missed decades ago.
>
> regards, tom lane
>