

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


Fri, 17 May 2002 23:23:32 +0100
"Dave Page" <dpage@vale-housing.co.uk>
Re: +AFs-HACKERS+AF0- More schema queries 