I have a function returning a json defined in a postgresQL database.

CREATE OR REPLACE FUNCTION test() RETURNS JSON AS $$
  SELECT
  '[
    {"fName":"John","lName":"Doe"},
    {"fName":"Jane","lName":"Doe"}
  ]'::JSON;
$$ 
LANGUAGE SQL STRICT IMMUTABLE;


SELECT test();
-------------------------------------
 [                                  
     {"fName":"John","lName":"Doe"},
     {"fName":"Jane","lName":"Doe"} 
 ]

Furthermore I have a nginx including the Postgres nginx Module (openresty) with the following config file:

worker_processes  1;
error_log logs/error.log;
events {
    worker_connections 1024;
}
http {

    upstream database {
        postgres_server localhost dbname=example user=postgres;
        postgres_keepalive max=200 overflow=reject;
    }

    server {
        listen 8080;
        location /test/ {
                postgres_pass database;
                rds_json  on;
                postgres_query    HEAD GET  "SELECT test()";
                postgres_rewrite  HEAD GET  no_rows 410;
        }
    }
}

With rds_json on; all quotes are escaped in the output and it looks like this:

curl http://localhost:8080/test/
[{"test":"[\n    {\"fName\":\"John\",\"lName\":\"Doe\"},\n    {\"fName\":\"Jane\",\"lName\":\"Doe\"}\n  ]"}]

An if I set rds_json off; I receive a properly formatted json but the return string starts and ends with some awkward signs:

@^C^@^@^@^@^@^@^B^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@<80>r^@^D^@test^AL^@^@^@[
    {"fName":"John","lName":"Doe"},
    {"fName":"Jane","lName":"Doe"}
  ]^@

What am I doing wrong? I definitely miss something I'm not aware of.
Best reagards

I'm using this module in couple with ngx_rds_json.
One of my table contains field with type 'interval'. When I want to build json page with simple select result of this table, I receive a broken json content.

It's because I have something like that inside a response:
... ,"postpone":00:00:02, ...

You treat postgres interval type as float for RDS.
src/resty_dbd_stream.h: rds_col_type_interval = 10 | rds_rough_col_type_float,

I am not familiar with RDS at all. May be in its conception it's all right, but not sure...
May be change rds_rough_col_type_float with rds_rough_col_type_str?
For me it solved the problem but I'm not sure that this is correct.

Any plan to have Postgres' array column type serialized into a JSON array instead of a string?

Example #5 of the README demonstrates a simple RESTful API, but a useful API is not really achievable since any POST data is discarded by ngx_postgres_handler, so no records can be added or changed unless the data can be calculated within Nginx or PostgreSQL.

I need different time zones for sites. So I need to set the TimeZone session variable for all queries. It would be great to add optional parameter for postgres_server.
For example:

upstream db_site_common {
    postgres_server  127.0.0.1
        dbname=test user=test password=test
        init="
            SET TimeZone TO 'UTC';
            SET client_encoding TO 'UTF8';
        ";
}

upstream db_site_ru {
    postgres_server  127.0.0.1
        dbname=test user=test password=test
        init="
            SET TimeZone TO 'Europe/Moscow';
            SET client_encoding TO 'WIN1251';
        ";
}

It may be a postgres_pass optional parameter.
For example:

postgres_pass db_site_ru init="
            SET TimeZone TO 'Europe/Moscow';
            SET client_encoding TO 'WIN1251';
";

I spent some time reading and hacking out some examples and have been unable to retrieve GET params from the query string. None of the examples address this as the one specific example has it in the path of the url.

The module is great, but it would be useful to have more flexibility whne choosing the way Nginx connects to Postgres.

I'm working on adding a way to pass arbitrary libpq connection strings, so you could do things like:

postgres_connstring host=/var/run/postgresql dbname=mydb

to get a connection over UNIX sockets or even:

postgres_connstring sslcert=... sslrootcert=... ...

which is our use case, since we're authenticating all applications using the database with SSL certificates.

I'm wondering what would be the best way to implement it (and whether you'd be willing to accept such a patch). A separate directive (like postgres_connstring) that overrides postgres_server? Making postgres_server understand that if the directive is not "host:ip params", it's a literal libpq connstring? Something else entirely?

I tried to combine postgres_output value with postgres_rewrite no_rows =404 to achieve returning the single value that is returned by postgres or a 404 error when nothing is returned. However, this results in a server error 500 because postgres_output value expects exactly 1 value but received 0.

I have not been able to figure out how to construct a proper postgres_query when my table uses mixed case. For instance I can run on psql:

