Not restored postgresql sequences

[jpascal]

Hello! You make great plugin! =)

I found one bug when used PostgreSQL.

Not restore sequences....

[toy]

Could you provide more info? For example run rake task with --trace.

[jpascal]

$ rake dump --trace

** Invoke dump (first_time)
** Invoke dump:create (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute dump:create
** Invoke db:schema:dump (first_time)
** Invoke environment 
** Execute db:schema:dump
Tables: 100.0%                                      
** Invoke assets (first_time)
** Execute assets
Assets: 100.0%                                     
20101225114034.tgz
** Execute dump

$ rake dump:restore --trace

** Invoke dump:restore (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute dump:restore
** Invoke db:schema:load (first_time)
** Invoke environment 
** Execute db:schema:load
-- create_table("backup", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "backup_id_seq" for serial column "backup.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "backup_pkey" for table "backup"
   -> 0.1370s
-- create_table("cities", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "cities_id_seq" for serial column "cities.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
   -> 0.0748s
-- create_table("comments", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "comments_id_seq" for serial column "comments.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "comments_pkey" for table "comments"
   -> 0.1250s
-- create_table("countries", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "countries_id_seq" for serial column "countries.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "countries_pkey" for table "countries"
   -> 0.0748s
-- create_table("events", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "events_id_seq" for serial column "events.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "events_pkey" for table "events"
   -> 0.1251s
-- create_table("events_users", {:force=>true, :id=>false})
   -> 0.0163s
-- create_table("experts", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "experts_id_seq" for serial column "experts.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "experts_pkey" for table "experts"
   -> 0.1168s
-- create_table("groups", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "groups_id_seq" for serial column "groups.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
   -> 0.0665s
-- create_table("groups_roles", {:force=>true, :id=>false})
   -> 0.0164s
-- create_table("groups_users", {:force=>true, :id=>false})
   -> 0.0165s
-- create_table("menuitems", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "menuitems_id_seq" for serial column "menuitems.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "menuitems_pkey" for table "menuitems"
   -> 0.1166s
-- create_table("menus", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "menus_id_seq" for serial column "menus.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "menus_pkey" for table "menus"
   -> 0.0814s
-- create_table("news", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "news_id_seq" for serial column "news.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "news_pkey" for table "news"
   -> 0.0998s
-- create_table("options", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "options_id_seq" for serial column "options.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "options_pkey" for table "options"
   -> 0.1248s
-- create_table("pages", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "pages_id_seq" for serial column "pages.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pages_pkey" for table "pages"
   -> 0.1245s
-- create_table("profiles", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "profiles_id_seq" for serial column "profiles.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "profiles_pkey" for table "profiles"
   -> 0.1249s
-- create_table("regions", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "regions_id_seq" for serial column "regions.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "regions_pkey" for table "regions"
   -> 0.0830s
-- create_table("roles", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
   -> 0.1415s
-- create_table("users", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
   -> 0.1330s
-- initialize_schema_migrations_table()
   -> 0.0029s
-- assume_migrated_upto_version(20101225102158, "db/migrate")
   -> 0.0029s
** Invoke db:schema:dump (first_time)
** Invoke environment 
** Execute db:schema:dump
Tables: 100.0%                                      
** Invoke assets:delete (first_time)
** Invoke assets (first_time)
** Execute assets
** Execute assets:delete
Assets: 100.0%  

This error on insert new record.

ActiveRecord::StatementInvalid (PGError: ERROR:  duplicate key violates unique constraint "menus_pkey"
: INSERT INTO "menus" ("created_at", "enabled", "name", "updated_at") VALUES ('2010-12-25 11:48:36.637673', 'f', '11', '2010-12-25 11:48:36.637673') RETURNING "id"):

Need do somthing.. on each tables...

SELECT max(id) into v FROM menus
SELECT setval('menus_id_seq', max_id);

[toy]

Try running rake db:schema:load (note that data will be delated), it should show same notices.

[jpascal]

$ rake db:schema:load --trace

** Invoke db:schema:load (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:schema:load
-- create_table("backup", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "backup_id_seq" for serial column "backup.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "backup_pkey" for table "backup"
   -> 0.3169s
-- create_table("cities", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "cities_id_seq" for serial column "cities.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
   -> 0.0831s
-- create_table("comments", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "comments_id_seq" for serial column "comments.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "comments_pkey" for table "comments"
   -> 0.1083s
-- create_table("countries", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "countries_id_seq" for serial column "countries.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "countries_pkey" for table "countries"
   -> 0.0748s
-- create_table("event_photos", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "event_photos_id_seq" for serial column "event_photos.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "event_photos_pkey" for table "event_photos"
   -> 0.0752s
-- create_table("event_videos", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "event_videos_id_seq" for serial column "event_videos.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "event_videos_pkey" for table "event_videos"
   -> 0.0581s
-- create_table("events", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "events_id_seq" for serial column "events.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "events_pkey" for table "events"
   -> 0.1167s
-- create_table("events_users", {:force=>true, :id=>false})
   -> 0.0166s
-- create_table("experts", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "experts_id_seq" for serial column "experts.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "experts_pkey" for table "experts"
   -> 0.1001s
-- create_table("groups", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "groups_id_seq" for serial column "groups.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
   -> 0.0834s
-- create_table("groups_roles", {:force=>true, :id=>false})
   -> 0.0164s
-- create_table("groups_users", {:force=>true, :id=>false})
   -> 0.0166s
-- create_table("menuitems", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "menuitems_id_seq" for serial column "menuitems.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "menuitems_pkey" for table "menuitems"
   -> 0.1398s
-- create_table("menus", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "menus_id_seq" for serial column "menus.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "menus_pkey" for table "menus"
   -> 0.0746s
-- create_table("news", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "news_id_seq" for serial column "news.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "news_pkey" for table "news"
   -> 0.1332s
-- create_table("options", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "options_id_seq" for serial column "options.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "options_pkey" for table "options"
   -> 0.0831s
-- create_table("pages", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "pages_id_seq" for serial column "pages.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pages_pkey" for table "pages"
   -> 0.1081s
-- create_table("photos", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "photos_id_seq" for serial column "photos.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "photos_pkey" for table "photos"
   -> 0.1162s
-- create_table("profiles", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "profiles_id_seq" for serial column "profiles.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "profiles_pkey" for table "profiles"
   -> 0.1252s
-- create_table("regions", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "regions_id_seq" for serial column "regions.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "regions_pkey" for table "regions"
   -> 0.0830s
-- create_table("roles", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
   -> 0.1415s
-- create_table("users", {:force=>true})
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
   -> 0.1330s
-- initialize_schema_migrations_table()
   -> 0.0034s
-- assume_migrated_upto_version(20101225120505, "db/migrate")
   -> 0.0148s

[toy]

As you can see this problem is in rails, postgres gem/database or maybe in database.yml. Though I'll try to test dump with postgres.

[jpascal]

:-) No problem with it.

Secuenses changes when need generate new value for relaten table (ID - serial).

You plugin insert with declaring ID of records from dump. In this case postgresql not call changes in related sequence.

For fixing this need do also two steps on each table after inserts to table.

SELECT max(id) FROM table
SELECT setval('table_id_seq', max_id);

[jpascal]

My self rake task:

namespace :repair do
  desc "Reparin sequences in PostgreSQL"
  task :sequences => :environment do
    ActiveRecord::Base.connection.tables.each do |table|
      if ActiveRecord::Base.connection.column_exists?(table,"id")        
        result = ActiveRecord::Base.connection.select_one("SELECT max(id) FROM #{table}")
        if result["max"]
          puts "Update public.#{table}_id_seq = #{result["max"]}"
          ActiveRecord::Base.connection.select_one("SELECT setval('public.#{table}_id_seq', #{result["max"]}, true);")
        end
      end
    end
  end  
end

To do:

$ rake dump
$ rake dump:restore
$ rake repair:sequences

Right now work all perfect! :) Please add this in you plugin.