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

> On 14 Feb 2018, at 2:48, DrakoRod  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.