> -----Original Message-----
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
> Sent: 21 May 2002 01:00
> To: Dave Page
> Cc: [EMAIL PROTECTED]
> Subject: Re: [HACKERS] More schema queries
>
>
> "Dave Page" <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
