I accidentally replied to Tom only. Here's my response again. Apparently, I'm using the default max_locks_per_transaction: ``` ddevienne=> show max_locks_per_transaction ; max_locks_per_transaction --------------------------- 64 (1 row) ```
Given max_locks_per_transaction * (max_connections <https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS> + max_prepared_transactions <https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS>) from https://www.postgresql.org/docs/current/runtime-config-locks.html, and max_conn being 100, that's not many locks. Tom wrote "relation" for the number of locks necessary for DROP OWNED BY. What does it mean in this context? relation = table? Given there's only 64 locks per conn by default, how can this work with over 100 tables? I'm confused... --DD On Mon, Jan 10, 2022 at 7:06 PM Dominique Devienne <ddevie...@gmail.com> wrote: > On Mon, Jan 10, 2022 at 6:39 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Dominique Devienne <ddevie...@gmail.com> writes: >> > I'm trying to DROP a ROLE that has 4 schemas: >> > * 2 smallish ones (1 or 2 dozen tables each), >> > * 2 largish ones (250 tables, totalling around 4000 columns each). >> >> > And of course there are various indexes, constraints, etc... on each >> schema. >> >> You're going to need a lock per dropped relation. The number of >> columns or rows doesn't enter into it, but the number of indexes does. >> > > Here are the current stats of the larguish schema: > > const size_t expected_table_count = 244; > const size_t expected_index_count = 409; > const size_t expected_unique_index_count = 181; > const size_t expected_cnstr_count = 989; > const size_t expected_pk_cnstr_count = 243; > const size_t expected_fk_cnstr_count = 506; > const size_t expected_check_cnstr_count = 64; > const size_t expected_unique_cnstr_count = 176; > > > DDL Error: DROP OWNED BY "Epos-PNS (a73e1fbdcc5b4460ba55703a0d6b6ad7)" >> > CASCADE: #53200: ERROR: out of shared memory >> > HINT: You might need to increase max_locks_per_transaction. >> >> I'd not have expected that when dropping 500-or-so tables, but maybe >> you have a lot of indexes per table? >> > > See above for the total. > Since we use ON DELETE CASCADE and FKs, and coming from Oracle, I do index > all my FKs... > > >> > And please note that there could be dozens even hundreds of largish >> schemas >> > associated to the dropped ROLE (2 + N), not just the 2+2 it fails with >> here. >> >> There's not a lot of penalty to increasing max_locks_per_transaction, >> but no you can't make it "unbounded". >> > > Is the HINT valid? How do I determine the current value, and change it? > Could it be some other problem? > > Dropping each largish schema individually is certainly possible, but again > coming from Oracle, > I'm used to making transaction as big as they logically need to be, with > little physical limitations. > The action is drop all schemas of that instance of the "system", which has > 2+N schemas. So the > fact I'd need to use several transactions to work-around > max_locks_per_transaction is a bummer... --DD >
