Skip to content

Instantly share code, notes, and snippets.

@tadman
Created February 19, 2015 21:51
Show Gist options
  • Save tadman/113350a6bafe5baa8422 to your computer and use it in GitHub Desktop.
Save tadman/113350a6bafe5baa8422 to your computer and use it in GitHub Desktop.
Postgres Database Sequence Resync Rake Task
namespace :db do
namespace :sequences do
desc 'Brings Postgres database sequences into sync with MAX(id)'
task sync: :environment do
ActiveRecord::Base.connection.execute("SET search_path TO public")
updates = ActiveRecord::Base.connection.select_rows(%Q{
SELECT 'SELECT setval('''||_schema||'.'||_seq||''', (SELECT MAX('||_column||') FROM '||_schema||'.'||_table||')+1); ' AS updatestring
FROM (
SELECT n.nspname AS _schema,
c.relname AS _table,
a.attname AS _column,
SUBSTRING(d.adsrc FROM E'nextval\\\\(''([^'')]+)''') AS _seq
FROM pg_attrdef AS d
JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
JOIN pg_class AS c ON c.oid = d.adrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE adsrc LIKE 'nextval(''%'
) t
})
updates.each do |query|
ActiveRecord::Base.connection.execute(query[0].sub(/\;\s+$/, ''))
end
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment