Re: Table Partitioning: Sequence jump issue 10 in 10 with serialdatatype

> On 14 Feb 2018, at 2:48, DrakoRod <drakoflames@hotmail.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
substitutedby the default value, which happens to call nextval. You have 3 references to new.id in your rules, so the
sequenceincrements 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.