Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore.

In fact, I would like to force the isolation level of my database to SERIALIZABLE DEFERRABLE. Is this possible at all? If yes, how?

share|improve this question

closed as off-topic by Phil, billinkc, RolandoMySQLDBA, Max Vernon, Kin Apr 10 '14 at 16:12

If this question can be reworded to fit the rules in the help center, please edit the question.

I have never heard of such isolation level. Read about Postgres isolation levels. The strictest (highest) level is the Serializable. – ypercube Mar 28 '14 at 22:28
You can only do it at session level because DB level makes no sense. – Phil Mar 28 '14 at 23:19
This question appears to be off-topic because it is not possible – Phil Mar 28 '14 at 23:22
@GrumpyPhil That's not accurate, you can. You probably shouldn't, though. – Craig Ringer Mar 29 '14 at 2:18
@Gracchus Some apps may assume the default of READ COMMITTED, so they might not cope with SERIALIZABLE tx aborts. Bad coding, but not uncommon. – Craig Ringer Jul 4 '14 at 2:25

1 Answer 1

up vote 3 down vote accepted

You can set SERIALIZABLE as the default isolation level in postgresql.conf:

default_transaction_isolation = 'serializable'

This may confuse clients that assume READ COMMITTED isolation, so IMO it's better to set it explicitly in your sessions.

As for the rest, per the manual:

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY

... and given the properties of such transactions, I find it hard to imagine you wanting to make it the default. Are you perhaps confusing it with SET CONSTRAINTS DEFERRED?

You can make transactions read-only and deferrable by default with:

transaction_read_only = on
transaction_deferrable = on

in postgresql.conf but it's really a rather bad idea. Instead, just use an appropriate BEGIN, or SET TRANSACTION in your session before your first "real" SQL statement.

share|improve this answer
Ok, thanks for your feedback. I was interested in DEFERRABLE to avoid serialization failures, but may be I am overshooting here. – JVerstry Mar 29 '14 at 6:44

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