PG::Error: ERROR: syntax error

[RobWu]

Just updated identity_cache on a project to 0.0.3

Received following error (Worked fine with 0.0.2):

/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec': PG::Error: ERROR:  syntax error at or near "`" (ActiveRecord::StatementInvalid)
LINE 1: SELECT `id` FROM `b56_survey_surveys` WHERE `name` = 'busine...
                         ^
: SELECT `id` FROM `b56_survey_surveys` WHERE `name` = 'business_bootcamp' LIMIT 1
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec_no_cache'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:660:in `block in exec_query'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:659:in `exec_query'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1256:in `select'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_one'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:30:in `select_value'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/identity_cache-0.0.3/lib/identity_cache/configuration_dsl.rb:188:in `block in identity_cache_single_value_dynamic_fetcher'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/identity_cache-0.0.3/lib/identity_cache.rb:60:in `fetch'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/identity_cache-0.0.3/lib/identity_cache/configuration_dsl.rb:188:in `identity_cache_single_value_dynamic_fetcher'
  from /home/row/.rvm/gems/ruby-1.9.3-p385@mannaz-bb/gems/identity_cache-0.0.3/lib/identity_cache/configuration_dsl.rb:56:in `fetch_by_name'

[RobWu]

Postgres does not like quotes

SELECT `id` FROM `b56_survey_surveys` WHERE `name` = 'business_bootcamp' LIMIT 1;
ERROR:  syntax error at or near "`"
LINE 1: SELECT `id` FROM `b56_survey_surveys` WHERE `name` = 'busine...


SELECT 'id' FROM 'b56_survey_surveys' WHERE 'name' = 'business_bootcamp' LIMIT 1;
ERROR:  syntax error at or near "'b56_survey_surveys'"
LINE 1: SELECT 'id' FROM 'b56_survey_surveys' WHERE 'name' = 'busine...


SELECT id FROM b56_survey_surveys WHERE name = 'business_bootcamp' LIMIT 1;
 id 
----
  1
(1 row)

[camilo]

It has to do with #44, which fixed an issue in mysql. if you need this soon, would you be willing to send a patch?

[RobWu]

Can look into it.

Would a condition on the db-adapter be fine enough?

if pg 
   Old cold
Else 
   New code
End

[RobWu]

Will lock for a nicer solution first, will come back when i found out more.

[dylanahsmith]

We should be able to use connection.quote_table_name & connection.quote_column_name database specific code.

[RobWu]

Fixed the quoting issue, but locks like there is an other issue.

[TSMMark]

Was getting this syntax error. Switched my gemfile to use github master and now I'm getting another error:

2.0.0-p0 :002 > Source.fetch_by_name('YOUTE')
   (0.8ms)  SELECT "id" FROM "sources" WHERE "name" = 'YOUTE' LIMIT 1
 => nil 
2.0.0-p0 :003 > Source.fetch_by_name('YOUTUBE')
  Source Load (0.7ms)  SELECT "sources".* FROM "sources" WHERE "sources"."id" = 1 LIMIT 1
   (0.5ms)  SELECT "asset_details".id FROM "asset_details" WHERE "asset_details"."source_id" = 1
NoMethodError: undefined method `tag=' for #
    from /Users/trendsettermm/.rvm/gems/ruby-2.0.0-p0/gems/delayed_job-4.0.0/lib/delayed/psych_ext.rb:6:in `encode_with_override'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/query_api.rb:140:in `coder_from_record'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/query_api.rb:36:in `block (2 levels) in fetch_by_id'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache.rb:76:in `fetch'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/query_api.rb:36:in `block in fetch_by_id'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/query_api.rb:161:in `require_if_necessary'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/query_api.rb:34:in `fetch_by_id'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/configuration_dsl.rb:190:in `identity_cache_single_value_dynamic_fetcher'
    from /Users/trendsettermm/.bundler/ruby/2.0.0/identity_cache-6d5fabc55cbd/lib/identity_cache/configuration_dsl.rb:57:in `fetch_by_name'
    from (irb):3
    from /Users/trendsettermm/.rvm/gems/ruby-2.0.0-p0/gems/railties-3.2.13/lib/rails/commands/console.rb:47:in `start'
    from /Users/trendsettermm/.rvm/gems/ruby-2.0.0-p0/gems/railties-3.2.13/lib/rails/commands/console.rb:8:in `start'
    from /Users/trendsettermm/.rvm/gems/ruby-2.0.0-p0/gems/railties-3.2.13/lib/rails/commands.rb:41:in `'
    from script/rails:6:in `require'
    from script/rails:6:in `'

[link82]

Same issue for me, it seems to be caused by delayed_job.
I'm using "delayed_job_active_record" gem and even if I remove it, existing database tables and relationship seems to cause this error anyway.

[link82]

Maybe i found a fix from delayed_job issues pages collectiveidea/delayed_job#435

[TSMMark]

@link82 did that solve anything for you? It didn't do it for me ):
I only get the error when I use cache_index and try to fetch_by_* it

[TSMMark]

# dj_identity_cache_fix.rb
# require in initializers/application.rb
class ::Hash
  attr_accessor :tag
end

This solved the error, but I'm not sure of any side effects this might cause.. Hopefully a real solution arises so I can cut out the shady monkey patch.

I feel so dirty right now.

[link82]

yep! It worked for me.
Anyway i was still having an issue using 'rabl' gem to render my json data and i fixed it changing json serializer used by rabl from 'yajl-ruby' to 'oj'

Hope it helps