Re: Lots of memory allocated when reassigning Large Objects

On Mon, Nov 29, 2021 at 01:40:31PM -0500, Tom Lane wrote:
> DROP OWNED BY likely has similar issues.

I tried a few more commands but found no significant issue.
IMO if you have 100k tables, then you can afford 1GB RAM.

SELECT format('CREATE TABLE t%s()', a) FROM generate_series(1,9999)a\gexec
SET client_min_messages=debug; SET log_statement_stats=on;

CREATE TABLESPACE tbsp LOCATION '/home/pryzbyj/tblspc';
ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE tbsp;
-- 10k tables uses 78MB RAM, which seems good enough (64MB the 2nd time??)

GRANT ALL ON ALL TABLES IN SCHEMA public TO current_user;
-- 10k tables uses 50MB RAM, which seems good enough
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO current_user
-- 10k sequences uses 47MB RAM, which seems good enough

SELECT format('CREATE FUNCTION f%s() RETURNS int RETURN 1;', a) FROM generate_series(1,9999)a;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO current_user;
-- 10k functions uses 62MB RAM, which seems good enough

And it looks like for ALTER PUBLICATION .. FOR ALL TABLES IN SCHEMA, the
namespace itself is stored, rather than enumerating all its tables.

>> IOW, it's asking for at least 481MB to reassign 1 million empty LO. It
>> strikes me as odd.

@Guillaume: Even if memory use with the patch isn't constant, I imagine it's
enough to have avoided OOM.

--
Justin