Re: Odd query execution behavior with extended protocol

>
> > 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.
>
> Looks like the first updating statement is not committed, assuming that
> the two statements run in different transactions.
>

I did try to prefix the message chain with an explicit transaction BEGIN
(with the several different isolation levels) without a difference in
behavior.

> 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
>
> I never used Npgsql so I don't know if there is something missing there.
> Would you need an explicit commit before closing MQ0?
>

I guess this is exactly my question to PostgreSQL... But unless I'm
misunderstanding the transaction semantics I shouldn't need to commit the
first UPDATE in order to see its effect in the second SELECT...

Also I am not in clear what "statement=unnamed" means, but it is used
> twice. Is it possible that the update is overwritten with select before it
> executes?
>

statement=unnamed means that the destination statement is the unnamed
prepared statement (as described in
http://www.postgresql.org/docs/current/static/protocol-message-formats.html)
Right after the Parse I bind the unnamed statement which I just parsed to
cursor MQ0. In other words, Npgsql first parses the two queries and binds
them to portals MQ0 and MQ1, and only then executes both portals

BTW: Do you see the change after update in your DB if you look into it with
> another tool (e.g. psql)?
>

That's a good suggestion, I'll try to check it out, thanks!