Re: +AFs-HACKERS+AF0- More schema queries - Mailing list pgsql-hackers

> -----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.