pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bryn Llewellyn <bryn@yugabyte.com>
To: Christophe Pettus <xof@thebuild.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: rjuju123@gmail.com, pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
Date: Mon, 20 Feb 2023 12:50:28 -0800
Message-ID: <3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com> (raw)
In-Reply-To: <931EFF56-1B39-4EA9-B9C1-3D3E2315401B@thebuild.com>
References: <954ABE4C-C17B-4269-ACAD-DE47E696515E@yugabyte.com>
	<CAKFQuwZ7jQpT09-6_vnhcinUSmBmo9YcoAuOO3ui7tgTjK4kFQ@mail.gmail.com>
	<D2CA5CA6-D809-4FD2-A127-C2B3952DF9F5@yugabyte.com>
	<931EFF56-1B39-4EA9-B9C1-3D3E2315401B@thebuild.com>

> xof@thebuild.com wrote:
> 
>> bryn@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.




view thread (8+ messages)  latest in thread

Message-ID: <3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com>
Permalink:  ../3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com/
Also on:    postgresql.org/message-id/3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: bryn@yugabyte.com, xof@thebuild.com, david.g.johnston@gmail.com
  Subject: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
  In-Reply-To: <3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com>
  References: <954ABE4C-C17B-4269-ACAD-DE47E696515E@yugabyte.com> <CAKFQuwZ7jQpT09-6_vnhcinUSmBmo9YcoAuOO3ui7tgTjK4kFQ@mail.gmail.com> <D2CA5CA6-D809-4FD2-A127-C2B3952DF9F5@yugabyte.com> <931EFF56-1B39-4EA9-B9C1-3D3E2315401B@thebuild.com> <3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox