postgresql.conf max_prepared_transactions doesn't work

Question

I opened up my postgresql.conf file in the postgres data folder and changed the value of max_prepared_connections to a non-zero value.

However, every time I try using a "PREPARE TRANSACTION 'foo';"command, I get an error saying that max_prepared_connections is set to zero.

Am I doing anything wrong? I just want to be able to use the prepare transaction command.

Edmonton

try to recheck max_prepared_connections before using PREPARE TRANSACTION - try to execute: show max_prepared_transactions; sometimes people editing other config file that is used – Clincher

Answer 1

You must restart the PosgreSQL server after changing this parameter.

Disadvantage

Answer 2

1. Run following Query e.g. ALTER SYSTEM SET max_prepared_transactions = 100;
2. Restart Service
3. Validate by running following Query :- SHOW max_prepared_transactions;

Donets

Answer 3

go to place where postgresql is installed... in windows , go to C:\Program Files\PostgreSQL\9.5\data\postgresql.conf open and then search for "max_prepared_transactions" and uncomment it. and then set max_prepared_transactions = 1
zero disables the feature (change requires restart) Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory per transaction slot, plus lock space (see max_locks_per_transaction). It is not advisable to set max_prepared_transactions nonzero unless you actively intend to use prepared transactions.

after that type services in start menu and search for postgresql and stop the service from left top corner. then restart this service. then restart the jboss server .

Disparagement

Answer 4

I would like to add that there can be a file C:\Program Files\PostgreSQL\10\share\postgresql.conf.sample which has the same structure as Files\PostgreSQL\9.5\data\postgresql.conf. So, you should also change max_prepared_transactions in that file.

Forgiven

Can you say why this is needed? The file sounds like a installation sample which is not used at runtime. – Treaty