

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: 17 May 2002 23:24
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries 
> 
> 
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > helpdesk=# select relnamespace, relname from pg_class where relname 
> > like 'pg_%';
> 
> >  relnamespace |             relname
> > --------------+---------------------------------
> > ...
> >          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
> 
> Bizarre.  It's not that way here.  Would you mind updating to 
> CVS tip, rebuilding, and seeing if you can duplicate that?  
> Also, make sure you're using the right initdb script ...

No problem, but it won't be until Monday now. I'll let you know what I
find.

> > ... 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.
> 
> Actually, I'd venture that you do *not* want to do namespace 
> search resolution for yourself; have you thought about how 
> messy the SQL query would be?  The new datatypes regclass, 
> etc are intended to handle it for you.  For example
> 
> select 'foo'::regclass::oid;	-- get OID of table foo in search path
> 
> select 'foo.bar'::regclass::oid;  -- get OID of table foo.bar
> 
> select relkind from pg_class where oid = 'foo'::regclass; -- 
> is foo a view?

It doesn't work quite like that anyway. pgAdmin has a base library
(pgSchema) which is a hierarchy of collections of objects which
represent an entire server. It populates itself on demand, so the first
time you access a collection of views (for example), pgSchema queries
the database to build the collection of views in that database (now
schema of course as there's an extra level in the hierarchy). Future
accesses to that part of the hierarchy are *very* quick (not that
initial ones are particularly slow). The only downside is that you may
not notice new objects from other developers immediately (though the
user can manually refresh any part of the hierarchy).

Anyway, long story short, once I know the search path is
testschema,public I'll just do:

If
svr.Databases("dbname").Namespaces("testschema").Views.Exists("viewname"
) Then ...
If svr.Databases("dbname").Namespaces("public").Views.Exists("viewname")
Then ...

Anyway, current_schemas() seems ideal, thanks.

> > 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.
> 
> Curious.  I have not noticed much of any change in postmaster 
> startup time on Unix.  Can you run a profile or something to 
> see where the time is going?

Probably, but I'd need hand-holding as I don't have a clue how to do
that. If you can send some instructions I'll give it a go though it'll
probably be tomorrow now as I'm starting to fall asleep.

Regards, Dave.

Fri, 17 May 2002 23:38:04 +0100
"Dave Page" <dpage@vale-housing.co.uk>
Re: More schema queries 
"Dave Page" <dpage@vale-housing.co.uk> writes:
> It doesn't work quite like that anyway.

Oh, so essentially you want to simulate the namespace search on the
application side.  I see.

> Anyway, current_schemas() seems ideal, thanks.

It may not be exactly what you need, because it doesn't tell you about
implicitly searched schemas --- which always includes pg_catalog and
will include a temp namespace if you've activated one.  For instance,
if current_schemas claims the search path is

regression=> select current_schemas();
 current_schemas
-----------------
 {tgl,public}
(1 row)

then the real path is effectively {pg_catalog,tgl,public}, or possibly
{pg_temp_NNN,pg_catalog,tgl,public}.

There was already some discussion about making a variant version of
current_schemas() that would tell you the Whole Truth, including the
implicitly searched schemas.  Seems like we'd better do that; otherwise
we'll find people hardwiring knowledge of these implicit search rules
into their apps, which is probably a bad idea.

Anyone have a preference about what to call it?  I could see making a
version of current_schemas() that takes a boolean parameter, or we
could choose another function name for the implicit-schemas-too version.


>> Curious.  I have not noticed much of any change in postmaster 
>> startup time on Unix.  Can you run a profile or something to 
>> see where the time is going?

> Probably, but I'd need hand-holding as I don't have a clue how to do
> that.

I'm not sure how to do it on Cygwin, either.  On Unix you'd build a
profilable backend executable using
	cd pgsql/src/backend
	gmake clean
	gmake PROFILE="-pg" all
install same, run it, and then use gprof on the gmon.out file dumped
at postmaster termination.  Dunno if it has to be done differently
on Cygwin.

			regards, tom lane

Fri, 17 May 2002 19:01:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More schema queries 
On Sat, 2002-05-18 at 01:01, Tom Lane wrote:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > It doesn't work quite like that anyway.
> 
> Oh, so essentially you want to simulate the namespace search on the
> application side.  I see.
> 
> > Anyway, current_schemas() seems ideal, thanks.
> 
> It may not be exactly what you need, because it doesn't tell you about
> implicitly searched schemas --- which always includes pg_catalog and
> will include a temp namespace if you've activated one.  For instance,
> if current_schemas claims the search path is
> 
> regression=> select current_schemas();
>  current_schemas
> -----------------
>  {tgl,public}
> (1 row)
> 
> then the real path is effectively {pg_catalog,tgl,public}, or possibly
> {pg_temp_NNN,pg_catalog,tgl,public}.
> 
> There was already some discussion about making a variant version of
> current_schemas() that would tell you the Whole Truth, including the
> implicitly searched schemas.  Seems like we'd better do that; otherwise
> we'll find people hardwiring knowledge of these implicit search rules
> into their apps, which is probably a bad idea.
> 
> Anyone have a preference about what to call it?  I could see making a
> version of current_schemas() that takes a boolean parameter, or we
> could choose another function name for the implicit-schemas-too version.

or we could make another function with the same name :)

current_schemas('full')

--------------
Hannu



20 May 2002 17:17:52 +0200
Hannu Krosing <hannu@tm.ee>
Re: More schema queries