Sequel::Model#find_or_create does not work with postgres "timestamp without time zone" columns #531

Closed
DRMacIver opened this Issue Aug 8, 2012 · 2 comments

Comments

Projects
None yet
2 participants
@DRMacIver

As far as I can tell, it's using a different formatting for the select and the create.

Run the following script with DATABASE_URL pointing to a postgres database:

unless ENV["DATABASE_URL"] 
  STDERR.puts "No DATABASE_URL specified"
  exit 1
end

require "rubygems"
gem "sequel", "=3.38.0"
gem "pg", "=0.14.0"

require "sequel"
require "date"
require "logger"

DB = Sequel.connect(ENV["DATABASE_URL"])
DB.logger = Logger.new(STDERR).tap{|t| t.level = Logger::DEBUG } 

DB << "create table if not exists some_dates(a_date timestamp without time zone unique not null)"
DB << "truncate table some_dates"

class SomeDate < Sequel::Model; end

d = DateTime.now

SomeDate.find_or_create(:a_date => d)
SomeDate.find_or_create(:a_date => d)

Output from running it:

I, [2012-08-08T11:38:15.519829 #4002]  INFO -- : (0.000659s) SET standard_conforming_strings = ON
I, [2012-08-08T11:38:15.520693 #4002]  INFO -- : (0.000561s) SET client_min_messages = 'WARNING'
I, [2012-08-08T11:38:15.521355 #4002]  INFO -- : (0.000468s) SET DateStyle = 'ISO'
I, [2012-08-08T11:38:15.522424 #4002]  INFO -- : (0.000845s) create table if not exists some_dates(a_date timestamp without time zone unique not null)
I, [2012-08-08T11:38:15.619272 #4002]  INFO -- : (0.096561s) truncate table some_dates
I, [2012-08-08T11:38:15.637692 #4002]  INFO -- : (0.011648s) SELECT "pg_attribute"."attname" AS "name", CAST("pg_attribute"."atttypid" AS integer) AS "oid", format_type("pg_type"."oid", "pg_attribute"."atttypmod") AS "db_type", pg_get_expr("pg_attrdef"."adbin", "pg_class"."oid") AS "default", NOT "pg_attribute"."attnotnull" AS "allow_null", COALESCE(("pg_attribute"."attnum" = ANY("pg_index"."indkey")), false) AS "primary_key", "pg_namespace"."nspname" FROM "pg_class" INNER JOIN "pg_attribute" ON ("pg_attribute"."attrelid" = "pg_class"."oid") INNER JOIN "pg_type" ON ("pg_type"."oid" = "pg_attribute"."atttypid") INNER JOIN "pg_namespace" ON ("pg_namespace"."oid" = "pg_class"."relnamespace") LEFT OUTER JOIN "pg_attrdef" ON (("pg_attrdef"."adrelid" = "pg_class"."oid") AND ("pg_attrdef"."adnum" = "pg_attribute"."attnum")) LEFT OUTER JOIN "pg_index" ON (("pg_index"."indisprimary" IS TRUE) AND ("pg_index"."indrelid" = "pg_class"."oid")) WHERE (("pg_attribute"."attisdropped" IS FALSE) AND ("pg_attribute"."attnum" > 0) AND ("pg_class"."relname" = 'some_dates') AND ("pg_namespace"."nspname" !~* 'pg_*|information_schema')) ORDER BY "pg_attribute"."attnum"
I, [2012-08-08T11:38:15.642904 #4002]  INFO -- : (0.001443s) SELECT * FROM "some_dates" WHERE ("a_date" = '2012-08-08 11:38:15.639007+0100') LIMIT 1
I, [2012-08-08T11:38:15.645801 #4002]  INFO -- : (0.000583s) BEGIN
I, [2012-08-08T11:38:15.648739 #4002]  INFO -- : (0.001579s) INSERT INTO "some_dates" ("a_date") VALUES ('2012-08-08 11:38:15.000000+0100') RETURNING *
I, [2012-08-08T11:38:15.659643 #4002]  INFO -- : (0.010176s) COMMIT
I, [2012-08-08T11:38:15.663585 #4002]  INFO -- : (0.001303s) SELECT * FROM "some_dates" WHERE ("a_date" = '2012-08-08 11:38:15.639007+0100') LIMIT 1
I, [2012-08-08T11:38:15.665727 #4002]  INFO -- : (0.000222s) BEGIN
E, [2012-08-08T11:38:15.667238 #4002] ERROR -- : PG::Error: ERROR:  duplicate key value violates unique constraint "some_dates_a_date_key"
DETAIL:  Key (a_date)=(2012-08-08 11:38:15) already exists.: INSERT INTO "some_dates" ("a_date") VALUES ('2012-08-08 11:38:15.000000+0100') RETURNING *
I, [2012-08-08T11:38:15.667761 #4002]  INFO -- : (0.000304s) ROLLBACK
/home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in `async_exec': PG::Error: ERROR:  duplicate key value violates unique constraint "some_dates_a_date_key" (Sequel::DatabaseError)
DETAIL:  Key (a_date)=(2012-08-08 11:38:15) already exists.
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in `execute_query'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/logging.rb:37:in `log_yield'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in `execute_query'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:132:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:111:in `check_disconnect_errors'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:132:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:372:in `_execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:379:in `check_database_errors'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in `synchronize'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in `synchronize'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/dataset/actions.rb:744:in `execute'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:483:in `fetch_rows'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/dataset/actions.rb:803:in `returning_fetch_rows'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/dataset/actions.rb:297:in `insert'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/shared/postgres.rb:954:in `insert'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/adapters/shared/postgres.rb:969:in `insert_select'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1528:in `_insert_select_raw'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1501:in `_insert'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1560:in `_save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:846:in `around_create'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1557:in `_save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:846:in `around_save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1552:in `_save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1233:in `save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1701:in `checked_transaction'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/query.rb:300:in `_transaction'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/query.rb:263:in `transaction'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in `synchronize'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/connection_pool/threaded.rb:105:in `hold'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in `synchronize'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/database/query.rb:261:in `transaction'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1701:in `checked_transaction'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1233:in `save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1689:in `checked_save_failure'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:1233:in `save'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:163:in `create'
  from /home/david/.rvm/gems/ruby-1.8.7-p334/gems/sequel-3.38.0/lib/sequel/model/base.rb:323:in `find_or_create'
  from sequel_find_or_create_date.rb:25
@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Aug 8, 2012

Owner

I can replicate this on 1.8.7, but not on 1.9.3. My guess is your problem is related to typecasting. Sequel.datetime_class defaults to Time, not DateTime, so in the create part, it's going to convert the DateTime to a Time value. The fractional part should get copied over when that happens, but that doesn't appear to be the case on 1.8.7. I'll work on a fix. In the meantime, you can just set Sequel.datetime_class = DateTime, or use Time.now instead of DateTime.now.

Owner

jeremyevans commented Aug 8, 2012

I can replicate this on 1.8.7, but not on 1.9.3. My guess is your problem is related to typecasting. Sequel.datetime_class defaults to Time, not DateTime, so in the create part, it's going to convert the DateTime to a Time value. The fractional part should get copied over when that happens, but that doesn't appear to be the case on 1.8.7. I'll work on a fix. In the meantime, you can just set Sequel.datetime_class = DateTime, or use Time.now instead of DateTime.now.

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Aug 8, 2012

Owner

The difference between the two is that on 1.9, Time#to_datetime and DateTime#to_time is used for conversion. Those don't exist on 1.8, so on 1.8, Sequel does a DateTime->String->Time and Time->String->DateTime, and the ->String conversions weren't including the fractional seconds. I'm currently testing a fix, it should be pushed up soon.

Owner

jeremyevans commented Aug 8, 2012

The difference between the two is that on 1.9, Time#to_datetime and DateTime#to_time is used for conversion. Those don't exist on 1.8, so on 1.8, Sequel does a DateTime->String->Time and Time->String->DateTime, and the ->String conversions weren't including the fractional seconds. I'm currently testing a fix, it should be pushed up soon.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment