Makara Rubygem (DB read/write sharding)

Jon_McKenzie · March 10, 2016, 2:10pm

Has anyone been successful in using the "makara" Rubygem to route DB reads
to a DB read replica? (Or can recommend some other approach?)

I'm running into a problem where when I use the "postgresql_makara"
adapter, the scoped search evaluates a particular search query on the
Foreman dashboard into invalid Postgres SQL, causing the DB to reject the
query with an error.

Specifically, it's this line:

github.com

theforeman/foreman/blob/94508562b8c10b8b53dcb0badaa8abe31887d059/app/services/dashboard/data.rb#L50


  report[:percentage]         = percentage
  report[:reports_missing]    = reports_missing
end


def percentage
  return 0 if report[:ok_hosts_enabled] == 0 or report[:total_hosts] == 0
  report[:ok_hosts_enabled] * 100 / report[:total_hosts]
end


def reports_missing
  hosts.search_for('not has last_report and status.enabled = true').count
end
end

When I run with the plain "postgresql" Rails DB adapter, the SQL translates
to:

SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND
(((NOT COALESCE("hosts"."last_report" IS NOT NULL, false)) AND
(("hosts"."enabled" <> 'f'))))

However when I run using "postgresql_makara" I'm getting:

SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND
(((NOT COALESCE("hosts"."last_report" IS NOT NULL, 0)) AND
(("hosts"."enabled" <> 'f'))))

The COALESCE statement should resolve to a boolean value, but the default
is an integer – 0 – instead of a boolean – false, which causes
PostgreSQL to throw a PGError.

I can see in the scoped_search code where the Postgres-specific SQL should
be generated
(https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L529),
but I'm not really understanding how the generation is routed based on the
adapter. It seems like there's something in the chain of execution that
isn't recognizing the Makara adapter as a valid PostgreSQL adapter, but I'm
just not seeing where

Appreciate the help!

Jon_McKenzie · March 10, 2016, 2:36pm

Actually, I think I just found the issue. In scoped_search, the particular
query builder chosen is based on the class of the underlying DB connection:

https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L30

In my case, this method resolves to MakaraPostgreSQLAdapter not
PostgreSQLAdapter, so scoped_search choses the default query builder.

I submitted a bug to scoped_search here:

https://github.com/wvanbergen/scoped_search/issues/139

…I suppose I will need to either directly patch this to get it working or
monkey patch it somewhere.

···

On Thursday, March 10, 2016 at 9:10:03 AM UTC-5, Jon McKenzie wrote: > > Has anyone been successful in using the "makara" Rubygem to route DB reads > to a DB read replica? (Or can recommend some other approach?) > > I'm running into a problem where when I use the "postgresql_makara" > adapter, the scoped search evaluates a particular search query on the > Foreman dashboard into invalid Postgres SQL, causing the DB to reject the > query with an error. > > Specifically, it's this line: > > > https://github.com/theforeman/foreman/blob/94508562b8c10b8b53dcb0badaa8abe31887d059/app/services/dashboard/data.rb#L50 > > When I run with the plain "postgresql" Rails DB adapter, the SQL > translates to: > > SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') > AND (((NOT COALESCE("hosts"."last_report" IS NOT NULL, false)) AND > (("hosts"."enabled" <> 'f')))) > > However when I run using "postgresql_makara" I'm getting: > > SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') > AND (((NOT COALESCE("hosts"."last_report" IS NOT NULL, 0)) AND > (("hosts"."enabled" <> 'f')))) > > The COALESCE statement should resolve to a boolean value, but the default > is an integer -- 0 -- instead of a boolean -- false, which causes > PostgreSQL to throw a PGError. > > I can see in the scoped_search code where the Postgres-specific SQL should > be generated ( > https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L529), > but I'm not really understanding how the generation is routed based on the > adapter. It seems like there's something in the chain of execution that > isn't recognizing the Makara adapter as a valid PostgreSQL adapter, but I'm > just not seeing where > > Appreciate the help! >

Dominic_Cleal2 · March 10, 2016, 2:36pm

github.com

wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L28-L34


# Loads the QueryBuilder class for the connection of the given definition.
# If no specific adapter is found, the default QueryBuilder class is returned.
def self.class_for(definition)
  case definition.klass.connection.class.name.split('::').last
  when /postgresql/i
    PostgreSQLAdapter
  else

loads a DB-specific class if it exists, which is the PostgreSQL you
found at the bottom:

github.com

wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L499


  builder.sql_test(field, operator, rhs.value,lhs.value, &block)
end


# Convert this AST node to an SQL fragment.
def to_sql(builder, definition, &block)
  if operator == :not && children.length == 1
    builder.to_not_sql(rhs, definition, &block)
  elsif [:null, :notnull].include?(operator)
    to_null_sql(builder, definition, &block)
  elsif children.length == 1
    to_default_fields_sql(builder, definition, &block)
  elsif children.length == 2
    to_single_field_sql(builder, definition, &block)
  else
    raise ScopedSearch::QueryNotSupported, "Don't know how to handle this operator node: #{operator.inspect} with #{children.inspect}!"
  end
end


private


def validate_value(field, value)

You could probably define a MakaraPostgreSQLAdapter underneath which
should be found and used automatically, perhaps:

class MakaraPostgreSQLAdapter < PostgreSQLAdapter; end

This would inherit the main PostgreSQLAdapter functionality. If it
works, please do send it into the scoped_search project.

···

On 10/03/16 14:10, Jon McKenzie wrote: > However when I run using "postgresql_makara" I'm getting: > > SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') > AND (((NOT COALESCE("hosts"."last_report" IS NOT NULL, 0)) AND > (("hosts"."enabled" <> 'f')))) > > The COALESCE statement should resolve to a boolean value, but the > default is an integer -- 0 -- instead of a boolean -- false, which > causes PostgreSQL to throw a PGError. > > I can see in the scoped_search code where the Postgres-specific SQL > should be generated > (https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L529), > but I'm not really understanding how the generation is routed based on > the adapter. It seems like there's something in the chain of execution > that isn't recognizing the Makara adapter as a valid PostgreSQL adapter, > but I'm just not seeing where

–
Dominic Cleal
dominic@cleal.org

Jon_McKenzie · March 10, 2016, 4:30pm

For anyone who's interested, I solved the issue by creating a Rails
initializer @ /usr/share/foreman/config/initializers/makara.rb with the
following monkey patch:

require 'scoped_search'

class ScopedSearch::QueryBuilder
class MakaraPostgreSQLAdapter < PostgreSQLAdapter; end
end

···

On Thursday, March 10, 2016 at 9:36:41 AM UTC-5, Jon McKenzie wrote: > > Actually, I think I just found the issue. In scoped_search, the particular > query builder chosen is based on the class of the underlying DB connection: > > > https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L30 > > In my case, this method resolves to MakaraPostgreSQLAdapter not > PostgreSQLAdapter, so scoped_search choses the default query builder. > > I submitted a bug to ``scoped_search`` here: > > https://github.com/wvanbergen/scoped_search/issues/139 > > ..I suppose I will need to either directly patch this to get it working or > monkey patch it somewhere. > > On Thursday, March 10, 2016 at 9:10:03 AM UTC-5, Jon McKenzie wrote: >> >> Has anyone been successful in using the "makara" Rubygem to route DB >> reads to a DB read replica? (Or can recommend some other approach?) >> >> I'm running into a problem where when I use the "postgresql_makara" >> adapter, the scoped search evaluates a particular search query on the >> Foreman dashboard into invalid Postgres SQL, causing the DB to reject the >> query with an error. >> >> Specifically, it's this line: >> >> >> https://github.com/theforeman/foreman/blob/94508562b8c10b8b53dcb0badaa8abe31887d059/app/services/dashboard/data.rb#L50 >> >> When I run with the plain "postgresql" Rails DB adapter, the SQL >> translates to: >> >> SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') >> AND (((NOT COALESCE("hosts"."last_report" IS NOT NULL, false)) AND >> (("hosts"."enabled" <> 'f')))) >> >> However when I run using "postgresql_makara" I'm getting: >> >> SELECT COUNT(*) FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') >> AND (((NOT COALESCE("hosts"."last_report" IS NOT NULL, 0)) AND >> (("hosts"."enabled" <> 'f')))) >> >> The COALESCE statement should resolve to a boolean value, but the default >> is an integer -- 0 -- instead of a boolean -- false, which causes >> PostgreSQL to throw a PGError. >> >> I can see in the scoped_search code where the Postgres-specific SQL >> should be generated ( >> https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L529), >> but I'm not really understanding how the generation is routed based on the >> adapter. It seems like there's something in the chain of execution that >> isn't recognizing the Makara adapter as a valid PostgreSQL adapter, but I'm >> just not seeing where >> >> Appreciate the help! >> >