How to make Rails use default DB values on INSERT?

rubyonrails-talk
1

When creating a new record, rails does not use the default DB values that are functions. For example, ``col1 timestamp with time zone not null default NOW().'' Instead, Rails sets col1 to "NULL" on an INSERT and my Postgresql DB complains and fails.

Using magical column names, such as created_at is not an option. I want the DB to set the timestamps, not the Rails application servers.

If Rails would just leave these untouched/unset/nil columns out of the INSERT statement, everything would be fine because the DB would set the values using its defaults. I have tried removing the instance variables using the method remove_instance_variables and have tried removing elements in the attributes hash using attr_accessible, that correspond to the nil columns; nothing works. Also, I don't want to resort to custom SQL queries.

Many people have mentioned this issue on the web, but I cannot find a resolution. Also, at least three tickets, #4540, #2257, #3913, on dev.rubyonrails.org/ticket/ have mentioned this same thing. It seems this issue has been in limbo between fixed and broken.

I have searched through active_record/connection_adapters/postgresql_adapter.rb in my Rails Edge for parsings of "now" (DB function), but came up empty. It seems that the code that fixed this in the tickets I mentioned above was for Rails 1.1. Unfortunately, it looks like this patch was removed recently.

Suggestions and insights will be greatly appreciated.

-pachl

2

The first thing that comes to mind is removing the null constraint and using a trigger to update col1. I'm sure there is a better way to handle this, but the following should work.

CREATE FUNCTION touch() RETURNS "trigger"     AS $$ begin NEW.col1 = now(); return NEW; end; $$     LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER t_tablename     AFTER INSERT OR UPDATE ON tablename     FOR EACH ROW     EXECUTE PROCEDURE touch();

3

Cayce Balara wrote:

Looked at my log, more information...

Here's the insert statement:

INSERT INTO brands (`name`, `TESTF`, `TESTR`, `description`) VALUES('TESTRUBY', NULL, 'XXX', 'TESTRUBY')

So, in the case of the timestamp, yes Rails is inserting NULL as you said, and apparently MySQL deals well with this but PostGreSQL does not.

Actually, I think Postgresql is doing the right thing. What if you wanted TESTF=NULL?

I found the reason for NULL being inserted for the timestamp column in an embedded comment in the file postgresql_adapter.rb. This snippit is at the end of the method ``default_values''.

  # Anything else is blank, some user type, or some function   # and we can't know the value of that, so return nil.   return nil

I believe the reason for not accepting user-defined data and DB functions is that they would not pass the SQL validation process. For example, to use the DB's NOW() function, it would need to be passed as a non-quoted string. This would violate the validation of a timestamp column. The way Rails is setup currently, it just doesn't worry about non-conforming data types and just uses nil/NULL.

ALTER TABLE `brands` ADD `TESTR` VARCHAR( 5 ) NOT NULL DEFAULT 'XXX'; ALTER TABLE `brands` ADD `TESTF` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Interestingly (to me at least) is that Rails pulled the default value for the character field from the database dictionary and used that in the INSERT statement.

Yes, that's because the TESTR column is a VARCHAR and the default value is a string ('XXX'). This conforms to Rails's rules. I verified this behavior in the ActiveRecord source. So, Rails treats MySQL and PostgreSQL the same (i.e. builds the same INSERT query), but the two DBs handle incoming requests differently. I have used MySQL in the past and IIRC, a NULL value for a timestamp field would result in using the default value. This works, but sometimes you may want a NULL. I think Postgresql is doing the right thing according to SQL standards.

So you are correct, Rails is not leaving these fields 'alone' and defaulting to the database. Sorry - talking/posting as I think through it, it's an interesting issue.

I just wish that Rails would leave the nil key/value pairs out of the SQL INSERT queries. This would be more efficient and would let the DB make the final decision on what gets stored in the DB, which is how it should be. Rails is not the only application using the DB. I wonder if this behavior would break Rails in some way.

-pachl

4

Jean-Etienne Durand wrote:

Hi,

AR needs all the fields values for validating. Leaving default values at the db level is then not possible.

I wonder then if it is possible to some how remove the attributes before an insert so they are not included in the query? I have done this and it works great when inserting a record, but I'm not sure if it breaks something of which I am unaware.

Summary of my hack: I remove all key/value pairs from <model_obj>.attributes that are nil before creating a record. After the record has been inserted, I reload the object attributes from the DB.

class User < ActiveRecord::Base   after_create :reload

  def before_create     @attributes = @attributes.delete_if {|k,v| v.nil?}   end end

After setting only the "application required" fields from the controller, I call save on the model object. Below is the simple and elegant SQL query constructed by ActiveRecord, which is sent to Postgresql: INSERT INTO users ("pw", "usr", "email") VALUES('XXXX', 'username', 'usr@email.com')

