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.

Answer 1

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.

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