Re: More schema queries - Mailing list pgsql-hackers

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