Postgres migrations do not respect functional defaults for columns

[kenaniah]

PostgreSQL allows columns to have functional defaults, but Rails does not appear to support this when using the migrations API. The most notorious example of this is outlined below:

CREATE TABLE a ( t1 TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- works
CREATE TABLE b ( t1 TIMESTAMPTZ DEFAULT NOW() ); -- works
CREATE TABLE c ( t1 TIMESTAMPTZ DEFAULT 'NOW()' ); -- fails and uses a static timestamp

Tables A and B defined above will work as expected, allowing any new rows to have a proper default value that is calculated at the time of insert. Table C, however, will convert the string 'now' to the current time when the table is defined, causing all inserted default values to reflect the static timestamp instead.

When attempting to use the migration API, one might write code such as:

create_table :c do |t|
  t.timestamp default: 'NOW()'
end

This unfortunately yields the broken use-case of table C from the SQL above.

Proposed Solution

To ensure backwards-compatibility, database portability, and coverage of cases not listed here, I propose adding an option such as raw_default that could be used to pass unquoted defaults to the database in the DDL of a migration.

create_table :c do |t|
  t.timestamp raw_default: 'NOW()'
end

Which would yield the following DDL:

CREATE TABLE c ( t1 TIMESTAMPTZ DEFAULT NOW() );

[yawboakye]

I had this issue too, brought it up on the #RubyOnRails IRC channel. I don't remember well but I think someone talked about a feature coming in Rails 5 that solves our problem. In the meantime I have to write raw SQL in order to add a functional default. But the problem with this solution is that since ActiveRecord's SQL returns only the id after successful insertion the default values generated by Postgres are ignored.

[kenaniah]

@yawboakye fortunately that's a non-issue since Postgres will return the entire row if you append RETURNING * onto the end of an INSERT or UPDATE statement. This can be accomplished at the connection adapter level and would be completely transparent from the perspective of userland.

[yawboakye]

@kenaniah Sure, but it makes sense why the full row shouldn't be returned by default: there could be too much data, and ActiveRecord already knows both default and non-default values. (Or at least it thinks so.) But if the default value is generated by a function in PG then ActiveRecord doesn't know. Turns out that is not enough reason to make RETURNING * the default, and I'm ok with that. I think what we need is another parameter to tell ActiveRecord, that for a particular model, it should use RETURNING * instead.

[kenaniah]

@yawboakye in that case, the RETURNING clause could be used to only change the fields that used raw defaults as a targeted solution. I agree that there's no need to return the entire row if we already know the values of the other fields.

[yawboakye]

@kenaniah 👍

[kamipo]

Related issue with #20005.

[rails-bot]

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 5-0-stable branch or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

[jhirn]

Hi. I wanted to +1 on this issue since it's stale but still happens. I'd be happy to talk through expected behavior and take a stab at a PR if it's welcomed. It is a bit confusing and arguably incorrect to have the model instance not reflect the DB record after create or update.

Currently resolving this by doing a reload after_create since it for a default. I could use a pluck but my my model is small enough. Using targeted returning would be ideal for dbs that support it.

I believe an option to set this explicitly in the model is a necessary although many cases can be automatically detected. If there are triggers in play, support for updates might also be nice although it complicates things in all but naive cases.

[maclover7]

@jhirn Can you confirm whether the reproduction information in the original issue body works for you? If not, can you create an executable test script using one of the templates here, that would demonstrate the problem you are seeing?

[jhirn]

Hi @maclover7

Here's a script to demonstrate the lack of default values for any database generated columns being set on the model without an explicit reload.

I'm not 100% sure that was the exact original issue and this might be out of AR's hands, especially for databases that don't support RETURNING. I just wanted to share it's potentially confusing to have a model in memory that doesn't match what the database values are after a save operation. This could also be a trigger on update.

Perhaps something to flag database managed columns and manage defaults or after update triggers? That might go against the philosophy of Rails keeping DB logic within the apps.

I'd be interested to participate in conversation about that might look like, or at least definitively rule it out.

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'rails', github: 'rails/rails'
  gem 'arel', github: 'rails/arel'
  gem 'pg'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# Ensure backward compatibility with Minitest 4
Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

# This connection will do for database-independent bug reports.

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'template1')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  execute 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
  execute 'CREATE SEQUENCE IF NOT EXISTS default_power_level START 9000'

  create_table :payments, force: true do |t|
    t.uuid :uuid_default, default: 'uuid_generate_v4()'
    t.uuid :uuid_default_as_proc, default: -> { 'uuid_generate_v4()' }
    t.integer :non_id_sequence, default: -> { "nextval('default_power_level')" }
    t.time :time_as_default, default: -> { 'now()' }
    t.integer :default_literal, default: 8
    t.integer :default_ruby_proc, default: -> { 4 + 4 }
    t.string :default_string_as_proc, default: -> { "nextval('default_power_level')" }
  end
end

class Payment < ActiveRecord::Base
end

class BugTest < Minitest::Test
  extend Minitest::Spec::DSL

  let(:p) { Payment.create }

  def test_default_uuid
    refute_nil p.uuid_default
  end

  def test_default_uuid_as_proc
    refute_nil p.uuid_default_as_proc
  end

  def test_default_from_sequence
    refute_nil p.non_id_sequence
  end

  def test_default_now
    refute_nil p.time_as_default
  end

  def test_default_literal
    assert_equal 8, p.default_literal
  end

  def test_default_ruby_proc
    assert_equal 8, p.default_ruby_proc
  end

  def test_default_string_as_proc
    refute_equal "nextval('default_power_level')", p.default_string_as_proc
    refute_nil p.default_string_as_proc
  end

  def test_db_functions_not_null_after_reload
    p.reload
    refute_nil p.uuid_default
    refute_nil p.uuid_default_as_proc
    refute_nil p.non_id_sequence
    refute_nil p.time_as_default
  end
end

postgres-function-defaults.rb.txt

[januszm]

Also I think that there's a common confusion around the world about using this default: 'uuid_generate_v4()' with MySQL, which doesn't have this function. But still, this is passed to db engine as string, not function call.

ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid DEFAULT 'uuid_generate_v4' NOT NULL' at line 1: ALTER TABLE `households` ADD `uuid` uuid DEFAULT 'uuid_generate_v4' NOT NULL