INSERT INTO mytable ("MixedCase", "FooBAR") VALUES ('Some Values', 'Others');

However postgres_query already uses quotes, so I cannot simply state:

postgres_escape $val1 $arg_MixedCase;
postgres_escape $val2 $arg_FooBAR;
postgres_query POST "INSERT INTO PatientLevel (MixedCase, FooBAR) VALUES($val1, $val2) RETURNING *";

and the following does not work:

postgres_escape $quote1 "MixedCase";
postgres_escape $quote2 "FooBAR";
postgres_escape $val1 $arg_MixedCase;
postgres_escape $val2 $arg_FooBAR;
postgres_query POST "INSERT INTO PatientLevel ($quote1, $quote2) VALUES($val1, $val2) RETURNING *";

Hi there!
Can you tag a new release on some commit after NgX-1.7.5-related fixes?

make[1]: Entering directory '/usr/local/src/nginx-1.13.2'
cc -c -pipe -O -W -Wall -Wpointer-arith -Wno-unused-parameter -Werror -g -I src/core -I src/event -I src/event/modules -I src/os/unix -I /usr/include/postgresql -I objs -I src/http -I src/http/modules -I ../njs/nginx/../nxt -I ../njs/nginx/../njs -I src/stream -I ../njs/nginx/../nxt -I ../njs/nginx/../njs
-o objs/addon/src/ngx_postgres_module.o
../ngx_postgres/src/ngx_postgres_module.c
../ngx_postgres/src/ngx_postgres_module.c: In function ‘ngx_postgres_find_upstream’:
../ngx_postgres/src/ngx_postgres_module.c:1323:21: error: ‘ngx_http_upstream_srv_conf_t {aka struct ngx_http_upstream_srv_conf_s}’ has no member named ‘default_port’
if (uscfp[i]->default_port && url->default_port
^
../ngx_postgres/src/ngx_postgres_module.c:1324:25: error: ‘ngx_http_upstream_srv_conf_t {aka struct ngx_http_upstream_srv_conf_s}’ has no member named ‘default_port’
&& (uscfp[i]->default_port != url->default_port))
^
objs/Makefile:1454: recipe for target 'objs/addon/src/ngx_postgres_module.o' failed

I'm trying to invoke a function in Postgres using ngx_postgres, but it seems to be timing out.

This the route handler definition:

location /allocate {
      postgres_result_timeout 2s;
      postgres_pass           database;
      postgres_output         text;
      postgres_query          POST  "SELECT allocate('{1,2,3}')";
}

And this is the error log from openresty:

2014/11/16 23:50:04 [error] 17363#0: *98 upstream timed out (60: Operation timed out) while waiting for result from PostgreSQL database, client: 127.0.0.1, server: localhost, request: "POST /allocate HTTP/1.1", upstream: "postgres://127.0.0.1:5432", host: "127.0.0.1:7999"
2014/11/16 23:50:04 [info] 17363#0: *98 kevent() reported that client 127.0.0.1 closed keep alive connection

If I invoke the function with exactly the arguments directly using a postgres client, it returns works as expected.

Also, if I comment out the portions of the function in Postgres that is actually inserting into the database, and just get the function to return a count of the elements it received from nginx, this seems to work (i.e. the actually call syntax between openresty and Postgres seems to be ok).

So I'm left wondering whether under the covers ngx_postgres is using an async driver for Postgres, and whether that is having any effect on the interaction between openresty and Postgres.

What steps should I take to go about debugging this?

[src/ngx_postgres_escape.c:50] -> [src/ngx_postgres_escape.c:55]: (warning) Either the condition 'v==0' is redundant or there is possible null pointer dereference: v.
[src/ngx_postgres_escape.c:56] -> [src/ngx_postgres_escape.c:55]: (warning) Either the condition 'v==0' is redundant or there is possible null pointer dereference: v.
[src/ngx_postgres_escape.c:57] -> [src/ngx_postgres_escape.c:55]: (warning) Either the condition 'v==0' is redundant or there is possible null pointer dereference: v.

The current way of passing untrusted user input is via manually escaping and then forming the sql query with the escaped values.

This is prone to human error and could result in sql injection attacks being possible.

The ideal situation would be to add support for prepared statements using placeholders. Is there any technical limitation of the nginx engine preventing this?

I suspect that there is some bug in ngx_postgres' connection cache code. While stressing the nginx with "ab -c 20 ..." it occasionally segfaulted, so I ran it under valgrind. There is the result:

