prepared statement rounds Time parameter value down to second #450

Closed
olek opened this Issue Mar 9, 2012 · 3 comments

Comments

Projects
None yet
2 participants
@olek

olek commented Mar 9, 2012

When used with PostgreSQL, normal generated query SQL contains Time information with microseconds.

Switching to prepared statements, time information is rounded to a second (and overall, time string sent to DB looks like just output of the .to_s method).

For now, I am working around this issue by binding variable to 'dataset.literal(some_time)' instead of 'some_time'.

Here are 2 log lines for same query, one plain, one prepared:

UPDATE "alert_subscriptions" SET "locked_by" = '75584', "locked_at" = '2012-03-08 20:52:04.162872-0500' WHERE (("locked_by" IS NULL) OR (locked_at < '2012-03-08 18:52:04.162872-0500'))

EXECUTE lock_all_available_subscriptions; ["75584", 2012-03-08 20:52:04 -0500, 2012-03-08 18:52:04 -0500]

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Mar 9, 2012

Owner

I'm pretty sure this is due to ruby-pg bug #111: https://bitbucket.org/ged/ruby-pg/issue/111/fractional-seconds-ignored-for-time

It's probably not difficult to work around that bug as I did in the pg_auto_parameterize extension. We'd just need to preprocess the args inside execute_prepared_statement in the postgres adapter. Best first step for this would be to add integration tests for the fractional seconds to the prepared statement integration tests. It's also likely that Sequel::SQL::Blob arguments need to be escaped, as otherwise you miss stuff after "\0" (pg bug #112).

It would be better to fix these issues in ruby-pg, but I'll try to commit the above workarounds tomorrow.

Owner

jeremyevans commented Mar 9, 2012

I'm pretty sure this is due to ruby-pg bug #111: https://bitbucket.org/ged/ruby-pg/issue/111/fractional-seconds-ignored-for-time

It's probably not difficult to work around that bug as I did in the pg_auto_parameterize extension. We'd just need to preprocess the args inside execute_prepared_statement in the postgres adapter. Best first step for this would be to add integration tests for the fractional seconds to the prepared statement integration tests. It's also likely that Sequel::SQL::Blob arguments need to be escaped, as otherwise you miss stuff after "\0" (pg bug #112).

It would be better to fix these issues in ruby-pg, but I'll try to commit the above workarounds tomorrow.

@olek

This comment has been minimized.

Show comment Hide comment
@olek

olek Mar 9, 2012

You are right, Jeremy, it looks like this is indeed issue with ruby-pg driver.

Thank you for fixing it in the sequel itself in the meantime.

olek commented Mar 9, 2012

You are right, Jeremy, it looks like this is indeed issue with ruby-pg driver.

Thank you for fixing it in the sequel itself in the meantime.

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Mar 9, 2012

Owner

I plan to commit this tomorrow after additional testing. You might want to give it a shot in the meantime: http://pastie.org/3553185

Owner

jeremyevans commented Mar 9, 2012

I plan to commit this tomorrow after additional testing. You might want to give it a shot in the meantime: http://pastie.org/3553185

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