public inbox for [email protected]
help / color / mirror / Atom feedDependencies on the system view
2+ messages / 2 participants
[nested] [flat]
* Dependencies on the system view
@ 2024-09-20 11:32 Olleg <[email protected]>
2024-09-20 13:37 ` Re: Dependencies on the system view Ron Johnson <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Olleg @ 2024-09-20 11:32 UTC (permalink / raw)
To: pgsql-general
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;
--
Olleg
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Dependencies on the system view
2024-09-20 11:32 Dependencies on the system view Olleg <[email protected]>
@ 2024-09-20 13:37 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Ron Johnson @ 2024-09-20 13:37 UTC (permalink / raw)
To: pgsql-general
On Fri, Sep 20, 2024 at 7:32 AM Olleg <[email protected]> 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!
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-20 13:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-20 11:32 Dependencies on the system view Olleg <[email protected]>
2024-09-20 13:37 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox