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

On Mon, Jan 10, 2022 at 10:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Mon, Jan 10, 2022 at 12:09 PM Dominique Devienne <ddevienne(at)gmail(dot)com
> >
> > 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 tables
> > and indexes, both of which manifest as files on the filesystem. But not
> > all relations do (e.g., views). But if this isn't tied to the filesystem
> > then I would expect that other object types, especially functions, would
> > require locking as well, but those are decidedly not relations.
>
> I was wrong actually --- I wrote that thinking that we acquire exclusive
> lock when dropping a relation (where relation may be defined as "something
> with a pg_class entry"). That's true, but these days we acquire a lock
> when deleting *any* cataloged database object. So you'd also need a lock
> for each schema, function, etc that was due to get dropped. This is
> basically to avoid problems in case of concurrent drop commands.
>
> It's still true that the size of a relation in columns or rows is not
> relevant here.
>

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
large"...

Obviously 64*100 is not quite large enough to be safe in this case. I'd
appreciate some advise. TIA, --DD