> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 17 May 2002 21:26
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
>
> "Dave Page" <dpage@vale-housing.co.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.
