Re: More schema queries

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 17 May 2002 23:24
> 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:
> > 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.