Subject: Request for new column in pg_namespace

From: Ron Johnson <[email-masked]>
Date: Sun, 15 Dec 2024 11:58:49 -0500

https://www.postgresql.org/docs/current/catalog-pg-namespace.html

Currently, when I want to query all "userland" tables, I write something
like:
select ...
from pg_class cl, pg_namespace nsp
where cl.relnamespace = nsp.oid
  and nsp.nspname not like 'pg_%
  and nsp.nspname != 'information_schema';

A new boolean column named "indissystem" that's true only for system
relations would make *many* maintenance queries cleaner, since they'd look
like:
select ...
from pg_class cl, pg_namespace nsp
where cl.relnamespace = nsp.oid
  and nsp.indissystem = false;

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Pavel Stehule <[email-masked]>
Date: Sun, 15 Dec 2024 18:15:57 +0100

Hi

ne 15. 12. 2024 v 17:59 odesílatel Ron Johnson <[email-masked]>
napsal:

> relations would make *many* maintenance queries cleaner, since they'd
> look like:
> select ...
oid of all system objects is less then 0x4000

Regards

Pavel

---

From: Tom Lane <[email-masked]>
Date: Sun, 15 Dec 2024 12:29:26 -0500

> oid of all system objects is less then 0x4000

That wouldn't help for excluding temp schemas, and it's not totally
trustworthy for information_schema either.

But I think the real problem with Ron's proposal is that it presumes
there is a one-size-fits-all notion of "system schema".  As a
counterexample, for some maintenance activities (such as vacuuming)
you might wish to process pg_catalog.

What I'd suggest as an improvement that could be implemented
immediately is to wrap the checks in a user-defined function
like "is_system_schema(nspname name)".

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Sun, 15 Dec 2024 12:46:42 -0500

> you might wish to process pg_catalog.

In that case, one would explicitly mention pg_catalog, no?
where cl.relnamespace = nsp.oid
  and (nsp.indissystem = false or nsp.nspname = 'pg_catalog');

> like "is_system_schema(nspname name)".

Good idea.