Postgres serial value incrementing by 2

Question

I have some tables in my postgres database, and a few of them inherit from each other.

For example:

CREATE TABLE parent_table(parent_table_id SERIAL PRIMARY KEY, 
       my_col1 VARCHAR(16) NOT NULL, 
       my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL);
CREATE TABLE child_table() INHERITS (parent_table);
CREATE TABLE step_child_table() INHERITS (parent_table);

I have a trigger that will let me perform an insert on parent_table, and then figure out whether or not the data should really go into child or step_child table.

for example:

CREATE OR REPLACE FUNCTION my_parent_trigger() RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.my_col1 > 100) THEN
      INSERT INTO child_table(my_col1, my_timestamp) VALUES (NEW.my_col1, NEW.my_timestamp);
  ELSE
      INSERT INTO step_child_table(my_col1, my_timestamp) VALUES (NEW.my_col1, NEW.my_timestamp);
  END IF;
  RETURN NULL;
END;
$$
CREATE TRIGGER my_trigger BEFORE INSERT ON parent_table FOR EACH ROW EXECUTE PROCEDURE my_parent_trigger();

The problem is that my indexes are incrementing by 2. If I insert some data, and I do a select on parent_table, I see that my indexes start at 2, and go to 4, 6, 8, 10,... If I do a select on the child_table, I will see my indexes (say 2, 8, and 10), and the rest will be in the other table.

Why is this? Is there a way I can stop it? The trigger is set up BEFORE the insert, so I dont see why the serial would get incremented twice.

Does it matter?

Thanks in advance.

Magnus Hagander · Accepted Answer · 2010-02-18 09:32:13Z

2

When you insert into the child table, you need to include the parent_table_id column, I think.

Right now, the sequence is stepped forward once for the parent insert, and the value from that is passed to your function in NEW. You then throw that away, and ask the system to generate a new one when you insert into the child table(s).

So try to just include parent_table_id in the list of columns, and insert the value of NEW.parent_table_id in it.

answered Feb 18, 2010 at 9:32

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cornercuttin Over a year ago

that seemed to solve my problem. My inserts now look like "INSERT INTO child_table(parent_table_id, my_col1, my_timestamp) VALUES (NEW.my_parent_id, NEW.my_col1, NEW.my_timestamp); It solved my problem, but is this a legal move? I'm not screwing with any locks or anything by manipulating a serial value like this am i?

Tometzky Over a year ago

It is all right because child tables use the same sequence as parent table.

Magnus Hagander Over a year ago

No, that is absolutely a legal move. If anything, it's "more legal" than what you originally did, because having defaults firing on the childs can lead do.. Eh. Interesting things. If not done right :-)

Wayne Conrad · Accepted Answer · 2010-02-17 22:50:20Z

2

Even if you get this code to only increment the sequence by one, you can't in general count on there being no gaps in a column initialized from a sequence: begin; insert into foo (...); abort will leave any sequences incremented even though the transaction was aborted.

A. Elein Mustain shows how to create a gapless sequence.

answered Feb 17, 2010 at 22:50

Wayne Conrad

109k26 gold badges158 silver badges195 bronze badges

Collectives™ on Stack Overflow

Postgres serial value incrementing by 2

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related