Thread: Entities created in one query not available in another in extended protocol

On Thu, Jun 11, 2015 at 5:38 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 11 June 2015 at 11:20, Shay Rojansky <roji@roji.org> wrote:
>>
>> In Npgsql, the .NET driver for PostgreSQL, we've switched from simple to
>> extended protocol and have received a user complaint.
>>
>> It appears that when we send two messages in an extended protocol (so two
>> Parse/Bind/Execute followed by a single Sync), where the first one creates
>> some entity (function, table), and the second one can't query that entity
>> (not found). This isn't terribly important but does seem a bit odd, I wanted
>> to make sure you're aware of this.
>
> Sounds somewhat unlikely, but thank you for the report. Can we see a test
> case?

Actually, I think I've seen this before.   The code that handles the
Sync message does this:
                       case 'S':                       /* sync */
pq_getmsgend(&input_message);                              finish_xact_command();
send_ready_for_query= true;                               break;
 

finish_xact_command() calls CommitTransactionCommand(), which does
CommandCounterIncrement() or CommitTransaction() as appropriate.  So
without the Sync, I think it's expected that you don't see the results
of the previous command.

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

Simon Riggs <simon@2ndQuadrant.com> writes:
> On 11 June 2015 at 11:20, Shay Rojansky <roji@roji.org> wrote:
>> It appears that when we send two messages in an extended protocol (so two
>> Parse/Bind/Execute followed by a single Sync), where the first one creates
>> some entity (function, table), and the second one can't query that entity
>> (not found). This isn't terribly important but does seem a bit odd, I
>> wanted to make sure you're aware of this.

> Sounds somewhat unlikely, but thank you for the report. Can we see a test
> case?

> Most commonly in such cases the first request failed and error messages
> weren't checked before running second message.

I'm wondering if it was really more like
Parse/Parse/Bind/Bind/Execute/Execute/Sync, in which case the described
behavior wouldn't be too surprising at all.

I do note that if a transaction is implicitly started to execute these
messages, it's not closed until Sync.  But that should only affect the
visibility of the results to other sessions, not to the current one.
There's definitely a CommandCounterIncrement in exec_execute_message ...
        regards, tom lane

On 2015-06-11 10:50:31 -0400, Tom Lane wrote:
> I do note that if a transaction is implicitly started to execute these
> messages, it's not closed until Sync.  But that should only affect the
> visibility of the results to other sessions, not to the current one.
> There's definitely a CommandCounterIncrement in exec_execute_message ...

exec_execute_message() only does so if the command has run to
completion. Shay, Is it possible that a row limit was used and the
commands didn't run fully?

Andres Freund <andres@anarazel.de> writes:
> On 2015-06-11 10:50:31 -0400, Tom Lane wrote:
>> I do note that if a transaction is implicitly started to execute these
>> messages, it's not closed until Sync.  But that should only affect the
>> visibility of the results to other sessions, not to the current one.
>> There's definitely a CommandCounterIncrement in exec_execute_message ...

> exec_execute_message() only does so if the command has run to
> completion. Shay, Is it possible that a row limit was used and the
> commands didn't run fully?

That wouldn't affect utility statements like CREATE FUNCTION or CREATE
TABLE, though.
        regards, tom lane

Simon Riggs <simon@2ndquadrant.com> writes:
> On 11 June 2015 at 22:12, Shay Rojansky <roji@roji.org> wrote:
>> Just in case it's interesting to you... The reason we implemented things
>> this way is in order to avoid a deadlock situation - if we send two queries
>> as P1/D1/B1/E1/P2/D2/B2/E2, and the first query has a large resultset,
>> PostgreSQL may block writing the resultset, since Npgsql isn't reading it
>> at that point. Npgsql on its part may get stuck writing the second query
>> (if it's big enough) since PostgreSQL isn't reading on its end (thanks to
>> Emil Lenngren for pointing this out originally).

> That part does sound like a problem that we have no good answer to. Sounds
> worth starting a new thread on that.

I do not accept that the backend needs to deal with that; it's the
responsibility of the client side to manage buffering properly if it is
trying to overlap sending the next query with receipt of data from a
previous one.  See commit 2a3f6e368 for a related issue in libpq.
        regards, tom lane

Shay Rojansky <roji@roji.org> writes:
> [ rely on non-blocking sockets to avoid deadlock ]

Yeah, that's pretty much the approach libpq has taken: write (or read)
when you can, but press on when you can't.

> The main issue I'm concerned about
> is SSL/TLS, which is a layer on top of the sockets and which might not work
> well with non-blocking sockets...

We have not had word of any such problem with libpq.  It's possible that
the intersection of SSL users with non-blocking-mode users is nil, but
I kinda doubt that.  You do need to interpret openssl's return codes
correctly ...
        regards, tom lane