Обсуждение: Table Partitioning: Sequence jump issue 10 in 10 with serialdatatype

Hi folks!!
I have a problem with a serial data type and partitioned table, I used rules
to insert in child tables. But the problem is that the some does'nt insert
and the sequence value jump sometimes 3 in 3 or 10 in 10.

The example is the next:




I don't understand why sequence jumps in this case 4 in 4. And how to avoid
this? I really can't change the use serial data type and how generate the
id, because I don't developed the app.

If I use triggers this don't happen?

Thanks!






-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Hi folks!!
I have a problem with a serial data type and partitioned table, I used rules
to insert in child tables. But the problem is that the some does'nt insert
and the sequence value jump sometimes 3 in 3 or 10 in 10.

The example is the next:

I don't understand why sequence jumps in this case 4 in 4. And how to avoid
this? I really can't change the use serial data type and how generate the
id, because I don't developed the app.

If I use triggers this don't happen?

Sorry, your right! The example is:

CREATE TABLE customers (
    id serial PRIMARY KEY,
    name TEXT,
    other_data TEXT
);

CREATE TABLE customers_part1(
 CHECK (id<10000)
)INHERITS (customers);

CREATE TABLE customers_part2(
 CHECK (id>=10000 AND id<20000)
)INHERITS (customers);

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.*;

INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');
INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');
INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');
INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');
INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');
INSERT INTO customers (name, other_data) VALUES ('XXXXXXx','YYYYYYYYYY');


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)




-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

> 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.

Yep!!

Today I tested with triggers instead rules and the sequence goings well.

Thanks for your help!!



-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html