Thread: More schema queries

Hi,

I have some schema queries/thoughts that I would appreciate some
help/insights/fixes with/for please!

(Apologies if these have been asked before or have been addressed in a
recent snapshot - my ISP's been having routing problems recently & I
can't reach postgresql.org via http right now).

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?

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?

There were more than that when I started typing this but I had a flash
of inspiration and they went away :-)

TIA,

Regards, Dave.

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

> 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?
        regards, tom lane

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


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

> 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?
        regards, tom lane

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

"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 usingcd pgsql/src/backendgmake cleangmake 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

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 18 May 2002 00:01
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: More schema queries
>
> 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.

Use of a parameter seems fine to me. Save having Yet Another Function
:-) and trying to figure out a sensible name for it!

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

Well, I have gcc & gprof so I assume it'll be pretty much the same. I'll
have a play tonight.

Thanks Tom,

Dave.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 18 May 2002 00:01
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: More schema queries
>
> 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.

Hmm, I tried this and got some errors, then got side tracked with #1
daughters birthday..

Tried again today with the latest snapshot from ftp.postgresql.org and
got the same errors.

Downloaded a complete new Cygwin installation (in case my old one was
screwed up - it is very old and has been well hacked about) and still
get the same error when doing a complete build (./configure --with-CXX
--prefix=/usr/local/pgsql73 --docdir=/usr/doc/postgresql-
--with-pgport=5433):

dlltool --dllname postgres.exe --def postgres.def --output-lib
libpostgres.a
dlltool --dllname postgres.exe --output-exp postgres.exp --def
postgres.def
gcc -g -o postgres.exe -Wl,--base-file,postgres.base postgres.exp
access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o
commands/SUBSYS.o executor /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o
main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o
postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/ SUBSYS.o
tcop/SUBSYS.o utils/SUBSYS.o -lcygipc -lcrypt
access/SUBSYS.o(.text+0x13):heaptuple.c: undefined reference to `mcount'
access/SUBSYS.o(.text+0xdf):heaptuple.c: undefined reference to `mcount'
access/SUBSYS.o(.text+0x319):heaptuple.c: undefined reference to
`mcount'
access/SUBSYS.o(.text+0x3a3):heaptuple.c: undefined reference to
`mcount'
access/SUBSYS.o(.text+0x790):heaptuple.c: undefined reference to
`mcount'
access/SUBSYS.o(.text+0x826):heaptuple.c: more undefined references to
`mcount' follow
main/SUBSYS.o(.text+0x186):main.c: undefined reference to `_monstartup'
main/SUBSYS.o(.text+0x190):main.c: undefined reference to `mcount'
nodes/SUBSYS.o(.text+0x10):nodeFuncs.c: undefined reference to `mcount'
nodes/SUBSYS.o(.text+0x44):nodeFuncs.c: undefined reference to `mcount'
nodes/SUBSYS.o(.text+0x68):nodeFuncs.c: undefined reference to `mcount'
nodes/SUBSYS.o(.text+0x8e):nodeFuncs.c: undefined reference to `mcount'
nodes/SUBSYS.o(.text+0xd1):nodeFuncs.c: more undefined references to
`mcount' follow
collect2: ld returned 1 exit status
make[2]: *** [postgres] Error 1
make[2]: Leaving directory
`/usr/local/src/postgresql-snapshot/src/backend'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/usr/local/src/postgresql-snapshot/src'
make: *** [all] Error 2

