azurewang/lua-resty-postgres

Nonblocking Lua PostgreSQL driver library for ngx_lua

lua-resty-postgres

Nonblocking Lua PostgreSQL driver library for ngx_lua

Synopsis

local pg = require("resty.postgres")

local db = pg:new()
db:set_timeout(3000)

local ok, err = db:connect({host="127.0.0.1",port=5432, database="postgres",
                            user="admin",password="123456",compact=false})

if not ok then
    ngx.say(err)
end

local res, err = db:query("select id,name from test")
db:set_keepalive(0,100)

for i,v in ipairs(res) do
    ngx.say(string.format("%s\t%s",v.id,v.name))
end

Author

Azure Wang (王非)[email protected]

Copyright and License

This module is licensed under the BSD license.

Copyright (C) 2013, by Azure Wang (王非) [email protected].

All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

• Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

• Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

lua-resty-postgres's Issues

"Not Initialized"

Even with hardcoded env variables, the ok variable returns nil and the err variable returns "not initialized".
Firewall disabled, and can successfully access the postgresql database with psql from the digitalocean droplet instance.

local pg = postgres:new()
pg:set_timeout(3000)

local ok, err = pg:connect{
    host = process.env.POSTGRESQL_DATABASE_HOST_1,
    port = process.env.POSTGRESQL_DATABASE_PORT_1,
    database = process.env.POSTGRESQL_DATABASE_NAME_1,
    user = process.env.POSTGRESQL_DATABASE_USERNAME_1,
    password = process.env.POSTGRESQL_DATABASE_PASSWORD_1
}

if not ok then
    ngx.log(ngx.ERR, "Failed to connect to the database: ", err)
    return nil
end

Can you add prepared sql support ?

thank you !

incompatibility with pgbouncer

here is the error from pgbouncer.log when trying to connect through pgbouncer:

2013-11-25 14:48:20.152 10075 LOG S-0x15d2270: prplk/[email protected]:5431 new connection to server
2013-11-25 14:48:20.156 10075 ERROR C-0x15b48c0: test/[email protected]:39868 unknown pkt from client: 112/0x70
2013-11-25 14:48:20.156 10075 LOG C-0x15b48c0: test/[email protected]:39868 closing because: unknown pkt (age=0)
2013-11-25 14:48:20.156 10075 WARNING C-0x15b48c0: test/[email protected]:39868 Pooler Error: unknown pkt

Socket error

During benchmarking I got this error:

attempt to receive data on a closed socket: u:0000000040246F30, c:0000000000000000, ft:5 eof:1

And no postgresql commands would work until restart of nginx

Unable to connect to pgpool, auth return type not support

Even though md5 is configured on pgpool, I am unable to connect. It looks like resty.postgres is not sending md5 auth type to pgpool.

Here is what I see on pgpool debug output

2015-02-17 11:11:59 DEBUG: pid 8423: I am 8423 accept fd 6
2015-02-17 11:11:59 DEBUG: pid 8423: Protocol Major: 3 Minor: 0 database: v user: postgres
2015-02-17 11:11:59 DEBUG: pid 8423: new_connection: connecting 0 backend
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length: slot: 0 length: 8
2015-02-17 11:11:59 DEBUG: pid 8423: pool_do_auth: auth kind:0
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 22
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: application_name value:
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 25
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: client_encoding value: UTF8
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 23
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: DateStyle value: ISO, MDY
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 25
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: integer_datetimes value: on
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 27
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: IntervalStyle value: postgres
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 20
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: is_superuser value: on
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 25
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: server_encoding value: UTF8
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 25
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: server_version value: 9.4.0
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 35
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: session_authorization value: postgres
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 35
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: standard_conforming_strings value: on
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length2: master slot: 0 length: 23
2015-02-17 11:11:59 DEBUG: pid 8423: 0 th backend: name: TimeZone value: localtime
2015-02-17 11:11:59 DEBUG: pid 8423: pool_flush_it: write failed to frontend. reason: Connection reset by peer offset: 0 wlen: 24
2015-02-17 11:11:59 DEBUG: pid 8423: pool_read_message_length: slot: 0 length: 12
2015-02-17 11:11:59 DEBUG: pid 8423: pool_do_auth: cp->info[i]:0x7f42503c0000 pid:9290
2015-02-17 11:11:59 DEBUG: pid 8423: pool_send_auth_ok: send pid 9290 to frontend
2015-02-17 11:11:59 DEBUG: pid 8423: pool_flush_it: write failed to frontend. reason: Broken pipe offset: 0 wlen: 13

cannot send query in the current context: nil: nil: nil.

luarocks install lua-resty-postgres

....

    local db = pgsql:new()
    db:set_timeout(3000)
    local ok,err = db:connect({database= config.pgsql.database ,
                              host = config.pgsql.host,
                              port = config.pgsql.port,
                              user = config.pgsql.user,
                              password = config.pgsql.password,compact = false
                          })
    if not ok then
        ngx.say(err)
    end
    -- db:set_timeout(conf.timeout) -- 1 sec

    -- local ok, err, errno, sqlstate = db:connect(conf.pgsql)
    -- local ok, err, errno, sqlstate = db:connect()
    -- if not ok then
    --     ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
    --     return
    -- end

    -- ngx.log(ngx.ERR, "connected to pgsql, reused_times:", db:get_reused_times(), " sql:", sql)

    -- db:query("SET NAMES utf8")
    ngx.log(ngx.ERR,"sql is ----- ",sql)
    local res, err, errno, sqlstate = db:query(sql)
    db:set_keepalive(0,100)
    if not res then
        ngx.log(ngx.ERR, "bad result: ", err, ": ", errno, ": ", sqlstate, ".")
    end

    -- local ok, err = db:set_keepalive(conf.pool_config.max_idle_timeout, conf.pool_config.pool_size)
    -- if not ok then
    --     ngx.say("failed to set keepalive: ", err)
    -- end

    return res, err, errno, sqlstate

db.lua:77: query(): bad result: failed to send query: cannot send query in the current context: nil: nil: nil.,

new pg err

when we use the POSTGRESQL 11 ,execute the update or insert sql ,have no result return.It just return {},a empty table.

auth return type not support

I'm trying to connect to my Postgres 9.2 server. I tried both password and trust auth and I get the same error when calling connect:

local pg = require "resty.postgres"
local db = pg:new()
db:connect { host = "127.0.0.1", port = 5432, user = "leafo", database = "leafo", password = "leafo" }

returns nil, "auth return type not support"

when trust in pg_hba.conf

auth_type is "00 00 00 00" ; no need to send Auth