需求

需要监控不同应用的数据库连接

排错思路、过程

在 config/database.yml 中添加如下配置

production:
  adapter: postgresql
  database: dbname
  application_name: 'app_name'
  username: dbuser
  password: dbpwd
  host: ip
  port: 5432
  encoding: utf-8
  pool: 5

在 console 中执行

PGconn.connect({:application_name=>"app_name", :host=>"ip", :password=>"pwd", :user=>"dbuser", :dbname=>"dbname"})

报错：

PG::ConnectionBad: invalid connection option "application_name"
from /opt/rails_apps/app_name/shared/bundle/ruby/2.0.0/gems/railties-4.2.4/lib/rails/commands/console.rb:110:in `start'
from /opt/rails_apps/app_name/shared/bundle/ruby/2.0.0/gems/railties-4.2.4/lib/rails/commands/console.rb:9:in `start'
from /opt/rails_apps/app_name/shared/bundle/ruby/2.0.0/gems/railties-4.2.4/lib/rails/commands/commands_tasks.rb:68:in `console'
from /opt/rails_apps/app_name/shared/bundle/ruby/2.0.0/gems/railties-4.2.4/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
from /opt/rails_apps/app_name/shared/bundle/ruby/2.0.0/gems/railties-4.2.4/lib/rails/commands.rb:17:in `<top (required)>'

查看 active_record postgresql_adapter 源码

# Establishes a connection to the database that's used by all Active Record objects
def postgresql_connection(config)
   #省略非关键代码
  ConnectionAdapters::PostgreSQLAdapter.new(nil, logger, conn_params, config)
end

再看实例化 ConnectionAdapters::PostgreSQLAdapter 的代码

class StatementPool < ConnectionAdapters::StatementPool
   # Initializes and connects a PostgreSQL adapter.
   def initialize(connection, logger, connection_parameters, config)
       #省略非关键代码
     connect
       #省略非关键代码
   end
end

再看 postgresql adpater 的 connect 方法

       # Connects to a PostgreSQL server and sets up the adapter depending on the
# connected server's characteristics.
def connect
  @connection = PGconn.connect(@connection_parameters)

  # Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of
  # PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision
  # should know about this but can't detect it there, so deal with it here.
  OID::Money.precision = (postgresql_version >= 80300) ? 19 : 10

  configure_connection
rescue ::PG::Error => error
  if error.message.include?("does not exist")
    raise ActiveRecord::NoDatabaseError.new(error.message, error)
  else
    raise
  end
end

ok，到这里已经知道这里调用了 pg gem 的 connect 方法，所以这里打开 pg 源码

def self::connect( *args )
  return PG::Connection.new( *args )
end

这里再打开 pg 的 connection.rb 文件发现以下代码

### Parse the connection +args+ into a connection-parameter string. See PG::Connection.new
### for valid arguments.
def self::parse_connect_args( *args )
  return '' if args.empty?
  hash_arg = args.last.is_a?( Hash ) ? args.pop : {}
  option_string = ''
  options = {}

  # Parameter 'fallback_application_name' was introduced in PostgreSQL 9.0
  # together with PQescapeLiteral().
  if PG::Connection.instance_methods.find {|m| m.to_sym == :escape_literal }
    options[:fallback_application_name] = $0.sub( /^(.{30}).{4,}(.{30})$/ ){ $1+"..."+$2 }
  end
  #......
end

这里会对 application_name 做处理，详细情况看Postgresql 9.0 于是在 rails c 中执行

PG::Connection.instance_methods.find {|m| m.to_sym == :escape_literal }

这里返回空值，为什么没有这个方法呢？escape_literal 方法在哪里定义呢？ 最后在扩展包 pg_connection.c 里面发现了如下代码：

#ifdef HAVE_PQESCAPELITERAL
/*
 * call-seq:
 *    conn.escape_literal( str ) -> String
 *
 * Escape an arbitrary String +str+ as a literal.
 */
static VALUE
pgconn_escape_literal(VALUE self, VALUE string)
{
  PGconn *conn = pg_get_pgconn(self);
  .....
  escaped = PQescapeLiteral(conn, RSTRING_PTR(string), RSTRING_LEN(string));
  if (escaped == NULL)
  {
    .....
    return Qnil;
  }
   ......
  return result;
}
#endif

不懂 C 语言，根据代码猜测应该是如果定义了 HAVE_PQESCAPELITERAL 就应该可以通过 conn 调用 escape_literal 方法，返回一个 string 而刚才在控制台中执行的

PG::Connection.instance_methods.find {|m| m.to_sym == :escape_literal }
=>nil

返回了 nil，也就是说在 pg_connection.c 的 pgconn_escape_literal 返回了空，所以 escaped 返回了空值 Qnil(猜的) 那么在 PQescapeLiteral 中是做了什么事情呢？原文

PQescapeLiteral

sql char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);

PQescapeLiteral escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such

as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser. PQescapeLiteral performs this operation.

PQescapeLiteral returns an escaped version of the str parameter in memory allocated with malloc(). This memory should be freed using PQfreemem() when the result is no

longer needed. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed,

PQescapeLiteral stops at the zero; the behavior is thus rather like strncpy.) The return string has all special characters replaced so that they can be properly processed by the

PostgreSQL string literal parser. A terminating zero byte is also added. The single quotes that must surround PostgreSQL string literals are included in the result string.

On error, PQescapeLiteral returns NULL and a suitable message is stored in the conn object.

Tip: It is especially important to do proper escaping when handling strings that were received from an untrustworthy source. Otherwise there is a security risk: you are

vulnerable to "SQL injection" attacks wherein unwanted SQL commands are fed to your database.

Note that it is not necessary nor correct to do escaping when a data value is passed as a separate parameter in PQexecParams or its sibling routines.

求助

bundle config 配置情况

bundle config
输出如下信息：
Settings are listed in order of priority. The top value will be used.
build.pg
Set for the current user (/home/tester/.bundle/config): "--with-pg-config=/usr/pgsql-9.2/bin/pg_config"

frozen
Set for your local app (/opt/rails_apps/tester/release/appname-product-2.4.8.2/.bundle/config): "1"

path
Set for your local app (/opt/rails_apps/tester/release/appname-product-2.4.8.2/.bundle/config): "/opt/rails_apps/appname/shared/bundle"

without
Set for your local app (/opt/rails_apps/tester/release/appname-product-2.4.8.2/.bundle/config): "development:test"

disable_shared_gems
Set for your local app (/opt/rails_apps/tester/release/appname-product-2.4.8.2/.bundle/config): "1"

ruby 版本：ruby 2.0.0p643 (2015-02-25 revision 49749) [x86_64-linux]

rails 版本：rails-4.2.4

pg 版本：pg-0.18.3

系统版本：CentOS release 6.5 (Final)