how to use lua-resty-postgres effectively?

vlight00 · 2018-05-06T01:25:21+00:00

I have an other application to write base64 and gzip string into redis, the number of redis data about 6000 keys, i use lua script to read these data Fr...

how to use lua-resty-postgres effectively?

vlight00

Trying to build a webapp with Openresty+postgres, looking at the lua-resty-postgres library I was wondering how should one use it?

I have a execute query function defined in my init_by_lua_block like this

      db_func = function (q)
        local pg = require("resty.postgres")
        local db = pg:new()
        db:set_timeout(3000)
        local ok, err = db:connect({host=db_host,port=db_port, database=db_name,user=db_user,password=db_pass,compact=false})
        if not ok then
            ngx.log(ngx.ERR, err)
        end
        local res, err = db:query(q)
        return res, err
      end

and call this from locations by building queries with concatenating strings and quoting params with ndk.set_var.set_quote_pgsql_str() . Is this how it is supposed to be done? I'm sure I'm missing something, it feels bloated and error prone. Also I'm not sure whether the db connection is set up right or am I recreating it on every request?

golgote

Looks ok for me.

On Sat, May 5, 2018 at 7:25 PM, Könnyű Viktor <vli...@gmail.com> wrote:

Trying to build a webapp with Openresty+postgres, looking at the lua-resty-postgres library I was wondering how should one use it?

I have a execute query function defined in my init_by_lua_block like this

      db_func = function (q)
        local pg = require("resty.postgres")
        local db = pg:new()
        db:set_timeout(3000)
        local ok, err = db:connect({host=db_host,port=db_port, database=db_name,user=db_user,password=db_pass,compact=false})
        if not ok then
            ngx.log(ngx.ERR, err)
        end
        local res, err = db:query(q)
        return res, err
      end

and call this from locations by building queries with concatenating strings and quoting params with ndk.set_var.set_quote_pgsql_str() . Is this how it is supposed to be done? I'm sure I'm missing something, it feels bloated and error prone. Also I'm not sure whether the db connection is set up right or am I recreating it on every request?

.

rpaprocki

You are recreating the TCP connection on every request, because you are not calling setkeepalive on the connection object.

You might want to consider using https://github.com/leafo/pgmoon instead, which uses a modern style and is still maintained.

On Tue, May 8, 2018 at 9:39 AM, Bertrand Mansion <gol...@gmail.com> wrote:

Looks ok for me.

On Sat, May 5, 2018 at 7:25 PM, Könnyű Viktor <vli...@gmail.com> wrote:
Trying to build a webapp with Openresty+postgres, looking at the lua-resty-postgres library I was wondering how should one use it?

I have a execute query function defined in my init_by_lua_block like this

      db_func = function (q)
        local pg = require("resty.postgres")
        local db = pg:new()
        db:set_timeout(3000)
        local ok, err = db:connect({host=db_host,port=db_port, database=db_name,user=db_user,password=db_pass,compact=false})
        if not ok then
            ngx.log(ngx.ERR, err)
        end
        local res, err = db:query(q)
        return res, err
      end

and call this from locations by building queries with concatenating strings and quoting params with ndk.set_var.set_quote_pgsql_str() . Is this how it is supposed to be done? I'm sure I'm missing something, it feels bloated and error prone. Also I'm not sure whether the db connection is set up right or am I recreating it on every request?

.

golgote

If you use pgbouncer, the connection overhead is very low because connections are kept open. Even with standard connection the connection time is low, as measured here: http://blog.guillaume.lelarge.info/index.php/post/2015/02/22/Durée-d-une-connexion

pgmoon is slower than lua-resty-postgres which is slower than ngx_postgres according to my measures (from 2014, so it might have changed since)

http://mansion.im/2014/speed-comparison-of-postgresql-modules-for-nginx-openresty/

On Tue, May 8, 2018 at 9:00 PM, Robert Paprocki <rpap...@fearnothingproductions.net> wrote:

You are recreating the TCP connection on every request, because you are not calling setkeepalive on the connection object.

You might want to consider using https://github.com/leafo/pgmoon instead, which uses a modern style and is still maintained.

On Tue, May 8, 2018 at 9:39 AM, Bertrand Mansion <gol...@gmail.com> wrote:
Looks ok for me.

On Sat, May 5, 2018 at 7:25 PM, Könnyű Viktor <vli...@gmail.com> wrote:
Trying to build a webapp with Openresty+postgres, looking at the lua-resty-postgres library I was wondering how should one use it?

I have a execute query function defined in my init_by_lua_block like this

      db_func = function (q)
        local pg = require("resty.postgres")
        local db = pg:new()
        db:set_timeout(3000)
        local ok, err = db:connect({host=db_host,port=db_port, database=db_name,user=db_user,password=db_pass,compact=false})
        if not ok then
            ngx.log(ngx.ERR, err)
        end
        local res, err = db:query(q)
        return res, err
      end

and call this from locations by building queries with concatenating strings and quoting params with ndk.set_var.set_quote_pgsql_str() . Is this how it is supposed to be done? I'm sure I'm missing something, it feels bloated and error prone. Also I'm not sure whether the db connection is set up right or am I recreating it on every request?

.