PostgreSQL mishandling of 'now' date/time default

This is a part of larger problem with introspecting database defaults. For correctness, only constant default values should be used. Statements which need evaluation, such as now, should be evaluated when the record is inserted, but we are effectively evaluating it when the record is selected instead. This behavior is often useful, but broken.

Do not expect a quick resolution. If you want to use the current time as a default for an attribute, do it in pure Ruby for now.

In the long run, we'll have to resolve a way to remember the database defaults and omit those columns from the insert statement if they have not changed.

In that case, maybe it would be nice to try only to insert/update what has been given, that is, leave the unfilled fields up to the database to decide what to do with them (mainly set the default), and raise an exception if it's not possible.

For now, I did a :

module Gestion
  module ActiveRecord
    module FrechTimestamp
      def self.append_features(base)
        super
        base.before_create do |model|
          model.date_creation = Time.now if model.respond_to?(:date_creation)
        end
        base.before_save do |model|
          model.date_modif = Time.now if model.respond_to?(:date_modif)
        end
      end
    end
  end
end

module ActiveRecord
  class Base
    include Gestion::ActiveRecord::FrechTimestamp
  end
end

Which works well :-)

Another workaround is to do it through a trigger like this:

create table mytable (
  id serial primary key,
  creation_time timestamp not null
);

create function assign_mytable_creation_time() returns trigger as $$
  begin
    new.creation_time = current_timestamp;
    return new;
  end;
$$ language plpgsql;

create trigger assign_mytable_creation_time before insert
    on mytable for each row execute procedure assign_mytable_creation_time();

The proposed solution (from the second and third updates) might also close #1880.

Any estimates on when could this be fixed?

PS: I don't understand what the first workaround is doing.

I'm not sure the first solution works: the existing Timestamping mixin does this already. The problem is that not setting the field to Time.now doesn't omit it from the SQL insert. A nil value is inserted.

On save we could omit columns whose values haven't changed from the default or, more generally, we could use Jamis' smart save patch to omit those unchanged since load. This leads to dirty reads without a write lock, however.

As is, our use of the column defaults as initial values for new instances is for convenience and is unfaithful to their true behavior. A literal translation would make them before_save callbacks. Currently, they're the equivalent of after_initialize callbacks because we want editable defaults before saving.

Perhaps this is a sign that we ought to specify non-constant defaults in our application, not the database.

The simplest fix is to simply remove the offending clause in default_value as it's more harm than good right now.

          # Date / Time magic values
          return Time.now.to_s if value =~ /^now\(\)|^\('now'::text\)::(date|timestamp)/i

If someone wants to investigate a more general solution for the problem of dynamic column defaults, that's fine. In the meantime, you can either:

1. Use a trigger 1. Use created_at

(In [3913]) Remove broken attempts at handling columns with a a default of 'now()' in the postgresql adapter. Closes #2257

Why not simply use the SQL standard 'DEFAULT'? Right now, Rails is trying to insert NULL for my default not null fields.

I second csn's recommendation of using DEFAULT for the insert value. Even MySQL now supports it, as of v 5.0.19 or so, which is their latest RELEASE version (i.e. it's not a beta or anything.)

Since it IS the SQL spec behaviour and both databases support it...

Using DEFAULT is a good idea, but it's not the topic of this ticket.