Quirk of pg_temp schemas ...

While fixing up a patch I had dealing with temporary tables I noticed
a bit of a quirk with pg_temp schemas. Namely that we have no actual
meta data marking them as temporary aside from their names. And we
don't do anything to protect that -- superuser can happily issue ALTER
SCHEMA RENAME to rename it to a name that doesn't match pg_temp*. The
rest of the system then treats it as a perfectly normal schema that
just happens to contain temporary tables....

postgres=# create temporary table t(i integer);
CREATE TABLE

postgres=# \d t
Table "pg_temp_4.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |

postgres=# alter schema pg_temp_4 rename to fnord;
ALTER SCHEMA

postgres=# \d t
Table "fnord.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |

We could, I suppose, say this is just not a problem. Most, perhaps
all, of the existing code doesn't seem bothered by this situation. But
it seems a bit fragile. The worst side effect I've found is that
autovacuum won't drop orphaned temp tables because it can't check if
the backend is still alive connected to them.

A related point is that super-user is allowed to drop the temp schema.
If super-user does do this we still allow new temporary tables to be
created in the now-nonexistent schema resulting in tables that don't
print correctly:

postgres=# drop schema pg_temp_3 cascade;
NOTICE: drop cascades to table t3
DROP SCHEMA

postgres=# create temporary table t4( i integer);
CREATE TABLE

postgres=# \d t4
Table ".t4"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |

I suspect there are sites that will try to fprintf NULL using %s here
which on glibc prints "(null)" but may crash elsewhere...

At the very least we should probably disallow creating temporary
tables if the temporary schema has been dropped. That's just creating
broken references in the catalog tables. Alternately we could rig
something so that dropping the schema unsets myTempNamespace.

The real fix seems to me adding a "istemp" and "backendid" columns to
pg_namespace and not depending on the actual name of the schema to
store this info in. But I guess the current scheme has worked fine for
ages so I dunno. Perhaps the global temp table work will have to
invest in this area.

--
greg