Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srdpN-003UyY-FT for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 13:37:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1srdpL-006PhV-FF for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 13:37:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srdpL-006PdA-27 for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 13:37:40 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1srdpI-000Byt-KJ for pgsql-general@postgresql.org; Fri, 20 Sep 2024 13:37:39 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-5e5568f1baaso1147444eaf.2 for ; Fri, 20 Sep 2024 06:37:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726839455; x=1727444255; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=F9daSItjYmEJ3ja4T48Pg7ylyisQXSCldEtEsv+BgIw=; b=VtxtgXOy8giDgocIJe29gMaxu9VEqrgk7pj1MrO1FbM0WUs9Dt9QhVPu/hRfjBtkN1 uuCZ01fMREGMmWrNThJiLewOEKPDlRpnUFLQYv5iDp2N3GpDcjdGP1LoptbvnveNy9+z gKZrnq9YPGkFqpFAHDqztSjsqd+6jO5/Rw/jjFShanXk05Z05Bw1o0ytEx4vx5Wr2RWK BwLTd0Hbtn8M+SADWZBsJMPA2A8Ds3WIZyBvFsgCRRcJWvFc0uZqoc8xpoC9oAsq1rws yztAnnG0ur8XEWbauTUgccPDNRGqPLL2sOta1qegV7lyx5d5/3K+ITmwtFHdn+BKch0R GJ1g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726839455; x=1727444255; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=F9daSItjYmEJ3ja4T48Pg7ylyisQXSCldEtEsv+BgIw=; b=ODYbSgiVDSFxheLmU6TxgkpqLGWV6b5ZLWHV1vDY5mt5iEFHj718st+RFqX3h9EBE1 7x+LeN+oWyjEijTiHoc9M8T5oB738h2U5SFdOHvqAhr7TRct5Se26/dXhvIDDzy4Ijyy DvThEOLRxckKUivC2KDxnoaXrnD16wibfdUWo1FAs1c7xXrjr/J6KsHHn66DpAOvwNjs 0yhHd2AWQ2nC89ilMxL3YiqSiyxsSweE3L8jP1/qsHlusMWRS/g1/4ZEHPlMecR6CmI6 LRRLe9lrHK4NKc3Vv0mav9Iz2hTHp0n95hbDowpXIxWZRjEuLrK+Da1oI0PG53RUGZdH jOGg== X-Gm-Message-State: AOJu0YyeZh9iKJqTzDdggDxjdduWals2UYf2vLQ0iMJCrIiBV4yel68U lDaVwG9iXR/hbg6JJuxW4nXrs3lmQrUSqjBZuxSU5FYfOlIu4tp7WJgbpBN0FhytYHrn46yXdkx Neo6MwM1djaZHM1+qX/d0riaQk8ITElGe X-Google-Smtp-Source: AGHT+IHB9xXp8UpKOxYiusKzH/7EUMoxoIh1NRCtDwoA1VzgY3odqn2ITZmjHT/rsZ50LgW5ZKt1kmH9p0mSN975QUY= X-Received: by 2002:a05:6820:210:b0:5e1:d741:6f04 with SMTP id 006d021491bc7-5e58ba71f7amr2267592eaf.3.1726839455061; Fri, 20 Sep 2024 06:37:35 -0700 (PDT) MIME-Version: 1.0 References: <5ad41b52-865b-48cc-87cf-7c7ed9e2e978@ya.ru> In-Reply-To: <5ad41b52-865b-48cc-87cf-7c7ed9e2e978@ya.ru> From: Ron Johnson Date: Fri, 20 Sep 2024 09:37:23 -0400 Message-ID: Subject: Re: Dependencies on the system view To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004da0f606228d2416" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004da0f606228d2416 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Sep 20, 2024 at 7:32=E2=80=AFAM Olleg 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 =3D c.relnamespace > LEFT JOIN pg_tablespace t ON t.oid =3D 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.) --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --0000000000004da0f606228d2416 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Sep 20, 2024 at 7:32=E2=80=AFAM O= lleg <splarv@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,
=C2=A0 =C2=A0 =C2=A0c.relname AS tablename,
=C2=A0 =C2=A0 =C2=A0pg_get_userbyid(c.relowner) AS tableowner,
=C2=A0 =C2=A0 =C2=A0c.reltuples AS num_rows,
=C2=A0 =C2=A0 =C2=A0c.relkind,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CASE c.relkind
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'f'::"cha= r" THEN 'Foreign table'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'r'::"cha= r" THEN 'Relation'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'i'::"cha= r" THEN 'Index'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'S'::"cha= r" THEN 'Sequence'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 't'::"cha= r" THEN 'TOAST'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'v'::"cha= r" THEN 'View'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'm'::"cha= r" THEN 'Materialized view'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'c'::"cha= r" THEN 'Composite type'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'p'::"cha= r" THEN 'Partitioned table'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'I'::"cha= r" THEN 'partitioned Index'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ELSE NULL::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0END AS rel_type,
=C2=A0 =C2=A0 =C2=A0c.relpersistence,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CASE c.relpersistence
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'p'::"cha= r" THEN 'permanent'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'u'::"cha= r" THEN 'unlogged'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 't'::"cha= r" THEN 'temporary'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN 'c'::"cha= r" THEN 'constant'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ELSE NULL::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0END AS persistence,
=C2=A0 =C2=A0 =C2=A0t.spcname AS tablespace,
=C2=A0 =C2=A0 =C2=A0c.relhasindex AS hasindexes,
=C2=A0 =C2=A0 =C2=A0c.relhasrules AS hasrules,
=C2=A0 =C2=A0 =C2=A0c.relhastriggers AS hastriggers
=C2=A0 =C2=A0 FROM pg_class c
=C2=A0 =C2=A0 =C2=A0 LEFT JOIN pg_namespace n ON n.oid =3D c.relnamespace =C2=A0 =C2=A0 =C2=A0 LEFT JOIN pg_tablespace t ON t.oid =3D c.reltablespace= ;

1. pg_upgrade will throw an err= or if a column in one of those catalog tables is removed.=C2=A0 Vers= ion release notes should=C2=A0mention whether columns are dropped; y= ou'll just have to drop that view beforehand, and create a new version = afterwards, possibly modifying any pg/plsql.=C2=A0 =C2=A0 No big deal, hone= stly; just something to keep in mind.

2.= The query will become incomplete/wrong when new relkind and relpersistence= =C2=A0values are added, necessitating an updated version.=C2=A0 Again, not = a big deal,=C2=A0and just something to keep in mind.

Thus, I see no problem with that sort of view.=C2=A0 Heck, I've made= similar views; they're all over my cron jobs.=C2=A0 (I think it should= not be in public -- my views all go in the "dba" schema -- but t= hat's a different issue.)

--
Death to <Redacted>, and butter sauce.
Don't boi= l me, I'm still alive.
<Redacted> crustacean!
<= /div>
--0000000000004da0f606228d2416--