On Fri, Sep 20, 2024 at 7:32 AM Olleg <spl...@ya.ru> wrote: > Hi all. > > One of our programmer created a view based on the system view. I tried > to explain him, that he created a dependency from the ordinary database > to the system object and this is a bad idea. But he is not smart enough. > So I need a guru opinion. Is this permissible or will here be a trouble > with, for instance, pg_upgrade? > > CREATE OR REPLACE VIEW public.all_tables > AS SELECT n.nspname AS schemaname, > c.relname AS tablename, > pg_get_userbyid(c.relowner) AS tableowner, > c.reltuples AS num_rows, > c.relkind, > CASE c.relkind > WHEN 'f'::"char" THEN 'Foreign table'::text > WHEN 'r'::"char" THEN 'Relation'::text > WHEN 'i'::"char" THEN 'Index'::text > WHEN 'S'::"char" THEN 'Sequence'::text > WHEN 't'::"char" THEN 'TOAST'::text > WHEN 'v'::"char" THEN 'View'::text > WHEN 'm'::"char" THEN 'Materialized view'::text > WHEN 'c'::"char" THEN 'Composite type'::text > WHEN 'p'::"char" THEN 'Partitioned table'::text > WHEN 'I'::"char" THEN 'partitioned Index'::text > ELSE NULL::text > END AS rel_type, > c.relpersistence, > CASE c.relpersistence > WHEN 'p'::"char" THEN 'permanent'::text > WHEN 'u'::"char" THEN 'unlogged'::text > WHEN 't'::"char" THEN 'temporary'::text > WHEN 'c'::"char" THEN 'constant'::text > ELSE NULL::text > END AS persistence, > t.spcname AS tablespace, > c.relhasindex AS hasindexes, > c.relhasrules AS hasrules, > c.relhastriggers AS hastriggers > FROM pg_class c > LEFT JOIN pg_namespace n ON n.oid = c.relnamespace > LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace; >
1. pg_upgrade will throw an error if a column in one of those catalog tables is *removed*. Version release notes *should* mention whether columns are dropped; you'll just have to drop that view beforehand, and create a new version afterwards, possibly modifying any pg/plsql. No big deal, honestly; just something to keep in mind. 2. The query will become incomplete/wrong when new relkind and relpersistence values are added, necessitating an updated version. Again, not a big deal, and just something to keep in mind. Thus, I see no problem with that sort of view. Heck, I've made similar views; they're all over my cron jobs. (I think it should not be in public -- my views all go in the "dba" schema -- but that's a different issue.) -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> crustacean!
