Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory - Mailing list pgsql-general
| From | Dominique Devienne |
|---|---|
| Subject | Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory |
| Date | |
| Msg-id | CAFCRh--no2WL5OhSndVo=dkCfA7OAoEDd7wJPqj+QAxp7TqnaA@mail.gmail.com Whole thread Raw |
| In response to | DROP OWNED BY fails with #53200: ERROR: out of shared memory (Dominique Devienne <ddevienne@gmail.com>) |
| Responses |
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory (Alvaro Herrera <alvherre@alvh.no-ip.org>) Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-general |
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)
```
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 <ddevienne@gmail.com> wrote:
On Mon, Jan 10, 2022 at 6:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Dominique Devienne <ddevienne@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 thefact I'd need to use several transactions to work-around max_locks_per_transaction is a bummer... --DD
pgsql-general by date: