Weird sequence increasing with partitioned table

karas · #1

When a table is inherited, sequence doesn't increase by 1 but by more than 1.

For example, let be defined table parent with

Code:

CREATE TABLE parent(id serial, message text);

and two tables inheriting parent

Code:

CREATE TABLE child1(CHECK(id >= 1 AND id <= 100)) INHERITS(parent);
CREATE TABLE child2(CHECK(id >= 101 AND id <= 200)) INHERITS(parent);

Also, default id values are dropped for child tables

Code:

ALTER TABLE child1 ALTER id DROP DEFAULT;
ALTER TABLE child2 ALTER id DROP DEFAULT;

Finally, two rules are created to put rows into table child1 when 1 <= id <= 100 or into table child2 when 101 <= id <= 200:

Code:

CREATE OR REPLACE RULE r1 AS ON INSERT TO parent WHERE (id >=1 AND id <= 100) DO INSTEAD
	INSERT INTO child1 VALUES(NEW.id, NEW.message);
CREATE OR REPLACE RULE r2 AS ON INSERT TO parent WHERE (id >=101 AND id <= 200) DO INSTEAD
	INSERT INTO child2 VALUES(NEW.id, NEW.message);

When two rows are put into parent table with

Code:

INSERT INTO parent(message) VALUES('Test 1');
INSERT INTO parent(message) VALUES('Test 2');

they are inserted with id values of 6 and 12. If I continue to insert them, each row has id incremented by 6 instead by 1. Where's the problem?
(Remark: no other users, processes etc., it is test database created just to check out this problem.)

karas · #2

Let's give a simpler form of the problem:

Code:

CREATE TABLE parent(id serial, message text);
CREATE TABLE child() INHERITS(parent);
ALTER TABLE child ALTER id DROP DEFAULT;

If we set a rule

Code:

CREATE OR REPLACE RULE r AS ON INSERT TO parent DO INSTEAD INSERT INTO child VALUES(NEW.id, NEW.message);

then each INSERT into table parent increases sequence parent_id_seq by 1, as it should. But if we add a condition for id when setting the rule like

Code:

CREATE OR REPLACE RULE r AS ON INSERT TO parent WHERE id>=1 AND id <= 100 DO INSTEAD INSERT INTO child VALUES(NEW.id, NEW.message);

then the sequence is increasing by 5 when inserting into parent table.
Why is that happening?

karas · #3

Here's the response:

Quote:

"id" is basically a macro that gets replaced with the expression being
inserted, ie, the nextval() call. So you're getting bit with multiple
evaluation.

There isn't any very safe way to do this sort of thing with a rule.
Consider using a trigger instead.

regards, tom lane

However, this behavior during the table partitioning is not intuitive and it's not documented as a special case.

#4

karas <karas.2jf0q0 (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:

Quote:

"id" is basically a macro that gets replaced with the expression being
inserted, ie, the nextval() call. So you're getting bit with multiple
evaluation.

However, this behavior during the table partitioning is not intuitive
and it's not documented as a special case.

Yes, but it shouldn't be a problem, right?

Yours,
Laurenz Albe

karas · #5

It is a problem, because a table can't be partitioned on primary key using rules.

#6

[the sequence for serial columns gets increased several times when
trying to use a rule to facilitate partitioning]

karas <karas.2jj6q0 (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:

Quote:

It is a problem, because a table can't be partitioned on primary key
using rules.

I see - your problem is that the value of 'id' that actually gets inserted
is different from the one in the condition of the rule right?
This can indeed be a problem.

Maybe you can write a function that 'does the right thing' depending
on the values passed and just invoke that function unconditionally
in an INSTEAD OF INSERT rule? That way the sequence should be increased
only once.

Yours,
Laurenz Albe

karas · #7

I can write a trigger, too.

This behaviour should be considered as a bug, not like a feature.
One more thing related to this one: table inheritance is documented for the version 8.0.0. but it seems that it does not work well for the version 8.0.3. INSERT and DELETE for the inherited table work fine, but DROP and ALTER does not - they slow down the server causing that other queries does not execute at all. With versions 8.1.4 and 8.2.0 everything works fine.

#8

karas <karas.2jvzu0 (AT) no-mx (DOT) forums.yourdomain.com.au> wrote:

Quote:

I can write a trigger, too.

This behaviour should be considered as a
bug, not like a feature.

I do not think that anybody would consider this a feature.

The problem with a pure trigger approach that I can see is that there is
no INSTEAD OF trigger, so you would end up inserting into the parent table
too. You could delete the record again, but that would be a bad solution,
right?

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Weird sequence increasing with partitioned table

comp.databases.postgresql comp.databases.postgresql

Weird sequence increasing with partitioned table - 12-19-2006 , 08:03 AM

12-19-2006 , 10:17 AM

12-26-2006 , 03:42 AM

Re: Weird sequence increasing with partitioned table - 12-27-2006 , 03:59 AM

12-28-2006 , 09:18 AM

Re: Weird sequence increasing with partitioned table - 01-03-2007 , 05:15 AM

01-04-2007 , 07:02 AM

Re: Weird sequence increasing with partitioned table - 01-04-2007 , 08:45 AM