Re: +AFs-HACKERS+AF0- More schema queries

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 17 May 2002 21:26
> To: Dave Page
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] More schema queries
>
>
> "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes:
> > 1) All the system views are currently part of the public namespace.
> > Not a problem for me, but shouldn't they be in pg_catalog?
>
> Say what? They *are* in pg_catalog. initdb creates nothing
> in public.

You'll have to take my word for it that I haven't played with pg_class -
is it possible I got a snapshot that was built at precisely the wrong
moment?

helpdesk=# select * from pg_namespace;

oid | nspname | nspowner | nspacl
-------+-------------+----------+-------------------
11 | pg_catalog | 1 | {=U}
99 | pg_toast | 1 | {=}
2200 | public | 1 | {=UC}
16563 | pg_temp_1 | 1 |
40071 | Test Schema | 1 |
48273 | flurb | 1 |
40072 | test | 1 | {=UC,postgres=UC}
48276 | dave2 | 1 |
48277 | Gulp | 1 | {=UC,postgres=UC}
(9 rows)

helpdesk=# select relnamespace, relname from pg_class where relname like
'pg_%';

relnamespace | relname
--------------+---------------------------------
11 | pg_largeobject
11 | pg_aggregate
11 | pg_trigger
11 | pg_listener
11 | pg_namespace
11 | pg_attrdef
11 | pg_database
11 | pg_xactlock
11 | pg_description
11 | pg_group
11 | pg_proc
11 | pg_relcheck
11 | pg_rewrite
2200 | pg_user
2200 | pg_rules
2200 | pg_views
2200 | pg_tables
2200 | pg_indexes
2200 | pg_stats
2200 | pg_stat_all_tables
2200 | pg_stat_sys_tables
11 | pg_aggregate_fnoid_index
11 | pg_am_name_index
11 | pg_am_oid_index
11 | pg_amop_opc_opr_index
11 | pg_amop_opc_strategy_index
11 | pg_amproc_opc_procnum_index
11 | pg_attrdef_adrelid_adnum_index
11 | pg_attribute_relid_attnam_index
11 | pg_attribute_relid_attnum_index
11 | pg_class_oid_index
11 | pg_class_relname_nsp_index
11 | pg_database_datname_index
11 | pg_database_oid_index
11 | pg_description_o_c_o_index
11 | pg_group_name_index
11 | pg_group_sysid_index
11 | pg_index_indrelid_index
11 | pg_index_indexrelid_index
11 | pg_inherits_relid_seqno_index
11 | pg_language_name_index
11 | pg_language_oid_index
11 | pg_largeobject_loid_pn_index
11 | pg_namespace_nspname_index
11 | pg_namespace_oid_index
11 | pg_opclass_am_name_nsp_index
11 | pg_opclass_oid_index
11 | pg_operator_oid_index
11 | pg_operator_oprname_l_r_n_index
11 | pg_proc_oid_index
11 | pg_proc_proname_args_nsp_index
11 | pg_relcheck_rcrelid_index
11 | pg_rewrite_oid_index
11 | pg_rewrite_rel_rulename_index
11 | pg_shadow_usename_index
11 | pg_shadow_usesysid_index
11 | pg_statistic_relid_att_index
11 | pg_trigger_tgconstrname_index
11 | pg_trigger_tgconstrrelid_index
11 | pg_trigger_tgrelid_tgname_index
11 | pg_trigger_oid_index
11 | pg_type_oid_index
11 | pg_type_typname_nsp_index
2200 | pg_stat_user_tables
2200 | pg_statio_all_tables
2200 | pg_statio_sys_tables
2200 | pg_statio_user_tables
2200 | pg_stat_all_indexes
2200 | pg_stat_sys_indexes
99 | pg_toast_16384_index
99 | pg_toast_16384
2200 | pg_stat_user_indexes
2200 | pg_statio_all_indexes
2200 | pg_statio_sys_indexes
2200 | pg_statio_user_indexes
99 | pg_toast_1262_index
99 | pg_toast_1262
2200 | pg_statio_all_sequences
2200 | pg_statio_sys_sequences
2200 | pg_statio_user_sequences
2200 | pg_stat_activity
99 | pg_toast_16416_index
99 | pg_toast_16416
2200 | pg_stat_database
11 | pg_statistic
11 | pg_type
11 | pg_attribute
99 | pg_toast_1261_index
99 | pg_toast_1261
11 | pg_class
11 | pg_inherits
11 | pg_index
11 | pg_operator
99 | pg_toast_1255_index
...

> > 2) pgAdmin needs to be able to find out the namespace
> search path for
> > the current connection through an SQL query - is this
> possible yet or
> > can/will a suitable function be written?
>
> Either 'show search_path' or 'select current_schemas()' might
> do what you want; or perhaps not. Why do you want to know
> the search path? What's the scenario in which pgAdmin
> wouldn't set the search path for itself?

pgAdmin works 99% of the time in pg_catalog. When it creates objects, it
always specifies an absolute name (CREATE TABLE public.tablename...).

However, one of the features is the ability to use the wizard, or just
type in an SQL query and output the results to either a plugin exporter
(such as MS Excel, ACSII file etc) or to a screen grid. If the user
selects the screen grid, then some parsing of the query is done to
figure out if we can generate queries to add/delete/update rows and
therefore enable or disable the relevant buttons. One of the tests is to
figure out if one of the base datasources in the query is a view -
currently this is easy, but in 7.3 we could have a table & a view with
the same name in different schemas, hence by using the path we can
figure out what object we're actually using.

Incidently if you're interested at the moment, you may remember that in
7.2 beta there was a problem with slow startup under Cygwin which was
down to a few seconds by release... The last 2 snapshots I've run take
well over a minute for postmaster startup on a P3M 1.13GHz/512Mb under
little load. There is virtually no disk activity during this time.

Regards, Dave.