Postgresql: from shared memory?

Question

I am running a bunch of queries using Python and psycopg2. I create one large temporary table with approximately 2 million rows, then I get 1000 rows at a time with help cur.fetchmany(1000)and run more extensive queries involving these rows. Extensive queries are self-contained, although when they are finished, I no longer need their results when I move on to the next 1000.

However, with about 1,000,000 lines, I got an exception from psycopg2:

psycopg2.OperationalError: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

Oddly enough, this happened when I ran a query to delete some temporary tables that created more extensive queries.

Why could this happen? Is there any way to avoid this? It was annoying that this happened halfway, which means that I have to start all this again. What can max_locks_per_transactionbe related to everything?

NOTE. I do not make any .commit()s, but I delete all the temporary tables that I create, and I still touch the same 5 tables for each "extensive" transaction. See how the problem of starting table locks can occur ...

Answer 1

when you create a table, you get an exclusive lock that lasts until the end of the transaction. Even if you then go to him and let him go.

, 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)

, ... , / , .

, , , , , / . , , ?

Answer 2

, ?

, SAVEPOINT savepoint_name, - RELEASE SAVEPOINT savepoint_name. PostgreSQL , . , . , postgresql , ~ 10 000 , max_locks_per_transaction.

Answer 3

, create + ? , . , , , -, , .

Using a view can be an alternative to a temporary table and will certainly be my first choice if you create this thing and then delete it right away.