Re: named queries and the wire protocol

Hi,

On Thu, Mar 13, 2014 at 1:51 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Welton <davidw(at)dedasys(dot)com> writes:
>>> send(State, ?BIND, ["", 0, StatementName, 0, Bin1, Bin2]),
>>> send(State, ?EXECUTE, ["", 0, <<0:?int32>>]),
>>> send(State, ?CLOSE, [?PREPARED_STATEMENT, StatementName, 0]),
>>> send(State, ?SYNC, []),
>
>> And then the code above. So it's generating a name itself and then
>> destroying it once the query is done.
>> Perhaps this behavior is not a good idea and using the unnamed portal
>> would be a better idea?

> My point is that it *is* using the unnamed portal, AFAICS --- the ""s
> in the Bind and Execute commands appear to correspond to the empty
> strings that would select that portal.

Ok, yes, that makes sense.

> The Close on the other hand is specifying closing a prepared statement,
> not a portal. If you're right about the control flow around this
> function, then the code is generating a prepared statement, using it
> once, and destroying it. Which is dumb; you should instead use the
> unnamed-statement protocol flow, which is better optimized for that
> usage pattern.

We tracked down the commit that introduced the automatically generated
prepared statement names:

https://github.com/epgsql/epgsql/commit/dabf972f74735d2

The author wrote "Usage of unnamed prepared statement and portals
leads to unpredictable results in case of concurrent access to same
connection."

For my own clarification, going by
http://www.postgresql.org/docs/devel/static/protocol-overview.html -
the named statement has no parameters - it's just a parsed statement,
whereas a portal is a statement subsequently bound to some parameters?

Can you or someone speak to the concurrency issues?

A big thanks for taking the time to go over this with me,
--
David N. Welton

http://www.dedasys.com/