Skip to content

Commit 11023eb

Browse files
bmomjianbmomjian
committed
Meanwhile, database names with single quotes in names don't work very well
at all, and because of shell quoting rules this can't be fixed, so I put in error messages to that end. Also, calling create or drop database in a transaction block is not so good either, because the file system mysteriously refuses to roll back rm calls on transaction aborts. :) So I put in checks to see if a transaction is in progress and signal an error. Also I put the whole call in a transaction of its own to be able to roll back changes to pg_database in case the file system operations fail. The alternative location issues I posted recently were untouched, awaiting the outcome of that discussion. Other than that, this should be much more fool-proof now. The docs I cleaned up as well. Peter Eisentraut Sernanders väg 10:115
1 parent 1ff0a47 commit 11023eb

File tree

4 files changed

+235
-78
lines changed

4 files changed

+235
-78
lines changed

‎doc/TODO

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -71,7 +71,7 @@ MISC
7171
* Fix btree to give a useful elog when key > 1/2 (page - overhead)
7272
* -pg_dump should preserve primary key information
7373
* plpgsql regression tests fail on BSD/OS
74-
* database names with spaces fail
74+
* -database names with spaces fail
7575
* insert of 0.0 into DECIMAL(4,4) field fails
7676

7777
ENHANCEMENTS

‎doc/src/sgml/ref/create_database.sgml

Lines changed: 94 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.10 1999/12/04 04:53:15 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.11 1999/12/12 05:15:09 momjian Exp $
33
Postgres documentation
44
-->
55

@@ -20,15 +20,15 @@ Postgres documentation
2020
</refnamediv>
2121
<refsynopsisdiv>
2222
<refsynopsisdivinfo>
23-
<date>1999-07-20</date>
23+
<date>1999-12-11</date>
2424
</refsynopsisdivinfo>
2525
<synopsis>
2626
CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATION = '<replaceable class="parameter">dbpath</replaceable>' ]
2727
</synopsis>
2828

2929
<refsect2 id="R2-SQL-CREATEDATABASE-1">
3030
<refsect2info>
31-
<date>1998-04-15</date>
31+
<date>1999-12-11</date>
3232
</refsect2info>
3333
<title>
3434
Inputs
@@ -48,7 +48,8 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
4848
<term><replaceable class="parameter">dbpath</replaceable></term>
4949
<listitem>
5050
<para>
51-
An alternate location for the new database. See below for caveats.
51+
An alternate location where to store the new database in the filesystem.
52+
See below for caveats.
5253
</para>
5354
</listitem>
5455
</varlistentry>
@@ -58,7 +59,7 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
5859

5960
<refsect2 id="R2-SQL-CREATEDATABASE-2">
6061
<refsect2info>
61-
<date>1998-04-15</date>
62+
<date>1999-12-11</date>
6263
</refsect2info>
6364
<title>
6465
Outputs
@@ -67,66 +68,122 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
6768

6869
<variablelist>
6970
<varlistentry>
70-
<term><computeroutput>
71-
CREATE DATABASE
72-
</computeroutput></term>
71+
<term><computeroutput>CREATE DATABASE</computeroutput></term>
7372
<listitem>
7473
<para>
7574
Message returned if the command completes successfully.
7675
</para>
7776
</listitem>
7877
</varlistentry>
78+
79+
<varlistentry>
80+
<term><computeroutput>ERROR: user '<replaceable class="parameter">username</replaceable>' is not allowed to create/drop databases</computeroutput></term>
81+
<listitem>
82+
<para>
83+
You must have the special CREATEDB privilege to create databases.
84+
See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
85+
</para>
86+
</listitem>
87+
</varlistentry>
88+
89+
90+
<varlistentry>
91+
<term><computeroutput>ERROR: createdb: database "<replaceable class="parameter">name</replaceable>" already exists</computeroutput></term>
92+
<listitem>
93+
<para>
94+
This occurs if a database with the <replaceable class="parameter">name</replaceable>
95+
specified already exists.
96+
</para>
97+
</listitem>
98+
</varlistentry>
99+
79100
<varlistentry>
80-
<term><computeroutput>
81-
WARN: createdb: database "<replaceable class="parameter">name</replaceable>" already exists.
82-
</computeroutput></term>
101+
<term><computeroutput>ERROR: Single quotes are not allowed in database names.</computeroutput></term>
102+
<term><computeroutput>ERROR: Single quotes are not allowed in database paths.</computeroutput></term>
83103
<listitem>
84104
<para>
85-
This occurs if <replaceable class="parameter">database</replaceable> specified already exists.
105+
The database <replaceable class="parameter">name</replaceable> and
106+
<replaceable class="parameter">dbpath</replaceable> cannot contain
107+
single quotes. This is required so that the shell commands that
108+
create the database directory can execute safely.
86109
</para>
87110
</listitem>
88111
</varlistentry>
112+
89113
<varlistentry>
90-
<term><computeroutput>
91-
ERROR: Unable to create database directory <replaceable class="parameter">directory</replaceable>
92-
</computeroutput></term>
114+
<term><computeroutput>ERROR: The path 'xxx' is invalid.</computeroutput></term>
93115
<listitem>
94116
<para>
95-
There was a problem with creating the required directory; this operation will
96-
need permissions for the <literal>postgres</literal> user on the specified location.
117+
The expansion of the specified <replaceable class="parameter">dbpath</replaceable>
118+
(see below how) failed. Check the path you entered or make sure that the
119+
environment variable you are referencing does exist.
97120
</para>
98121
</listitem>
99122
</varlistentry>
123+
124+
<varlistentry>
125+
<term><computeroutput>ERROR: createdb: May not be called in a transaction block.</computeroutput></term>
126+
<listitem>
127+
<para>
128+
If you have an explicit transaction block in progress you cannot call
129+
<command>CREATE DATABASE</command>. You must finish the transaction first.
130+
</para>
131+
</listitem>
132+
</varlistentry>
133+
134+
<varlistentry>
135+
<term><computeroutput>ERROR: Unable to create database directory 'xxx'.</computeroutput></term>
136+
<term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
137+
<listitem>
138+
<para>
139+
These are most likely related to insufficient permissions on the data
140+
directory, a full disk, or other file system problems. The user under
141+
which the database server is running, must have access to the location.
142+
</para>
143+
</listitem>
144+
</varlistentry>
145+
100146
</variablelist>
101147
</para>
102148
</refsect2>
103149
</refsynopsisdiv>
104150