Any ideas?

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> gcc -g -o postgres.exe -Wl,--base-file,postgres.base postgres.exp
> access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o
> commands/SUBSYS.o executor /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o
> main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o
> postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/ SUBSYS.o
> tcop/SUBSYS.o utils/SUBSYS.o -lcygipc -lcrypt
> access/SUBSYS.o(.text+0x13):heaptuple.c: undefined reference to `mcount'

On Unix it's necessary for the link step to include a -pg switch, just
like the compile steps.  This is evidently not happening in the Windows
case.

In the Unix case, $(PROFILE) gets incorporated into $(LDFLAGS) in
src/Makefile.global, and then src/backend/Makefile uses $(LDFLAGS)
in the backend link rule (line 40 in current source).  I don't see any
inclusion of flags at all in the Windows link rule at lines 48, 50.
Presumably these ought to at least mention $(PROFILE), and I wonder
whether they should not say $(LDFLAGS).

Please check it out and submit a patch...
        regards, tom lane

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

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 20 May 2002 15:16
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: More schema queries
>
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > gcc -g -o postgres.exe -Wl,--base-file,postgres.base postgres.exp
> > access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o
> > commands/SUBSYS.o executor /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o
> > main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o
> > postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o
> storage/ SUBSYS.o
> > tcop/SUBSYS.o utils/SUBSYS.o -lcygipc -lcrypt
> > access/SUBSYS.o(.text+0x13):heaptuple.c: undefined reference to
> > `mcount'
>
> On Unix it's necessary for the link step to include a -pg
> switch, just like the compile steps.  This is evidently not
> happening in the Windows case.
>
> In the Unix case, $(PROFILE) gets incorporated into
> $(LDFLAGS) in src/Makefile.global, and then
> src/backend/Makefile uses $(LDFLAGS) in the backend link rule
> (line 40 in current source).  I don't see any inclusion of
> flags at all in the Windows link rule at lines 48, 50.
> Presumably these ought to at least mention $(PROFILE), and I
> wonder whether they should not say $(LDFLAGS).
>
> Please check it out and submit a patch...

Attached (& CC'd to -patches). It all built OK with this, so I'll go off
and play with initdb & gprof now.

Regards, Dave.

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

OK, brand new Cygwin installation, built from CVS tip and still the
views are in public. I checked 4 times that I'm using the correct
initdb, even manually installing it from the source tree. I then hacked
initdb and prepended 'pg_catalog.' to the view names in the CREATE
VIEWs. Cleared my data dir, ran initdb and the views are still in
public. I then cleared & ran initdb with --debug. The views were *again*
in public, and no errors were seen.

I'm confused. Does the standalone backend not deal with schemas fully
and is silently failing 'cos there's nothing technically wrong with the
pg_catalog.viewname syntax? Or do I just not know what the heck I'm
doing :-)

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

On my clean cygwin installation this problem is no longer present. I
guess something got screwed up in my old installation that - maybe
something from the 7.2 installation that ran in parallel (thought that
worked fine).

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> I'm confused. Does the standalone backend not deal with schemas fully
> and is silently failing 'cos there's nothing technically wrong with the
> pg_catalog.viewname syntax?

The standalone backend does schemas just fine.  What is supposed to
ensure that the views get created in pg_catalog is the bit in initdb:

PGSQL_OPT="$PGSQL_OPT -O --search_path=pg_catalog"

The -- parameter should do the equivalent ofSET search_path = pg_catalog;
but apparently it's not working for you; if it weren't there then the
views would indeed get created in public.

Any idea why it's not working?
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 21 May 2002 01:00
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > I'm confused. Does the standalone backend not deal with
> schemas fully
> > and is silently failing 'cos there's nothing technically wrong with
> > the pg_catalog.viewname syntax?
>
> The standalone backend does schemas just fine.  What is
> supposed to ensure that the views get created in pg_catalog
> is the bit in initdb:
>
> PGSQL_OPT="$PGSQL_OPT -O --search_path=pg_catalog"

That said, I'm still surprised that prepending 'pg_catalog.' to the view
names didn't force them into pg_catalog.

> The -- parameter should do the equivalent of
>     SET search_path = pg_catalog;
> but apparently it's not working for you; if it weren't there
> then the views would indeed get created in public.
>
> Any idea why it's not working?

Just to be doubly sure, I've installed a fresh Cygwin, and confirmed
that none of Jason's prepackaged 7.2 got in there by mistake. Built and
installed from CVS tip as of about 9:30AM BST 21/5/02. The problem still
remains.

I've played with initdb, and confirmed that

$PGSQL_OPT = -F -D/data -o /dev/null -O --search_path=pg_catalog

immediately prior to the views being created. I then tried running a
single user backend in exactly the same way initdb does (bar the
redirection of the output), and checking the search path:

----
PC9 $ postgres -F -D/data -O --search_path=pg_catalog template1
LOG:  database system was shut down at 2002-05-21 10:44:50
LOG:  checkpoint record is at 0/49D6B0
LOG:  redo record is at 0/49D6B0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 103; next oid: 16570
LOG:  database system is ready

POSTGRES backend interactive interface
$Revision: 1.267 $ $Date: 2002/05/18 15:44:47 $

backend> select current_schemas();
blank        1: current_schemas     (typeid = 1003, len = -1, typmod = -1,
byval = f)       ----        1: current_schemas = "{public}"        (typeid = 1003, len =
-1, typmod = -1, byval = f)       ----
----

