Aw: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> >> b...@yugabyte.com wrote: > >> > >> I’ve no idea how I might have found this without human help. > > > > x...@thebuild.com wrote: > > > > That sounds like an excellent documentation patch! > > Well, it’s already documented clearly enough. The question is how to find > it—especially if you don’t know that the feature that you’re looking > for exists or not.The psql doc would print out at about thirty pages with a > normal font size. So reading it from top to bottom would be quite a task. But, then, documentation is there to be read. And it tends to be the longer the more details it is expected to cover, isn't it ? Searching for generic terms on typical search engines can be quite a task, agreed. Karsten
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
>> b...@yugabyte.com wrote: >> >> I’ve no idea how I might have found this without human help. > > x...@thebuild.com wrote: > > That sounds like an excellent documentation patch! Well, it’s already documented clearly enough. The question is how to find it—especially if you don’t know that the feature that you’re looking for exists or not. The psql doc would print out at about thirty pages with a normal font size. So reading it from top to bottom would be quite a task.
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> On Feb 20, 2023, at 17:54, Bryn Llewellyn wrote: > > > I’ve no idea how I might have found this without human help. That sounds like an excellent documentation patch!
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
>> b...@yugabyte.com wrote: >> >> It seems a bit odd that psql has no syntax to ask for this in its >> interactive mode. > > dan...@manitou-mail.org wrote: > > Backslash-semicolon is the syntax. Thanks, Daniel. Yes, that works. And the server’s SQL statement log confirms this. I’ve no idea how I might have found this without human help. (Neither generic Google search nor using the PG docs own search got me anywhere.) But now I know that what I asked about is possible and I’ve seen it work, I trust that I’ll remember the trick.
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
Bryn Llewellyn wrote: > 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOG: 0: statement: > insert into s.t(v) values(17); insert into s.t(v) values(42); > 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOCATION: exec_simple_query, > postgres.c:971 > > It seems a bit odd that psql has no syntax to ask for this in its > interactive mode. Backslash-semicolon is the syntax. Quoted from the doc: \; Backslash-semicolon is not a meta-command in the same way as the preceding commands; rather, it simply causes a semicolon to be added to the query buffer without any further processing. Normally, psql will dispatch an SQL command to the server as soon as it reaches the command-ending semicolon, even if more input remains on the current line. Thus for example entering select 1; select 2; select 3; will result in the three SQL commands being individually sent to the server, with each one's results being displayed before continuing to the next command. However, a semicolon entered as \; will not trigger command processing, so that the command before it and the one after are effectively combined and sent to the server in one request. So for example select 1\; select 2\; select 3; results in sending the three SQL commands to the server in a single request, when the non-backslashed semicolon is reached. The server executes such a request as a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 55.2.2.1 for more details about how the server handles multi-query strings.) Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> 2. If I send over "begin" and then "insert into s.t(v) values(42)", then (so >> far) a second session will not see the effect of my SQL's. It sees this only >> when I send over "commit". (If I send over "rollback" instead of "commit", >> then other sessions never know what I did.) > > This may or may not be true. If the second session currently has a > transaction open in REPEATABLE READ or SERIALIZABLE mode, it *won't* see the > effects of that statement, since it took its snapshot at the start of the > transaction (to be technical, at the first statement in that transaction), > and holds it until commit time. However, a transaction in READ COMMITTED mode > *will* see the results after the statement completes. > >> I can't see that a client-side "autocommit off" mode like psql supports >> brings me anything of value. > > There's general agreement on that point. > > https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/ Thanks, Christophe. Yes, I sacrificed correctness for brevity. I should have stipulated that observations made from a second concurrent session are to be done using a singleton "select" in its own txn—i.e. outside of an explicitly started txn (whether this is started by hand or using a client's implementation of "autocommit off"). Thanks, too, for the xref to the Cybertec post by Laurenz Albe. And thanks, David, for your separate tip about using « psql -c ». I tried it and watched the server log. Sure enough, I saw this: 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOG: 0: statement: insert into s.t(v) values(17); insert into s.t(v) values(42); 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOCATION: exec_simple_query, postgres.c:971 It seems a bit odd that psql has no syntax to ask for this in its interactive mode. But, yes, it doesn't actually matter because I can get the same semantics by starting a txn myself.
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
On Mon, Feb 20, 2023 at 12:57 PM Bryn Llewellyn wrote: > 3. Chapter 55 also has a section "Multiple Statements In A Simple Query". > But this feature seems to do no more semantically beyond implicitly > achieving what I could do by surrounding several statements explicitly with > "begin; ... commit;". There is, though, the non-semantic aspect of > round-trip reduction. It seems that psql doesn't expose doing many > statements in a simple query. (At least, that's what I see in the server > log when I put several statements on a single line (with psql's autocommit > on). > IIRC psql -c 'insert into tbl values (1); select * from tbl;' uses the simply query protocol with multiple statements. Then, 55.2.2.1: > When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transaction So in effect bundling within the Simple Query Protocol has a similar effect to wrapping all statements in a begin/commit pair. David J.
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> On Feb 20, 2023, at 11:57, Bryn Llewellyn wrote: > 2. If I send over "begin" and then "insert into s.t(v) values(42)", then (so > far) a second session will not see the effect of my SQL's. It sees this only > when I send over "commit". (If I send over "rollback" instead of "commit", > then other sessions never know what I did.) This may or may not be true. If the second session currently has a transaction open in REPEATABLE READ or SERIALIZABLE mode, it *won't* see the effects of that statement, since it took its snapshot at the start of the transaction (to be technical, at the first statement in that transaction), and holds it until commit time. However, a transaction in READ COMMITTED mode *will* see the results after the statement completes. > I can't see that a client-side "autocommit off" mode like psql supports > brings me anything of value. There's general agreement on that point. https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/
