Re: 9.1 causing "out of shared memory" error and higher serialization conflicts

Hey Marti,

I almost replied that yes, I was 100% sure, since I know my code requests the REPEATABLE READ level. However, I figured before I replied, I should double-check the SQL statements that were being sent to Postgres.

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation == IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

*headslap*. I know this code is fine for 8, but I still would not have expected this code to exist in the driver itself instead of just letting Postgres do the switch. I guess Npgsql says right on their front page "Works with Postgresql 7.x and 8.x" so I shouldn't have assumed it'd behave correctly with 9.

So you're right, it turns out I was using SERIALIZABLE after all. I'm going to fix this right away. Thanks for the reply!

-----Original Message-----
From: Marti Raudsepp [mailto:marti(at)juffo(dot)org]
Sent: Friday, March 09, 2012 9:41 AM
To: Randy Ficker
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher serialization conflicts

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker(at)gmail(dot)com> wrote:
> Most writing transactions are using the REPEATABLE READ isolation
> level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was implementation for proper SERIALIZABLE isolation, which could indeed cause the sort of errors you described. Previously, asking for SERIALIZABLE level gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti