Re: pgbench doc fix - Mailing list pgsql-hackers

Hi Fabien,

> Ok, I understand that you mean that PQsendQueryParams uses an unamed
> query internally to separate parsing & execution, which seems indeed
> to be the case by looking at the libpq client-side code.
> 
> However, if I'm not mistaken, the params version always sends and
> possibly reparses the query each time (is there a server side cache to
> avoid re-parsing? a quick scan in the sources did not return a clear
> answer to this question, but I think to recall that the answer is
> yes).

Yes, you need to send params (thus send bind message) anyway.
Regarding re-parsing, maybe you mixed up parse-analythis with
planning? Re-parse-analythis can only be avoided if you can reuse
named (or unnamed) parepared statements. As for planning, PostgreSQL
could reuse plancache at the bind time if possible. See
exec_bind_message() and GetCachePlan() for more details.

BTW, "-M extended" calls PQsendQueryParams, which sends unnamed
statements and unnamed portals:

parse message (BEGIN)
bind message (BEGIN)
describe message (BEGIN)
execute message (BEGIN)
sync message

parse message (UPDATE)
bind message (UPDATE)
describe message (UPDATE)
execute message (UPDATE)
sync message
:
:
parse message (END)
bind message (END)
describe message (END)
execute message (END)
sync message

(repeat for next transaction)

While "-M prepared" calls PQsendPrepare + PQsendQueryParepared, which
sends named statemenst and unnamed portals:

[#1 transaction]

parse message (BEGIN, statement = PO_1)
bind message (BEGIN, statement = PO_1, portal = "")
describe message (BEGIN, portal = "")
execute message (BEGIN, portal = "")
sync message

parse message (UPDATE, statement = PO_5)
bind message (UPDATE, statement = PO_5, portal = "")
describe message (UPDATE, portal = "")
execute message (UPDATE, portal = "")
sync message
:
:
parse message (END, statement = PO_10, portal = "")
bind message (END, statement = PO_10, portal = "")
describe message (END, portal = "")
execute message (END, portal = "")
sync message

[#2 transaction]

bind message (BEGIN, statement = PO_1 portal = "")
describe message (BEGIN, portal = "")
execute message (BEGIN, portal = "")
sync message

bind message (UPDATE, statement = PO_5, portal = "")
describe message (UPDATE, portal = "")
execute message (UPDATE, portal = "")
sync message
:
:
bind message (END, statement = PO_10, portal = "")
describe message (END, portal = "")
execute message (END, portal = "")
sync message

As you can see, with "-M prepared" we can save one parse message for
each command step. This is an advantage to use named statements.

>> Patch attached.
> 
> Patch applies cleanly, compiles, doc generation ok, global & local
> tests are ok.
> 
> I'm fine having a more precise wording.
> 
> Maybe I would have also insisted on the fact that there is an explicit
> vs an implicit PREPARE, if it relies on a server-side cache. The

Not sure what you mean. There's no PREPARE in extended queries (SQL
PREPARE does exits of course). Probably you mean "parse" message in
extended queires? If so, both "-M extended" and "-M prepared" use
parse messages.

> "extended"
> documentation entry does not say that it is prepared.
> 
> I created an entry in the CF and marked the patch as ready anyway.

Thanks.

BTW, as you can see, each command step above has "sync" message. This
is pretty annoying because it hurts performance a lot, i.e. every time
sync is received PostgreSQL needs to return all results at this
point. Extended query is designed to issue only once per command set
(parse, bind, describe and execute). 

This is not a fault of pgbench, rather of libpq (the sync message is
issued inside libpq). This is a serious problem because libpq can be
used by other language APIs as well, and those languages are also
affected by the slowness of libpq. Probably we should redesign (or
add) better APIs for extended queries someday.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp