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

[DrakoRod]

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

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

[Alban Hertroys]

> On 14 Feb 2018, at 2:48, DrakoRod  wrote:

> CREATE OR REPLACE RULE inserts_customer_part1
> AS ON INSERT TO customers
> WHERE new.id < 1
> DO INSTEAD  INSERT INTO customers_part1 SELECT NEW.*;
> 
> CREATE OR REPLACE RULE inserts_customer_part2
> AS ON INSERT TO customers
> WHERE new.id >= 1 AND new.id < 2
> 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 | XXx | YY
>  7 | XXx | YY
> 11 | XXx | YY
> 15 | XXx | YY
> 19 | XXx | YY
> 23 | XXx | YY
> (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.

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

[DrakoRod]

Sorry, your right! The example is:

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

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

CREATE TABLE customers_part2(
 CHECK (id>=1 AND id<2) 
)INHERITS (customers);

CREATE OR REPLACE RULE inserts_customer_part1
AS ON INSERT TO customers
WHERE new.id < 1
DO INSTEAD  INSERT INTO customers_part1 SELECT NEW.*;

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

INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 


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




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