Обсуждение: BUG #17428: last_value incorrect for uninitialized sequence

The following bug has been logged on the website:

Bug reference:      17428
Logged by:          Glen Edmonds
Email address:      glen.edmonds@gmail.com
PostgreSQL version: 14.2
Operating system:   MacOS Monterey 12.2.1
Description:

For a freshly defined sequence, the following:

select last_value from mytable_id_seq

should return 0, but returns 1.

Reasoning: In every case except the uninitialised case, last_value is the
same as the current value of the sequence, which is the same as the number
of rows in the table (assuming no deletions). Logically, if there are no
rows in the table, last_value should return 0 to be consistent.

To reproduce:

create table mytable (
    id serial,
    other int
);
select (select count(*) from mytable), last_value from mytable_id_seq; -- 0,
1
insert into mytable (other) values (0);
select (select count(*) from mytable), last_value from mytable_id_seq; -- 1,
1
insert into mytable (other) values (0);
select (select count(*) from mytable), last_value from mytable_id_seq; -- 2,
2
insert into mytable (other) values (0);
select (select count(*) from mytable), last_value from mytable_id_seq; -- 3,
3
-- etc

As you can see, only the first row returns different results for the same
expression.

This isn't just theoretical. I was writing some DB units tests and this
caused my code to break, but was also a surprise.
IMHO this is a bug.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Mar 7, 2022 at 12:15 AM PG Bug reporting form <
> noreply@postgresql.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

Glen Edmonds <glen.edmonds@gmail.com> writes:
> 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.

They're not indistinguishable: if you look at the is_called flag
you'll see that it changes.

> ie it’s a reasonable expectation that the next value is
> last_value + 1. IMHO violating this makes it a bug.

You can call it that if you like, but it's operating as designed
and documented.  I fear it's about twenty years too late to propose
a redesign.

            regards, tom lane