David Welton <davidw@dedasys.com> writes:
> 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."
Um ... in general, concurrent use of a single PG connection by multiple
threads will not work, period. Neither the server nor the wire protocol
definition are designed for that.
Now, you can have multiple portals open and fetch a few rows at a time
from each one, if you're careful to serialize the fetch operations;
but TBH such usage is a niche case. It's possible this is of use
depending on the details of the API your driver exposes to applications,
but I don't have those details.
If there's not locking in your driver that restricts concurrent access to
the connection, then use of named rather than unnamed statements and
portals isn't going to fix that.
> 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?
A prepared statement is a static object created by a Parse protocol
command. The Bind command creates a portal (a query execution state) from
a statement, and must supply values for any parameters the statement has.
(Too lazy to double-check, but I think either a named or an unnamed
statement can have parameters.) Then you say Execute to run the portal.
The main difference between named and unnamed statements/portals is that
the protocol details are designed to simplify one-shot use of the unnamed
statement and portal, for instance the provision for implicit Close of the
old unnamed statement or portal if you just go and create a new one. Also
the server is optimized to expect a single use of an unnamed statement vs
multiple uses of named statements.
regards, tom lane