Out of shared memory when deleting rows with lots of incoming foreign keys-CodePudding

I develop a multi-tenancy application where we have a single master schema to keep track of tenants, along with 99 app databases to distribute load. Each of 33 tables within each app database also has a tenant column pointing to the master schema. This means there are 3,267 foreign keys pointing to the master schema's tenant id, and roughly 6000 triggers associated with the tenant table.

Recently, I added a table and started getting this error in the teardown portion of our test suite where we delete the test tenant:

psycopg2.errors.OutOfMemory: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "test2"."item" x WHERE $1 OPERATOR(pg_catalog.=) "tenant" FOR KEY SHARE OF x"
 For query
SET CONSTRAINTS ALL IMMEDIATE

Raising max_locks_per_transaction as suggested solves the problem, as does deleting some of the app schemas. The obvious solution here would be to reduce the number of redundant schemas or delete the foreign key constraints so we don't have to hold so many locks, but I'm curious if there's something else going on here.

I had imagined that only the rows to be deleted (associated with the test schema) would be locked, and so only the test schema would be locked. And anyway, by this point there is no data left pointing to the tenant table, so the locking is pretty much redundant in practice.

Update:

For more context, I'm not doing anything really fancy here. Below is a simplified example of what my schema and query look like:

CREATE SCHEMA master;
CREATE table master.tenant (id uuid NOT NULL PRIMARY KEY);

CREATE SCHEMA app_00;

CREATE table app_00.account (id uuid NOT NULL PRIMARY KEY, tenant uuid NOT NULL);
ALTER TABLE app_00.account ADD CONSTRAINT fk_tenant FOREIGN KEY (store) REFERENCES master.store(id) DEFERRABLE;

CREATE table app_00.item (id uuid NOT NULL PRIMARY KEY, tenant uuid NOT NULL);
ALTER TABLE app_00.item ADD CONSTRAINT fk_tenant FOREIGN KEY (store) REFERENCES master.store(id) DEFERRABLE;

In reality I'm creating 33 tables for each schema of app_00..99. Now assume my database is populated with data, the query that is failing with the above error is:

DELETE FROM master.tenant WHERE id = 'some uuid';

CodePudding user response：

You don't tell us much about the setup, but probably partitioning or inheritance are involved. These features often require that a statement recurse to table partitions or inheritance children, either during query planning or execution. At any rate, your SQL statements have to touch many tables.

Now whenever PostgreSQL touches a table, it places a lock on it to avoid conflicting concurrent executions. If lots of tables are involved, it can be that the lock table, that originally has max_connections * max_locks_per_transaction entries, is exhausted.

The solution simply is to increase max_locks_per_transaction. Don't worry, there is no negative consequence in raising that parameter, only a little bit more shared memory is allocated during server startup.