ActiveRecord: PostgreSQL column default expressions

Hi! I’ve recently found out that ActiveRecord allows you to set a column default value to a PostgreSQL expression since this PR for Rails 5: Add `:expression` option support on the schema default by kamipo · Pull Request #20005 · rails/rails · GitHub

I’m trying to do something where I add a column with a random value to a table. I’ve used the default value -> { "floor(random() * #{2**31} +1)" } to set it up, and set the field as not null. The strange thing is, because the default value is a DB expression, I guess ActiveRecord doesn’t “find out” about it since the value isn’t known until after the insert. This ends up meaning that the model’s field is set to nil initially, but if I reload the model, it gets the value that the database generated. Wondering if this is a bug in ActiveRecord or if it’s expected that when DB expressions are used the column values might not be read until the record is reloaded? It is pretty surprising to have a not-null constraint and then have the field read as null until the record is reloaded.

I set up a simple test case to demonstrate this issue: Test out using a DB expression with random() as a column default value by ibrahima · Pull Request #1 · ibrahima/rails_experiments · GitHub

(the base is a default Rails 6 app with PostgreSQL configured.)

Would appreciate guidance on whether this is expected behavior or a bug. I’d like to use this type of column default in my application so I’d like to understand the implications of doing this. Thanks!

I just tested this out with UUID columns since that was referenced in the PR that added expression support as a field type that already supported expressions. Those still have the same issue. But thinking about it, Rails does something for the primary key (ID) field to be loaded after the insert, so why are other fields not loaded? Perhaps there’s a way to add that behavior to other columns? I imagine after doing the insert ActiveRecord gets the row data somewhere hopefully (but maybe it’s just returning ID and not all the columns)?

This is just a guess, but I wonder if executing these expressions on initialization would be costly :thinking:

Hmm, could you clarify what you mean by that? The expressions are running within the DB on insert and so the expression is executed and the value is being stored whether or not the value makes its way back to Rails, it’s just that ActiveRecord is not reading that stored value when the record is saved.

I guess for the primary key case, ActiveRecord explicitly grabs the return value of inserting the record and sets it to the id attribute: rails/persistence.rb at b678667853b48751ea6134f1f8ba3d687c943889 · rails/rails · GitHub. I haven’t dug through all the layers of ActiveRecord/pg at this point but it seems likely that that method only returns the ID of the inserted record and not any other columns, so that would explain why this behavior exists. It would be nice if there was a way to tell ActiveRecord to return and refresh certain columns when doing an insert but in the meantime I guess I can use this behavior knowing how it works.

Ah never mind it does look like it’s a known issue, and in fact the issue thread that led me to the feature PR actually mentions the reloading issue.

I guess I’ll just manually reload the record for now…

@ibrahima Sorry it doesn’t seem like there is a better solution yet.

I did find two PRs that might help here though:

It would be great to get some more eyes on these, try them out and let us know if you find any bugs! :bow:

Hi @zzak, ah yeah it looks like the second PR to add a RETURNING feature for PostgreSQL would probably do the trick. Thanks for pointing that out!

At this point I’m in the early phase of implementing something and deciding whether or not to use the database-generated default values or not, but if I get a chance, I can try out the RETURNING feature with my simple test case.

Thanks!

1 Like