1

My code is simple:

eval {
    my $item = $_table1_class->new(
        field1 => $val1,
        field2 => $val2,
        field3 => $val3,
    );
    $item->save;
};
if ($@) {
   .
   .
   .
   # Error handling stuff.
   .
   .
   .
}

When the code runs, I get the error message, "ERROR: null value in column "id" violates not-null constraint". The error message also indicates that the line that the error is in is the "$item->save;" line.

The PostgreSQL database table that I am trying to write to has an id field in addition to field1, field2, and field3. The id field is defined as:

id | integer | not null default | nextval('table1_id_seq'::regclass)

This is reflected in the code for the class that I define for Rose::DB for this table. For the id field for this table I have the code:

id => { 
     type => 'serial', 
     not_null => 1, 
     primary_key => 1, 
     sequence => 'table1_id_seq', 
},

What in the world could be wrong here?

The strange thing is that this was all working this morning. I changed stuff that shouldn't effect this at all and it has just stopped working. I have been pulling my hair out all day. It seems pretty clear to me that Rose::DB should be getting PostgreSQL to create the id from the sequence. But, it seems not to be doing this. I use this straightforward way of updating other tables exactly the same as this particular table (mutatis mutandis) and it works fine for these other tables. But, here it is simply not working.

Does anybody have any ideas about this? Looking on the net, I see that others have had what seem to be related problems, but I cannot find any remedy in posts elsewhere on the net.

Anybody?

  • Check what values the sequence is generating (`select * from sequence_name` is ok for this, just never actually use the resulting information for anything important, only for debugging) and compare them to the maximum value currently in the table. Has your framework reset the sequence to behind the current max in the table? – Craig Ringer May 12 '13 at 03:26
  • Hi Craig. Thank you for responding. The "last_value" is 2 and the "max_value" is 9223372036854775807. Does that help? –  May 12 '13 at 03:33
  • Not without the current maximum value in the table, no. `SELECT max(id) FROM whatever_table_is_the_problem`. – Craig Ringer May 12 '13 at 03:37
  • The current max value in the table is 2. When I do "SELECT max(id) FROM the_table" I get 2. –  May 12 '13 at 03:40
  • If you discard a value with `select nextval('seq_name')` – Craig Ringer May 12 '13 at 04:19
  • If I do that what? I am not clear what you are saying here Craig. –  May 12 '13 at 05:21
  • Whoops, got cut off. The next value in the sequence 2, but 2 is already in the table. You need to throw that value away so the next value generated is appropriate. `SELECT nextval('the_seq_name')` will do so, or you can `SELECT setval('the_seq_name',3)`. – Craig Ringer May 12 '13 at 05:30
  • Craig: Yes, I thought that was probably what you had in mind. I did try that, but it had no effect. I have gone into psql and inserted records into the table with an ordinary insert command and it works fine. The problem is with Rose::DB, not with something in PostgreSQL, I believe. –  May 12 '13 at 15:43
  • Thanks for following up with the resolution. For the record, I wasn't suggesting an issue with PostgreSQL per se, but had been wondering if your ORM had inserted records using its own ID generation and got the table out of sync with the ID generator sequence. Clearly that wasn't the problem ;-) – Craig Ringer May 12 '13 at 23:47

1 Answers1

1

I figured it out. The problem was that in my class for the table, a different field was specified as the primary key from "id". The code read:

primary_key_columns => [ qw( col2 ) ],

(I hadn't written this code.) When I changed this to:

primary_key_columns => [ qw( id ) ],

It worked again. What bothers me is why this worked in the first place. It was definitely working, then stuff I did elsewhere in the code seemed to wake up Rose::DB that something was wrong here and it stopped working.

So, the fault was NOT with Rose::DB here really, but with our code.