psycopg2 out of shared memory and hints of increase max_pred_locks_per_transaction

Question

While inserting a lot of data into postgresql 9.1. using a Python script, we are getting the following error on this query:

X: psycopg2.ProgrammingError in /home/hosting/apps/X
X_psycopg.py:162 in : Execute 'execute' (
                        SELECT * FROM xml_fifo.fifo
                        WHERE type_id IN (1,2)
                        ORDER BY type_id, timestamp LIMIT 10
                        ): out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction

We increased this number but still we get a out of shared memory (max_pred_locks_per_transaction = 192). Everytime we start the script again it runs for some time then gives this error message. On Postgres 8.1 we did not have this problem.

Here is a piece of the postgresql log file:

2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:55:43 CEST WARNING:  nonstandard use of \\ in a string literal at character 271
2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:55:43 CEST WARNING:  nonstandard use of \\ in a string literal at character 271
2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:56:11 CEST WARNING:  there is already a transaction in progress
2012-06-28 02:57:01 CEST WARNING:  there is already a transaction in progress
2012-06-28 02:57:01 CEST ERROR:  out of shared memory
2012-06-28 02:57:01 CEST HINT:  You might need to increase max_pred_locks_per_transaction.
2012-06-28 02:57:01 CEST STATEMENT:
                                SELECT * FROM xml_fifo.fifo
                                WHERE type_id IN (1,2)
                                ORDER BY type_id ASC, timestamp LIMIT 10

2012-06-28 02:57:01 CEST ERROR:  out of shared memory
2012-06-28 02:57:01 CEST HINT:  You might need to increase max_pred_locks_per_transaction.
2012-06-28 02:57:01 CEST STATEMENT:
                                SELECT * FROM xml_fifo.fifo
                                WHERE type_id IN (1,2)
                                ORDER BY type_id ASC, timestamp LIMIT 10

What would be the problem?

Answer 1

PostgreSQL added new functionality to SERIALIZABLE transactions in version 9.1, to avoid some serialization anomalies which were previously possible at that isolation level. The error you are seeing is only possible when using these new serializable transactions. Some workloads have run into the issue you describe when using serializable transactions in 9.1.

One solution would be to use the REPEATABLE READ transaction isolation level instead of SERIALIZABLE. This will give you exactly the same behavior that SERIALIZABLE transactions did in PostgreSQL versions before 9.1. Before deciding to do that, you might want to read up on the differences, so that you know whether it is likely to be worthwhile to instead reconfigure your environment to avoid the issue at the SERIALIZABLE isolation level:

http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html

http://wiki.postgresql.org/wiki/SSI

If increasing max_pred_locks_per_transaction doesn't fix it (and you could try going significantly higher without chewing up too much RAM), you could try increasing max_connections (without increasing actual connections used).

I worked on the Serializable Snapshot Isolation feature for 9.1, along with Dan R.K. Ports of MIT. The cause of this problem is that the heuristic for combining multiple fine-grained predicate locks into a single coarser-grained lock is really simple in this initial version. I'm sure it can be improved, but any information you could give me on the circumstances under which it is hitting this problem would be valuable in terms of designing a better heuristic. If you could tell me a little bit about the number of CPUs you are using, the number of active database connections, and a bit about the workload where you hit this, I would really appreciate it.

Thanks for any info, and my apologies for the problem.