0

I know this question has been asked before. I am new to PostgreSQL and would like to turn off "autocommit" forever. After some research, I need to add "\set AUTOCOMMIT off" in the ~/.psqlrc file. Where do I find this file ~/.psqlrc? in directory /var/lib/pgsql/13/data/ I do not find it. I created it "vi ~/.psqlrc" and inserted the command \set AUTOCOMMIT, but it did not work. What is missing here? I would be very happy about any tip.

Many greetings, Ahmed

5
  • Please edit the question to include ~/.psqlrc contents. Use cat ~/.psqlrc to print it on the console and copy-paste (do not type, it's easy to make typos) it in to the question.
    – vonPryz
    Jun 16, 2022 at 9:33
  • as i said, i did not found th file .psqlrc, so i created: [root@~]# vi ~/.psqlrc and add "\set AUTOCOMMIT off" in it. the file contains only this "\set AUTOCOMMIT off". Am i missing somthing? Jun 16, 2022 at 10:26
  • It looks correct. You wrote inserted the command \set AUTOCOMMIT, but... without the off keyword, so just made sure it's there. Anyway, what does \echo :AUTOCOMMIT print in a psql session?
    – vonPryz
    Jun 16, 2022 at 10:40
  • Do not disable autocommit in psql. At some point you will forget to close a transaction, and your database may be damaged. Jun 16, 2022 at 20:30
  • Does this answer your question? How to turn off "autocommit" in PostgreSQL
    – John K. N.
    Jun 21, 2022 at 8:01

1 Answer 1

0

Where do I find this file ~/.psqlrc?

That already is the full path to the file.
Linux shells expand the "~" character into the path to the current user's Home directory, so something like /home/username.
The leading "." in the filename makes it a hidden file, so it won't show up in directory listings (ls) unless you include the "-a" flag. This is a security measure.

However, this is only for the current user.
If another user connects through psql, and they don't have this file with this command in it, you're shiny, new setting won't apply to them. Apparently, making a single psqlrc file for everyone is quite a challenge.

2
  • Thanks for your reply, I have created a db-link from Oracle to PostgreSQL and when I insert data via db-link the data is automatically committed, so I want to turn this off. is there a global setting here? Jun 16, 2022 at 9:55
  • @AhmedAldam: psqlrc only controls the settings for the psql tool. It won't affect transactions coming from different tools. You need to check the definition of the DB Link in Oracle.
    – user1822
    Jun 16, 2022 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.