

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


Tue, 21 May 2002 11:29:56 +0100
"Dave Page" <dpage@vale-housing.co.uk>
Re: More schema queries 
"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

Tue, 21 May 2002 09:17:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More schema queries 