**The users table contains about ten additional columns. The non-null columns are then set by the DB using default values. The "null-able" columns are then set by the DB to NULL or thier default values.

Can someone please tell me if I am blatantly breaking something? Otherwise, I will continue to use this hack and monitor its side effects.

-pachl

5

values.

Can someone please tell me if I am blatantly breaking something? Otherwise, I will continue to use this hack and monitor its side effects.

My first thought is that if you can supply the values you need in rails, then do that instead of using default values in the database. That seems to be the most coherent and logic way unless you run into cases where it won't work for whatever reason. I do think that there should be something added to AR to deal with this so people don't end up having to create their own hacks. If you don't want a column included in the insert, there should be a supported way to handle that scenario.

Maybe the only other thing I might add is something to restrict which columns can be deleted from the insert.

@attributes = @attributes.delete_if {|k,v| v.nil? and ['col1','col2'].include?(k)}

6

Why not simply use a before_save callback on your model to populate the fields.

class Person def before_save self.time_column ||= Time.now end end

-Jonathan.

7

I just wish that Rails would leave the nil key/value pairs out of the SQL INSERT queries.

That breaks things. SQL cannot determine then if you WANT a null vs. a default.

This would be more efficient and would let the DB make the final decision on what gets stored in the DB, which is how it should be.

But standard SQL does not work like this. If you have a column that IS nullable, but has a default, you have to, from the application's side, determine if you want a null there, or to use the default.

SQL's behavior is very well defined here; you insert a NULL, you get a null, you don't insert the value at all, then and only then, the default is used.

*RAILS* should have some way of saying "don't insert this column value". But it should not simply always use null, NOR should it always leave the nulls out. Either way breaks things.

8

If you want to be able to set a field value to "NULL" when you create the record, then you wouldn't set a default value in the database.

That's simply not true. "NULL" in many cases can be used as a reasonable value. The fact a column is set to null might mean something to some application. (I'm not defending that type of design mind you, only that it is valid.)

By it's very nature, a default value says "never let this field be anything else on creation of a new record".

To you, maybe, but not universally. Defaults mean exactly that; use this value if I didn't specify anything else. "Anything else" here can include null.

9

IIRC, SQL standard allows : INSERT INTO TableNameVALUES (DEFAULT, DEFAULT ...)

So, what about some crazy idea? Lets define nil constant, that isn't just NilClass: module DefaultClass; end DefaultValue = nil DefaultValue.extend(DefaultClass) DefaultClass === DefaultValue # => true NilClass === DefaultValue # => true

Next, initialize fresh model instance attributes with not 'nil' but such a DefaultValue, and add proper quoting:

module ActiveRecord   module ConnectionAdapters # :nodoc:     module Quoting       # Quotes the column value       def quote(value, column = nil)         # records are quoted as their primary key         return value.quoted_id if value.respond_to?(:quoted_id)

        case value           when String   [snip]           # add handling defaults:           when DefaultClass then "DEFAULT"           when NilClass then "NULL"

10

> By it's very nature, a default value says "never let this field be anything > else on creation of a new record".

To you, maybe, but not universally. Defaults mean exactly that; use this value if I didn't specify anything else. "Anything else" here can include null.

I think a lot of the confusion is that some people think NULL stands for nothing, or no value at all, instead of an 'unknown value'.

*RAILS* should have some way of saying "don't insert this column value". But it should not simply always use null, NOR should it always leave the nulls out. Either way breaks things.

Yes, well said.

Chris

11

Yes, this is right on. It would be excellent if Rails had this kind of flexibility.

12

Because this uses the application server's time, not the DB's time. What if time accuracy was a priority and there were multiple front end application servers, some in different time zones, and all with unsyncronized time?

More importantly, it's not a matter of accepting the current time (ruby's Time.now or DB's NOW()). What if I needed to set a timestamp/date column to a DB function that did some crazy calculation and must be hidden from the app? To do this, one would have to set the column attribute equal to a String describing the function, however, a String object will be discarded while being parsed in ActiveRecord::ConnectionAdapters::Column.string_to_time. See active_record/connection_adapters/abstract/schema_definitions.rb for details.

-pachl

13

This is an *excellent* idea. After looking at the ActiveRecord source code, I can see how this may be implemented, but it may take some work. One thing that AR does is quote everything. As a test, I just tried inserting a record in my Postgresql 8.1 DB using quoted and unquoted DEFAULT values. Quoting the keyword DEFAULT simply does not work. For example, if the column is a string type, the literal string "DEFAULT" gets stored and if the column is a timestamp, the DB complains of invalid syntax.

So, AR would have to discern the object types and avoid using quotes when inserting the DEFAULT keyword.

-pachl