On Sun, Dec 15, 2024 at 2:20 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Isaac Morland <isaac.morl...@gmail.com> writes: > > On Sun, 15 Dec 2024 at 12:29, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> 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)". > > > Would it make sense to make the parameter be of type regnamespace? > > Meh ... you could, but what the function really needs is the name. > Getting from regnamespace (which is an OID) to the name would incur > an extra syscache lookup. Admittedly, if it removes the need for > the calling query to join to pg_namespace at all, you'd probably > come out about even --- the net effect would be about like a > hashjoin to pg_namespace, I think, since the syscache would act > like the inner hashtable of a hashjoin. > It'll simplify the SQL to pass it a pg_class.relnamespace value, since that's what's stored in pg_class. select ... from pg_class cl INNER JOIN ... where not is_system_schema(cl.relnamespace) and ...; Might it be slightly slower? Sure... but pg_class and pg_namespace aren't giant tables, and the queries won't run thousands of times per day. Thus, in this case, a little less efficiency for much cleaner code is an acceptable trade-off TO ME. Heck, given how often "pg_class cl INNER JOIN pg_namespace nsp ON cl.relnamespace = nsp.oid" appears in my (and so much other code around the Internet), I should probably create a view that joins the two tables, and adds an is_system_schema column. That would *really* simplify my code... -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
