Re: BUG #17554: when i use rule on table which have serial column, the nextval exec twice.

On Mon, 18 Jul 2022 at 12:15, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> this a sample what make gap in sequence:

There are tons of ways to have gaps in sequences, and even to manage
to get values out of order ( in different sessions ).

Sequences are for simple and fast generation of unique integers, not
for generation of gapless monotonically increasing integer sequences.

> create or replace rule birkman as on insert to aa do select fika1(new);

A similar thing appeared a short time ago, and your problem is
probably the same, the systems needs to generate a row to apply the
rule. Once you generate a sequence value it is never reused, because
what they guarantee is unicity. You did not store it and it got lost,
not a problem for the sequence intended usage. Your problem seems to
be wrong expectations, you expect ti to do other thing.

It it returned the id back to avoid gaps it would have to lock
everybody who tries to get another value until commit time, because
until it gets the commit it does not know if you are going to use the
value it gave you or not. In your case the value is not used in the
function, but until you commit it does not know if you are going to
get it ( you can get a value previously given to you, read about
currval/nextval on the docs. ) and use it somewhere in the next
operation.

Sequences work this way because they can be very fast and avoid
locking. Each process needing values grabs a chunk of them ( default
is only 1 in the chunk, IIRC ), but never returns them, this way very
little locking is needed and they are fast, but it cannot put them
back. Read the docs a bit, it is explained there.

Francisco Olarte.