| Please try the following. Stop PostgreSQL with this: pg_ctl -D /usr/local/var/postgres stop Edit the file "/usr/local/var/postgres/postgresql.conf” and add these lines at the end: log_statement = 'all' log_directory = 'log' logging_collector = on Then start PostgreSQL with this: pg_ctl -D /usr/local/var/postgres start Then, in one terminal window, find the latest log file with this: ls -l /usr/local/var/postgres/log/postgresql-*.log Then, in a second terminal window, start psql and do these tests. By all means, add your own. -- Test 1. \set AUTOCOMMIT ON insert into t(n) values(42); I see _only_ the “insert” in the log—in other words, no “commit”. (And no “BEGIN” for that matter.) If it were the case the psql sends the commit after every statement when AUTOCOMMIT is ON, then I’d see this in the log. Yet the effect of the “insert” has indeed been committed. You can see this by issuing “rollback” (you get the "there is no transaction in progress” warning). And a subsequent “select” confirms that the “insert” was committed. My interpretation is that AUTOCOMMIT is a server-side phenomenon (but, as we shall see, the client does know that this mode has been set). -- Test 2. \set AUTOCOMMIT OFF insert into t(n) values(42); rollback; I see this in the log: …LOG: statement: BEGIN …LOG: statement: insert into t(n) values(42); …LOG: statement: rollback; (I also see this when I do the test using Python on psycopg2 after doing “my_session.set_session(autocommit=False)”. The “rollback” doesn’t draw a warning. And, indeed, the effect of my “insert” has been undone, as has been shown by a subsequent “select”.) -- Test 3. \set AUTOCOMMIT OFF start transaction; insert into t(n) values(42); rollback; I see this in the log: …LOG: statement: start transaction; …LOG: statement: insert into t(n) values(42); …LOG: statement: rollback; Same outcome as with Test 2. Test 2 and Test 3 are consistent with the hypothesis that it’s the client that issues the “BEGIN” before your first SQL command after setting AUTOCOMMIT to OFF. But it doesn’t _prove_ the hypothesis. However, experts on this list have said that this is how it works. And this is consistent with the fact that, when AUTOCOMMIT is ON, I don’t see this in the log: BEGIN; insert into t(n) values(42); COMMIT; If AUTOCOMMIT were entirely a client-side phenomenon, then you _would_ have to see this to get the defined semantics. In summary, my hypothesis is that AUTOCOMMIT is a server side phenomenon. But, because to set it with a call from the client, the client does know what mode you’re in and adds its own BEGIN before the first SQL command that follows setting AUTOCOMMIT to ON. Test 4 is more elaborate. Please run the attached setup_for_test_4.sql, and then do this by hand. But do read the definition of "p2()" carefully first. Then do this: -- Test 4. \set AUTOCOMMIT ON do $$ begin raise notice 'txid at top level before "p2()" ..... %', txid_current(); end $$; call p2(); do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$; select n from t order by n; This is the output: NOTICE: txid at top level before "p2()" ..... 9478 NOTICE: txid in "p2()" at start ............. 9479 NOTICE: txid in "p2()" after "set txn"....... 9480, level = repeatable read NOTICE: txid in "p2()" after "set txn"....... 9481, level = serializable NOTICE: txid at top level after "p1()" ...... 9482 n ---- 42 And this is what the log shows: …LOG: statement: do $$ begin raise notice 'txid at top level before "p2()" ..... %', txid_current(); end $$; …LOG: statement: call p2(); …LOG: statement: do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$; …LOG: statement: select n from t order by n; Notice that there’s no visible “BEGIN” between my first “do" block and the “call”. And (again) no visible “COMMIT” after the “call" finishes. But the txid values show that “call” did indeed start a new txt, the “rollback” in the proc ended it so that "set transaction isolation level” in the proc can (implicitly) start a new txn. And so on. All this is consistent with the hypothesis that there is no such thing as a “nested transaction”. Rather, there’s just a series of non-overlapping ordinary txns. The BLOG POST that I quoted with my first post on this topic explains that this is intended. This is consistent with the hypothesis that AUTOCOMMIT is indeed a server-side phenomenon and that, during the execution of a procedure, its usual effect is suspended—and is resumed when the procedure execution ends. I’ve said several times that my goal is to set the isolation level from inside a proc so that the proc can own the entire logic for concurrent txns that might violate a multi-row data-rule. And I’ve said several times that I discovered that starting my proc with “rollback” allows this—and that I've |
Attachment:
setup_for_test_4.sql
Description: Binary data
found no other way to meet my goal. I don’t understand, therefore, why some people (but not you, Daniel!) who’ve responded to my questions say that starting my proc with “rollback” is pointless. I tried, earlier, to say “case closed”. I’ll say it again now. On 08-Aug-2019, at 06:53, Daniel Verite <daniel@xxxxxxxxxxxxxxxx> wrote: Bryn Llewellyn wrote: B.t.w., I’m guessing that the “begin” SQL command that you see in the log Well, that's the point of Autocommit, and moreover it does nothing else. Maybe you're still confused about this. * Autocommit off = the client automatically adds a "BEGIN" when it thinks a transaction must be started on behalf of the user. * Autocommit on = the client does nothing. The fact that "off" implies doing something and "on" implies not interfering is counterintuitive, but that's how it is. Autocommit is for compatibility with other databases. If it was only for Postgres, I guesss either it wouldn't exist in the first place or it should be called "AutoStartTransactionBlock" or something like that, because that's just what it really does. Anyway, the server only know whether a BEGIN has been issued. It never knows or cares whether it has been added implicitly or explicitly, which is why it can be quite confusing to reason about server-side differences in terms of Autocommit, as you do in some of your previous messages. It should be stressed that Autocommit is not a parameter of the session between Postgres and the SQL client, but rather it's a parameter of the session between the user and their SQL client. So when you're hypothesizing that a plpgsql block in a procedure would look at this parameter or change it temporarily (your points #2 and #5 in your analysis of p2's execution), you should see that it's impossible, because on the server-side, this parameter just does not exist. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite |
