Re: Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

> On 14 Feb 2018, at 2:48, DrakoRod <drakoflames(at)hotmail(dot)com> wrote:

> CREATE OR REPLACE RULE inserts_customer_part1
> AS ON INSERT TO customers
> WHERE new.id < 10000
> DO INSTEAD INSERT INTO customers_part1 SELECT NEW.*;
>
> CREATE OR REPLACE RULE inserts_customer_part2
> AS ON INSERT TO customers
> WHERE new.id >= 10000 AND new.id < 20000
> DO INSTEAD INSERT INTO customers_part2 SELECT NEW.*;

Here's your problem. Rules substitute values. Since you didn't provide an id in your insert, the id column gets substituted by the default value, which happens to call nextval. You have 3 references to new.id in your rules, so the sequence increments by 3.

That's one of the reasons people usually advise to use triggers & procedures instead of rules.

> dd=# SELECT * FROM customers;
> id | name | other_data
> ----+---------+------------
> 3 | XXXXXXx | YYYYYYYYYY
> 7 | XXXXXXx | YYYYYYYYYY
> 11 | XXXXXXx | YYYYYYYYYY
> 15 | XXXXXXx | YYYYYYYYYY
> 19 | XXXXXXx | YYYYYYYYYY
> 23 | XXXXXXx | YYYYYYYYYY
> (6 rows)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.