0

I have a Rails 7 model that uses Postgres' virtual column feature:

create_table :time_entries do |t|
  # ...
  t.virtual :duration, type: :interval, as: %(("to" - "from")::interval), stored: true, null: false
  # ...
end

The problem is, that after I create a record via Rails create(...) these virtual column is nil:

[16] pry(main)> TimeEntry.create(from: Time.zone.now, to: 1.day.from_now)

  TRANSACTION (0.3ms)  BEGIN
  TimeEntry Create (0.5ms)  INSERT INTO "time_entries" ("from", "to", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"
                            [["from", "2022-11-18 06:45:11.419000"], ["to", "2022-11-19 06:45:11.420000"], ["created_at", "2022-11-18 06:45:11.420862"], ["updated_at", "2022-11-18 06:45:11.420862"]]
  TRANSACTION (0.9ms)  COMMIT
=> #<TimeEntry:0x0000ffff86f1ae10
 id: 13,
 from: Fri, 18 Nov 2022 06:45:11.419000000 UTC +00:00,
 to: Sat, 19 Nov 2022 06:45:11.420000000 UTC +00:00,
 duration: nil,
 created_at: Fri, 18 Nov 2022 06:45:11.420862000 UTC +00:00,
 updated_at: Fri, 18 Nov 2022 06:45:11.420862000 UTC +00:00>

When you reload the model, duration is set.

I found out, that this is due to Rails only returning the id column using RETURNING "id" at the end of the INSERT INTO statement. When you execute the query in Postgres directly you can return the generated duration column directly after the insert:

app_development=# INSERT INTO "time_entries" ("from", "to", "created_at", "updated_at") VALUES ( '2022-11-18 06:34:46.889000', '2022-11-18 06:34:56.889000', '2022-11-18 06:34:46.889000', '2022-11-18 06:34:46.889000') RETURNING "duration", "id";
 duration | id
----------+----
 00:00:10 | 11
(1 row)

Is it possible to customize the RETURNING "id" part of the SQL query in my model, so that the instance of TimeEntry already have the duration set after I create it?

EDIT:

I found the code segment inside the Postgres Adapter and tried to monkey patch it like this:

require "active_record/connection_adapters/postgresql/database_statements"

module PostgresReturningPatch
  def sql_for_insert(...)
    sql, *args = super
    if sql.include?(TimeEntry.table_name) && sql.ends_with?(%(RETURNING "id"))
      returning_virtual_columns = TimeEntry::columns.select(&:virtual?).map do |column|
        quote_column_name(column.name)
      end.join(", ")
      sql += ", #{returning_virtual_columns}"
    end
    binding.pry
    [sql, *args]
  end
end

ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.module_eval do
  prepend PostgresReturningPatch
end

Unfortunately, it's still nil when Rails returns the instance of my model, despite that the SQL ends now with RETURNING "id", "date", "duration".

23tux
  • 14,104
  • 15
  • 88
  • 187
  • I wouldn't really expect ActiveRecord to do know what to do with additional returned columns besides the primary key. I tried to dig though the source out of curisisty and when you go down the rabbit hole from `create` you end up in [`_insert_record`](https://github.com/rails/rails/blob/8015c2c2cf5c8718449677570f372ceb01318a32/activerecord/lib/active_record/persistence.rb#L477) but I have not been able to locate where it actually assigns the returned columns to the record. – max Nov 19 '22 at 10:09

0 Answers0