public inbox for [email protected]help / color / mirror / Atom feed
set autocommit only for select statements 9+ messages / 7 participants [nested] [flat]
* set autocommit only for select statements @ 2022-04-11 16:14 Sbob <[email protected]> 0 siblings, 3 replies; 9+ messages in thread From: Sbob @ 2022-04-11 16:14 UTC (permalink / raw) To: [email protected] Hi; Is there a way to set "autocommit = on" for all select statements and have "autocommit = off" for all other statements? Thanks in advance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-11 16:19 David G. Johnston <[email protected]> parent: Sbob <[email protected]> 2 siblings, 0 replies; 9+ messages in thread From: David G. Johnston @ 2022-04-11 16:19 UTC (permalink / raw) To: Sbob <[email protected]>; +Cc: Pgsql-admin <[email protected]> On Mon, Apr 11, 2022 at 9:15 AM Sbob <[email protected]> wrote: > Is there a way to set "autocommit = on" for all select statements and > have "autocommit = off" for all other statements? > > In what? The server doesn't have an autocommit option. But I doubt it; regardless of the answer to the previous question. David J. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 01:53 Mladen Gogala <[email protected]> parent: Sbob <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: Mladen Gogala @ 2022-04-12 01:53 UTC (permalink / raw) To: [email protected] On 4/11/22 12:14, Sbob wrote: > Hi; > > > Is there a way to set "autocommit = on" for all select statements and > have "autocommit = off" for all other statements? > > > Thanks in advance > > > > The "autocommit" is a tool option which tells the tool whether to add "COMMIT" statement after each and every SQL. The RDBMS server only knows about transactions, as mandated by the ACID compliance. What the "autocommit" option of tools like psql actually does is to turn each of your SQL statements into a separate transaction. That can have some drawbacks, but it also has some positive sides. Your lock duration is much shorter and you don't get lock waits. However, some things may surprise you: [mgogala@umajor ~]$ psql Password for user mgogala: psql (13.6, server 14.2) WARNING: psql major version 13, server major version 14. Some psql features might not work. Type "help" for help. mgogala=# select ename,sal from emp where deptno=20 for update; ename | sal -------+------ SMITH | 800 JONES | 2975 SCOTT | 3000 ADAMS | 1100 FORD | 3000 (5 rows) mgogala=# select l.locktype,d.datname,r.relname from pg_locks l join mgogala-# pg_database d on (l.database=d.oid) join pg_class r on (l.relation=r.oid); locktype | datname | relname ----------+---------+----------------------------------- relation | mgogala | pg_class_tblspc_relfilenode_index relation | mgogala | pg_class_relname_nsp_index relation | mgogala | pg_class_oid_index relation | mgogala | pg_class relation | mgogala | pg_locks (5 rows) mgogala=# As you can see, I did "SELECT FOR UPDATE" from the table named "emp". When I check the locks from pg_locks, there are no locks on the "emp" table. That is because psql (and not the database) has executed "COMMIT" immediately after "SELECT FOR UPDATE", thereby ending the transaction and releasing the locks. However, if I open another session and do the following: mgogala=# begin transaction; BEGIN mgogala=*# select ename,sal from emp where deptno=20 for update; ename | sal -------+------ SMITH | 800 JONES | 2975 SCOTT | 3000 ADAMS | 1100 FORD | 3000 (5 rows) The result of query to pg_locks is now very different: mgogala=# select l.locktype,l.mode,d.datname,r.relname from pg_locks l join pg_database d on (l.database=d.oid) join pg_class r on (l.relation=r.oid); locktype | mode | datname | relname ----------+-----------------+---------+----------------------------------- _*relation | RowShareLock | mgogala | emp_pkey*__* *__* relation | RowShareLock | mgogala | emp*_ relation | AccessShareLock | mgogala | pg_class_tblspc_relfilenode_index relation | AccessShareLock | mgogala | pg_class_relname_nsp_index relation | AccessShareLock | mgogala | pg_class_oid_index relation | AccessShareLock | mgogala | pg_class relation | AccessShareLock | mgogala | pg_locks (7 rows) Now, there are two locks in RowShare mode on the emp table and its primary key. That is because the transaction on the "emp" table has not finished and locks are still intact. BTW, you don't have to turn off the autocommit mode to use "BEGIN TRANSACTION". The morals of the story is that the "autocommit option" is something that regulates the behavior of the tool, not the database. The point of "SELECT FOR UPDATE" is to lock certain rows in the database to modify them later. That will not work without "BEGIN TRANSACTION". In the autocommit mode, each SQL is a separate transaction, delineated by the transaction terminating statements by the tool executing the SQL. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 06:31 Holger Jakobs <[email protected]> parent: Mladen Gogala <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Holger Jakobs @ 2022-04-12 06:31 UTC (permalink / raw) To: [email protected] Am 12.04.22 um 03:53 schrieb Mladen Gogala: > > The "autocommit" is a tool option which tells the tool whether to add > "COMMIT" statement after each and every SQL. The RDBMS server only > knows about transactions, as mandated by the ACID compliance. What the > "autocommit" option of tools like psql actually does is to turn each > of your SQL statements into a separate transaction. > What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements. This can be proved easily by not using psql as a client, but some programming language. Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 Attachments: [application/pgp-signature] OpenPGP_signature (203B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: set autocommit only for select statements @ 2022-04-12 09:01 Michel SALAIS <[email protected]> parent: Holger Jakobs <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Michel SALAIS @ 2022-04-12 09:01 UTC (permalink / raw) To: 'Holger Jakobs' <[email protected]>; [email protected] -----Message d'origine----- De : Holger Jakobs <[email protected]> Envoyé : mardi 12 avril 2022 08:31 À : [email protected] Objet : Re: set autocommit only for select statements Am 12.04.22 um 03:53 schrieb Mladen Gogala: > > The "autocommit" is a tool option which tells the tool whether to add > "COMMIT" statement after each and every SQL. The RDBMS server only > knows about transactions, as mandated by the ACID compliance. What the > "autocommit" option of tools like psql actually does is to turn each > of your SQL statements into a separate transaction. > What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements. This can be proved easily by not using psql as a client, but some programming language. Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 ----------------------------------------------------------------------------------- Hi, It is a little more "complex" :-) A session can be put in AUTOCOMMIT mode or transaction mode. Tool "psql" is by default in AUTOCOMMIT mode and can be put in transaction mode using \set AUTOCOMMIT off Pay attention to the variable case. It should be in capital letters. When this is done, You don't need to start transactions explicitly and the server behaves like Oracle but it is not an identical behavior. Other points matter like the reaction to errors when in a transaction... Other programming environments could choose another default operating mode and however can offer to choose the operating mode at connection. Michel SALAIS ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 11:55 Mladen Gogala <[email protected]> parent: Michel SALAIS <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Mladen Gogala @ 2022-04-12 11:55 UTC (permalink / raw) To: [email protected] On 4/12/22 05:01, Michel SALAIS wrote: > What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. > > PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements. > > This can be proved easily by not using psql as a client, but some programming language. > > Regards, > > Holger RDBMS, in order to be ACID compliant, deals with transactions, not with single statements. It is the client who starts transaction, not the database. It is also the client who sends commit. And yes, I am also using Python which allows me to set autocommit property: ttps://www.psycopg.org/docs/connection.html#connection.autocommit If I set autocommit connection property to True, each cursor.execute will send a commit. Autocommit cannot be set or disabled on the database level because the database deals with transactions. The point where the transaction is started and finished is the client. That is so for MySQL, that is so for Oracle and that is so for Postgres.BTW, speaking of Python, has anybody here tried Psycopg3? I know it was released but I am not sure whether it's stable enough to use in production? -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 12:00 Mladen Gogala <[email protected]> parent: Holger Jakobs <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Mladen Gogala @ 2022-04-12 12:00 UTC (permalink / raw) To: [email protected] On 4/12/22 02:31, Holger Jakobs wrote: > What you write about psql sending a COMMIT statement after each > statement is wrong. It may be true for other database systems. > > PostgreSQL as a server commits each statement automatically unless the > client has started a transaction with BEGIN or START TRANSACTION > statements. > > This can be proved easily by not using psql as a client, but some > programming language. > > Regards, If what you are saying was true, then autocommit would be a database mode, not a tool mode. It isn't a database mode. Q.E.D. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 12:49 Simon Riggs <[email protected]> parent: Sbob <[email protected]> 2 siblings, 0 replies; 9+ messages in thread From: Simon Riggs @ 2022-04-12 12:49 UTC (permalink / raw) To: Sbob <[email protected]>; +Cc: Pgsql-admin <[email protected]> On Mon, 11 Apr 2022 at 17:15, Sbob <[email protected]> wrote: > Is there a way to set "autocommit = on" for all select statements and > have "autocommit = off" for all other statements? Please explain what it is you are hoping to achieve? -- Simon Riggs http://www.EnterpriseDB.com/ ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set autocommit only for select statements @ 2022-04-12 14:20 Julien Rouhaud <[email protected]> parent: Mladen Gogala <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Julien Rouhaud @ 2022-04-12 14:20 UTC (permalink / raw) To: Mladen Gogala <[email protected]>; +Cc: [email protected] On Tue, Apr 12, 2022 at 08:00:35AM -0400, Mladen Gogala wrote: > On 4/12/22 02:31, Holger Jakobs wrote: > > What you write about psql sending a COMMIT statement after each > > statement is wrong. It may be true for other database systems. > > > > PostgreSQL as a server commits each statement automatically unless the > > client has started a transaction with BEGIN or START TRANSACTION > > statements. > > > > This can be proved easily by not using psql as a client, but some > > programming language. > > > > Regards, > > If what you are saying was true, then autocommit would be a database mode, > not a tool mode. It isn't a database mode. Q.E.D. That's actually mostly true. Postgres will start an implicit transaction for every query (or multiple queries) outside a transaction and will implicitly commit it after a successful execution. The only difference is there you won't see a BEGIN / COMMIT anywhere, but the same underlying code will be executed. ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-04-12 14:20 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-04-11 16:14 set autocommit only for select statements Sbob <[email protected]> 2022-04-11 16:19 ` David G. Johnston <[email protected]> 2022-04-12 01:53 ` Mladen Gogala <[email protected]> 2022-04-12 06:31 ` Holger Jakobs <[email protected]> 2022-04-12 09:01 ` Michel SALAIS <[email protected]> 2022-04-12 11:55 ` Mladen Gogala <[email protected]> 2022-04-12 12:00 ` Mladen Gogala <[email protected]> 2022-04-12 14:20 ` Julien Rouhaud <[email protected]> 2022-04-12 12:49 ` Simon Riggs <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox