Обсуждение: BUG #17554: when i use rule on table which have serial column, the nextval exec twice.
BUG #17554: when i use rule on table which have serial column, the nextval exec twice.
От
PG Bug reporting form
Дата:
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
Re: BUG #17554: when i use rule on table which have serial column, the nextval exec twice.
От
Francisco Olarte
Дата:
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.