==25581== Invalid read of size 8
==25581== at 0x406DAB: ngx_destroy_pool (ngx_palloc.c:76)
==25581== by 0x4B6457: ngx_postgres_upstream_free_connection (ngx_postgres_upstream.c:584)
==25581== by 0x4B00FE: ngx_postgres_keepalive_free_peer (ngx_postgres_keepalive.c:218)
==25581== by 0x4B60DC: ngx_postgres_upstream_free_peer (ngx_postgres_upstream.c:509)
==25581== by 0x4B6674: ngx_postgres_upstream_finalize_request (ngx_postgres_util.c:79)
==25581== by 0x4B4DCD: ngx_postgres_upstream_done (ngx_postgres_processor.c:507)
==25581== by 0x4B4D46: ngx_postgres_upstream_get_ack (ngx_postgres_processor.c:488)
==25581== by 0x4B4951: ngx_postgres_upstream_get_result (ngx_postgres_processor.c:366)
==25581== by 0x4B40F0: ngx_postgres_process_events (ngx_postgres_processor.c:76)
==25581== by 0x4AF7D2: ngx_postgres_rev_handler (ngx_postgres_handler.c:314)
==25581== by 0x46415C: ngx_http_upstream_handler (ngx_http_upstream.c:976)
==25581== by 0x437C1D: ngx_epoll_process_events (ngx_epoll_module.c:691)
==25581== by 0x42853F: ngx_process_events_and_timers (ngx_event.c:248)
==25581== by 0x4347E4: ngx_single_process_cycle (ngx_process_cycle.c:315)
==25581== by 0x403DF4: main (nginx.c:404)
==25581== Address 0x5641930 is 96 bytes inside a block of size 256 free'd
==25581== at 0x4A063F0: free (vg_replace_malloc.c:446)
==25581== by 0x406E39: ngx_destroy_pool (ngx_palloc.c:87)
==25581== by 0x44E8D2: ngx_http_close_connection (ngx_http_request.c:3489)
==25581== by 0x44E56D: ngx_http_close_request (ngx_http_request.c:3350)
==25581== by 0x44E0C1: ngx_http_lingering_close_handler (ngx_http_request.c:3209)
==25581== by 0x44DF6B: ngx_http_set_lingering_close (ngx_http_request.c:3171)
==25581== by 0x44C858: ngx_http_finalize_connection (ngx_http_request.c:2493)
==25581== by 0x44C443: ngx_http_finalize_request (ngx_http_request.c:2384)
==25581== by 0x4B6800: ngx_postgres_upstream_finalize_request (ngx_postgres_util.c:140)
==25581== by 0x4B4DCD: ngx_postgres_upstream_done (ngx_postgres_processor.c:507)
==25581== by 0x4B4D46: ngx_postgres_upstream_get_ack (ngx_postgres_processor.c:488)
==25581== by 0x4B4951: ngx_postgres_upstream_get_result (ngx_postgres_processor.c:366)
==25581== by 0x4B40F0: ngx_postgres_process_events (ngx_postgres_processor.c:76)
==25581== by 0x4AF7D2: ngx_postgres_rev_handler (ngx_postgres_handler.c:314)
==25581== by 0x46415C: ngx_http_upstream_handler (ngx_http_upstream.c:976)
==25581== by 0x437C1D: ngx_epoll_process_events (ngx_epoll_module.c:691)
==25581== by 0x42853F: ngx_process_events_and_timers (ngx_event.c:248)
==25581== by 0x4347E4: ngx_single_process_cycle (ngx_process_cycle.c:315)
==25581== by 0x403DF4: main (nginx.c:404)

I believe that "ngx_postgres_keepalive_free_peer" (ngx_postgres_keepalive.c:164) somehow fails to correctly isolate some nginx memory pool from freeing which occurs later at (ngx_postgres_util.c:140) and eventually leads to an attempt of reading invalid data from freed and reused memory area.

I'm looking forward to giving any comprehensive information to anybody whom it may concern.

I'm unable to get something like sample configuration #3 to work. I apologize in advance if this question is a bit naive -- I'm not extremely familiar with nginx. When I include eval_subrequest_in_memory off; or the subsequent directives, I receive:

nginx: [emerg] unknown directive "eval_subrequest_in_memory" in /foo/nginx.conf:70

