Re: [HACKERS] Re: [SQL] createdb -D xxxx not working

> >> Surely you want $PGDATA2 in the latter two lines?
> > You might recall me mentioning that the whole alternative location
> > business doesn't work in the first place.

I'm not recalling the details here; what is the problem? It works for
me (but then I wrote it ;)

> Fine.  BTW, am I the only one who thinks that it's really silly for
> initlocation to expect to be given an unexpanded environment variable
> name?  That's not normal practice (either elsewhere in Postgres, or
> anywhere else that I know of).  It's confusing because it's not how
> an ordinary Unix user would expect a shell command to behave, and it
> doesn't buy any functionality that I can see.

It is not silly at all, unless you want to shoot holes in the basic
premise of the alternate location. This is described in the docs, but
the short form is:

initlocation is used to create the directory structure *with correct
permissions* for an alternate location. It takes an environment
variable because usually the backend should have that same environment
variable defined to ensure consistancy and as a security measure. The
environment variable can be expanded or not; initlocation does the
right thing in either case.

createdb takes a "-D" argument, which specifies the alternate
location. Unless allowed at compile-time, via the
ALLOW_ABSOLUTE_DBPATHS variable, all references to an alternate
location must refer to an environment variable, to give the dbadmin
control over *where* alternate locations will appear. The mechanism is
enforced by the backend, by looking for a directory delimiter in the
datpath field of pg_database, then expanding the first field as an
environment variable. On my system, with one database in an alternate
location, this table looks like:

test=# select * from pg_database; datname   | datdba | encoding |   datpath    
------------+--------+----------+--------------template1  |    100 |        0 | template1postgres   |    100 |        0
|postgresregression |    100 |        0 | regressiontest       |    100 |        0 | PGDATA2/test
 
(4 rows)

So, this works:

[postgres@golem parser]$ createdb -D PGDATA2 test2
CREATE DATABASE

But this does not (but can be enabled at compile-time if the sysadmin
wants to allow users to scatter databases everywhere (?!!)):

[postgres@golem regress]$ createdb -D /opt/postgres/data2 test2
ERROR:  The path '/opt/postgres/data2/test2' is invalid.
This may be due to a missing environment variable on the server.
createdb: Database creation failed.

> I'd vote for taking
> out the auto-expansion, so that the correct invocation becomes
>         initlocation $PGDATA2
> which is what an average user would expect.

But the average user does not necessarily have access to the PGDATA2
environment variable! This is usually a sysadmin function.

> Actually, after looking at what initlocation really does, which is
> darn near nothing, I wonder whether we shouldn't dispense with it
> altogether...

?? I hope the explanation above helps...
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California