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

Ahmed Aldam
  • 3
  • 2
  • 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 '22 at 09: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? – Ahmed Aldam Jun 16 '22 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 '22 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. – Laurenz Albe Jun 16 '22 at 20:30
  • Does this answer your question? How to turn off "autocommit" in PostgreSQL – John K. N. Jun 21 '22 at 08:01

1 Answers1

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.

Phill W.
  • 8,706
  • 1
  • 11
  • 21
  • 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? – Ahmed Aldam Jun 16 '22 at 09: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. –  Jun 16 '22 at 11:01