How to use Postgresql?

[Roberto Ostinelli]

Hello,

I've seen that to use pgsql you need to install the postgresql module, the drizzle module, then use the RDSjson module to interpret the results.

I was hoping for something closer to mysql implementation (https://github.com/agentzh/lua-resty-mysql), I found some experiments (https://github.com/hugozhu/lua-resty-oceanbase) but they work only for some of pgsql protocols (btw not the latest).

Is something like this coming soon? Or else, can someone please point me to an example on how to use postgresql + drizzle + RDSjson?

Thank you,

r.

[Yichun Zhang (agentzh)]

Hello!

On Fri, Nov 22, 2013 at 10:23 AM, Roberto Ostinelli wrote:
> I've seen that to use pgsql you need to install the postgresql module, the
> drizzle module, then use the RDSjson module to interpret the results.
>

Nah, you don't need the ngx_drizzle module for PostgreSQL
communication at all because ngx_drizzle is a MySQL driver :)

> I was hoping for something closer to mysql implementation
> (https://github.com/agentzh/lua-resty-mysql), I found some experiments
> (https://github.com/hugozhu/lua-resty-oceanbase) but they work only for some
> of pgsql protocols (btw not the latest).

It seems that you missed the 3rd-party lua-resty-postgres library
contributed by Azure Wang:

https://github.com/azurewang/lua-resty-postgres

I have not read its source or used it myself yet, but it is worth trying :)

>
> Is something like this coming soon?

An official implementation of lua-resty-postgresql has been on my TODO
list for long ;) Because our company (CloudFlare) is also using
PostgreSQL, I'd expect it to be coming soon (but not very soon) :)

I'm currently focusing on LuaJIT 2.1 integration and lua-resty-core
development, under the name of more speed :)

> Or else, can someone please point me to
> an example on how to use postgresql + drizzle + RDSjson?
>

If you want to use ngx_postgres from within your Lua code running atop
ngx_lua, then you're encouraged to use the lua-rds-parser library to
parse the raw RDS output from ngx_postgres directly (that is, without
using ngx_rds_json at all). See

https://github.com/agentzh/lua-rds-parser#readme

It's more efficient to use JSON as an (extra) intermediate data format.

Regards,
-agentzh

[Roberto Ostinelli]

On Friday, November 22, 2013 2:37:47 PM UTC-5, agentzh wrote:

Hello!

On Fri, Nov 22, 2013 at 10:23 AM, Roberto Ostinelli wrote:
> I've seen that to use pgsql you need to install the postgresql module, the
> drizzle module, then use the RDSjson module to interpret the results.
>

Nah, you don't need the ngx_drizzle module for PostgreSQL
communication at all because ngx_drizzle is a MySQL driver :)

I guess I was confused by the docs that state:

ngx_postgres is an upstream module that allows Nginx to communicate directly with PostgreSQL database.

Response is generated in RDS format, so it's compatible with RdsJsonNginxModule and DrizzleNginxModule modules.

http://openresty.org/#PostgresNginxModule

 

> I was hoping for something closer to mysql implementation
> (https://github.com/agentzh/lua-resty-mysql), I found some experiments
> (https://github.com/hugozhu/lua-resty-oceanbase) but they work only for some
> of pgsql protocols (btw not the latest).

It seems that you missed the 3rd-party lua-resty-postgres library
contributed by Azure Wang:

    https://github.com/azurewang/lua-resty-postgres

I have not read its source or used it myself yet, but it is worth trying :)

Nah I already did try that one, it blows up while authenticating with latest postgresql here:

https://github.com/azurewang/lua-resty-postgres/blob/master/lib/resty/postgres.lua#L245

>
> Is something like this coming soon?

An official implementation of lua-resty-postgresql has been on my TODO
list for long ;) Because our company (CloudFlare) is also using
PostgreSQL, I'd expect it to be coming soon (but not very soon) :)

I'm currently focusing on LuaJIT 2.1 integration and lua-resty-core
development, under the name of more speed :)

Ok sounds great.

 

> Or else, can someone please point me to
> an example on how to use postgresql + drizzle + RDSjson?
>

