multiple postgres_query in location?

[msva]

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?

[msva]

ping?

[agentzh]

@msva AFAIK, multiple postgres queries are not really supported in this module.

[msva]

yes, they aren't ;) But README says:

This directive can be used more than once within same context.

Which means the opposite ;)

[agentzh]

@msva I didn't write that document :) My hunch is that this might be a copy&paste mistake.

[msva]

Btw, I can send PR with change, that will allow it to be declared multiple times (but is it really needed?..)

Although, it is also strange, that "_pass" can be specified only in location/if_loc, while "_query" can be specified in upper blocks ;)

[agentzh]

@msva I think maybe we should just fix the docs for now ;)

[rowanthorpe]

It would be very useful to have multiple postgres_queries per location. I'm working on a REST-like app and am trying to achieve something like the following MWE: curl -X POST --data table=new_table https://x.x.x.x/api -> CREATE TABLE $table (id serial, title varchar(50), body JSONB); CREATE INDEX ${table}_gin ON $table USING GIN (body). Having the whole SQL line in one postgres_query causes the following self-explanatory error:

postgres: receiving ACK failed: multiple queries(?) while waiting for ACK from PostgreSQL database, client: x.x.x.x, server: www.xx.com, request: "POST /api HTTP/1.1", upstream: "postgres://127.0.0.1:5432", host: "www.xx.com"

...and breaking it into two separate postgres_query directives has the problem @msva already described.

At the moment I guess I need to fall back on either using a content_by_lua_block with a lua/pg library loaded there to call out to multiple times, or adding to postgres a pl/pgsql trigger on table creation to auto-create the index. I think it would be much simpler and more elegant if multiple postgres_queries per location (in order of declaration) were possible though.

Is there a reason that adding such functionality to the module is opening a can of worms, or would it be relatively straightforward and side-effect-free? @msva, when you say you could send a PR, do you already have a patch ready? I'd personally love to see it as a gist if so, even if not as a PR...

[msva]

No, I meant that I can try to work on this a bit. Although, I already tried and given up (I was lack of free time to find a proper way to implement that) 🤕.