public inbox for [email protected]
help / color / mirror / Atom feedset 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]>
2022-04-11 16:19 ` Re: set autocommit only for select statements David G. Johnston <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
2022-04-12 12:49 ` Re: set autocommit only for select statements Simon Riggs <[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:14 set autocommit only for select statements Sbob <[email protected]>
@ 2022-04-11 16:19 ` David G. Johnston <[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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
@ 2022-04-12 01:53 ` Mladen Gogala <[email protected]>
2022-04-12 06:31 ` Re: set autocommit only for select statements Holger Jakobs <[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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
@ 2022-04-12 06:31 ` Holger Jakobs <[email protected]>
2022-04-12 09:01 ` RE: set autocommit only for select statements Michel SALAIS <[email protected]>
2022-04-12 12:00 ` Re: set autocommit only for select statements 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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
2022-04-12 06:31 ` Re: set autocommit only for select statements Holger Jakobs <[email protected]>
@ 2022-04-12 09:01 ` Michel SALAIS <[email protected]>
2022-04-12 11:55 ` Re: set autocommit only for select statements Mladen Gogala <[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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
2022-04-12 06:31 ` Re: set autocommit only for select statements Holger Jakobs <[email protected]>
2022-04-12 09:01 ` RE: set autocommit only for select statements Michel SALAIS <[email protected]>
@ 2022-04-12 11:55 ` Mladen Gogala <[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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
2022-04-12 06:31 ` Re: set autocommit only for select statements Holger Jakobs <[email protected]>
@ 2022-04-12 12:00 ` Mladen Gogala <[email protected]>
2022-04-12 14:20 ` Re: set autocommit only for select statements Julien Rouhaud <[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-11 16:14 set autocommit only for select statements Sbob <[email protected]>
2022-04-12 01:53 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
2022-04-12 06:31 ` Re: set autocommit only for select statements Holger Jakobs <[email protected]>
2022-04-12 12:00 ` Re: set autocommit only for select statements Mladen Gogala <[email protected]>
@ 2022-04-12 14:20 ` Julien Rouhaud <[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
* Re: set autocommit only for select statements
2022-04-11 16:14 set autocommit only for select statements Sbob <[email protected]>
@ 2022-04-12 12:49 ` Simon Riggs <[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
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