Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block

Tom Lane schrieb:
> Brar Piening <brar(at)gmx(dot)de> writes:
>> At the wire protocol level npgsql sends this as two queries via the
>> extended query protocol without a sync inbetween them.
>> (Parse,Bind,Describe,Execute;Parse,Bind,Describe,Execute,Sync)
> There is no implicit transaction block around the two commands in such
> a case, so that explains why it doesn't act as Mike was hoping.
>
> Omitting the Sync has zero effect on transactional semantics; it only
> means that if the first command fails, we'll skip the second one.

I think what Mike is wondering about (and I am too)  is the fat that
Sync seems to have *partial* effect on transactional semantics.
The libpq program I showed above outputs the following:

WARNING:  SET LOCAL can only be used in transaction blocks
100kB

So it produes the same tuple as If I had written 'PQexec(conn, "SET
LOCAL work_mem = 100;show work_mem;");' but with the additional warning.

I can reproduce the behavior of the sequence 'PQexec(conn, "SET LOCAL
work_mem = 100;");/*PQclear,...*/PQexec(conn, "show work_mem;");' by
introducing a Sync between the two commands in extended query protocol
with pipelining mode like the following:

int
main(int argc, char **argv)
{
    PGconn* conn;
    PGresult* res;

    conn = PQconnectdb("");
    PQenterPipelineMode(conn);
    PQsendQueryParams(conn, "SET LOCAL work_mem = 100;", 0, NULL, NULL,
NULL, NULL, 1);
    PQpipelineSync(conn);
    PQsendQueryParams(conn, "SHOW work_mem;", 0, NULL, NULL, NULL,
NULL, 1);
    PQpipelineSync(conn);

    res = PQgetResult(conn); // SET LOCAL => PGRES_COMMAND_OK
    PQclear(res);

    PQgetResult(conn); // NULL

    res = PQgetResult(conn); // PGRES_PIPELINE_SYNC
    PQclear(res);

    res = PQgetResult(conn); // SHOW => PGRES_TUPLES_OK
    fprintf(stdout, "%s\n", PQgetvalue(res, 0, 0));
    PQclear(res);

    PQgetResult(conn); // NULL

    res = PQgetResult(conn); // PGRES_PIPELINE_SYNC
    PQclear(res);

    PQfinish(conn);
    return 0;
}

This returns:

WARNING:  SET LOCAL can only be used in transaction blocks
4MB

Which is not surprising at all and completely what I'd expect. What's
strange though, is that omitting the sync between the commands changes
the result and not the warning.

So zero effect is not how omitting the sync between two commands in
extended query protocol actually works.
It has an effect on the scope of 'SET LOCAL' but it suggests otherwise
because doesn't suppress the warning.

Regards,

Brar