From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | DROP OWNED BY fails with #53200: ERROR: out of shared memory |
Date: | 2022-01-10 17:26:52 |
Message-ID: | CAFCRh-9jU41R3WV0BOAf55KFuN4_ePzQ_TBjeDMq82e0yic7DA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
This fails with:
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.
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.
So how can I determine a max_locks_per_transaction, when the number of
tables is basically unbounded?
From a PostgreSQL newbie perspective, this feels like a serious limitation
of DROP OWNED BY,
if it is limited by how many schemas/tables it has to DROP.
Will I need to DROP each larguish schema individually???
Thanks for any guidance. --DD
PS: Also note that in this case, the SCHEMAS are mostly empty (just ~ 200
rows per schema).
But in production, there could be thousands / millions of rows per SCHEMA.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-01-10 17:39:26 | Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory |
Previous Message | Devrim Gündüz | 2022-01-10 15:13:14 | Re: Install pg_dump and pg_restore on UBI8 and UBI8-minimal |