If you want to use ngx_postgres from within your Lua code running atop
ngx_lua, then you're encouraged to use the lua-rds-parser library to
parse the raw RDS output from ngx_postgres directly (that is, without
using ngx_rds_json at all). See

    https://github.com/agentzh/lua-rds-parser#readme

Thank you, unfortunately all the examples there use drizzle to setup a ngx_postgresql upstream and I don't understand how to programmatically issue a query from within LUA to use ngx_postgresql without drizzle. Is there an example to call ngx_postgresql from lua?

r.

[Roberto Ostinelli]

If anyone else wonders, this is a working example of an nginx.conf file. You only need to install OpenResty with the postgres module (use the --with-http_postgres_module flag).

[...]

http {

    [...]

    upstream pgsql_db_1 {

        postgres_server  127.0.0.1 dbname=demo user=postgres password=test;

    }

    server {

        [...]

        location /pgsql_1_gate {

            postgres_pass   pgsql_db_1;

            postgres_query  $echo_request_body;

        }

        location /pgsql {

           content_by_lua '

               local sql = "select * from users"

               local resp = ngx.location.capture("/pgsql_1_gate", {

                   method = ngx.HTTP_POST, body = sql

               })

               if resp.status ~= ngx.HTTP_OK or not resp.body then

                   error("failed to query pgsql")

               end

               local parser = require "rds.parser"

               local res, err = parser.parse(resp.body)

               if res == nil then

                   error("failed to parse RDS: " .. err)

               end

               local rows = res.resultset

               if not rows or #rows == 0 then

                   ngx.say("empty resultset")

                   ngx.exit(0)

               end

               for i, row in ipairs(rows) do

                   ngx.print("row ", i, ": ")

                   for col, val in pairs(row) do

                       if val ~= parser.null then

                           ngx.print(col, "=", val, " ")

                       else

                           ngx.print(col, "=null ")

                       end

                   end

                   ngx.say()

               end

           ';

        }

    }

}

Cheers,

r.

[Yichun Zhang (agentzh)]

Hello!

On Fri, Nov 22, 2013 at 4:27 PM, Roberto Ostinelli wrote:
> If anyone else wonders, this is a working example of an nginx.conf file. You
> only need to install OpenResty with the postgres module (use the
> --with-http_postgres_module flag).
>

Thank you for contributing the working sample!

It's worth mentioning that it's recommended to use
ndk.set_var.set_quote_pgsql_str() to do the SQL literal quoting (to
protect against SQL injection attacks). See

https://github.com/chaoslawful/lua-nginx-module#ndkset_vardirective

https://github.com/agentzh/set-misc-nginx-module#set_quote_pgsql_str

Hopefully you can make this usage into your sample ;)

Best regards,
-agentzh

[Roberto Ostinelli]

Indeed, as usual :)

My example has an hardcoded "SELECT * FROM users;" so that obviously doesn't expose any risks.

Good thinking though to remind of this, and give us the exact function to use :)

Thanks,

r.

[Seven Du]

Hi @agentzh,

I'd like to ask how's going the official lua_resty_postgresql

I'm new to openresty, ngx_postgres seems lack of transaction support. lua_resty_postgres seems not being updating for years, I wonder what you currently using if lua_resty_postgresql is not ready.

There's also a https://github.com/leafo/pgmoon  looks interesting.

Thanks.

[Yichun Zhang (agentzh)]

Hello!

On Sun, Feb 21, 2016 at 3:55 PM, Seven Du wrote:
> I'd like to ask how's going the official lua_resty_postgresql
>

No progress on lua-resty-postgresql at all.

> There's also a https://github.com/leafo/pgmoon looks interesting.
>

Yeah, you're recommended to try this library for now. I personally
trust its author.

Regards,
-agentzh

[Seven Du]

Thanks, already got it work, not really have time to test that much though.

 

Regards,

-agentzh

--

You received this message because you are subscribed to the Google Groups "openresty-en" group.

To unsubscribe from this group and stop receiving emails from it, send an email to openresty-en...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.