Re: temp schemas

"Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com> writes:
> This is what I'm trying to understand. At what point does PostgreSQL
> determine it needs to create a new temp schema versus reusing an
> existing one? Maybe we are doing something incorrectly in our code.

They're associated with backends' slot numbers in a shared memory array
("MyBackendId" in the code). New backends always take the lowest unused
slot, so what should happen is that the low-numbered pg_temp_n schemas
are re-used constantly, and it should certainly never be possible for
one numbered higher than max_connections to appear.

> On Wednesday, we had 170,243 temp schemas and today, we have 173,384.

This seems flat out impossible in a stock PG build. I see from
elsewhere in the thread that you are running a Greenplum-modified
backend. What I conclude is that Greenplum has broken the intended
behavior, probably by redefining how MyBackendId is set. Better ask
them.

regards, tom lane