Postgresql database API

[Brent Tucker]

I am looking to build a http/json API on an existing Lua application that I have that binds stored procedures in a Postgresql database schema to a Lua global object table with the associated functions as closures wrapping the stored procedure function call.

It uses the module below (a minimal luajit ffi binding to libpq) . Is anyone able to provide some guidance on the best way to re-implement this with the NGINX API for Lua

https://github.com/cthulhuology/pgproc.lua

What I am hoping for is a way to run the binding query once when Nginx is loading the config file and then have the function calls  available at one and/or many different locations. I am just beginning to learn about Nginx directives, etc.

Much Appreciated.

Brent

[Lord Nynex]

Hello,

It's not likely you will get any performance gain from using FFI bindings to pq. In particular this would negate really nifty/performant socket pooling inside ngx_lua core. 

You're better off using one of the postgres modules that come with openresty or a pure lua implementation like @leafo's pgmoon (https://github.com/leafo/pgmoon)

--
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.

[Yichun Zhang (agentzh)]

Hello!

On Tue, Apr 7, 2015 at 1:39 PM, Brent Tucker wrote:
> It uses the module below (a minimal luajit ffi binding to libpq) . Is anyone
> able to provide some guidance on the best way to re-implement this with the
> NGINX API for Lua
>
> https://github.com/cthulhuology/pgproc.lua
>

Mind you, this FFI + libpq approach will tragically block nginx's
event loop on I/O operations and ruin the performance under relatively
high concurrency level! As Lord Nynex has suggested, better use the
3rd-party Lua libraries designed specifically for OpenResty, like
lua-resty-postgres or pgmoon, which are based on the nonblocking
cosocket API provided by ngx_lua.

We used to go with the libpq route by writing the ngx_postgres nginx C module:

https://github.com/FRiCKLE/ngx_postgres

But we ran into quite some quirks in libpq due to the fact that its
nonblocking mode does not take edge-triggered events into serious
account, thus requiring dirty workarounds and etc. Well, you could use
subrequests to call into ngx_postgres via ngx.location.capture but
this approach is much less flexible (and may be also less efficient
than) the pure Lua approach mentioned above.

Regards,
-agentzh