Project

General

Profile

Actions

Defect #10537

closed

Several tables do not have a primary key...indirectly causes problems with PostgreSQL in Rails 2.3

Added by M T over 12 years ago. Updated over 12 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Wont fix
Affected version:

Description

There are four tables that do not have a primary key defined in redmine:

  • custom_fields_trackers
  • custom_fields_projects
  • groups_users
  • project_trackers

This is technically not illegal, but some argue this is not good practice for apps using AR.

My problem is that this exposes what I think is a latent bug in ActiveRecord's postgres adapter in Rails 2.3.14, whereby it can't insert new records in these tables using a generic ActiveRecord:Base class model.

PG environment: 9.1.3 with latest db gem, i.e. pg-0.13.2 for x86.

I am using a generic rails database transfer script to move redmine from a mysql database across to postgres. It fails on these tables. The error message shows that the INSERT statement that AR constructs is putting the clause RETURNING ("ID") onto the INSERT statement, but because there is no column called ID, the insert fails. I have raised a bug report 5562 on (github) rails for this.

Here is the clip of code that does the transfer:

         # Now, write out the prod data to the dev db
          DevelopmentModelClass.transaction do
            models.each do |model|
              new_model = DevelopmentModelClass.new(model.attributes)
              if (model.attributes.has_key?('id'))
                new_model.id = model.id
              end
                new_model.save(false)
            end

Even though this problem is technically not a defect in redmine itself, because it looks like we're going to be stuck with rails 2.3 for a while yet, I think this problem needs to be worked around by adding the primary keys. I don't see any harm in it, it's good practice anyway, and it's not a postgres-specific change in terms of redmine db schema.

I have attached a rake db:migrate script to add the missing primary keys.

It also makes it possible to use the very useful "convert" rake script (see attached). Run as db:convert:prod2dev to transfer production to development database, crossing db types in the process. The script was originally written my Rama McIntosh but refined a little by me. (Converting redmine exposed a couple of issues not catered for by the original script).


Files

20120324000000_add_missing_primary_keys.rb (507 Bytes) 20120324000000_add_missing_primary_keys.rb M T, 2012-03-24 03:13
convert.rake (5.51 KB) convert.rake M T, 2012-03-24 03:13
convert.rake (6.16 KB) convert.rake M T, 2012-04-03 12:10
Actions

Also available in: Atom PDF