"Roberts, Jon" <Jon.Roberts@asurion.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