How to make autocommit off in PostgreSQL?

Member

by lottie , in category: PHP Databases , 7 months ago

How to make autocommit off in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by addison , 3 months ago

@lottie 

To turn off autocommit in PostgreSQL, you can use the SET command to change the value of the autocommit variable to OFF. Here's an example of how to do this:

1
SET autocommit TO OFF;


Alternatively, you can use the BEGIN command to start a transaction block, which will disable autocommit until you end the block with a COMMIT or ROLLBACK command. Here's an example of how to use a transaction block to disable autocommit:

1
2
3
BEGIN;
-- insert, update, or delete statements go here
COMMIT;


By default, PostgreSQL has autocommit turned on, so any individual statement is treated as a transaction and is automatically committed to the database when it is completed. Disabling autocommit allows you to group multiple statements together into a single transaction, so that you can either commit or roll back all of the statements as a unit. This can be useful if you want to ensure that multiple related statements are either all applied or all rolled back, to maintain the integrity of your data.


Keep in mind that turning off autocommit does not automatically start a transaction block - you still need to use the BEGIN command to do that. Additionally, you should be careful to use the COMMIT or ROLLBACK command to end the transaction block, to avoid leaving the database in an uncommitted state.