postgresql: из общей памяти?
Я запускаю кучу запросов, используя Python и psycopg2. Я создаю одну большую временную таблицу с примерно 2 миллионами строк, а затем получаю из нее 1000 строк одновременно, используя cur.fetchmany(1000) и выполнять более обширные запросы, включающие эти строки. Обширные запросы самодостаточны, но, как только они будут выполнены, мне больше не нужны их результаты, когда я перейду к следующей 1000.
Тем не менее, около 1000000 строк, я получил исключение из psycopg2:
psycopg2.OperationalError: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
Как ни странно, это произошло, когда я выполнял запрос, чтобы удалить некоторые временные таблицы, созданные более обширными запросами.
Почему это может случиться? Есть ли способ избежать этого? Было досадно, что это произошло на полпути, а это значит, что мне нужно все повторить. Что может max_locks_per_transaction связано с чем-нибудь?
ПРИМЕЧАНИЕ: я не делаю никаких .commit()s, но я удаляю все временные таблицы, которые я создаю, и я в любом случае касаюсь одних и тех же 5 таблиц для каждой "расширенной" транзакции, поэтому я не понимаю, как может быть проблема исчерпания блокировок таблицы..,
3 ответа
Когда вы создаете таблицу, вы получаете на нее эксклюзивную блокировку, которая действует до конца транзакции. Даже если вы потом бросите это.
Так что, если я запустил tx и создал временную таблицу:
steve@steve@[local] *=# create temp table foo(foo_id int);
CREATE TABLE
steve@steve@[local] *=# select * from pg_locks where pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+-----------+------+-------+------------+---------------+---------+-----------+----------+--------------------+-------+---------------------+---------
virtualxid | | | | | 2/105315 | | | | | 2/105315 | 19098 | ExclusiveLock | t
transactionid | | | | | | 291788 | | | | 2/105315 | 19098 | ExclusiveLock | t
relation | 17631 | 10985 | | | | | | | | 2/105315 | 19098 | AccessShareLock | t
relation | 17631 | 214780901 | | | | | | | | 2/105315 | 19098 | AccessExclusiveLock | t
object | 17631 | | | | | | 2615 | 124616403 | 0 | 2/105315 | 19098 | AccessExclusiveLock | t
object | 0 | | | | | | 1260 | 16384 | 0 | 2/105315 | 19098 | AccessShareLock | t
(6 rows)
Эти "отношения" блокировки не удаляются, когда я отбрасываю таблицу:
steve@steve@[local] *=# drop table foo;
DROP TABLE
steve@steve@[local] *=# select * from pg_locks where pid = pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+-----------+------+-------+------------+---------------+---------+-----------+----------+--------------------+-------+---------------------+---------
virtualxid | | | | | 2/105315 | | | | | 2/105315 | 19098 | ExclusiveLock | t
object | 17631 | | | | | | 1247 | 214780902 | 0 | 2/105315 | 19098 | AccessExclusiveLock | t
transactionid | | | | | | 291788 | | | | 2/105315 | 19098 | ExclusiveLock | t
relation | 17631 | 10985 | | | | | | | | 2/105315 | 19098 | AccessShareLock | t
relation | 17631 | 214780901 | | | | | | | | 2/105315 | 19098 | AccessExclusiveLock | t
object | 17631 | | | | | | 2615 | 124616403 | 0 | 2/105315 | 19098 | AccessExclusiveLock | t
object | 17631 | | | | | | 1247 | 214780903 | 0 | 2/105315 | 19098 | AccessExclusiveLock | t
object | 0 | | | | | | 1260 | 16384 | 0 | 2/105315 | 19098 | AccessShareLock | t
(8 rows)
Фактически, он добавил еще две блокировки... Кажется, если я постоянно создаю / удаляю эту временную таблицу, он добавляет 3 блокировки каждый раз.
Поэтому я думаю, что один из ответов заключается в том, что вам понадобится достаточно блокировок, чтобы справиться со всеми этими таблицами, добавляемыми / удаляемыми на протяжении транзакции. В качестве альтернативы, вы можете попытаться повторно использовать временные таблицы между запросами, просто обрезать их, чтобы удалить все временные данные?
Вы создали несколько точек сохранения с одним и тем же именем, не отпуская их?
Я следовал этим инструкциям, многократно выполняя SAVEPOINT savepoint_name но без выполнения каких-либо соответствующих RELEASE SAVEPOINT savepoint_name заявления. PostgreSQL просто маскировал старые точки сохранения, никогда не освобождая их. Он отслеживал каждый из них, пока не исчерпал память для блокировок. Я думаю, что мои ограничения памяти postgresql были намного ниже, мне потребовалось всего ~10000 точек сохранения, чтобы попасть в max_locks_per_transaction.
Хорошо, вы выполняете все запросы create + внутри одной транзакции? Возможно, это объяснит проблему. То, что это произошло, когда вы отбрасывали столы, не обязательно будет что-то значить, может случиться так, что в нем закончатся свободные блокировки.
Использование представления может быть альтернативой временной таблице, и я определенно выберу первый вариант, если вы создаете эту вещь, а затем сразу ее удаляете.