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

The following bug has been logged on the website:

Bug reference:      17554
Logged by:          Szabolcs Klement
Email address:      szittya314@gmail.com
PostgreSQL version: 14.4
Operating system:   Rocky Linux release 8.5 (Green Obsidian)
Description:

Hi,
(sorry my poor english)
the problem is in 10-14 postgresql too.

this a sample what make gap in sequence:
create table aa (id serial primary key,tt text);
create or replace function fika1(p1 aa) returns boolean language sql as
$body$
    select (p1.id)::boolean;
$body$;
create or replace rule birkman as on insert to aa do select fika1(new);

insert into aa (tt) values ('uu');
select * from aa order by id;

(when inner the function isnt reference a new record, there isnt gap:
create or replace function fika1(p1 aa) returns boolean language sql as
$body$ select true;    $body$;
)

inner the function then p1.id is always X, but after the insert in the new
record is X-1;

i try with plpgsql function:
create or replace function fika1(p1 aa) returns boolean language plpgsql
as
$body$
  begin
      raise notice 'id:%', p1.id;
    return true;    --I didnt referenced the new value
  end;
$body$;

Best regards
Szabolcs

On Mon, 18 Jul 2022 at 12:15, PG Bug reporting form
<noreply@postgresql.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.