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

On Tuesday, February 13, 2018, DrakoRod <drakoflames@hotmail.com> wrote:

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.

Do not know what you mean by 'n in n'

The example is the next:

Don't see an example...

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?

Without more details it's hard to say but if you need hapless sequences
aren't the answer. There are many reasons committed values could contain
gaps. Or in this case avoid requiring it to be when using it.

David J.

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

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