Re: named queries and the wire protocol

From: David Welton <davidw(at)dedasys(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>, epgsql(at)googlegroups(dot)com
Subject: Re: named queries and the wire protocol
Date: 2014-03-14 10:37:35
Message-ID: CA+b9R_uGTmeNc4Z70BFNEFb6vTQ=iNWaujVtezBdEmKNH8M8Tw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Antman, Jason (CMG-Atlanta) 2014-03-14 11:08:57 Re: High Level Committers Wanted
Previous Message Ian Lawrence Barwick 2014-03-14 02:19:10 Re: [PERFORM] Very slow query in PostgreSQL 9.3.3