Notices for Sequence After Running Migration in Rails on Postgresql Application

NOTICES for sequence after running migration in rails on postgresql Application

Rails (ActiveRecord to be more precise) is adding an id column to your table and making this column the primary key. For PostgreSQL, this column will have type serial. A serial column is essentially a four byte integer combined with a sequence to automatically provide auto-incrementing values.

The first notice:

NOTICE: CREATE TABLE will create implicit sequence "notification_settings_id_seq" for serial column "notification_settings.id"

is just telling you that PostgreSQL is creating a sequence behind the scenes to make the serial column function.

The second notice:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "notification_settings_pkey" for table "notification_settings"

is just telling you that PostgreSQL is creating an index to help implement the primary key even though you didn't explicitly ask it to.

You can just ignore these notices, they're just informational. If you want to suppress them, you can add min_messages: WARNING to the appropriate section of your database.yml.

Rails (PostgreSQL?) implicit sequence/index warning

No. That's just Postgres being awesome and automatically creating stuff for you that you actually want. It's not a warning it's just an FYI

ALTER SEQUENCE on rake db:reset db:migrate not working

I suppose rake db:migrate:reset.

Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Use CREATE SEQUENCE:

CREATE SEQUENCE scores_job_id_seq;  -- = default name for plain a serial

Then add a column default to scores.job_id:

ALTER TABLE scores ALTER COLUMN job_id SET DEFAULT nextval('scores_job_id_seq');

If you want to bind the sequence to the column (so it is deleted when the column is deleted), also run:

ALTER SEQUENCE scores_job_id_seq OWNED BY scores.job_id;

All of this can be replaced with using the pseudo data type serial for the column job_id to begin with:

• Safely and cleanly rename tables that use serial primary key columns in Postgres?

If your table already has rows, you may want to set the SEQUENCE to the next highest value and fill in missing serial values in the table:

SELECT setval('scores_job_id_seq', COALESCE(max(job_id), 1)) FROM scores;

Optionally:

UPDATE scores
SET    job_id = nextval('scores_job_id_seq')
WHERE  job_id IS NULL;

• How to check a sequence efficiently for used and unused values in PostgreSQL
• Postgres manually alter sequence
• How to reset postgres' primary key sequence when it falls out of sync?

The only remaining difference, a serial column is also set to NOT NULL. You may or may not want that, too:

ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;

But you cannot just alter the type of an existing integer:

ALTER TABLE scores ALTER job_id TYPE serial;

serial is not an actual data type. It's just a notational convenience feature for CREATE TABLE.

In Postgres 10 or later consider an IDENTITY column:

• Auto increment table column

How do I avoid the implicit sequence on the id field during rails migration

ActiveRecord make the :primary_key column auto_increment by default. Maybe you have to create the primary key by yourself if you don't want it auto_increment.

create_table :table_name, :id => false do |t|
  t.integer :id
  t.timestamps
end
ActiveRecord::Base.connection.execute("ALTER TABLE table_name ADD PRIMARY KEY (id)")

How to add sequences to a migration and use them in a model?

I have no suggestions for a more 'rails way' of handling custom sequences, but I can tell you why the customer_no field appears not to be being populated after a save.

When ActiveRecord saves a new record, the SQL statement will only return the ID of the new record, not all of its fields, you can see where this happens in the current rails source here https://github.com/rails/rails/blob/cf013a62686b5156336d57d57cb12e9e17b5d462/activerecord/lib/active_record/persistence.rb#L313

In order to see the value you will need to reload the object...

cust = Accounts::Customer.new
cust.save
cust.reload

If you always want to do this, consider adding an after_create hook in to your model class...

class Accounts::Customer < ActiveRecord::Base
  after_create :reload
end

Why is rails 5 adding nextval method in schema file?

This is a bit long of an answer, so I've broken it into sections. Buckle up!

My theory

My guess is that your development database does contain the lessons_id_seq sequence, and that its definition of flightlessons.id is set to depend on it (i.e., exactly what Rails is putting into your schema file).

How and why? You likely renamed the lessons table to flightlessons at some point in the past, but that rename didn't change the sequence that the table depended on -- and since schema.rb does not record sequences, the lessons_id_seq sequence does not get copied to your test database, and thus you get this error.

To verify my theory, run rails db and try the following commands:

\d lessons_id_seq

This should return the definition of that sequence. Then, try:

\d flightlessons

And look at the definition of the id column. I expect it to include DEFAULT nextval('lessons_id_seq').

Fixes

The easiest way to fix this is to switch to using structure.sql instead of schema.rb (see the docs). This will carry over the exact state of your database and avoid any interference or interpretation by Rails, which is what's causing your current issue. I always recommend structure.sql for production systems.

However, you can also go into your development database and change the sequence name:

ALTER SEQUENCE lessons_id_seq RENAME TO flightlessons_id_seq;
ALTER TABLE flightlessons ALTER COLUMN id SET DEFAULT nextval('flightlessons_id_seq');

This would be a terrible idea on a production system, but if your issue is just local, it should rectify your current database state with your schema.rb and thus address your current problem. You may wish to encode that into a migration, if you want rails db:drop db:create db:migrate to work on a fresh app.

Why now?

The behavior where Rails is dumping out the default value for your table's primary key may very well be new in Rails 5. Previously, Rails may have just trusted that your ID column had a sane default, and ignored whatever value it actually saw. But I haven't done the research to see if that's true or not.

What's the best practice for changing migration timestamp so that it changes the order a migration is run in rails?

The prefix of the file name is whats used to determine order, not the timestamp on the file itself just so you know, but yes that would work.

Remember you can:

rails db:rollback <number of migrations>

To undo any you've applied.

Heroku Rails db migration file order and relationships

I fixed all my migrations and got the API running on Heroku server now.

The real answer is: deploy to Heroku early, don't wait till after finishing development on local machine then deploy.

Deploy early will identify problems early like my migration discrepancies between SQlite and PostgreSQL.

Additionally, when doing references in migration file, if not using matching model and table names e.g. author vs user, modify the migration file to use add_foreign_key before running the migration.

For example:

class CreateBooks < ActiveRecord::Migration[5.0]
  def change
    create_table :books do |t|
      t.string :title
      t.boolean :adult_content
      t.references :author, foreign_key: true

      t.timestamps
    end
  end
end

Needs to become:

class CreateBooks < ActiveRecord::Migration[5.0]
  def change
    create_table :books do |t|
      t.string :title
      t.boolean :adult_content
      t.references :author, index: true # this line changed

      t.timestamps
    end

    # new foreign key specifying correct table name and column
    add_foreign_key :books, :users, column: :author_id 

  end
end

Found the new knowledge from this link:

http://sevenseacat.net/2015/02/24/add_foreign_key_gotchas.html

---

Related Topics