Which makes sense because as you said previously pg_catalog is implictly
included at the beginning of the search path anyway. It then struck me
that as that is the case, does the --search_path=pg_catalog get ignored?
I tested this by creating a view, and then examining it's
pg_class.relnamespace:

----
backend> create view testview as select * from pg_class;
backend> select relnamespace from pg_class where relname = 'testview';
blank        1: relnamespace        (typeid = 26, len = 4, typmod = -1,
byval = t)       ----        1: relnamespace = "2200"       (typeid = 26, len = 4, typmod =
-1, byval = t)       ----
----

2200 is the oid of 'public', so it seems to me that the
--search_path=pg_catalog is being ignored by the standalone backend for
some reason. I then tried explicitly naming the schema:

----
backend> create view pg_catalog.testview2 as select * from pg_class;
backend> select relnamespace from pg_class where relname = 'testview2';
blank        1: relnamespace        (typeid = 26, len = 4, typmod = -1,
byval = t)       ----        1: relnamespace = "11" (typeid = 26, len = 4, typmod = -1,
byval = t)       ----
----

This appears to work fine, so I hacked initdb to prepend the
'pg_catalog.' to the viewnames. Cleared $PGDATA, confirmed I was running
the correct initdb, and still, the views are in public - Arrrggghhh!

Any suggestions?

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> This appears to work fine, so I hacked initdb to prepend the
> 'pg_catalog.' to the viewnames. Cleared $PGDATA, confirmed I was running
> the correct initdb, and still, the views are in public - Arrrggghhh!

Weird.  Maybe there is more than one bug involved, because adding
pg_catalog. to the create view should definitely have worked.
Will try to duplicate that here.

> Any suggestions?

Try changing the PGOPTS setting to use
-c search_path=pg_catalog 

That shouldn't make any difference but ...

Also, you could try setting a breakpoint at RangeVarGetCreationNamespace
(in backend/catalog/namespace.c) to see what it thinks it's doing and
what's in namespace_search_path at the time.
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 21 May 2002 14:17
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
>
>
> Try changing the PGOPTS setting to use
>
>     -c search_path=pg_catalog
>
> That shouldn't make any difference but ...

Shouldn't but does :-). Checked & double-checked, that works perfectly.

> Also, you could try setting a breakpoint at
> RangeVarGetCreationNamespace (in backend/catalog/namespace.c)
> to see what it thinks it's doing and what's in
> namespace_search_path at the time.

I'm going to try to do this regardless of the fact it now works - this
will be my first play with gdb so it might take me a while but would
probably be a useful learning experience. I'll let you know what I find.

Regards, Dave.

> -----Original Message-----
> From: Dave Page
> Sent: 21 May 2002 14:39
> To: 'Tom Lane'
> Cc: pgsql-hackers@postgresql.org
> Subject: RE: [HACKERS] More schema queries
>
>
> > Also, you could try setting a breakpoint at
> > RangeVarGetCreationNamespace (in backend/catalog/namespace.c)
> > to see what it thinks it's doing and what's in
> > namespace_search_path at the time.
>
> I'm going to try to do this regardless of the fact it now
> works - this will be my first play with gdb so it might take
> me a while but would probably be a useful learning
> experience. I'll let you know what I find.
>

Sorry Tom, I know this isn't strictly a PostgreSQL problem, but despite
much time on Google I'm stuck with gdb. I can attach it to the
standalone backend at the relevant point in initdb, and have got it to
break in RangeVarGetCreationNamespace. I can also see the call stack &
registers etc.

What I cannot do is get it to show me anything useful. I only seem to be
able to step through the assembly code (is it possible to load the C
source?), and more importantly, adding a watch (or print-ing)
namespace_search_path gives: 167839776. Attempting to watch or print
namespaceId gives 'Error: No symbol "namespaceId" in current context.'.

I'd appreciate any pointers you can give me...

Regards, Dave.

> What I cannot do is get it to show me anything useful.

It sounds like gdb does not have access to debugging symbol tables.

Firstly, did you compile with -g (configure --enable-debug)?

Secondly, did you point gdb at the postgres executable when you
started it?
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 21 May 2002 16:33
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
>
> > What I cannot do is get it to show me anything useful.
>
> It sounds like gdb does not have access to debugging symbol tables.
>
> Firstly, did you compile with -g (configure --enable-debug)?

