Rails misbehaving wrt Postgres SERIAL NOT NULL column

Question

I am developing a (currently) Rails 2.3.x application with a PostgreSQL 8.4 database backend. In my Rails application, I have a model corresponding to a database table that has two columns of datatype SERIAL and set as NOT NULL. I have one of these columns set as the primary key in both Rails and as a PostgreSQL constraint.

Table definition:

CREATE TABLE problem_table
(
  col1 serial NOT NULL,
  col2 serial NOT NULL,
  other_col1 character varying,
  other_col2 character varying,
  ...,
  CONSTRAINT problem_table_pkey PRIMARY KEY (col1)
);

Model class definition:

class ModelClass1 < ActiveRecord::Base
  self.table_name = 'problem_table'
  self.primary_key = 'col1'
end

My problem is with respect to the non-primary-key SERIAL NOT NULL column. When I try to do a Rails ActiveRecord::Base#create, Rails rightly doesn't set a value for the primary key SERIAL NOT NULL column, but sets a column value of NULL for the other one, which causes PostgreSQL to complain that the NOT NULL column is being set to NULL.

What I tell Rails to do:

ModelClass1.create(
  other_col1: 'normal'
  other_col2: 'data',
  ...
);

What Rails tells PostgreSQL:

INSERT INTO problem_table (
  col2, 
  other_col1, 
  other_col2,
  ...
) VALUES (
  NULL,
  'normal',
  'data',
  ...
);

My question is, how can I get Rails to stop passing NULL for this column and just not pass anything, letting the DEFAULT nextval(my_seq) take over? Or, if that is not possible, how can I tell PostgreSQL to ignore this NULL value when passed and/or recognize this to be the same as 'set as DEFAULT'?

I would try to just monkey patch Rails 2.3.x ActiveRecord internals, but I know if I did so I'd be screwed when it came to transitioning to Rails 3.

I've looked into trying to fix things with a PL/pgSQL trigger BEFORE INSERT, but I can't figure out how to tell PostgreSQL with PL/pgSQL to 'undefine' the NEW.col2 value or say NEW.col2 := DEFAULT (which does not work).

Answers and/or suggestions are appreciated!

Answer 1

Not sure about the precise PL/pgSQL syntax (my PostgreSQL installation is at home so I can't toy with it) but in Oracle PL/SQL I'd do something like

CREATE OR REPLACE TRIGGER MYSCHEMA.PROBLEM_TABLE_BI
  BEFORE INSERT ON MYSCHEMA.PROBLEM_TABLE
  REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  IF :NEW.COL2 IS NULL THEN
    :NEW.COL2 := MY_SEQ.NEXT_VAL;
  END IF;
END PROBLEM_TABLE_BI;

PL/pgSQL should be similar.

Hope this helps.

Answer 2

Found something that works. I potentially need this second SERIAL column in quite a few tables using different sequences for incrementing. Below is a solution where only 1 trigger function is needed for any number of tables containing col1 incremented by a unique sequence.

CREATE OR REPLACE FUNCTION fn_set_col1_as_nextval_sequence_if_null()
  RETURNS trigger AS
$BODY$
  BEGIN
    IF NEW.col1 IS NULL THEN
      SELECT nextval(TG_ARGV[0]) INTO NEW.col1;
    END IF;
    RETURN NEW;
  END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE TRIGGER trg_set_col1_as_nextval_sequence_on_problem_table_create
BEFORE INSERT
ON problem_table
FOR EACH ROW
EXECUTE PROCEDURE fn_set_col1_as_nextval_sequence_if_null('problem_table_col1_seq');

I would still like to figure out how to fix the Rails behavior for 2.3.x and 3.0 if possible, but this will work in the meantime.