Re: Odd query execution behavior with extended protocol

On Sat, Oct 3, 2015 at 5:03 AM, Shay Rojansky <roji(at)roji(dot)org> wrote:
> Hi hackers, some odd behavior has been reported with Npgsql and I wanted to
> get your help.
>
> Npgsql supports sending multiple SQL statements in a single packet via the
> extended protocol. This works fine, but when the second query SELECTs a
> value modified by the first's UPDATE, I'm getting a result as if the UPDATE
> hasn't yet occurred.
>
> The exact messages send by Npgsql are:
>
> Parse (UPDATE data SET name='foo' WHERE id=1), statement=unnamed
> Describe (statement=unnamed)
> Bind (statement=unnamed, portal=MQ0)
> Parse (SELECT * FROM data WHERE id=1), statement=unnamed
> Describe (statement=unnamed)
> Bind (statement=unnamed, portal=MQ1)
> Execute (portal=MQ0)
> Close (portal=MQ0)
> Execute (portal=MQ1)
> Close (portal=MQ1)
> Sync
>
> Instead of returning the expected 'foo' value set in the first command's
> UPDATE, I'm getting whatever value was previously there.
> Note that this happen regardless of whether a transaction is already set and
> of the isolation level.
>
> Is this the expected behavior, have I misunderstood the protocol specs?

From looking at the code, it appears to me that if the Execute is run
to completion, then its results will be seen by future statements, but
if the portal is closed earlier, then not. See the end of
exec_execute_message. The handler for the Close message (inside
PostgresMain) doesn't seem to do anything that would result in a
CommandCounterIncrement() or CommitTransactionCommand().

This does seem a little strange.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company