Yes, but when I read this I realised that I forget to 'make clean'
before rebuilding. Having done that I then found that gdb eats about
100Mb of memory and 50% of cpu without actually displaying itself until
killed 10 minutes later. I tried this twice - I guess that gdb under
cygwin has trouble with large exe's as my machine should handle it
(PIII-M 1.13GHz, 512Mb).

> Secondly, did you point gdb at the postgres executable when
> you started it?

Yes, I added a 60 second wait to the appropriate part of initdb (-W 60).
I could also get a stack trace which showed that I had broken in
RangeVarGetCreationNamespace as intended.

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> Yes, but when I read this I realised that I forget to 'make clean'
> before rebuilding. Having done that I then found that gdb eats about
> 100Mb of memory and 50% of cpu without actually displaying itself until
> killed 10 minutes later. I tried this twice - I guess that gdb under
> cygwin has trouble with large exe's as my machine should handle it
> (PIII-M 1.13GHz, 512Mb).

That's annoying.  gdb is quite memory-hungry when dealing with big
programs, but as long as you're not running out of memory or swap it
should work.  AFAIK anyway.  I remember having to compile only parts
of a big program with debug support, years ago on a machine that was
pretty small and slow by current standards.

If you can't get gdb to work then another possibility is the low-tech
approach: add some debugging printf's to RangeVarGetCreationNamespace.
        regards, tom lane

"Dave Page" <dpage@vale-housing.co.uk> writes:
>> Try changing the PGOPTS setting to use
>> -c search_path=pg_catalog 
>> That shouldn't make any difference but ...

> Shouldn't but does :-). Checked & double-checked, that works perfectly.

I guess your version of getopt() won't cooperate with -- switches.
I've committed this change in CVS.

I'm still interested in why explicitly saying "create view pg_catalog.foo"
didn't work ...
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 21 May 2002 20:09
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
>
> I guess your version of getopt() won't cooperate with --
> switches. I've committed this change in CVS.

Thanks.

>
> I'm still interested in why explicitly saying "create view
> pg_catalog.foo" didn't work ...

I've just been playing with this as you suggested, and using an initdb
with both 'create view foo' and 'create view pg_catalog.bar', with the
-- style switch I get (for both types of view):

namespace_search_path   = $user,public
newRelation->schemaname = null
namespaceId             = 2200 (public)

So I guess the problem is a combination of the getopt() that we've
already found, and schemaname being null in the newRelation structure.

Using the -c style switch in PGSQL_OPTS gives namespace_search_path =
pg_catalog as expected.

I am interested in learning more about this so any pointers you might
offer would be useful (I seriously doubt I'd find the fault myself
though) but I do understand that you probably have better things to do
than help me begin to understand the internals so I won't be overly
offended if you don't have time :-)

Cheers, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
>> I'm still interested in why explicitly saying "create view 
>> pg_catalog.foo" didn't work ...

> I've just been playing with this as you suggested, and using an initdb
> with both 'create view foo' and 'create view pg_catalog.bar', with the
> -- style switch I get (for both types of view): 

> namespace_search_path   = $user,public
> newRelation->schemaname = null
> namespaceId             = 2200 (public)

> So I guess the problem is a combination of the getopt() that we've
> already found, and schemaname being null in the newRelation structure. 

Given that getopt wasn't working, I'd expect namespace_search_path to be
that, and since there won't be any $user view at initdb time, public
should be the default creation target.  For "create view foo",
newRelation->schemaname *should* be null and thus public would be
selected.  But if you say "create view pg_catalog.foo" then
newRelation->schemaname should be "pg_catalog".  Can you trace it back a
little further and try to see why it's not?  It works fine here AFAICT,
so I'm wondering about portability problems ...
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 21 May 2002 20:31
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] More schema queries
>
> Can you
> trace it back a
> little further and try to see why it's not?  It works fine
> here AFAICT, so I'm wondering about portability problems ...

This week just gets wierder. I haven't a clue what I overlooked, but
there must have been something - I put initdb back with the -- switch &
pg_catalog. prefixes. Ran it, same problem as expected.

I then added various printf's right back to DefineRelation (iirc), ran
initdb again and _could_ see the schema name in every function, and, the
views were created in pg_catalog!!

Took all the printf's back out, and it still works as expected.

Oh well :-)

Thanks for your help with this Tom, if nothing else, at least I've
learnt a fair bit.

Regards, Dave.