Does RelCache/SysCache shrink except when relations are deleted? - Mailing list pgsql-hackers

Hello,

Please let me ask you some questions about RelCache/SysCache/CatCache 
design. I know I should post this to pgsql-general, but I decided to post 
here because the content includes design questions.

<<Background>>
My customer is facing a "out of memory" problem during a batch job. I'd like 
to know the cause and solutions. PostgreSQL version is 8.2.7 (32-bit on 
Linux).

The batch job consists of two steps in a single psql session:

1. call some PL/pgSQL function (say "somefunc" here)
2. VACUUM tables (at this time, maintenance_work_mem=256MB)

The step 2 emitted the following messages in syslog.

ERROR:  out of memory
DETAIL:  Failed on request of size 268435452.
STATEMENT:  VACUUM some_table_name

somefunc copies rows from a single table to 100,000 tables (table_1 - 
table_100000) as follows:

[somefunc]
FOR id in 1 .. 100000 LOOP   check if the table "table_${ID}" exists by searching pg_class   if the table exists
INSERTINTO table_${id} SELECT * FROM some_table           WHERE pk = id;   else /* the table does not exist */
CREATETABLE table_${id} AS SELECT * FROM some_table           WHERE pk = id;
 
END LOOP;

Before starting somefunc, the virtual memory of the backend postgres is 
1.6GB, as reported by top command as "VIRT" column. When somefunc completes, 
it becomes 2.6GB. So, VACUUM cannot allocate 256MB because the virtual 
memory space is full.

This is all the information I have now. I requested the customer to collect 
PostgreSQL server log so that memory context statistics can be obtained when 
"out of memory" occurs. Plus, I asked for the result of "SHOW ALL" and the 
minimal procedure to reproduce the problem. However, I'd like to ask your 
opinions rather than waiting for the problem to happen again.


<<Question>>
I'm guessing that CacheMemoryContext might be using much memory, because 
somefunc accesses as many as 100,000 tables. But I don't understand 
RelCache/SysCache implementation yet.

Q1: When are the RelCache/SysCache entries removed from CacheMemoryContext?
Are they removed only when the corresponding relations are deleted? If so, 
"many tables and indexes" is not friendly for the current PostgreSQL?

Q2: somefunc increased 1GB of virtual memory after accessing 100,000 tables. 
This means that one table uses 10KB of local memory.
Is it common that this much memory is used for RelCache/SysCache or other 
control information?
Does the number of attributes in a table affect local memory usage much?

Q3: I think one solution is to run VACUUM in a separate psql session.
Are there any other solutions you can think of?

Q4: The customer says one strange thing. If the 100,000 tables exist before 
somefunc starts (i.e., somefunc just copy records), the virtual memory of 
postgres does not increase.
Is there anything to reason about his comment?

Regards
MauMau