I'm using openresty, and from what I'm reading, it sounds like nginx-eval-module (agentzh's fork) is part of openresty. Below are the relevant version details and modules:

$ nginx -V
nginx version: openresty/1.7.10.2
built by clang 6.1.0 (clang-602.0.49) (based on LLVM 3.6.0svn)
TLS SNI support enabled
configure arguments: --prefix=/usr/local/openresty/nginx --with-cc-opt='-O2 -I/usr/local/include' --add-module=../ngx_devel_kit-0.2.19 --add-module=../echo-nginx-module-0.58 --add-module=../xss-nginx-module-0.05 --add-module=../ngx_coolkit-0.2rc3 --add-module=../set-misc-nginx-module-0.29 --add-module=../form-input-nginx-module-0.11 --add-module=../encrypted-session-nginx-module-0.04 --add-module=../ngx_postgres-1.0rc6 --add-module=../srcache-nginx-module-0.30 --add-module=../ngx_lua-0.9.16 --add-module=../ngx_lua_upstream-0.03 --add-module=../headers-more-nginx-module-0.26 --add-module=../array-var-nginx-module-0.04 --add-module=../memc-nginx-module-0.16 --add-module=../redis2-nginx-module-0.12 --add-module=../redis-nginx-module-0.3.7 --add-module=../rds-json-nginx-module-0.14 --add-module=../rds-csv-nginx-module-0.06 --with-ld-opt=-L/usr/local/lib --with-http_ssl_module

Bigger picture, what I'd like to do is source a URL from a table and redirect for a download. I'm less concerned about how this is accomplished, and more concerned about being able to do it. As a practical example, say I have the following table:

create table foo (
    id varchar,
    url varchar
)

What I'd like to be able to do from nginx is something along these lines:

location ~ /foo/(?<id>\d+) {
    postgres_output none;
    postgres_pass    database;
    postgres_escape $escaped $id;
    postgres_query  GET "SELECT url FROM foo WHERE id=$escaped";
    postgres_rewrite no_rows 410;
    postgres_set $url 0 0 required;
    return 301 $url;   
}

...however, I realize that (for reasons I don't fully grok) that return and the postgres_* statements aren't compatible due to how nginx processes things.

Any help would be greatly appreciated, thanks!

I want to escape a variable that gets captured from the url via a regex.

location ~ ^/test/(\d+)$ {
postgres_pass database;
rds_json on;
postgres_escape $name $1;
postgres_query "SELECT $name";
}

nginx complains when parsing the config file.
[emerg]: unknown "1" variable

Is there a special syntax for captured variables?
Is it something not supported?

regards,

Arnaud

I get the following compilation error when trying to compile nginx.

Configuration =
./configure --add-module=../ngx_postgres-0.5 --add-module=../chaoslawful-drizzle-nginx-module-f0ba8f5 --add-module=../agentzh-rds-json-nginx-module-bb9a578/

Error msg =
cc1: warnings being treated as errors
../ngx_postgres-0.5/src/ngx_postgres_escape.c: In function ‘ngx_postgres_escape_string’:
../ngx_postgres-0.5/src/ngx_postgres_escape.c:60: warning: pointer targets in assignment differ in signedness
make[1]: *** [objs/addon/src/ngx_postgres_escape.o] Error 1
make: *** [build] Error 2

OS is
%uname -a
Darwin sahuguet-macbookpro.local 9.8.0 Darwin Kernel Version 9.8.0: Wed Jul 15 16:55:01 PDT 2009; root:xnu-1228.15.4~1/RELEASE_I386 i386

regards,

Arnaud

I wanted to convert module myself, but I can miss something (config with too many sections). Currently this is only one non dynamic module I currently use.

The eval module has been deprecated, "production use is discouraged"; is the postgres_set feature usable at all in the nginx control flow? There does not seem to be a means of passing the value of postgres_set into an if statement, redirect or error page; the value seems to be opaque until sending it to the client.

would be nice if the query string takes parameters....

int PQconsumeInput(PGconn *conn)

PQconsumeInput function only call pqReadData once.

so if my pg output larger than 16kb( the initial size of libpq' in buffer), there maybe have some data in input buffer. In epoll EPOLLET mode, this is not right.

I have patched libpq's code, In patched library, PQconsumeInput functioncall pqReadData in a while.

while ((res = pqReadData(conn)) > 0)
        ;

if (res < 0)
     return 0;

This patch solved my problem. and the response time is also reduced to the origin's 2/3.

see [1]

Why this restriction?
This forbid users to factorise the configuration and put postgres_set directive inside a file that will be included in multiple server

[1] https://github.com/FRiCKLE/ngx_postgres/blob/master/src/ngx_postgres_module.c#L1097

Hi there! I've get this, when trying to compile NgX-1.1.6 with latest "tagged" version of ngx_postgres:

make -j3 -s 'LINK=x86_64-pc-linux-gnu-gcc -Wl,-O1,--as-needed' OTHERLDFLAGS=-Wl,-O1,--as-needed
/var/tmp/portage/www-servers/nginx-1.1.6/work/FRiCKLE-ngx_postgres-f28923c/src/ngx_postgres_output.c: In function 'ngx_postgres_output_chain':
/var/tmp/portage/www-servers/nginx-1.1.6/work/FRiCKLE-ngx_postgres-f28923c/src/ngx_postgres_output.c:583:5: warning: passing argument 1 of 'ngx_chain_update_chains' from incompatible pointer type
src/core/ngx_buf.h:157:6: note: expected 'struct ngx_pool_t ' but argument is of type 'struct ngx_chain_t *'
/var/tmp/portage/www-servers/nginx-1.1.6/work/FRiCKLE-ngx_postgres-f28923c/src/ngx_postgres_output.c:583:5: error: too few arguments to function 'ngx_chain_update_chains'
src/core/ngx_buf.h:157:6: note: declared here
make[1]: *** [objs/addon/src/ngx_postgres_output.o] Error 1
make[1]: *** Waiting for unfinished jobs....
make: *** [build] Error 2

Any Ideas, how to fix this? :)

I get reproducible segfaults in ngx_postgres_keepalive_get_peer_single (and sometimes in ngx_postgres_keepalive_cleanup). Postgres connections are ok (functional) but they segfault after the requests have finished.

These are the versions used:

• openresty 1.7.0.1
• nginx-1.7.1.tar.gz (not packaged w/ openresty, but want syslog facility)
• ngx_postgres-1.0rc3-8f0eae297fc9e88e6ab08e6c9d18628568befba9
  (not packaged, but want unix socket facility)

It runs in a chroot jail with "daemon off" under supervision of sv.
Any ideas? Should I provide the binary and the coredumps?

nginx/sbin/nginx -V
nginx version: nginx/1.7.1
TLS SNI support enabled
configure arguments: --prefix=/opt/foo/sys/opt/ngx_openresty-1.7.0.1.tar.gz-b7f7b683e0d0748db1ec2d66c23959bf5f13cb1471f8e33631d132d0-neo-20140615200651/nginx --with-debug --add-module=../ngx_devel_kit-0.2.19 --add-module=../echo-nginx-module-0.53 --add-module=../xss-nginx-module-0.04 --add-module=../ngx_coolkit-0.2rc1 --add-module=../set-misc-nginx-module-0.24 --add-module=../form-input-nginx-module-0.08 --add-module=../encrypted-session-nginx-module-0.03 --add-module=../ngx_postgres-1.0rc3-8f0eae297fc9e88e6ab08e6c9d18628568befba9 --add-module=../srcache-nginx-module-0.27 --add-module=../ngx_lua-0.9.8 --add-module=../ngx_lua_upstream-0.02 --add-module=../headers-more-nginx-module-0.25 --add-module=../array-var-nginx-module-0.03 --add-module=../memc-nginx-module-0.14 --add-module=../redis2-nginx-module-0.11 --add-module=../redis-nginx-module-0.3.7 --add-module=../rds-json-nginx-module-0.13 --add-module=../rds-csv-nginx-module-0.05 --with-ld-opt=-Wl,-rpath,/opt/foo/sys/opt/ngx_openresty-1.7.0.1.tar.gz-b7f7b683e0d0748db1ec2d66c23959bf5f13cb1471f8e33631d132d0-neo-20140615200651/luajit/lib --pid-path=/var/run/orsty.pid --error-log-path=/var/log/orsty/error.log --http-fastcgi-temp-path=/var/tmp/orsty/fcgi --http-uwsgi-temp-path=/var/tmp/orsty/uwsgi --http-scgi-temp-path=/var/tmp/orsty/scgi --http-client-body-temp-path=/var/tmp/orsty/cbody --http-proxy-temp-path=/var/tmp/orsty/proxy --with-http_ssl_module --with-http_spdy_module --with-http_gzip_static_module --with-http_stub_status_module --with-file-aio --without-http_autoindex_module --without-http_browser_module --without-http_map_module --without-http_ssi_module --without-http_memcached_module --without-mail_pop3_module --without-mail_imap_module --without-mail_smtp_module --without-http_userid_module --without-http_uwsgi_module --add-module=/opt/foo/pkg/patch/nginx/naxsi-0.53-2/naxsi_src

I had tried this configuratio for my project:

postgres_pass database;
postgres_query "SELECT id FROM articles WHERE slug='$slug' LIMIT 1";
postgres_set $id 0 0;
postgres_output none;

When i try to use $id for rewrite operator it returns nothing:
rewrite ^/(.*)$ /$id permanent; => redirects to "/" but not /$id

When i try echo it, it returns nothing:
echo "id = '$id'"; => id=''

But when i try example from your tests with header, it works:
add_header "X-ID" $id;

Why it is? Why i can`t get variable value?

I am using currently using content_by_lua_file, so I was thinking if I can use the module from the lua script itself?

/var/ports/basejail/usr/ports/www/nginx/work/ngx_postgres-0.9/src/ngx_postgres_upstream.c:106:65: error: too few arguments to function call,
expected 5, have 4
NGX_SOCKADDR_STRLEN, 0);

https://gist.github.com/anonymous/df608d3dc920455a8bfb

Hi Frickle,

Just curious, when to use postgres_rewrite?

Thanks
Eric Xin

...
gmake[1]: Entering directory /home/ano/1/nginx/nginx-1.5.3' cc -c -pipe -O -W -Wall -Wpointer-arith -Wno-unused-parameter -Werror -g -DNDK_SET_VAR -DNDK_SET_VAR -DNDK_UPSTREAM_LIST -DNDK_SET_VAR -DNDK_SET_VAR -I src/core -I src/event -I src/event/modules -I src/os/unix -I ../ngx_devel_kit/objs -I objs/addon/ndk -I /usr/include/pgsql -I ../lua-nginx-module-0.8.4/src/api -I objs -I src/http -I src/http/modules -I ../ngx_devel_kit/src -I src/mail \ -o objs/addon/src/ngx_postgres_upstream.o \ ../ngx_postgres/src/ngx_postgres_upstream.c ../ngx_postgres/src/ngx_postgres_upstream.c: In function ‘ngx_postgres_upstream_init’: ../ngx_postgres/src/ngx_postgres_upstream.c:107:43: error: passing argument 2 of ‘ngx_sock_ntop’ makes integer from pointer without a cast [-Werror] In file included from src/core/ngx_core.h:69:0, from ../ngx_postgres/src/ngx_postgres_ddebug.h:32, from ../ngx_postgres/src/ngx_postgres_upstream.c:34: src/core/ngx_inet.h:110:8: note: expected ‘socklen_t’ but argument is of type ‘u_char *’ ../ngx_postgres/src/ngx_postgres_upstream.c:107:43: error: passing argument 3 of ‘ngx_sock_ntop’ makes pointer from integer without a cast [-Werror] In file included from src/core/ngx_core.h:69:0, from ../ngx_postgres/src/ngx_postgres_ddebug.h:32, from ../ngx_postgres/src/ngx_postgres_upstream.c:34: src/core/ngx_inet.h:110:8: note: expected ‘u_char *’ but argument is of type ‘long unsigned int’ ../ngx_postgres/src/ngx_postgres_upstream.c:107:43: error: too few arguments to function ‘ngx_sock_ntop’ In file included from src/core/ngx_core.h:69:0, from ../ngx_postgres/src/ngx_postgres_ddebug.h:32, from ../ngx_postgres/src/ngx_postgres_upstream.c:34: src/core/ngx_inet.h:110:8: note: declared here cc1: all warnings being treated as errors gmake[1]: *** [objs/addon/src/ngx_postgres_upstream.o] Error 1 gmake[1]: Leaving directory/home/ano/1/nginx/nginx-1.5.3'
gmake: *** [build] Error 2

Currently only one upstream database is supported. In the 'http' section of the nginx.conf file. For different 'server' section in nginx.conf file, how to get different upstream database?

e.g.

http {

upstream database {
    postgres_server  127.0.0.1 dbname=<dbname> user=<user> password=<password>;
}   

....

http {

server {
    location = /numbers/ {
        postgres_pass     database;
        rds_json          on; 

        postgres_query    HEAD GET  "SELECT * FROM <table>";
    }   
    server_name vhost1;
    upstream database {
        postgres_server  127.0.0.1 dbname=<dbname> user=<user> password=<password>;
    }   
}
server {
    location = /alphabets/ {
        postgres_pass     database;
        rds_json          on; 

        postgres_query    HEAD GET  "SELECT * FROM <another_table>";
    }   
    server_name vhost2;
    upstream database {
        postgres_server  127.0.0.1 dbname=<another_dbname> user=<another_user> password=<another_password>;
    }   
}

...

0down votefavorite | I am trying Openresty connecting to Postgresql returning html content to the browser from postgresql functions. I am able to receive the response but the response also contains the headers of the SQL table with some junk characters which is something I do not want.My nginx conf file is as follows

`worker_processes 1;
error_log logs/error.log;
events {
worker_connections 1024;
}
http {

upstream database {
postgres_server 127.0.0.1 dbname=mydb user=postgres password=mypass;
postgres_keepalive max=200 overflow=reject;
}

server {
listen 8080;

location /articles/ {

        postgres_pass database;


        postgres_query    HEAD GET  "SELECT getArticles()";
        more_set_headers  'Content-Type: text/html;charset=utf-8';
        postgres_rewrite  HEAD GET  no_rows 410;

}

}
}`

My Postgres function is as follows

`CREATE OR REPLACE FUNCTION getArticles() RETURNS text AS $$
DECLARE
returnText text;
p1 record;
BEGIN
returnText := '';

FOR p1 in SELECT * FROM articles LOOP
  returnText := returnText || '<div>' || p1.title || '</div>';
END LOOP;
returnText := returnText || '</body></html>';

RETURN returnText;

END;
$$ LANGUAGE 'plpgsql';`

When I run the code I get the following response

���getarticles^

The getArticles is the SQL table header of the result which I do not need as a response. Is there a way to avoid these headers and garbage characters coming as part of the response?

Hello

I am using Postgres 9.4 with JSONB fields.

I want to do a query which returns JSON to the web browser from Nginx.

How do I do this? Currently I get a result set back that contains the field name and then JSON is provided as text.

thanks!

Hi!
The README, in the postgres_query section says "This directive can be used more than once within same context."
Although, when I trying to add multiple postgres_query directives inside the location (on NginX 1.9.0), I get:

2015/05/16 01:28:07 [emerg] 181869#0: "postgres_query" directive is duplicate in /etc/nginx/nginx.conf:159

is it a bug or does README lie?

Does a mechanism exist for capturing SQL exceptions and returning the error message in a http response?

Are there any plans to support LISTEN and NOTIFY? Specifically, I'd like to configure a location block such that when I make an HTTP request, it will block, listening on some condition, until it is notified.

Hello there!
I think, it would be great to make default behaviour to inherit postgres_pass setting from top-level location to it's nested locations.

For example, I assume that to work fine, instead of throwing 404:

location /db {
  postgres_pass   db;
  internal;
  location /db/test {
    postgres_query "SELECT id,name from users";
  }
}

Thanks a lot!

Hello,

Thanks for the module.

I am trying to do a data Insert followed by nginx return but it seems postgres_query is skipped if I do a return. If I comment out the return, then insert works fine:

        postgres_pass  database2;
        rds_json on;
        postgres_query "INSERT INTO numbers VALUES(80)";
        return 301 $remote_url;

Any way to execute postgres_query before doing 301 redirection.

Thanks!

Jimish

The rc's seems to have travelled for quite a while now. How about just slicing 1.0? :)

Hi, i have some trouble.

I have query that returns 789 rows:

SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC;
Execution Time: 2.558 ms

But sometimes i have suspension this query and failing by timeout.
In postgres in pg_stat_activity i see:

=# select state, query, now() - query_start as duration from pg_stat_activity order by duration desc limit 1;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
state | idle
query | SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC
duration | 00:00:57.017424

According to http://www.postgresql.org/docs/9.4/static/monitoring-stats.html
query: "Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed."
state = idle: The backend is waiting for a new client command.

That means that query was executed and postgres is waiting for a new nginx command. Then timeout (1 minute) expires and nginx returns 500 with timeout.

config:
upstream database {
postgres_server xxxxxxxxxxx;
postgres_keepalive off;
}
..................
.................
server {
location = /query {
internal;
postgres_pass database;
postgres_connect_timeout 30s;
postgres_result_timeout 1m;
postgres_query $echo_request_body;
}
}

Also i have similarly query (returns 928 rows) that also sometimes suspending.
I noticed that hang queries that return many rows.

Please help me to understand, that's going on.
Thanks.

It seems there's no such hook in ngx_postgres to allow custom queries to be sent upon upstream connection establishment.

I am trying out ngx_postgres with pgrest (http://github.com/clkao/pgrest). However for each connection, we need to call a postgresql function via select, something like: SELECT pgrest_boot('{...bootstrap_json}').

I also tried calling the bootstrap function along with other queries mapped from GET requests, but ngx_postgres is not handling multiple queries very well.

Is there any way of making the two compatible?
The usecase is that i do not want (can't) to hardcode the connection info in the config file, i have to read it from the env.
For a proxy upstream i can do this

upstream myhost {
        server 127.0.0.1:3000; #dummy
        balancer_by_lua_block {
            local balancer = require 'ngx.balancer'
            local host = os.getenv('HOST')
            local port = os.getenv('PORT')
            local ok, err = balancer.set_current_peer(host, port)
            if not ok then
                return ngx.exit(500)
            end
        }
        keepalive 64;
    }

How can i have something similar with ngx_postgrest?
Thank you

Hi,

We have the following config:

    location = "/foo" {                                                                                                                                                                                         
        echo_read_request_body;                                                                                                                                                                                 
        echo $request_body;                                                                                                                                                                                     
    }                                                                                                                                                                                                           

    location ~ "/(?<db>\w+)/(?<schema>\w+)/(?<function>\w+)(?<path>/.*){0,}$" {                                                                                                                                 
        echo_read_request_body;                                                                                                                                                                                 
        postgres_escape $user $remote_user;                                                                                                                                                                     
        postgres_output text;                                                                                                                                                                                   
        postgres_pass $db;                                                                                                                                                                                      
        postgres_query POST "SELECT * FROM http.post('$schema', '$function', '$path', '$user', '$request_body')";                                                                                               
        postgres_query HEAD GET "SELECT * FROM http.get('$schema', '$function', '$path', '$user')";                                                                                                             
    }   

POSTing to /foo works fine (the request body is correctly echoed) but POSTing to any urls that match the other location, $request_body is always an empty string. Otherwise the module works perfectly and we are very happy with it. We have been considering switching to a lua content block to get what we want as a workaround, but we'd rather keep it simple.

Thanks so much for your excellent module!

There are some JSON-RPC support or a simple way to configure it? If not, it is a suggestion.

Example: supposing rds_json_format=normal and rds_json_root=result, it is near to a JSON-RPC response,

 {"result":[{"id":2,"name":null},{"id":3,"name":"bob"}]}

need only to add jsonrpc and id attributes,

 { "jsonrpc":2.0, "id":123, "result":[{"id":2,"name":null},{"id":3,"name":"bob"}]}

where id is the ID for control the AJAX promises or any other kind of request-ID, and jsonrpc only a standard way to say that the client-server dialog is a JSON-RPC 2.0 (REST-adapted) dialect.

Error response also need some minimal adaptations, and use as primary-error-code the HTTP status codes... It is a convention over configuration proposal.

PS: no "protocol conflict" here, JSON-RPC can be used in a context of REST standards. It is only a standarized way to encapsulate JSON, and can be used also internally as generic SQL command tool.

Exemple of snippet code for internal error handling

/**
 * Add attributes "code" and "message" in a JSONb object, or create a new one.
 */
CREATE or replace FUNCTION lib.resp_error_add(
  p_code float, -- the error code in the form general_httpStatus.detail_internalCode
  p_msg text DEFAULT NULL,  -- the error message
  p_data JSONb DEFAULT NULL   -- optional data for warnings like 200.X
) RETURNS JSONb AS $f$
  SELECT CASE
      WHEN p_data IS NULL OR jsonb_typeof(p_data)='null' THEN x
      ELSE jsonb_build_object('result',p_data) || x
    END
  FROM (SELECT jsonb_build_object('code',p_code, 'message',p_msg)) t(x)
$f$ language SQL IMMUTABLE;

CREATE or replace FUNCTION lib.resp_error_add(
  int, text, JSONb DEFAULT NULL
) RETURNS JSONb AS $wrap$
  SELECT lib.resp_error_add((200.0+$1/1000.0)::float, $2, $3);
$wrap$ language SQL IMMUTABLE;

So, another interesting (optional) adaptation is to get the integer part of a float error code, or check that it is to by-pass as status 200.

awesome project, it helps a lot.

but unix socket is missing, please support them ...

unix socket #ftw

A config like the following config fails:

location /x {  postgres_escape $data $arg_data; }
location /y {  postgres_escape $data $arg_data; }

result is error such as:

[emerg]: the duplicate "data" variable in /etc/nginx/nginx.conf:31
configuration file /etc/nginx/nginx.conf test failed

Replacing "postgres_escape" with "set" does not result in similar error.

I am trying to pass on JSON POST requests to postgres directly. Once a postgres_pass is included, all request info is lost. Is there a work around? Would really appreciate some help here.

Things I have tried without success so far:

1. specified a fasctcgi inside the location block.
2. Removed ngx_http_discard_request_body from ngx_postgres_handler.c
3. tried setting "conf->upstream.pass_request_body = 1;" in ngx_postgres_module.c

Thanks for good job & cool module for nginx!
I had only one question: how to cache database results in nginx using your module?