105151
<refsect1 id="R1-SQL-CREATEDATABASE-1">
106152
<refsect1info>
107-
<date>1998-04-15</date>
153+
<date>1999-12-11</date>
108154
</refsect1info>
109155
<title>
110156
Description
111157
</title>
112158
<para>
113-
<command>CREATE DATABASE</command> creates a new Postgres database.
114-
The creator becomes the administrator of the new database.
159+
<command>CREATE DATABASE</command> creates a new
160+
<productname>PostgreSQL</productname> database.
161+
The creator becomes the owner of the new database.
115162
</para>
116163

117164
<para>
118-
An alternate location can be specified as either an
119-
environment variable known to the backend server
120-
(e.g. '<envar>PGDATA2</envar>') or, if the server is built to
121-
allow it, as an absolute path name
122-
(e.g. '<filename>/usr/local/pgsql/data</filename>').
123-
In either case, the location must be pre-configured
124-
by <command>initlocation</command>.
165+
An alternate location can be specified in order to,
166+
for example, store the database on a different disk.
167+
The path must have been prepared with the <xref
168+
linkend="APP-INITLOCATION" endterm="APP-INITLOCATION-title">
169+
command.
170+
</para>
171+
<para>
172+
If the path contains a slash, the leading part is interpreted
173+
as an environment variable, which must be known to the
174+
server process. This way the database administrator can
175+
exercise control over at which locations databases can be created.
176+
(A customary choice is, e.g., '<envar>PGDATA2</envar>'.)
177+
If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
178+
(not so by default), absolute path names, as identified by
179+
a leading slash
180+
(e.g. '<filename>/usr/local/pgsql/data</filename>'),
181+
are allowed as well.
125182
</para>
126183

127184
<refsect2 id="R2-SQL-CREATEDATABASE-3">
128185
<refsect2info>
129-
<date>1998-04-15</date>
186+
<date>1999-12-11</date>
130187
</refsect2info>
131188
<title>
132189
Notes
@@ -136,7 +193,11 @@ ERROR: Unable to create database directory <replaceable class="parameter">direc
136193
language extension.
137194
</para>
138195
<para>
139-
Use <command>DROP DATABASE</command> to remove a database.
196+
Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
197+
</para>
198+
<para>
199+
The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
200+
shell script wrapper around this command, provided for convenience.
140201
</para>
141202

142203
<para>
@@ -183,16 +244,16 @@ comment from Olly; response from Thomas...
183244
<prompt>$</prompt> <userinput>initlocation ~/private_db</userinput>
184245
<computeroutput>Creating Postgres database system directory /home/olly/private_db/base</computeroutput>
185246

186-
<prompt>$</prompt> <userinput>psql olly</userinput>
187-
<computeroutput>Welcome to psql, the PostgreSQL interactive terminal.
247+
<prompt>$</prompt> <userinput>psql olly</userinput>
248+
<computeroutput>Welcome to psql, the PostgreSQL interactive terminal.
188249
(Please type \copyright to see the distribution terms of PostgreSQL.)
189250

190251
Type \h for help with SQL commands,
191252
\? for help on internal slash commands,
192253
\q to quit,
193254
\g or terminate with semicolon to execute query.
194-
<prompt>olly=></prompt></computeroutput> <userinput>create database elsewhere with location = '/home/olly/private_db';</userinput>
195-
<computeroutput>CREATE DATABASE</computeroutput>
255+
<prompt>olly=></prompt></computeroutput> <userinput>CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';</userinput>
256+
<computeroutput>CREATE DATABASE</computeroutput>
196257
</programlisting>
197258
</para>
198259
</refsect1>

0 commit comments

Comments
 (0)