Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 1/11/22 01:34, Dominique Devienne wrote: On Mon, Jan 10, 2022 at 11:13 PM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: I go into use cases in my previous message, send a minute ago. In your OP the error occurred here: DROP OWNED BY "Epos-PNS

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique Devienne writes: > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you > suggest) requires just the same. But it doesn't. regards, tom lane

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Tue, Jan 11, 2022 at 12:47 PM Wolfgang Walther wrote: > Dominique Devienne: > > I wish for DB-specific ROLEs BTW... > > Same here. That would be so useful. > In fact, in my case, I also want something even narrower than that, which are SCHEMA specific ROLEs. ROLEs tied to a given schema,

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique: On Tue, 11 Jan 2022 at 11:57, Dominique Devienne wrote: > On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte > wrote: >> Not going to enter into the lock situation but... > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you > suggest) requires just the same.

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique Devienne: I wish for DB-specific ROLEs BTW... Same here. That would be so useful.

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte wrote: > Dominique: > Not going to enter into the lock situation but... > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you suggest) requires just the same. > On Tue, 11 Jan 2022 at 10:24, Dominique Devienne > wrote:

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique: Not going to enter into the lock situation but... On Tue, 11 Jan 2022 at 10:24, Dominique Devienne wrote: ... > I need for unit testing purposes to be able to support multiple (2+N schemas) > "instances". > Each instance (of 2+N schemas) is owned by a separate ROLE, created for that

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Mon, Jan 10, 2022 at 11:13 PM Adrian Klaver wrote: > On 1/10/22 13:58, Dominique Devienne wrote: > > > Given that Tom mentions max_locks_per_transaction can be safely > increased, > > and given the stats I mentioned in this thread, what would a > > "reasonable" max_locks_per_transaction > >

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Mon, Jan 10, 2022 at 10:40 PM Alvaro Herrera wrote: > On 2022-Jan-10, Dominique Devienne wrote: > > > Btw, is there a catalog accurately count a schémas relations from > the catalog? > > pg_class > ddevienne=> select relnamespace::regnamespace::text, count(*) from pg_class where

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 1/10/22 13:58, Dominique Devienne wrote: Given that Tom mentions max_locks_per_transaction can be safely increased, and given the stats I mentioned in this thread, what would a "reasonable" max_locks_per_transaction be in my case? By reasonable, I mean "as large as possible w/o being too

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Mon, Jan 10, 2022 at 10:29 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Jan 10, 2022 at 12:09 PM Dominique Devienne > > > wrote: > >> Tom wrote "relation" for the number of locks necessary for DROP OWNED > BY. > >> What does it mean in this context? relation = table? > > >

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

"David G. Johnston" writes: > On Mon, Jan 10, 2022 at 12:09 PM Dominique Devienne > wrote: >> Tom wrote "relation" for the number of locks necessary for DROP OWNED BY. >> What does it mean in this context? relation = table? > I'm confused here a bit as well. The items being talked about are

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 1/10/22 13:08, David G. Johnston wrote: On Mon, Jan 10, 2022 at 12:09 PM Dominique Devienne > wrote: Given |max_locks_per_transaction| * (max_connections  +

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On Mon, Jan 10, 2022 at 12:09 PM Dominique Devienne wrote: > Given max_locks_per_transaction * (max_connections > > + max_prepared_transactions >

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 2022-Jan-10, Dominique Devienne wrote: > Given max_locks_per_transaction * (max_connections > > + max_prepared_transactions >

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 1/10/22 11:12, Dominique Devienne wrote: 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) ```

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

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

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique Devienne 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

DROP OWNED BY fails with #53200: ERROR: out of shared memory

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