using the postgresql gem async

Question

我正在使用Goliath(由eventmachine提供支持)和postgres gem pg，目前我正在以阻塞的方式使用pg gem: conn.exec('SELECT * FROM products')(例如)，我想知道是否有更好的方法连接到postgres数据库?

Answer 1

pg库提供了对PostgreSQL异步API的全面支持。我在samples/目录中添加了一个如何使用它的示例:

#!/usr/bin/env ruby
require 'pg'
# This is a example of how to use the asynchronous API to query the
# server without blocking other threads. It's intentionally low-level;
# if you hooked up the PGconn#socket to some kind of reactor, you
# could make this much nicer.
TIMEOUT = 5.0 # seconds to wait for an async operation to complete
CONN_OPTS = {
    :host     => 'localhost',
    :dbname   => 'test',
    :user     => 'jrandom',
    :password => 'banks!stealUR$',
}
# Print 'x' continuously to demonstrate that other threads aren't
# blocked while waiting for the connection, for the query to be sent,
# for results, etc. You might want to sleep inside the loop or 
# comment this out entirely for cleaner output.
progress_thread = Thread.new { loop { print 'x' } }
# Output progress messages
def output_progress( msg )
    puts "n>>> #{msg}n"
end
# Start the connection
output_progress "Starting connection..."
conn = PGconn.connect_start( CONN_OPTS ) or 
    abort "Unable to create a new connection!"
abort "Connection failed: %s" % [ conn.error_message ] if
    conn.status == PGconn::CONNECTION_BAD
# Now grab a reference to the underlying socket so we know when the
# connection is established
socket = IO.for_fd( conn.socket )
# Track the progress of the connection, waiting for the socket to 
# become readable/writable before polling it
poll_status = PGconn::PGRES_POLLING_WRITING
until poll_status == PGconn::PGRES_POLLING_OK ||
      poll_status == PGconn::PGRES_POLLING_FAILED
    # If the socket needs to read, wait 'til it becomes readable to
    # poll again
    case poll_status
    when PGconn::PGRES_POLLING_READING
        output_progress "  waiting for socket to become readable"
        select( [socket], nil, nil, TIMEOUT ) or
            raise "Asynchronous connection timed out!"
    # ...and the same for when the socket needs to write
    when PGconn::PGRES_POLLING_WRITING
        output_progress "  waiting for socket to become writable"
        select( nil, [socket], nil, TIMEOUT ) or
            raise "Asynchronous connection timed out!"
    end
    # Output a status message about the progress
    case conn.status
    when PGconn::CONNECTION_STARTED
        output_progress "  waiting for connection to be made."
    when PGconn::CONNECTION_MADE
        output_progress "  connection OK; waiting to send."
    when PGconn::CONNECTION_AWAITING_RESPONSE
        output_progress "  waiting for a response from the server."
    when PGconn::CONNECTION_AUTH_OK
        output_progress "  received authentication; waiting for " +
                        "backend start-up to finish."
    when PGconn::CONNECTION_SSL_STARTUP
        output_progress "  negotiating SSL encryption."
    when PGconn::CONNECTION_SETENV
        output_progress "  negotiating environment-driven " +
                        "parameter settings."
    end
    # Check to see if it's finished or failed yet
    poll_status = conn.connect_poll
end
abort "Connect failed: %s" % [ conn.error_message ] unless 
    conn.status == PGconn::CONNECTION_OK
output_progress "Sending query"
conn.send_query( "SELECT * FROM pg_stat_activity" )
# Fetch results until there aren't any more
loop do
    output_progress "  waiting for a response"
    # Buffer any incoming data on the socket until a full result 
    # is ready. 
    conn.consume_input
    while conn.is_busy
        select( [socket], nil, nil, TIMEOUT ) or
            raise "Timeout waiting for query response."
        conn.consume_input
    end
    # Fetch the next result. If there isn't one, the query is 
    # finished
    result = conn.get_result or break
    puts "nnQuery result:n%pn" % [ result.values ]
end
output_progress "Done."
conn.finish
if defined?( progress_thread )
    progress_thread.kill
    progress_thread.join
end

我建议你阅读关于PQconnectStart函数的文档和PostgreSQL手册中的异步命令处理部分，然后将其与上面的示例进行比较。

我以前没有使用过EventMachine，但是如果它允许你注册一个套接字和回调，当它变得可读/可写时，我想它会相当容易集成数据库调用到它。

我一直想使用Ilya Grigorik关于使用光纤清理事件代码以使异步API更易于使用的文章中的想法，但这是一种方式。如果你有兴趣或有动力自己去做的话，我有一张票可以跟踪。

Answer 2

是的，您可以从goliath以非阻塞的方式访问postgres。我有同样的需求，并把这个概念的证明:https://github.com/levicook/goliath-postgres-spike

Answer 3

我(不再)非常熟悉Pg，但我还没有听说任何流行的数据库可以异步连接。因此，您仍然需要在查询期间维护到数据库的连接。因此，你仍然需要阻塞堆栈下面的一些地方。

根据你的应用，你可能已经用了最好的方法。

但是当你正在处理某种轮询应用程序(同一客户端在短时间内发送大量请求)，并且更重要的是得到响应，即使它是空的，那么你可以编写一个ruby Fiber或完整的线程或进程，它是长期存在的，并代理查询到DB并缓存结果。

例如:一个请求来自客户端a . Goliath应用程序用一些唯一的ID处理对DB进程的查询，并以'no data yet'响应查询。DB进程完成查询并将结果保存到具有ID的缓存中。当来自同一客户机的下一个请求到来时，Goliath看到它已经有查询结果等待，从缓存中删除结果并响应客户机。同时，它与DB进程一起调度下一个查询，以便它能够更快地准备好。如果下一个请求在上一个请求完成之前进入，则不会安排新的查询(不会使查询增加)。

这样，您的响应是快速和非阻塞的，同时仍然从DB尽快提供新的数据。当然，它们可能与实际数据有点不同步，但根据应用程序的不同，这可能不是问题。

Answer 4

这个想法是将数据库(Postgresql)的异步适配器与事件web服务器(Goliath)结合使用以获得性能。Mike Perham去年为Rails 2.3编写了一个PG活动记录适配器。也许你可以用它。

作为另一个例子，Ilya Grigorik发布了这个异步Rails堆栈的演示。在本例中，事件服务器是Thin，数据库是Mysql。安装演示并尝试使用和不使用EM感知驱动程序的基准测试。