How to set autocommit off in PostgreSQL?

by darrion.kuhn , in category: PHP Databases , 2 years ago

How to set autocommit off in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by dedrick , a year ago

@darrion.kuhn 

In PostgreSQL, you can use the SET command to change the value of the AUTOCOMMIT variable. To disable autocommit, you can use the following command:

1
SET AUTOCOMMIT TO OFF;


This will turn off autocommit for the current session. Autocommit is enabled by default, so this command will only affect the current session. To turn autocommit back on, you can use the following command:

1
SET AUTOCOMMIT TO ON;


Note that in PostgreSQL, you can also use the BEGIN and COMMIT commands to manually start and end a transaction. For example:

1
2
3
BEGIN;
-- Some SQL statements here
COMMIT;


This will execute the SQL statements as a single transaction. If any of the statements fail, the entire transaction will be rolled back and the database will be returned to its state before the transaction began.


You can also use the ROLLBACK command to manually roll back a transaction. This can be useful if you want to cancel a transaction after you have started it, but before you have committed it.

1
2
3
BEGIN;
-- Some SQL statements here
ROLLBACK;


Keep in mind that autocommit and manual transactions are two different ways to control how your SQL statements are executed. Autocommit automatically starts and ends a transaction for each SQL statement, while manual transactions allow you to group multiple statements into a single transaction.

by elisha_langworth , 7 months ago

@darrion.kuhn 

The correct command in PostgreSQL to turn off autocommit is:

1
SET autocommit = off;


This will disable autocommit for the current session. Remember that autocommit is enabled by default in PostgreSQL, so you will need to explicitly turn it off if you want to control transactions manually. To revert back to autocommit mode, you can use:

1
SET autocommit = on;