> 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: 00000: 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.
