| 1 |
<!-- doc/src/sgml/ddl.sgml --> |
| 2 |
|
| 3 |
<chapter id="ddl"> |
| 4 |
<title>Data Definition</title> |
| 5 |
|
| 6 |
<para> |
| 7 |
This chapter covers how one creates the database structures that |
| 8 |
will hold one's data. In a relational database, the raw data is |
| 9 |
stored in tables, so the majority of this chapter is devoted to |
| 10 |
explaining how tables are created and modified and what features are |
| 11 |
available to control what data is stored in the tables. |
| 12 |
Subsequently, we discuss how tables can be organized into |
| 13 |
schemas, and how privileges can be assigned to tables. Finally, |
| 14 |
we will briefly look at other features that affect the data storage, |
| 15 |
such as inheritance, table partitioning, views, functions, and |
| 16 |
triggers. |
| 17 |
</para> |
| 18 |
|
| 19 |
<sect1 id="ddl-basics"> |
| 20 |
<title>Table Basics</title> |
| 21 |
|
| 22 |
<indexterm zone="ddl-basics"> |
| 23 |
<primary>table</primary> |
| 24 |
</indexterm> |
| 25 |
|
| 26 |
<indexterm> |
| 27 |
<primary>row</primary> |
| 28 |
</indexterm> |
| 29 |
|
| 30 |
<indexterm> |
| 31 |
<primary>column</primary> |
| 32 |
</indexterm> |
| 33 |
|
| 34 |
<para> |
| 35 |
A table in a relational database is much like a table on paper: It |
| 36 |
consists of rows and columns. The number and order of the columns |
| 37 |
is fixed, and each column has a name. The number of rows is |
| 38 |
variable — it reflects how much data is stored at a given moment. |
| 39 |
SQL does not make any guarantees about the order of the rows in a |
| 40 |
table. When a table is read, the rows will appear in an unspecified order, |
| 41 |
unless sorting is explicitly requested. This is covered in <xref |
| 42 |
linkend="queries"/>. Furthermore, SQL does not assign unique |
| 43 |
identifiers to rows, so it is possible to have several completely |
| 44 |
identical rows in a table. This is a consequence of the |
| 45 |
mathematical model that underlies SQL but is usually not desirable. |
| 46 |
Later in this chapter we will see how to deal with this issue. |
| 47 |
</para> |
| 48 |
|
| 49 |
<para> |
| 50 |
Each column has a data type. The data type constrains the set of |
| 51 |
possible values that can be assigned to a column and assigns |
| 52 |
semantics to the data stored in the column so that it can be used |
| 53 |
for computations. For instance, a column declared to be of a |
| 54 |
numerical type will not accept arbitrary text strings, and the data |
| 55 |
stored in such a column can be used for mathematical computations. |
| 56 |
By contrast, a column declared to be of a character string type |
| 57 |
will accept almost any kind of data but it does not lend itself to |
| 58 |
mathematical calculations, although other operations such as string |
| 59 |
concatenation are available. |
| 60 |
</para> |
| 61 |
|
| 62 |
<para> |
| 63 |
<productname>PostgreSQL</productname> includes a sizable set of |
| 64 |
built-in data types that fit many applications. Users can also |
| 65 |
define their own data types. Most built-in data types have obvious |
| 66 |
names and semantics, so we defer a detailed explanation to <xref |
| 67 |
linkend="datatype"/>. Some of the frequently used data types are |
| 68 |
<type>integer</type> for whole numbers, <type>numeric</type> for |
| 69 |
possibly fractional numbers, <type>text</type> for character |
| 70 |
strings, <type>date</type> for dates, <type>time</type> for |
| 71 |
time-of-day values, and <type>timestamp</type> for values |
| 72 |
containing both date and time. |
| 73 |
</para> |
| 74 |
|
| 75 |
<indexterm> |
| 76 |
<primary>table</primary> |
| 77 |
<secondary>creating</secondary> |
| 78 |
</indexterm> |
| 79 |
|
| 80 |
<para> |
| 81 |
To create a table, you use the aptly named <xref |
| 82 |
linkend="sql-createtable"/> command. |
| 83 |
In this command you specify at least a name for the new table, the |
| 84 |
names of the columns and the data type of each column. For |
| 85 |
example: |
| 86 |
<programlisting> |
| 87 |
CREATE TABLE my_first_table ( |
| 88 |
first_column text, |
| 89 |
second_column integer |
| 90 |
); |
| 91 |
</programlisting> |
| 92 |
This creates a table named <literal>my_first_table</literal> with |
| 93 |
two columns. The first column is named |
| 94 |
<literal>first_column</literal> and has a data type of |
| 95 |
<type>text</type>; the second column has the name |
| 96 |
<literal>second_column</literal> and the type <type>integer</type>. |
| 97 |
The table and column names follow the identifier syntax explained |
| 98 |
in <xref linkend="sql-syntax-identifiers"/>. The type names are |
| 99 |
usually also identifiers, but there are some exceptions. Note that the |
| 100 |
column list is comma-separated and surrounded by parentheses. |
| 101 |
</para> |
| 102 |
|
| 103 |
<para> |
| 104 |
Of course, the previous example was heavily contrived. Normally, |
| 105 |
you would give names to your tables and columns that convey what |
| 106 |
kind of data they store. So let's look at a more realistic |
| 107 |
example: |
| 108 |
<programlisting> |
| 109 |
CREATE TABLE products ( |
| 110 |
product_no integer, |
| 111 |
name text, |
| 112 |
price numeric |
| 113 |
); |
| 114 |
</programlisting> |
| 115 |
(The <type>numeric</type> type can store fractional components, as |
| 116 |
would be typical of monetary amounts.) |
| 117 |
</para> |
| 118 |
|
| 119 |
<tip> |
| 120 |
<para> |
| 121 |
When you create many interrelated tables it is wise to choose a |
| 122 |
consistent naming pattern for the tables and columns. For |
| 123 |
instance, there is a choice of using singular or plural nouns for |
| 124 |
table names, both of which are favored by some theorist or other. |
| 125 |
</para> |
| 126 |
</tip> |
| 127 |
|
| 128 |
<para> |
| 129 |
There is a limit on how many columns a table can contain. |
| 130 |
Depending on the column types, it is between 250 and 1600. |
| 131 |
However, defining a table with anywhere near this many columns is |
| 132 |
highly unusual and often a questionable design. |
| 133 |
</para> |
| 134 |
|
| 135 |
<indexterm> |
| 136 |
<primary>table</primary> |
| 137 |
<secondary>removing</secondary> |
| 138 |
</indexterm> |
| 139 |
|
| 140 |
<para> |
| 141 |
If you no longer need a table, you can remove it using the <xref |
| 142 |
linkend="sql-droptable"/> command. |
| 143 |
For example: |
| 144 |
<programlisting> |
| 145 |
DROP TABLE my_first_table; |
| 146 |
DROP TABLE products; |
| 147 |
</programlisting> |
| 148 |
Attempting to drop a table that does not exist is an error. |
| 149 |
Nevertheless, it is common in SQL script files to unconditionally |
| 150 |
try to drop each table before creating it, ignoring any error |
| 151 |
messages, so that the script works whether or not the table exists. |
| 152 |
(If you like, you can use the <literal>DROP TABLE IF EXISTS</literal> variant |
| 153 |
to avoid the error messages, but this is not standard SQL.) |
| 154 |
</para> |
| 155 |
|
| 156 |
<para> |
| 157 |
If you need to modify a table that already exists, see <xref |
| 158 |
linkend="ddl-alter"/> later in this chapter. |
| 159 |
</para> |
| 160 |
|
| 161 |
<para> |
| 162 |
With the tools discussed so far you can create fully functional |
| 163 |
tables. The remainder of this chapter is concerned with adding |
| 164 |
features to the table definition to ensure data integrity, |
| 165 |
security, or convenience. If you are eager to fill your tables with |
| 166 |
data now you can skip ahead to <xref linkend="dml"/> and read the |
| 167 |
rest of this chapter later. |
| 168 |
</para> |
| 169 |
</sect1> |
| 170 |
|
| 171 |
<sect1 id="ddl-default"> |
| 172 |
<title>Default Values</title> |
| 173 |
|
| 174 |
<indexterm zone="ddl-default"> |
| 175 |
<primary>default value</primary> |
| 176 |
</indexterm> |
| 177 |
|
| 178 |
<para> |
| 179 |
A column can be assigned a default value. When a new row is |
| 180 |
created and no values are specified for some of the columns, those |
| 181 |
columns will be filled with their respective default values. A |
| 182 |
data manipulation command can also request explicitly that a column |
| 183 |
be set to its default value, without having to know what that value is. |
| 184 |
(Details about data manipulation commands are in <xref linkend="dml"/>.) |
| 185 |
</para> |
| 186 |
|
| 187 |
<para> |
| 188 |
<indexterm><primary>null value</primary><secondary>default value</secondary></indexterm> |
| 189 |
If no default value is declared explicitly, the default value is the |
| 190 |
null value. This usually makes sense because a null value can |
| 191 |
be considered to represent unknown data. |
| 192 |
</para> |
| 193 |
|
| 194 |
<para> |
| 195 |
In a table definition, default values are listed after the column |
| 196 |
data type. For example: |
| 197 |
<programlisting> |
| 198 |
CREATE TABLE products ( |
| 199 |
product_no integer, |
| 200 |
name text, |
| 201 |
price numeric <emphasis>DEFAULT 9.99</emphasis> |
| 202 |
); |
| 203 |
</programlisting> |
| 204 |
</para> |
| 205 |
|
| 206 |
<para> |
| 207 |
The default value can be an expression, which will be |
| 208 |
evaluated whenever the default value is inserted |
| 209 |
(<emphasis>not</emphasis> when the table is created). A common example |
| 210 |
is for a <type>timestamp</type> column to have a default of <literal>CURRENT_TIMESTAMP</literal>, |
| 211 |
so that it gets set to the time of row insertion. Another common |
| 212 |
example is generating a <quote>serial number</quote> for each row. |
| 213 |
In <productname>PostgreSQL</productname> this is typically done by |
| 214 |
something like: |
| 215 |
<programlisting> |
| 216 |
CREATE TABLE products ( |
| 217 |
product_no integer <emphasis>DEFAULT nextval('products_product_no_seq')</emphasis>, |
| 218 |
... |
| 219 |
); |
| 220 |
</programlisting> |
| 221 |
where the <literal>nextval()</literal> function supplies successive values |
| 222 |
from a <firstterm>sequence object</firstterm> (see <xref |
| 223 |
linkend="functions-sequence"/>). This arrangement is sufficiently common |
| 224 |
that there's a special shorthand for it: |
| 225 |
<programlisting> |
| 226 |
CREATE TABLE products ( |
| 227 |
product_no <emphasis>SERIAL</emphasis>, |
| 228 |
... |
| 229 |
); |
| 230 |
</programlisting> |
| 231 |
The <literal>SERIAL</literal> shorthand is discussed further in <xref |
| 232 |
linkend="datatype-serial"/>. |
| 233 |
</para> |
| 234 |
</sect1> |
| 235 |
|
| 236 |
<sect1 id="ddl-identity-columns"> |
| 237 |
<title>Identity Columns</title> |
| 238 |
|
| 239 |
<indexterm zone="ddl-identity-columns"> |
| 240 |
<primary>identity column</primary> |
| 241 |
</indexterm> |
| 242 |
|
| 243 |
<para> |
| 244 |
An identity column is a special column that is generated automatically from |
| 245 |
an implicit sequence. It can be used to generate key values. |
| 246 |
</para> |
| 247 |
|
| 248 |
<para> |
| 249 |
To create an identity column, use the <literal>GENERATED ... |
| 250 |
AS IDENTITY</literal> clause in <command>CREATE TABLE</command>, for example: |
| 251 |
<programlisting> |
| 252 |
CREATE TABLE people ( |
| 253 |
id bigint <emphasis>GENERATED ALWAYS AS IDENTITY</emphasis>, |
| 254 |
..., |
| 255 |
); |
| 256 |
</programlisting> |
| 257 |
or alternatively |
| 258 |
<programlisting> |
| 259 |
CREATE TABLE people ( |
| 260 |
id bigint <emphasis>GENERATED BY DEFAULT AS IDENTITY</emphasis>, |
| 261 |
..., |
| 262 |
); |
| 263 |
</programlisting> |
| 264 |
See <xref linkend="sql-createtable"/> for more details. |
| 265 |
</para> |
| 266 |
|
| 267 |
<para> |
| 268 |
If an <command>INSERT</command> command is executed on the table with the |
| 269 |
identity column and no value is explicitly specified for the identity |
| 270 |
column, then a value generated by the implicit sequence is inserted. For |
| 271 |
example, with the above definitions and assuming additional appropriate |
| 272 |
columns, writing |
| 273 |
<programlisting> |
| 274 |
INSERT INTO people (name, address) VALUES ('A', 'foo'); |
| 275 |
INSERT INTO people (name, address) VALUES ('B', 'bar'); |
| 276 |
</programlisting> |
| 277 |
would generate values for the <literal>id</literal> column starting at 1 |
| 278 |
and result in the following table data: |
| 279 |
<screen> |
| 280 |
id | name | address |
| 281 |
----+------+--------- |
| 282 |
1 | A | foo |
| 283 |
2 | B | bar |
| 284 |
</screen> |
| 285 |
Alternatively, the keyword <literal>DEFAULT</literal> can be specified in |
| 286 |
place of a value to explicitly request the sequence-generated value, like |
| 287 |
<programlisting> |
| 288 |
INSERT INTO people (id, name, address) VALUES (<emphasis>DEFAULT</emphasis>, 'C', 'baz'); |
| 289 |
</programlisting> |
| 290 |
Similarly, the keyword <literal>DEFAULT</literal> can be used in |
| 291 |
<command>UPDATE</command> commands. |
| 292 |
</para> |
| 293 |
|
| 294 |
<para> |
| 295 |
Thus, in many ways, an identity column behaves like a column with a default |
| 296 |
value. |
| 297 |
</para> |
| 298 |
|
| 299 |
<para> |
| 300 |
The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal> in |
| 301 |
the column definition determine how explicitly user-specified values are |
| 302 |
handled in <command>INSERT</command> and <command>UPDATE</command> |
| 303 |
commands. In an <command>INSERT</command> command, if |
| 304 |
<literal>ALWAYS</literal> is selected, a user-specified value is only |
| 305 |
accepted if the <command>INSERT</command> statement specifies |
| 306 |
<literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY |
| 307 |
DEFAULT</literal> is selected, then the user-specified value takes |
| 308 |
precedence. Thus, using <literal>BY DEFAULT</literal> results in a |
| 309 |
behavior more similar to default values, where the default value can be |
| 310 |
overridden by an explicit value, whereas <literal>ALWAYS</literal> provides |
| 311 |
some more protection against accidentally inserting an explicit value. |
| 312 |
</para> |
| 313 |
|
| 314 |
<para> |
| 315 |
The data type of an identity column must be one of the data types supported |
| 316 |
by sequences. (See <xref linkend="sql-createsequence"/>.) The properties |
| 317 |
of the associated sequence may be specified when creating an identity |
| 318 |
column (see <xref linkend="sql-createtable"/>) or changed afterwards (see |
| 319 |
<xref linkend="sql-altertable"/>). |
| 320 |
</para> |
| 321 |
|
| 322 |
<para> |
| 323 |
An identity column is automatically marked as <literal>NOT NULL</literal>. |
| 324 |
An identity column, however, does not guarantee uniqueness. (A sequence |
| 325 |
normally returns unique values, but a sequence could be reset, or values |
| 326 |
could be inserted manually into the identity column, as discussed above.) |
| 327 |
Uniqueness would need to be enforced using a <literal>PRIMARY KEY</literal> |
| 328 |
or <literal>UNIQUE</literal> constraint. |
| 329 |
</para> |
| 330 |
|
| 331 |
<para> |
| 332 |
In table inheritance hierarchies, identity columns and their properties in |
| 333 |
a child table are independent of those in its parent tables. A child table |
| 334 |
does not inherit identity columns or their properties automatically from |
| 335 |
the parent. During <command>INSERT</command> or <command>UPDATE</command>, |
| 336 |
a column is treated as an identity column if that column is an identity |
| 337 |
column in the table named in the statement, and the corresponding identity |
| 338 |
properties are applied. |
| 339 |
</para> |
| 340 |
|
| 341 |
<para> |
| 342 |
Partitions inherit identity columns from the partitioned table. They |
| 343 |
cannot have their own identity columns. The properties of a given identity |
| 344 |
column are consistent across all the partitions in the partition hierarchy. |
| 345 |
</para> |
| 346 |
</sect1> |
| 347 |
|
| 348 |
<sect1 id="ddl-generated-columns"> |
| 349 |
<title>Generated Columns</title> |
| 350 |
|
| 351 |
<indexterm zone="ddl-generated-columns"> |
| 352 |
<primary>generated column</primary> |
| 353 |
</indexterm> |
| 354 |
|
| 355 |
<para> |
| 356 |
A generated column is a special column that is always computed from other |
| 357 |
columns. Thus, it is for columns what a view is for tables. There are two |
| 358 |
kinds of generated columns: stored and virtual. A stored generated column |
| 359 |
is computed when it is written (inserted or updated) and occupies storage |
| 360 |
as if it were a normal column. A virtual generated column occupies no |
| 361 |
storage and is computed when it is read. Thus, a virtual generated column |
| 362 |
is similar to a view and a stored generated column is similar to a |
| 363 |
materialized view (except that it is always updated automatically). |
| 364 |
</para> |
| 365 |
|
| 366 |
<para> |
| 367 |
To create a generated column, use the <literal>GENERATED ALWAYS |
| 368 |
AS</literal> clause in <command>CREATE TABLE</command>, for example: |
| 369 |
<programlisting> |
| 370 |
CREATE TABLE people ( |
| 371 |
..., |
| 372 |
height_cm numeric, |
| 373 |
height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54)</emphasis> |
| 374 |
); |
| 375 |
</programlisting> |
| 376 |
A generated column is by default of the virtual kind. Use the keywords |
| 377 |
<literal>VIRTUAL</literal> or <literal>STORED</literal> to make the choice |
| 378 |
explicit. See <xref linkend="sql-createtable"/> for more details. |
| 379 |
</para> |
| 380 |
|
| 381 |
<para> |
| 382 |
A generated column cannot be written to directly. In |
| 383 |
<command>INSERT</command> or <command>UPDATE</command> commands, a value |
| 384 |
cannot be specified for a generated column, but the keyword |
| 385 |
<literal>DEFAULT</literal> may be specified. |
| 386 |
</para> |
| 387 |
|
| 388 |
<para> |
| 389 |
Consider the differences between a column with a default and a generated |
| 390 |
column. The column default is evaluated once when the row is first |
| 391 |
inserted if no other value was provided; a generated column is updated |
| 392 |
whenever the row changes and cannot be overridden. A column default may |
| 393 |
not refer to other columns of the table; a generation expression would |
| 394 |
normally do so. A column default can use volatile functions, for example |
| 395 |
<literal>random()</literal> or functions referring to the current time; |
| 396 |
this is not allowed for generated columns. |
| 397 |
</para> |
| 398 |
|
| 399 |
<para> |
| 400 |
Several restrictions apply to the definition of generated columns and |
| 401 |
tables involving generated columns: |
| 402 |
|
| 403 |
<itemizedlist> |
| 404 |
<listitem> |
| 405 |
<para> |
| 406 |
The generation expression can only use immutable functions and cannot |
| 407 |
use subqueries or reference anything other than the current row in any |
| 408 |
way. |
| 409 |
</para> |
| 410 |
</listitem> |
| 411 |
<listitem> |
| 412 |
<para> |
| 413 |
A generation expression cannot reference another generated column. |
| 414 |
</para> |
| 415 |
</listitem> |
| 416 |
<listitem> |
| 417 |
<para> |
| 418 |
A generation expression cannot reference a system column, except |
| 419 |
<varname>tableoid</varname>. |
| 420 |
</para> |
| 421 |
</listitem> |
| 422 |
<listitem> |
| 423 |
<para> |
| 424 |
A virtual generated column cannot have a user-defined type, and the |
| 425 |
generation expression of a virtual generated column must not reference |
| 426 |
user-defined functions or types, that is, it can only use built-in |
| 427 |
functions or types. This applies also indirectly, such as for functions |
| 428 |
or types that underlie operators or casts. (This restriction does not |
| 429 |
exist for stored generated columns.) |
| 430 |
</para> |
| 431 |
</listitem> |
| 432 |
<listitem> |
| 433 |
<para> |
| 434 |
A generated column cannot have a column default or an identity definition. |
| 435 |
</para> |
| 436 |
</listitem> |
| 437 |
<listitem> |
| 438 |
<para> |
| 439 |
A generated column cannot be part of a partition key. |
| 440 |
</para> |
| 441 |
</listitem> |
| 442 |
<listitem> |
| 443 |
<para> |
| 444 |
Foreign tables can have generated columns. See <xref |
| 445 |
linkend="sql-createforeigntable"/> for details. |
| 446 |
</para> |
| 447 |
</listitem> |
| 448 |
<listitem> |
| 449 |
<para>For inheritance and partitioning:</para> |
| 450 |
<itemizedlist> |
| 451 |
<listitem> |
| 452 |
<para> |
| 453 |
If a parent column is a generated column, its child column must also |
| 454 |
be a generated column of the same kind (stored or virtual); however, |
| 455 |
the child column can have a different generation expression. |
| 456 |
</para> |
| 457 |
|
| 458 |
<para> |
| 459 |
For stored generated columns, the generation expression that is |
| 460 |
actually applied during insert or update of a row is the one |
| 461 |
associated with the table that the row is physically in. (This is |
| 462 |
unlike the behavior for column defaults: for those, the default value |
| 463 |
associated with the table named in the query applies.) For virtual |
| 464 |
generated columns, the generation expression of the table named in the |
| 465 |
query applies when a table is read. |
| 466 |
</para> |
| 467 |
</listitem> |
| 468 |
<listitem> |
| 469 |
<para> |
| 470 |
If a parent column is not a generated column, its child column must |
| 471 |
not be generated either. |
| 472 |
</para> |
| 473 |
</listitem> |
| 474 |
<listitem> |
| 475 |
<para> |
| 476 |
For inherited tables, if you write a child column definition without |
| 477 |
any <literal>GENERATED</literal> clause in <command>CREATE TABLE |
| 478 |
... INHERITS</command>, then its <literal>GENERATED</literal> clause |
| 479 |
will automatically be copied from the parent. <command>ALTER TABLE |
| 480 |
... INHERIT</command> will insist that parent and child columns |
| 481 |
already match as to generation status, but it will not require their |
| 482 |
generation expressions to match. |
| 483 |
</para> |
| 484 |
</listitem> |
| 485 |
<listitem> |
| 486 |
<para> |
| 487 |
Similarly for partitioned tables, if you write a child column |
| 488 |
definition without any <literal>GENERATED</literal> clause |
| 489 |
in <command>CREATE TABLE ... PARTITION OF</command>, then |
| 490 |
its <literal>GENERATED</literal> clause will automatically be copied |
| 491 |
from the parent. <command>ALTER TABLE ... ATTACH PARTITION</command> |
| 492 |
will insist that parent and child columns already match as to |
| 493 |
generation status, but it will not require their generation |
| 494 |
expressions to match. |
| 495 |
</para> |
| 496 |
</listitem> |
| 497 |
<listitem> |
| 498 |
<para> |
| 499 |
In case of multiple inheritance, if one parent column is a generated |
| 500 |
column, then all parent columns must be generated columns. If they |
| 501 |
do not all have the same generation expression, then the desired |
| 502 |
expression for the child must be specified explicitly. |
| 503 |
</para> |
| 504 |
</listitem> |
| 505 |
</itemizedlist> |
| 506 |
</listitem> |
| 507 |
</itemizedlist> |
| 508 |
</para> |
| 509 |
|
| 510 |
<para> |
| 511 |
Additional considerations apply to the use of generated columns. |
| 512 |
<itemizedlist> |
| 513 |
<listitem> |
| 514 |
<para> |
| 515 |
Generated columns maintain access privileges separately from their |
| 516 |
underlying base columns. So, it is possible to arrange it so that a |
| 517 |
particular role can read from a generated column but not from the |
| 518 |
underlying base columns. |
| 519 |
</para> |
| 520 |
|
| 521 |
<para> |
| 522 |
For virtual generated columns, this is only fully secure if the |
| 523 |
generation expression uses only leakproof functions (see <xref |
| 524 |
linkend="sql-createfunction"/>), but this is not enforced by the system. |
| 525 |
</para> |
| 526 |
</listitem> |
| 527 |
<listitem> |
| 528 |
<para> |
| 529 |
Privileges of functions used in generation expressions are checked when |
| 530 |
the expression is actually executed, on write or read respectively, as |
| 531 |
if the generation expression had been called directly from the query |
| 532 |
using the generated column. The user of a generated column must have |
| 533 |
permissions to call all functions used by the generation expression. |
| 534 |
Functions in the generation expression are executed with the privileges |
| 535 |
of the user executing the query or the function owner, depending on |
| 536 |
whether the functions are defined as <literal>SECURITY INVOKER</literal> |
| 537 |
or <literal>SECURITY DEFINER</literal>. |
| 538 |
<!-- matches create_view.sgml --> |
| 539 |
</para> |
| 540 |
</listitem> |
| 541 |
<listitem> |
| 542 |
<para> |
| 543 |
Generated columns are, conceptually, updated after |
| 544 |
<literal>BEFORE</literal> triggers have run. Therefore, changes made to |
| 545 |
base columns in a <literal>BEFORE</literal> trigger will be reflected in |
| 546 |
generated columns. But conversely, it is not allowed to access |
| 547 |
generated columns in <literal>BEFORE</literal> triggers. |
| 548 |
</para> |
| 549 |
</listitem> |
| 550 |
<listitem> |
| 551 |
<para> |
| 552 |
Generated columns are allowed to be replicated during logical replication |
| 553 |
according to the <command>CREATE PUBLICATION</command> parameter |
| 554 |
<link linkend="sql-createpublication-params-with-publish-generated-columns"> |
| 555 |
<literal>publish_generated_columns</literal></link> or by including them |
| 556 |
in the column list of the <command>CREATE PUBLICATION</command> command. |
| 557 |
This is currently only supported for stored generated columns. |
| 558 |
See <xref linkend="logical-replication-gencols"/> for details. |
| 559 |
</para> |
| 560 |
</listitem> |
| 561 |
</itemizedlist> |
| 562 |
</para> |
| 563 |
</sect1> |
| 564 |
|
| 565 |
<sect1 id="ddl-constraints"> |
| 566 |
<title>Constraints</title> |
| 567 |
|
| 568 |
<indexterm zone="ddl-constraints"> |
| 569 |
<primary>constraint</primary> |
| 570 |
</indexterm> |
| 571 |
|
| 572 |
<para> |
| 573 |
Data types are a way to limit the kind of data that can be stored |
| 574 |
in a table. For many applications, however, the constraint they |
| 575 |
provide is too coarse. For example, a column containing a product |
| 576 |
price should probably only accept positive values. But there is no |
| 577 |
standard data type that accepts only positive numbers. Another issue is |
| 578 |
that you might want to constrain column data with respect to other |
| 579 |
columns or rows. For example, in a table containing product |
| 580 |
information, there should be only one row for each product number. |
| 581 |
</para> |
| 582 |
|
| 583 |
<para> |
| 584 |
To that end, SQL allows you to define constraints on columns and |
| 585 |
tables. Constraints give you as much control over the data in your |
| 586 |
tables as you wish. If a user attempts to store data in a column |
| 587 |
that would violate a constraint, an error is raised. This applies |
| 588 |
even if the value came from the default value definition. |
| 589 |
</para> |
| 590 |
|
| 591 |
<sect2 id="ddl-constraints-check-constraints"> |
| 592 |
<title>Check Constraints</title> |
| 593 |
|
| 594 |
<indexterm> |
| 595 |
<primary>check constraint</primary> |
| 596 |
</indexterm> |
| 597 |
|
| 598 |
<indexterm> |
| 599 |
<primary>constraint</primary> |
| 600 |
<secondary>check</secondary> |
| 601 |
</indexterm> |
| 602 |
|
| 603 |
<para> |
| 604 |
A check constraint is the most generic constraint type. It allows |
| 605 |
you to specify that the value in a certain column must satisfy a |
| 606 |
Boolean (truth-value) expression. For instance, to require positive |
| 607 |
product prices, you could use: |
| 608 |
<programlisting> |
| 609 |
CREATE TABLE products ( |
| 610 |
product_no integer, |
| 611 |
name text, |
| 612 |
price numeric <emphasis>CHECK (price > 0)</emphasis> |
| 613 |
); |
| 614 |
</programlisting> |
| 615 |
</para> |
| 616 |
|
| 617 |
<para> |
| 618 |
As you see, the constraint definition comes after the data type, |
| 619 |
just like default value definitions. Default values and |
| 620 |
constraints can be listed in any order. A check constraint |
| 621 |
consists of the key word <literal>CHECK</literal> followed by an |
| 622 |
expression in parentheses. The check constraint expression should |
| 623 |
involve the column thus constrained, otherwise the constraint |
| 624 |
would not make too much sense. |
| 625 |
</para> |
| 626 |
|
| 627 |
<indexterm> |
| 628 |
<primary>constraint</primary> |
| 629 |
<secondary>name</secondary> |
| 630 |
</indexterm> |
| 631 |
|
| 632 |
<para> |
| 633 |
You can also give the constraint a separate name. This clarifies |
| 634 |
error messages and allows you to refer to the constraint when you |
| 635 |
need to change it. The syntax is: |
| 636 |
<programlisting> |
| 637 |
CREATE TABLE products ( |
| 638 |
product_no integer, |
| 639 |
name text, |
| 640 |
price numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (price > 0) |
| 641 |
); |
| 642 |
</programlisting> |
| 643 |
So, to specify a named constraint, use the key word |
| 644 |
<literal>CONSTRAINT</literal> followed by an identifier followed |
| 645 |
by the constraint definition. (If you don't specify a constraint |
| 646 |
name in this way, the system chooses a name for you.) |
| 647 |
</para> |
| 648 |
|
| 649 |
<para> |
| 650 |
A check constraint can also refer to several columns. Say you |
| 651 |
store a regular price and a discounted price, and you want to |
| 652 |
ensure that the discounted price is lower than the regular price: |
| 653 |
<programlisting> |
| 654 |
CREATE TABLE products ( |
| 655 |
product_no integer, |
| 656 |
name text, |
| 657 |
price numeric CHECK (price > 0), |
| 658 |
discounted_price numeric CHECK (discounted_price > 0), |
| 659 |
<emphasis>CHECK (price > discounted_price)</emphasis> |
| 660 |
); |
| 661 |
</programlisting> |
| 662 |
</para> |
| 663 |
|
| 664 |
<para> |
| 665 |
The first two constraints should look familiar. The third one |
| 666 |
uses a new syntax. It is not attached to a particular column, |
| 667 |
instead it appears as a separate item in the comma-separated |
| 668 |
column list. Column definitions and these constraint |
| 669 |
definitions can be listed in mixed order. |
| 670 |
</para> |
| 671 |
|
| 672 |
<para> |
| 673 |
We say that the first two constraints are column constraints, whereas the |
| 674 |
third one is a table constraint because it is written separately |
| 675 |
from any one column definition. Column constraints can also be |
| 676 |
written as table constraints, while the reverse is not necessarily |
| 677 |
possible, since a column constraint is supposed to refer to only the |
| 678 |
column it is attached to. (<productname>PostgreSQL</productname> doesn't |
| 679 |
enforce that rule, but you should follow it if you want your table |
| 680 |
definitions to work with other database systems.) The above example could |
| 681 |
also be written as: |
| 682 |
<programlisting> |
| 683 |
CREATE TABLE products ( |
| 684 |
product_no integer, |
| 685 |
name text, |
| 686 |
price numeric, |
| 687 |
CHECK (price > 0), |
| 688 |
discounted_price numeric, |
| 689 |
CHECK (discounted_price > 0), |
| 690 |
CHECK (price > discounted_price) |
| 691 |
); |
| 692 |
</programlisting> |
| 693 |
or even: |
| 694 |
<programlisting> |
| 695 |
CREATE TABLE products ( |
| 696 |
product_no integer, |
| 697 |
name text, |
| 698 |
price numeric CHECK (price > 0), |
| 699 |
discounted_price numeric, |
| 700 |
CHECK (discounted_price > 0 AND price > discounted_price) |
| 701 |
); |
| 702 |
</programlisting> |
| 703 |
It's a matter of taste. |
| 704 |
</para> |
| 705 |
|
| 706 |
<para> |
| 707 |
Names can be assigned to table constraints in the same way as |
| 708 |
column constraints: |
| 709 |
<programlisting> |
| 710 |
CREATE TABLE products ( |
| 711 |
product_no integer, |
| 712 |
name text, |
| 713 |
price numeric, |
| 714 |
CHECK (price > 0), |
| 715 |
discounted_price numeric, |
| 716 |
CHECK (discounted_price > 0), |
| 717 |
<emphasis>CONSTRAINT valid_discount</emphasis> CHECK (price > discounted_price) |
| 718 |
); |
| 719 |
</programlisting> |
| 720 |
</para> |
| 721 |
|
| 722 |
<indexterm> |
| 723 |
<primary>null value</primary> |
| 724 |
<secondary sortas="check constraints">with check constraints</secondary> |
| 725 |
</indexterm> |
| 726 |
|
| 727 |
<para> |
| 728 |
It should be noted that a check constraint is satisfied if the |
| 729 |
check expression evaluates to true or the null value. Since most |
| 730 |
expressions will evaluate to the null value if any operand is null, |
| 731 |
they will not prevent null values in the constrained columns. To |
| 732 |
ensure that a column does not contain null values, the not-null |
| 733 |
constraint described in the next section can be used. |
| 734 |
</para> |
| 735 |
|
| 736 |
<note> |
| 737 |
<para> |
| 738 |
<productname>PostgreSQL</productname> does not support |
| 739 |
<literal>CHECK</literal> constraints that reference table data other than |
| 740 |
the new or updated row being checked. While a <literal>CHECK</literal> |
| 741 |
constraint that violates this rule may appear to work in simple |
| 742 |
tests, it cannot guarantee that the database will not reach a state |
| 743 |
in which the constraint condition is false (due to subsequent changes |
| 744 |
of the other row(s) involved). This would cause a database dump and |
| 745 |
restore to fail. The restore could fail even when the complete |
| 746 |
database state is consistent with the constraint, due to rows not |
| 747 |
being loaded in an order that will satisfy the constraint. If |
| 748 |
possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>, |
| 749 |
or <literal>FOREIGN KEY</literal> constraints to express |
| 750 |
cross-row and cross-table restrictions. |
| 751 |
</para> |
| 752 |
|
| 753 |
<para> |
| 754 |
If what you desire is a one-time check against other rows at row |
| 755 |
insertion, rather than a continuously-maintained consistency |
| 756 |
guarantee, a custom <link linkend="triggers">trigger</link> can be used |
| 757 |
to implement that. (This approach avoids the dump/restore problem because |
| 758 |
<application>pg_dump</application> does not reinstall triggers until after |
| 759 |
restoring data, so that the check will not be enforced during a |
| 760 |
dump/restore.) |
| 761 |
</para> |
| 762 |
</note> |
| 763 |
|
| 764 |
<note> |
| 765 |
<para> |
| 766 |
<productname>PostgreSQL</productname> assumes that |
| 767 |
<literal>CHECK</literal> constraints' conditions are immutable, that |
| 768 |
is, they will always give the same result for the same input row. |
| 769 |
This assumption is what justifies examining <literal>CHECK</literal> |
| 770 |
constraints only when rows are inserted or updated, and not at other |
| 771 |
times. (The warning above about not referencing other table data is |
| 772 |
really a special case of this restriction.) |
| 773 |
</para> |
| 774 |
|
| 775 |
<para> |
| 776 |
An example of a common way to break this assumption is to reference a |
| 777 |
user-defined function in a <literal>CHECK</literal> expression, and |
| 778 |
then change the behavior of that |
| 779 |
function. <productname>PostgreSQL</productname> does not disallow |
| 780 |
that, but it will not notice if there are rows in the table that now |
| 781 |
violate the <literal>CHECK</literal> constraint. That would cause a |
| 782 |
subsequent database dump and restore to fail. |
| 783 |
The recommended way to handle such a change is to drop the constraint |
| 784 |
(using <command>ALTER TABLE</command>), adjust the function definition, |
| 785 |
and re-add the constraint, thereby rechecking it against all table rows. |
| 786 |
</para> |
| 787 |
</note> |
| 788 |
</sect2> |
| 789 |
|
| 790 |
<sect2 id="ddl-constraints-not-null"> |
| 791 |
<title>Not-Null Constraints</title> |
| 792 |
|
| 793 |
<indexterm> |
| 794 |
<primary>not-null constraint</primary> |
| 795 |
</indexterm> |
| 796 |
|
| 797 |
<indexterm> |
| 798 |
<primary>constraint</primary> |
| 799 |
<secondary>NOT NULL</secondary> |
| 800 |
</indexterm> |
| 801 |
|
| 802 |
<para> |
| 803 |
A not-null constraint simply specifies that a column must not |
| 804 |
assume the null value. A syntax example: |
| 805 |
<programlisting> |
| 806 |
CREATE TABLE products ( |
| 807 |
product_no integer <emphasis>NOT NULL</emphasis>, |
| 808 |
name text <emphasis>NOT NULL</emphasis>, |
| 809 |
price numeric |
| 810 |
); |
| 811 |
</programlisting> |
| 812 |
An explicit constraint name can also be specified, for example: |
| 813 |
<programlisting> |
| 814 |
CREATE TABLE products ( |
| 815 |
product_no integer NOT NULL, |
| 816 |
name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL, |
| 817 |
price numeric |
| 818 |
); |
| 819 |
</programlisting> |
| 820 |
</para> |
| 821 |
|
| 822 |
<para> |
| 823 |
A not-null constraint is usually written as a column constraint. The |
| 824 |
syntax for writing it as a table constraint is |
| 825 |
<programlisting> |
| 826 |
CREATE TABLE products ( |
| 827 |
product_no integer, |
| 828 |
name text, |
| 829 |
price numeric, |
| 830 |
<emphasis>NOT NULL product_no</emphasis>, |
| 831 |
<emphasis>NOT NULL name</emphasis> |
| 832 |
); |
| 833 |
</programlisting> |
| 834 |
But this syntax is not standard and mainly intended for use by |
| 835 |
<application>pg_dump</application>. |
| 836 |
</para> |
| 837 |
|
| 838 |
<para> |
| 839 |
A not-null constraint is functionally equivalent to creating a check |
| 840 |
constraint <literal>CHECK (<replaceable>column_name</replaceable> |
| 841 |
IS NOT NULL)</literal>, but in |
| 842 |
<productname>PostgreSQL</productname> creating an explicit |
| 843 |
not-null constraint is more efficient. |
| 844 |
</para> |
| 845 |
|
| 846 |
<para> |
| 847 |
Of course, a column can have more than one constraint. Just write |
| 848 |
the constraints one after another: |
| 849 |
<programlisting> |
| 850 |
CREATE TABLE products ( |
| 851 |
product_no integer NOT NULL, |
| 852 |
name text NOT NULL, |
| 853 |
price numeric NOT NULL CHECK (price > 0) |
| 854 |
); |
| 855 |
</programlisting> |
| 856 |
The order doesn't matter. It does not necessarily determine in which |
| 857 |
order the constraints are checked. |
| 858 |
</para> |
| 859 |
|
| 860 |
<para> |
| 861 |
However, a column can have at most one explicit not-null constraint. |
| 862 |
</para> |
| 863 |
|
| 864 |
<para> |
| 865 |
The <literal>NOT NULL</literal> constraint has an inverse: the |
| 866 |
<literal>NULL</literal> constraint. This does not mean that the |
| 867 |
column must be null, which would surely be useless. Instead, this |
| 868 |
simply selects the default behavior that the column might be null. |
| 869 |
The <literal>NULL</literal> constraint is not present in the SQL |
| 870 |
standard and should not be used in portable applications. (It was |
| 871 |
only added to <productname>PostgreSQL</productname> to be |
| 872 |
compatible with some other database systems.) Some users, however, |
| 873 |
like it because it makes it easy to toggle the constraint in a |
| 874 |
script file. For example, you could start with: |
| 875 |
<programlisting> |
| 876 |
CREATE TABLE products ( |
| 877 |
product_no integer NULL, |
| 878 |
name text NULL, |
| 879 |
price numeric NULL |
| 880 |
); |
| 881 |
</programlisting> |
| 882 |
and then insert the <literal>NOT</literal> key word where desired. |
| 883 |
</para> |
| 884 |
|
| 885 |
<tip> |
| 886 |
<para> |
| 887 |
In most database designs the majority of columns should be marked |
| 888 |
not null. |
| 889 |
</para> |
| 890 |
</tip> |
| 891 |
</sect2> |
| 892 |
|
| 893 |
<sect2 id="ddl-constraints-unique-constraints"> |
| 894 |
<title>Unique Constraints</title> |
| 895 |
|
| 896 |
<indexterm> |
| 897 |
<primary>unique constraint</primary> |
| 898 |
</indexterm> |
| 899 |
|
| 900 |
<indexterm> |
| 901 |
<primary>constraint</primary> |
| 902 |
<secondary>unique</secondary> |
| 903 |
</indexterm> |
| 904 |
|
| 905 |
<para> |
| 906 |
Unique constraints ensure that the data contained in a column, or a |
| 907 |
group of columns, is unique among all the rows in the |
| 908 |
table. The syntax is: |
| 909 |
<programlisting> |
| 910 |
CREATE TABLE products ( |
| 911 |
product_no integer <emphasis>UNIQUE</emphasis>, |
| 912 |
name text, |
| 913 |
price numeric |
| 914 |
); |
| 915 |
</programlisting> |
| 916 |
when written as a column constraint, and: |
| 917 |
<programlisting> |
| 918 |
CREATE TABLE products ( |
| 919 |
product_no integer, |
| 920 |
name text, |
| 921 |
price numeric, |
| 922 |
<emphasis>UNIQUE (product_no)</emphasis> |
| 923 |
); |
| 924 |
</programlisting> |
| 925 |
when written as a table constraint. |
| 926 |
</para> |
| 927 |
|
| 928 |
<para> |
| 929 |
To define a unique constraint for a group of columns, write it as a |
| 930 |
table constraint with the column names separated by commas: |
| 931 |
<programlisting> |
| 932 |
CREATE TABLE example ( |
| 933 |
a integer, |
| 934 |
b integer, |
| 935 |
c integer, |
| 936 |
<emphasis>UNIQUE (a, c)</emphasis> |
| 937 |
); |
| 938 |
</programlisting> |
| 939 |
This specifies that the combination of values in the indicated columns |
| 940 |
is unique across the whole table, though any one of the columns |
| 941 |
need not be (and ordinarily isn't) unique. |
| 942 |
</para> |
| 943 |
|
| 944 |
<para> |
| 945 |
You can assign your own name for a unique constraint, in the usual way: |
| 946 |
<programlisting> |
| 947 |
CREATE TABLE products ( |
| 948 |
product_no integer <emphasis>CONSTRAINT must_be_different</emphasis> UNIQUE, |
| 949 |
name text, |
| 950 |
price numeric |
| 951 |
); |
| 952 |
</programlisting> |
| 953 |
</para> |
| 954 |
|
| 955 |
<para> |
| 956 |
Adding a unique constraint will automatically create a unique B-tree |
| 957 |
index on the column or group of columns listed in the constraint. |
| 958 |
A uniqueness restriction covering only some rows cannot be written as |
| 959 |
a unique constraint, but it is possible to enforce such a restriction by |
| 960 |
creating a unique <link linkend="indexes-partial">partial index</link>. |
| 961 |
</para> |
| 962 |
|
| 963 |
<indexterm> |
| 964 |
<primary>null value</primary> |
| 965 |
<secondary sortas="unique constraints">with unique constraints</secondary> |
| 966 |
</indexterm> |
| 967 |
|
| 968 |
<para> |
| 969 |
In general, a unique constraint is violated if there is more than |
| 970 |
one row in the table where the values of all of the |
| 971 |
columns included in the constraint are equal. |
| 972 |
By default, two null values are not considered equal in this |
| 973 |
comparison. That means even in the presence of a |
| 974 |
unique constraint it is possible to store duplicate |
| 975 |
rows that contain a null value in at least one of the constrained |
| 976 |
columns. This behavior can be changed by adding the clause <literal>NULLS |
| 977 |
NOT DISTINCT</literal>, like |
| 978 |
<programlisting> |
| 979 |
CREATE TABLE products ( |
| 980 |
product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>, |
| 981 |
name text, |
| 982 |
price numeric |
| 983 |
); |
| 984 |
</programlisting> |
| 985 |
or |
| 986 |
<programlisting> |
| 987 |
CREATE TABLE products ( |
| 988 |
product_no integer, |
| 989 |
name text, |
| 990 |
price numeric, |
| 991 |
UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no) |
| 992 |
); |
| 993 |
</programlisting> |
| 994 |
The default behavior can be specified explicitly using <literal>NULLS |
| 995 |
DISTINCT</literal>. The default null treatment in unique constraints is |
| 996 |
implementation-defined according to the SQL standard, and other |
| 997 |
implementations have a different behavior. So be careful when developing |
| 998 |
applications that are intended to be portable. |
| 999 |
</para> |
| 1000 |
</sect2> |
| 1001 |
|
| 1002 |
<sect2 id="ddl-constraints-primary-keys"> |
| 1003 |
<title>Primary Keys</title> |
| 1004 |
|
| 1005 |
<indexterm> |
| 1006 |
<primary>primary key</primary> |
| 1007 |
</indexterm> |
| 1008 |
|
| 1009 |
<indexterm> |
| 1010 |
<primary>constraint</primary> |
| 1011 |
<secondary>primary key</secondary> |
| 1012 |
</indexterm> |
| 1013 |
|
| 1014 |
<para> |
| 1015 |
A primary key constraint indicates that a column, or group of columns, |
| 1016 |
can be used as a unique identifier for rows in the table. This |
| 1017 |
requires that the values be both unique and not null. So, the following |
| 1018 |
two table definitions accept the same data: |
| 1019 |
<programlisting> |
| 1020 |
CREATE TABLE products ( |
| 1021 |
product_no integer UNIQUE NOT NULL, |
| 1022 |
name text, |
| 1023 |
price numeric |
| 1024 |
); |
| 1025 |
</programlisting> |
| 1026 |
|
| 1027 |
<programlisting> |
| 1028 |
CREATE TABLE products ( |
| 1029 |
product_no integer <emphasis>PRIMARY KEY</emphasis>, |
| 1030 |
name text, |
| 1031 |
price numeric |
| 1032 |
); |
| 1033 |
</programlisting> |
| 1034 |
</para> |
| 1035 |
|
| 1036 |
<para> |
| 1037 |
Primary keys can span more than one column; the syntax |
| 1038 |
is similar to unique constraints: |
| 1039 |
<programlisting> |
| 1040 |
CREATE TABLE example ( |
| 1041 |
a integer, |
| 1042 |
b integer, |
| 1043 |
c integer, |
| 1044 |
<emphasis>PRIMARY KEY (a, c)</emphasis> |
| 1045 |
); |
| 1046 |
</programlisting> |
| 1047 |
</para> |
| 1048 |
|
| 1049 |
<para> |
| 1050 |
Adding a primary key will automatically create a unique B-tree index |
| 1051 |
on the column or group of columns listed in the primary key, and will |
| 1052 |
force the column(s) to be marked <literal>NOT NULL</literal>. |
| 1053 |
</para> |
| 1054 |
|
| 1055 |
<para> |
| 1056 |
A table can have at most one primary key. (There can be any number |
| 1057 |
of unique constraints, which combined with not-null constraints are functionally almost the |
| 1058 |
same thing, but only one can be identified as the primary key.) |
| 1059 |
Relational database theory |
| 1060 |
dictates that every table must have a primary key. This rule is |
| 1061 |
not enforced by <productname>PostgreSQL</productname>, but it is |
| 1062 |
usually best to follow it. |
| 1063 |
</para> |
| 1064 |
|
| 1065 |
<para> |
| 1066 |
Primary keys are useful both for |
| 1067 |
documentation purposes and for client applications. For example, |
| 1068 |
a GUI application that allows modifying row values probably needs |
| 1069 |
to know the primary key of a table to be able to identify rows |
| 1070 |
uniquely. There are also various ways in which the database system |
| 1071 |
makes use of a primary key if one has been declared; for example, |
| 1072 |
the primary key defines the default target column(s) for foreign keys |
| 1073 |
referencing its table. |
| 1074 |
</para> |
| 1075 |
</sect2> |
| 1076 |
|
| 1077 |
<sect2 id="ddl-constraints-fk"> |
| 1078 |
<title>Foreign Keys</title> |
| 1079 |
|
| 1080 |
<indexterm> |
| 1081 |
<primary>foreign key</primary> |
| 1082 |
</indexterm> |
| 1083 |
|
| 1084 |
<indexterm> |
| 1085 |
<primary>constraint</primary> |
| 1086 |
<secondary>foreign key</secondary> |
| 1087 |
</indexterm> |
| 1088 |
|
| 1089 |
<indexterm> |
| 1090 |
<primary>referential integrity</primary> |
| 1091 |
</indexterm> |
| 1092 |
|
| 1093 |
<para> |
| 1094 |
A foreign key constraint specifies that the values in a column (or |
| 1095 |
a group of columns) must match the values appearing in some row |
| 1096 |
of another table. |
| 1097 |
We say this maintains the <firstterm>referential |
| 1098 |
integrity</firstterm> between two related tables. |
| 1099 |
</para> |
| 1100 |
|
| 1101 |
<para> |
| 1102 |
Say you have the product table that we have used several times already: |
| 1103 |
<programlisting> |
| 1104 |
CREATE TABLE products ( |
| 1105 |
product_no integer PRIMARY KEY, |
| 1106 |
name text, |
| 1107 |
price numeric |
| 1108 |
); |
| 1109 |
</programlisting> |
| 1110 |
Let's also assume you have a table storing orders of those |
| 1111 |
products. We want to ensure that the orders table only contains |
| 1112 |
orders of products that actually exist. So we define a foreign |
| 1113 |
key constraint in the orders table that references the products |
| 1114 |
table: |
| 1115 |
<programlisting> |
| 1116 |
CREATE TABLE orders ( |
| 1117 |
order_id integer PRIMARY KEY, |
| 1118 |
product_no integer <emphasis>REFERENCES products (product_no)</emphasis>, |
| 1119 |
quantity integer |
| 1120 |
); |
| 1121 |
</programlisting> |
| 1122 |
Now it is impossible to create orders with non-NULL |
| 1123 |
<structfield>product_no</structfield> entries that do not appear in the |
| 1124 |
products table. |
| 1125 |
</para> |
| 1126 |
|
| 1127 |
<para> |
| 1128 |
We say that in this situation the orders table is the |
| 1129 |
<firstterm>referencing</firstterm> table and the products table is |
| 1130 |
the <firstterm>referenced</firstterm> table. Similarly, there are |
| 1131 |
referencing and referenced columns. |
| 1132 |
</para> |
| 1133 |
|
| 1134 |
<para> |
| 1135 |
You can also shorten the above command to: |
| 1136 |
<programlisting> |
| 1137 |
CREATE TABLE orders ( |
| 1138 |
order_id integer PRIMARY KEY, |
| 1139 |
product_no integer <emphasis>REFERENCES products</emphasis>, |
| 1140 |
quantity integer |
| 1141 |
); |
| 1142 |
</programlisting> |
| 1143 |
because in absence of a column list the primary key of the |
| 1144 |
referenced table is used as the referenced column(s). |
| 1145 |
</para> |
| 1146 |
|
| 1147 |
<para> |
| 1148 |
You can assign your own name for a foreign key constraint, |
| 1149 |
in the usual way. |
| 1150 |
</para> |
| 1151 |
|
| 1152 |
<para> |
| 1153 |
A foreign key can also constrain and reference a group of columns. |
| 1154 |
As usual, it then needs to be written in table constraint form. |
| 1155 |
Here is a contrived syntax example: |
| 1156 |
<programlisting> |
| 1157 |
CREATE TABLE t1 ( |
| 1158 |
a integer PRIMARY KEY, |
| 1159 |
b integer, |
| 1160 |
c integer, |
| 1161 |
<emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis> |
| 1162 |
); |
| 1163 |
</programlisting> |
| 1164 |
Of course, the number and type of the constrained columns need to |
| 1165 |
match the number and type of the referenced columns. |
| 1166 |
</para> |
| 1167 |
|
| 1168 |
<indexterm> |
| 1169 |
<primary>foreign key</primary> |
| 1170 |
<secondary>self-referential</secondary> |
| 1171 |
</indexterm> |
| 1172 |
|
| 1173 |
<para> |
| 1174 |
Sometimes it is useful for the <quote>other table</quote> of a |
| 1175 |
foreign key constraint to be the same table; this is called |
| 1176 |
a <firstterm>self-referential</firstterm> foreign key. For |
| 1177 |
example, if you want rows of a table to represent nodes of a tree |
| 1178 |
structure, you could write |
| 1179 |
<programlisting> |
| 1180 |
CREATE TABLE tree ( |
| 1181 |
node_id integer PRIMARY KEY, |
| 1182 |
parent_id integer REFERENCES tree, |
| 1183 |
name text, |
| 1184 |
... |
| 1185 |
); |
| 1186 |
</programlisting> |
| 1187 |
A top-level node would have NULL <structfield>parent_id</structfield>, |
| 1188 |
while non-NULL <structfield>parent_id</structfield> entries would be |
| 1189 |
constrained to reference valid rows of the table. |
| 1190 |
</para> |
| 1191 |
|
| 1192 |
<para> |
| 1193 |
A table can have more than one foreign key constraint. This is |
| 1194 |
used to implement many-to-many relationships between tables. Say |
| 1195 |
you have tables about products and orders, but now you want to |
| 1196 |
allow one order to contain possibly many products (which the |
| 1197 |
structure above did not allow). You could use this table structure: |
| 1198 |
<programlisting> |
| 1199 |
CREATE TABLE products ( |
| 1200 |
product_no integer PRIMARY KEY, |
| 1201 |
name text, |
| 1202 |
price numeric |
| 1203 |
); |
| 1204 |
|
| 1205 |
CREATE TABLE orders ( |
| 1206 |
order_id integer PRIMARY KEY, |
| 1207 |
shipping_address text, |
| 1208 |
... |
| 1209 |
); |
| 1210 |
|
| 1211 |
CREATE TABLE order_items ( |
| 1212 |
product_no integer REFERENCES products, |
| 1213 |
order_id integer REFERENCES orders, |
| 1214 |
quantity integer, |
| 1215 |
PRIMARY KEY (product_no, order_id) |
| 1216 |
); |
| 1217 |
</programlisting> |
| 1218 |
Notice that the primary key overlaps with the foreign keys in |
| 1219 |
the last table. |
| 1220 |
</para> |
| 1221 |
|
| 1222 |
<indexterm> |
| 1223 |
<primary>CASCADE</primary> |
| 1224 |
<secondary>foreign key action</secondary> |
| 1225 |
</indexterm> |
| 1226 |
|
| 1227 |
<indexterm> |
| 1228 |
<primary>RESTRICT</primary> |
| 1229 |
<secondary>foreign key action</secondary> |
| 1230 |
</indexterm> |
| 1231 |
|
| 1232 |
<para> |
| 1233 |
We know that the foreign keys disallow creation of orders that |
| 1234 |
do not relate to any products. But what if a product is removed |
| 1235 |
after an order is created that references it? SQL allows you to |
| 1236 |
handle that as well. Intuitively, we have a few options: |
| 1237 |
<itemizedlist spacing="compact"> |
| 1238 |
<listitem><para>Disallow deleting a referenced product</para></listitem> |
| 1239 |
<listitem><para>Delete the orders as well</para></listitem> |
| 1240 |
<listitem><para>Something else?</para></listitem> |
| 1241 |
</itemizedlist> |
| 1242 |
</para> |
| 1243 |
|
| 1244 |
<para> |
| 1245 |
To illustrate this, let's implement the following policy on the |
| 1246 |
many-to-many relationship example above: when someone wants to |
| 1247 |
remove a product that is still referenced by an order (via |
| 1248 |
<literal>order_items</literal>), we disallow it. If someone |
| 1249 |
removes an order, the order items are removed as well: |
| 1250 |
<programlisting> |
| 1251 |
CREATE TABLE products ( |
| 1252 |
product_no integer PRIMARY KEY, |
| 1253 |
name text, |
| 1254 |
price numeric |
| 1255 |
); |
| 1256 |
|
| 1257 |
CREATE TABLE orders ( |
| 1258 |
order_id integer PRIMARY KEY, |
| 1259 |
shipping_address text, |
| 1260 |
... |
| 1261 |
); |
| 1262 |
|
| 1263 |
CREATE TABLE order_items ( |
| 1264 |
product_no integer REFERENCES products <emphasis>ON DELETE RESTRICT</emphasis>, |
| 1265 |
order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>, |
| 1266 |
quantity integer, |
| 1267 |
PRIMARY KEY (product_no, order_id) |
| 1268 |
); |
| 1269 |
</programlisting> |
| 1270 |
</para> |
| 1271 |
|
| 1272 |
<para> |
| 1273 |
The default <literal>ON DELETE</literal> action is <literal>ON DELETE NO |
| 1274 |
ACTION</literal>; this does not need to be specified. This means that the |
| 1275 |
deletion in the referenced table is allowed to proceed. But the |
| 1276 |
foreign-key constraint is still required to be satisfied, so this |
| 1277 |
operation will usually result in an error. But checking of foreign-key |
| 1278 |
constraints can also be deferred to later in the transaction (not covered |
| 1279 |
in this chapter). In that case, the <literal>NO ACTION</literal> setting |
| 1280 |
would allow other commands to <quote>fix</quote> the situation before the |
| 1281 |
constraint is checked, for example by inserting another suitable row into |
| 1282 |
the referenced table or by deleting the now-dangling rows from the |
| 1283 |
referencing table. |
| 1284 |
</para> |
| 1285 |
|
| 1286 |
<para> |
| 1287 |
<literal>RESTRICT</literal> is a stricter setting than <literal>NO |
| 1288 |
ACTION</literal>. It prevents deletion of a referenced row. |
| 1289 |
<literal>RESTRICT</literal> does not allow the check to be deferred until |
| 1290 |
later in the transaction. |
| 1291 |
</para> |
| 1292 |
|
| 1293 |
<para> |
| 1294 |
<literal>CASCADE</literal> specifies that when a referenced row is deleted, |
| 1295 |
row(s) referencing it should be automatically deleted as well. |
| 1296 |
</para> |
| 1297 |
|
| 1298 |
<para> |
| 1299 |
There are two other options: |
| 1300 |
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>. |
| 1301 |
These cause the referencing column(s) in the referencing row(s) |
| 1302 |
to be set to nulls or their default |
| 1303 |
values, respectively, when the referenced row is deleted. |
| 1304 |
Note that these do not excuse you from observing any constraints. |
| 1305 |
For example, if an action specifies <literal>SET DEFAULT</literal> |
| 1306 |
but the default value would not satisfy the foreign key constraint, the |
| 1307 |
operation will fail. |
| 1308 |
</para> |
| 1309 |
|
| 1310 |
<para> |
| 1311 |
The appropriate choice of <literal>ON DELETE</literal> action depends on |
| 1312 |
what kinds of objects the related tables represent. When the referencing |
| 1313 |
table represents something that is a component of what is represented by |
| 1314 |
the referenced table and cannot exist independently, then |
| 1315 |
<literal>CASCADE</literal> could be appropriate. If the two tables |
| 1316 |
represent independent objects, then <literal>RESTRICT</literal> or |
| 1317 |
<literal>NO ACTION</literal> is more appropriate; an application that |
| 1318 |
actually wants to delete both objects would then have to be explicit about |
| 1319 |
this and run two delete commands. In the above example, order items are |
| 1320 |
part of an order, and it is convenient if they are deleted automatically |
| 1321 |
if an order is deleted. But products and orders are different things, and |
| 1322 |
so making a deletion of a product automatically cause the deletion of some |
| 1323 |
order items could be considered problematic. The actions <literal>SET |
| 1324 |
NULL</literal> or <literal>SET DEFAULT</literal> can be appropriate if a |
| 1325 |
foreign-key relationship represents optional information. For example, if |
| 1326 |
the products table contained a reference to a product manager, and the |
| 1327 |
product manager entry gets deleted, then setting the product's product |
| 1328 |
manager to null or a default might be useful. |
| 1329 |
</para> |
| 1330 |
|
| 1331 |
<para> |
| 1332 |
The actions <literal>SET NULL</literal> and <literal>SET DEFAULT</literal> |
| 1333 |
can take a column list to specify which columns to set. Normally, all |
| 1334 |
columns of the foreign-key constraint are set; setting only a subset is |
| 1335 |
useful in some special cases. Consider the following example: |
| 1336 |
<programlisting> |
| 1337 |
CREATE TABLE tenants ( |
| 1338 |
tenant_id integer PRIMARY KEY |
| 1339 |
); |
| 1340 |
|
| 1341 |
CREATE TABLE users ( |
| 1342 |
tenant_id integer REFERENCES tenants ON DELETE CASCADE, |
| 1343 |
user_id integer NOT NULL, |
| 1344 |
PRIMARY KEY (tenant_id, user_id) |
| 1345 |
); |
| 1346 |
|
| 1347 |
CREATE TABLE posts ( |
| 1348 |
tenant_id integer REFERENCES tenants ON DELETE CASCADE, |
| 1349 |
post_id integer NOT NULL, |
| 1350 |
author_id integer, |
| 1351 |
PRIMARY KEY (tenant_id, post_id), |
| 1352 |
FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL <emphasis>(author_id)</emphasis> |
| 1353 |
); |
| 1354 |
</programlisting> |
| 1355 |
Without the specification of the column, the foreign key would also set |
| 1356 |
the column <literal>tenant_id</literal> to null, but that column is still |
| 1357 |
required as part of the primary key. |
| 1358 |
</para> |
| 1359 |
|
| 1360 |
<para> |
| 1361 |
Analogous to <literal>ON DELETE</literal> there is also |
| 1362 |
<literal>ON UPDATE</literal> which is invoked when a referenced |
| 1363 |
column is changed (updated). The possible actions are the same, |
| 1364 |
except that column lists cannot be specified for <literal>SET |
| 1365 |
NULL</literal> and <literal>SET DEFAULT</literal>. |
| 1366 |
In this case, <literal>CASCADE</literal> means that the updated values of the |
| 1367 |
referenced column(s) should be copied into the referencing row(s). |
| 1368 |
There is also a noticeable difference between <literal>ON UPDATE NO |
| 1369 |
ACTION</literal> (the default) and <literal>ON UPDATE RESTRICT</literal>. |
| 1370 |
The former will allow the update to proceed and the foreign-key constraint |
| 1371 |
will be checked against the state after the update. The latter will |
| 1372 |
prevent the update to run even if the state after the update would still |
| 1373 |
satisfy the constraint. This prevents updating a referenced row to a |
| 1374 |
value that is distinct but compares as equal (for example, a character |
| 1375 |
string with a different case variant, if a character string type with a |
| 1376 |
case-insensitive collation is used). |
| 1377 |
</para> |
| 1378 |
|
| 1379 |
<para> |
| 1380 |
Normally, a referencing row need not satisfy the foreign key constraint |
| 1381 |
if any of its referencing columns are null. If <literal>MATCH FULL</literal> |
| 1382 |
is added to the foreign key declaration, a referencing row escapes |
| 1383 |
satisfying the constraint only if all its referencing columns are null |
| 1384 |
(so a mix of null and non-null values is guaranteed to fail a |
| 1385 |
<literal>MATCH FULL</literal> constraint). If you don't want referencing rows |
| 1386 |
to be able to avoid satisfying the foreign key constraint, declare the |
| 1387 |
referencing column(s) as <literal>NOT NULL</literal>. |
| 1388 |
</para> |
| 1389 |
|
| 1390 |
<para> |
| 1391 |
A foreign key must reference columns that either are a primary key or |
| 1392 |
form a unique constraint, or are columns from a non-partial unique index. |
| 1393 |
This means that the referenced columns always have an index to allow |
| 1394 |
efficient lookups on whether a referencing row has a match. Since a |
| 1395 |
<command>DELETE</command> of a row from the referenced table or an |
| 1396 |
<command>UPDATE</command> of a referenced column will require a scan of |
| 1397 |
the referencing table for rows matching the old value, it is often a good |
| 1398 |
idea to index the referencing columns too. Because this is not always |
| 1399 |
needed, and there are many choices available on how to index, the |
| 1400 |
declaration of a foreign key constraint does not automatically create an |
| 1401 |
index on the referencing columns. |
| 1402 |
</para> |
| 1403 |
|
| 1404 |
<para> |
| 1405 |
More information about updating and deleting data is in <xref |
| 1406 |
linkend="dml"/>. Also see the description of foreign key constraint |
| 1407 |
syntax in the reference documentation for |
| 1408 |
<xref linkend="sql-createtable"/>. |
| 1409 |
</para> |
| 1410 |
</sect2> |
| 1411 |
|
| 1412 |
<sect2 id="ddl-constraints-exclusion"> |
| 1413 |
<title>Exclusion Constraints</title> |
| 1414 |
|
| 1415 |
<indexterm> |
| 1416 |
<primary>exclusion constraint</primary> |
| 1417 |
</indexterm> |
| 1418 |
|
| 1419 |
<indexterm> |
| 1420 |
<primary>constraint</primary> |
| 1421 |
<secondary>exclusion</secondary> |
| 1422 |
</indexterm> |
| 1423 |
|
| 1424 |
<para> |
| 1425 |
Exclusion constraints ensure that if any two rows are compared on |
| 1426 |
the specified columns or expressions using the specified operators, |
| 1427 |
at least one of these operator comparisons will return false or null. |
| 1428 |
The syntax is: |
| 1429 |
<programlisting> |
| 1430 |
CREATE TABLE circles ( |
| 1431 |
c circle, |
| 1432 |
EXCLUDE USING gist (c WITH &&) |
| 1433 |
); |
| 1434 |
</programlisting> |
| 1435 |
</para> |
| 1436 |
|
| 1437 |
<para> |
| 1438 |
See also <link linkend="sql-createtable-exclude"><command>CREATE |
| 1439 |
TABLE ... CONSTRAINT ... EXCLUDE</command></link> for details. |
| 1440 |
</para> |
| 1441 |
|
| 1442 |
<para> |
| 1443 |
Adding an exclusion constraint will automatically create an index |
| 1444 |
of the type specified in the constraint declaration. |
| 1445 |
</para> |
| 1446 |
</sect2> |
| 1447 |
</sect1> |
| 1448 |
|
| 1449 |
<sect1 id="ddl-system-columns"> |
| 1450 |
<title>System Columns</title> |
| 1451 |
|
| 1452 |
<para> |
| 1453 |
Every table has several <firstterm>system columns</firstterm> that are |
| 1454 |
implicitly defined by the system. Therefore, these names cannot be |
| 1455 |
used as names of user-defined columns. (Note that these |
| 1456 |
restrictions are separate from whether the name is a key word or |
| 1457 |
not; quoting a name will not allow you to escape these |
| 1458 |
restrictions.) You do not really need to be concerned about these |
| 1459 |
columns; just know they exist. |
| 1460 |
</para> |
| 1461 |
|
| 1462 |
<indexterm> |
| 1463 |
<primary>column</primary> |
| 1464 |
<secondary>system column</secondary> |
| 1465 |
</indexterm> |
| 1466 |
|
| 1467 |
<variablelist> |
| 1468 |
<varlistentry id="ddl-system-columns-tableoid"> |
| 1469 |
<term><structfield>tableoid</structfield></term> |
| 1470 |
<listitem> |
| 1471 |
<indexterm> |
| 1472 |
<primary>tableoid</primary> |
| 1473 |
</indexterm> |
| 1474 |
|
| 1475 |
<para> |
| 1476 |
The OID of the table containing this row. This column is |
| 1477 |
particularly handy for queries that select from partitioned |
| 1478 |
tables (see <xref linkend="ddl-partitioning"/>) or inheritance |
| 1479 |
hierarchies (see <xref linkend="ddl-inherit"/>), since without it, |
| 1480 |
it's difficult to tell which individual table a row came from. The |
| 1481 |
<structfield>tableoid</structfield> can be joined against the |
| 1482 |
<structfield>oid</structfield> column of |
| 1483 |
<structname>pg_class</structname> to obtain the table name. |
| 1484 |
</para> |
| 1485 |
</listitem> |
| 1486 |
</varlistentry> |
| 1487 |
|
| 1488 |
<varlistentry id="ddl-system-columns-xmin"> |
| 1489 |
<term><structfield>xmin</structfield></term> |
| 1490 |
<listitem> |
| 1491 |
<indexterm> |
| 1492 |
<primary>xmin</primary> |
| 1493 |
</indexterm> |
| 1494 |
|
| 1495 |
<para> |
| 1496 |
The identity (transaction ID) of the inserting transaction for |
| 1497 |
this row version. (A row version is an individual state of a |
| 1498 |
row; each update of a row creates a new row version for the same |
| 1499 |
logical row.) |
| 1500 |
</para> |
| 1501 |
</listitem> |
| 1502 |
</varlistentry> |
| 1503 |
|
| 1504 |
<varlistentry id="ddl-system-columns-cmin"> |
| 1505 |
<term><structfield>cmin</structfield></term> |
| 1506 |
<listitem> |
| 1507 |
<indexterm> |
| 1508 |
<primary>cmin</primary> |
| 1509 |
</indexterm> |
| 1510 |
|
| 1511 |
<para> |
| 1512 |
The command identifier (starting at zero) within the inserting |
| 1513 |
transaction. |
| 1514 |
</para> |
| 1515 |
</listitem> |
| 1516 |
</varlistentry> |
| 1517 |
|
| 1518 |
<varlistentry id="ddl-system-columns-xmax"> |
| 1519 |
<term><structfield>xmax</structfield></term> |
| 1520 |
<listitem> |
| 1521 |
<indexterm> |
| 1522 |
<primary>xmax</primary> |
| 1523 |
</indexterm> |
| 1524 |
|
| 1525 |
<para> |
| 1526 |
The identity (transaction ID) of the deleting transaction, or |
| 1527 |
zero for an undeleted row version. It is possible for this column to |
| 1528 |
be nonzero in a visible row version. That usually indicates that the |
| 1529 |
deleting transaction hasn't committed yet, or that an attempted |
| 1530 |
deletion was rolled back. |
| 1531 |
</para> |
| 1532 |
</listitem> |
| 1533 |
</varlistentry> |
| 1534 |
|
| 1535 |
<varlistentry id="ddl-system-columns-cmax"> |
| 1536 |
<term><structfield>cmax</structfield></term> |
| 1537 |
<listitem> |
| 1538 |
<indexterm> |
| 1539 |
<primary>cmax</primary> |
| 1540 |
</indexterm> |
| 1541 |
|
| 1542 |
<para> |
| 1543 |
The command identifier within the deleting transaction, or zero. |
| 1544 |
</para> |
| 1545 |
</listitem> |
| 1546 |
</varlistentry> |
| 1547 |
|
| 1548 |
<varlistentry id="ddl-system-columns-ctid"> |
| 1549 |
<term><structfield>ctid</structfield></term> |
| 1550 |
<listitem> |
| 1551 |
<indexterm> |
| 1552 |
<primary>ctid</primary> |
| 1553 |
</indexterm> |
| 1554 |
|
| 1555 |
<para> |
| 1556 |
The physical location of the row version within its table. Note that |
| 1557 |
although the <structfield>ctid</structfield> can be used to |
| 1558 |
locate the row version very quickly, a row's |
| 1559 |
<structfield>ctid</structfield> will change if it is |
| 1560 |
updated or moved by <command>VACUUM FULL</command>. Therefore |
| 1561 |
<structfield>ctid</structfield> should not be used as a row |
| 1562 |
identifier. A primary key should be used to identify logical rows. |
| 1563 |
</para> |
| 1564 |
</listitem> |
| 1565 |
</varlistentry> |
| 1566 |
</variablelist> |
| 1567 |
|
| 1568 |
<para> |
| 1569 |
Transaction identifiers are also 32-bit quantities. In a |
| 1570 |
long-lived database it is possible for transaction IDs to wrap |
| 1571 |
around. This is not a fatal problem given appropriate maintenance |
| 1572 |
procedures; see <xref linkend="maintenance"/> for details. It is |
| 1573 |
unwise, however, to depend on the uniqueness of transaction IDs |
| 1574 |
over the long term (more than one billion transactions). |
| 1575 |
</para> |
| 1576 |
|
| 1577 |
<para> |
| 1578 |
Command identifiers are also 32-bit quantities. This creates a hard limit |
| 1579 |
of 2<superscript>32</superscript> (4 billion) <acronym>SQL</acronym> commands |
| 1580 |
within a single transaction. In practice this limit is not a |
| 1581 |
problem — note that the limit is on the number of |
| 1582 |
<acronym>SQL</acronym> commands, not the number of rows processed. |
| 1583 |
Also, only commands that actually modify the database contents will |
| 1584 |
consume a command identifier. |
| 1585 |
</para> |
| 1586 |
</sect1> |
| 1587 |
|
| 1588 |
<sect1 id="ddl-alter"> |
| 1589 |
<title>Modifying Tables</title> |
| 1590 |
|
| 1591 |
<indexterm zone="ddl-alter"> |
| 1592 |
<primary>table</primary> |
| 1593 |
<secondary>modifying</secondary> |
| 1594 |
</indexterm> |
| 1595 |
|
| 1596 |
<para> |
| 1597 |
When you create a table and you realize that you made a mistake, or |
| 1598 |
the requirements of the application change, you can drop the |
| 1599 |
table and create it again. But this is not a convenient option if |
| 1600 |
the table is already filled with data, or if the table is |
| 1601 |
referenced by other database objects (for instance a foreign key |
| 1602 |
constraint). Therefore <productname>PostgreSQL</productname> |
| 1603 |
provides a family of commands to make modifications to existing |
| 1604 |
tables. Note that this is conceptually distinct from altering |
| 1605 |
the data contained in the table: here we are interested in altering |
| 1606 |
the definition, or structure, of the table. |
| 1607 |
</para> |
| 1608 |
|
| 1609 |
<para> |
| 1610 |
You can: |
| 1611 |
<itemizedlist spacing="compact"> |
| 1612 |
<listitem> |
| 1613 |
<para>Add columns</para> |
| 1614 |
</listitem> |
| 1615 |
<listitem> |
| 1616 |
<para>Remove columns</para> |
| 1617 |
</listitem> |
| 1618 |
<listitem> |
| 1619 |
<para>Add constraints</para> |
| 1620 |
</listitem> |
| 1621 |
<listitem> |
| 1622 |
<para>Remove constraints</para> |
| 1623 |
</listitem> |
| 1624 |
<listitem> |
| 1625 |
<para>Change default values</para> |
| 1626 |
</listitem> |
| 1627 |
<listitem> |
| 1628 |
<para>Change column data types</para> |
| 1629 |
</listitem> |
| 1630 |
<listitem> |
| 1631 |
<para>Rename columns</para> |
| 1632 |
</listitem> |
| 1633 |
<listitem> |
| 1634 |
<para>Rename tables</para> |
| 1635 |
</listitem> |
| 1636 |
</itemizedlist> |
| 1637 |
|
| 1638 |
All these actions are performed using the |
| 1639 |
<xref linkend="sql-altertable"/> |
| 1640 |
command, whose reference page contains details beyond those given |
| 1641 |
here. |
| 1642 |
</para> |
| 1643 |
|
| 1644 |
<sect2 id="ddl-alter-adding-a-column"> |
| 1645 |
<title>Adding a Column</title> |
| 1646 |
|
| 1647 |
<indexterm> |
| 1648 |
<primary>column</primary> |
| 1649 |
<secondary>adding</secondary> |
| 1650 |
</indexterm> |
| 1651 |
|
| 1652 |
<para> |
| 1653 |
To add a column, use a command like: |
| 1654 |
<programlisting> |
| 1655 |
ALTER TABLE products ADD COLUMN description text; |
| 1656 |
</programlisting> |
| 1657 |
The new column is initially filled with whatever default |
| 1658 |
value is given (null if you don't specify a <literal>DEFAULT</literal> clause). |
| 1659 |
</para> |
| 1660 |
|
| 1661 |
<tip> |
| 1662 |
<para> |
| 1663 |
Adding a column with a constant default value does not require each row of |
| 1664 |
the table to be updated when the <command>ALTER TABLE</command> statement |
| 1665 |
is executed. Instead, the default value will be returned the next time |
| 1666 |
the row is accessed, and applied when the table is rewritten, making |
| 1667 |
the <command>ALTER TABLE</command> very fast even on large tables. |
| 1668 |
</para> |
| 1669 |
|
| 1670 |
<para> |
| 1671 |
If the default value is volatile (e.g., <function>clock_timestamp()</function>) |
| 1672 |
each row will need to be updated with the value calculated at the time |
| 1673 |
<command>ALTER TABLE</command> is executed. To avoid a potentially |
| 1674 |
lengthy update operation, particularly if you intend to fill the column |
| 1675 |
with mostly nondefault values anyway, it may be preferable to add the |
| 1676 |
column with no default, insert the correct values using |
| 1677 |
<command>UPDATE</command>, and then add any desired default as described |
| 1678 |
below. |
| 1679 |
</para> |
| 1680 |
</tip> |
| 1681 |
|
| 1682 |
<para> |
| 1683 |
You can also define constraints on the column at the same time, |
| 1684 |
using the usual syntax: |
| 1685 |
<programlisting> |
| 1686 |
ALTER TABLE products ADD COLUMN description text CHECK (description <> ''); |
| 1687 |
</programlisting> |
| 1688 |
In fact all the options that can be applied to a column description |
| 1689 |
in <command>CREATE TABLE</command> can be used here. Keep in mind however |
| 1690 |
that the default value must satisfy the given constraints, or the |
| 1691 |
<literal>ADD</literal> will fail. Alternatively, you can add |
| 1692 |
constraints later (see below) after you've filled in the new column |
| 1693 |
correctly. |
| 1694 |
</para> |
| 1695 |
|
| 1696 |
</sect2> |
| 1697 |
|
| 1698 |
<sect2 id="ddl-alter-removing-a-column"> |
| 1699 |
<title>Removing a Column</title> |
| 1700 |
|
| 1701 |
<indexterm> |
| 1702 |
<primary>column</primary> |
| 1703 |
<secondary>removing</secondary> |
| 1704 |
</indexterm> |
| 1705 |
|
| 1706 |
<para> |
| 1707 |
To remove a column, use a command like: |
| 1708 |
<programlisting> |
| 1709 |
ALTER TABLE products DROP COLUMN description; |
| 1710 |
</programlisting> |
| 1711 |
Whatever data was in the column disappears. Table constraints involving |
| 1712 |
the column are dropped, too. However, if the column is referenced by a |
| 1713 |
foreign key constraint of another table, |
| 1714 |
<productname>PostgreSQL</productname> will not silently drop that |
| 1715 |
constraint. You can authorize dropping everything that depends on |
| 1716 |
the column by adding <literal>CASCADE</literal>: |
| 1717 |
<programlisting> |
| 1718 |
ALTER TABLE products DROP COLUMN description CASCADE; |
| 1719 |
</programlisting> |
| 1720 |
See <xref linkend="ddl-depend"/> for a description of the general |
| 1721 |
mechanism behind this. |
| 1722 |
</para> |
| 1723 |
</sect2> |
| 1724 |
|
| 1725 |
<sect2 id="ddl-alter-adding-a-constraint"> |
| 1726 |
<title>Adding a Constraint</title> |
| 1727 |
|
| 1728 |
<indexterm> |
| 1729 |
<primary>constraint</primary> |
| 1730 |
<secondary>adding</secondary> |
| 1731 |
</indexterm> |
| 1732 |
|
| 1733 |
<para> |
| 1734 |
To add a constraint, the table constraint syntax is used. For example: |
| 1735 |
<programlisting> |
| 1736 |
ALTER TABLE products ADD CHECK (name <> ''); |
| 1737 |
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); |
| 1738 |
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; |
| 1739 |
</programlisting> |
| 1740 |
</para> |
| 1741 |
|
| 1742 |
<para> |
| 1743 |
To add a not-null constraint, which is normally not written as a table |
| 1744 |
constraint, this special syntax is available: |
| 1745 |
<programlisting> |
| 1746 |
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; |
| 1747 |
</programlisting> |
| 1748 |
This command silently does nothing if the column already has a |
| 1749 |
not-null constraint. |
| 1750 |
</para> |
| 1751 |
|
| 1752 |
<para> |
| 1753 |
The constraint will be checked immediately, so the table data must |
| 1754 |
satisfy the constraint before it can be added. |
| 1755 |
</para> |
| 1756 |
</sect2> |
| 1757 |
|
| 1758 |
<sect2 id="ddl-alter-removing-a-constraint"> |
| 1759 |
<title>Removing a Constraint</title> |
| 1760 |
|
| 1761 |
<indexterm> |
| 1762 |
<primary>constraint</primary> |
| 1763 |
<secondary>removing</secondary> |
| 1764 |
</indexterm> |
| 1765 |
|
| 1766 |
<para> |
| 1767 |
To remove a constraint you need to know its name. If you gave it |
| 1768 |
a name then that's easy. Otherwise the system assigned a |
| 1769 |
generated name, which you need to find out. The |
| 1770 |
<application>psql</application> command <literal>\d |
| 1771 |
<replaceable>tablename</replaceable></literal> can be helpful |
| 1772 |
here; other interfaces might also provide a way to inspect table |
| 1773 |
details. Then the command is: |
| 1774 |
<programlisting> |
| 1775 |
ALTER TABLE products DROP CONSTRAINT some_name; |
| 1776 |
</programlisting> |
| 1777 |
</para> |
| 1778 |
|
| 1779 |
<para> |
| 1780 |
As with dropping a column, you need to add <literal>CASCADE</literal> if you |
| 1781 |
want to drop a constraint that something else depends on. An example |
| 1782 |
is that a foreign key constraint depends on a unique or primary key |
| 1783 |
constraint on the referenced column(s). |
| 1784 |
</para> |
| 1785 |
|
| 1786 |
<para> |
| 1787 |
Simplified syntax is available to drop a not-null constraint: |
| 1788 |
<programlisting> |
| 1789 |
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; |
| 1790 |
</programlisting> |
| 1791 |
This mirrors the <literal>SET NOT NULL</literal> syntax for adding a |
| 1792 |
not-null constraint. This command will silently do nothing if the column |
| 1793 |
does not have a not-null constraint. (Recall that a column can have at |
| 1794 |
most one not-null constraint, so it is never ambiguous which constraint |
| 1795 |
this command acts on.) |
| 1796 |
</para> |
| 1797 |
</sect2> |
| 1798 |
|
| 1799 |
<sect2 id="ddl-alter-column-default"> |
| 1800 |
<title>Changing a Column's Default Value</title> |
| 1801 |
|
| 1802 |
<indexterm> |
| 1803 |
<primary>default value</primary> |
| 1804 |
<secondary>changing</secondary> |
| 1805 |
</indexterm> |
| 1806 |
|
| 1807 |
<para> |
| 1808 |
To set a new default for a column, use a command like: |
| 1809 |
<programlisting> |
| 1810 |
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; |
| 1811 |
</programlisting> |
| 1812 |
Note that this doesn't affect any existing rows in the table, it |
| 1813 |
just changes the default for future <command>INSERT</command> commands. |
| 1814 |
</para> |
| 1815 |
|
| 1816 |
<para> |
| 1817 |
To remove any default value, use: |
| 1818 |
<programlisting> |
| 1819 |
ALTER TABLE products ALTER COLUMN price DROP DEFAULT; |
| 1820 |
</programlisting> |
| 1821 |
This is effectively the same as setting the default to null. |
| 1822 |
As a consequence, it is not an error |
| 1823 |
to drop a default where one hadn't been defined, because the |
| 1824 |
default is implicitly the null value. |
| 1825 |
</para> |
| 1826 |
</sect2> |
| 1827 |
|
| 1828 |
<sect2 id="ddl-alter-column-type"> |
| 1829 |
<title>Changing a Column's Data Type</title> |
| 1830 |
|
| 1831 |
<indexterm> |
| 1832 |
<primary>column data type</primary> |
| 1833 |
<secondary>changing</secondary> |
| 1834 |
</indexterm> |
| 1835 |
|
| 1836 |
<para> |
| 1837 |
To convert a column to a different data type, use a command like: |
| 1838 |
<programlisting> |
| 1839 |
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); |
| 1840 |
</programlisting> |
| 1841 |
This will succeed only if each existing entry in the column can be |
| 1842 |
converted to the new type by an implicit cast. If a more complex |
| 1843 |
conversion is needed, you can add a <literal>USING</literal> clause that |
| 1844 |
specifies how to compute the new values from the old. |
| 1845 |
</para> |
| 1846 |
|
| 1847 |
<para> |
| 1848 |
<productname>PostgreSQL</productname> will attempt to convert the column's |
| 1849 |
default value (if any) to the new type, as well as any constraints |
| 1850 |
that involve the column. But these conversions might fail, or might |
| 1851 |
produce surprising results. It's often best to drop any constraints |
| 1852 |
on the column before altering its type, and then add back suitably |
| 1853 |
modified constraints afterwards. |
| 1854 |
</para> |
| 1855 |
</sect2> |
| 1856 |
|
| 1857 |
<sect2 id="ddl-alter-renaming-column"> |
| 1858 |
<title>Renaming a Column</title> |
| 1859 |
|
| 1860 |
<indexterm> |
| 1861 |
<primary>column</primary> |
| 1862 |
<secondary>renaming</secondary> |
| 1863 |
</indexterm> |
| 1864 |
|
| 1865 |
<para> |
| 1866 |
To rename a column: |
| 1867 |
<programlisting> |
| 1868 |
ALTER TABLE products RENAME COLUMN product_no TO product_number; |
| 1869 |
</programlisting> |
| 1870 |
</para> |
| 1871 |
</sect2> |
| 1872 |
|
| 1873 |
<sect2 id="ddl-alter-renaming-table"> |
| 1874 |
<title>Renaming a Table</title> |
| 1875 |
|
| 1876 |
<indexterm> |
| 1877 |
<primary>table</primary> |
| 1878 |
<secondary>renaming</secondary> |
| 1879 |
</indexterm> |
| 1880 |
|
| 1881 |
<para> |
| 1882 |
To rename a table: |
| 1883 |
<programlisting> |
| 1884 |
ALTER TABLE products RENAME TO items; |
| 1885 |
</programlisting> |
| 1886 |
</para> |
| 1887 |
</sect2> |
| 1888 |
</sect1> |
| 1889 |
|
| 1890 |
<sect1 id="ddl-priv"> |
| 1891 |
<title>Privileges</title> |
| 1892 |
|
| 1893 |
<indexterm zone="ddl-priv"> |
| 1894 |
<primary>privilege</primary> |
| 1895 |
</indexterm> |
| 1896 |
|
| 1897 |
<indexterm> |
| 1898 |
<primary>permission</primary> |
| 1899 |
<see>privilege</see> |
| 1900 |
</indexterm> |
| 1901 |
|
| 1902 |
<indexterm zone="ddl-priv"> |
| 1903 |
<primary>owner</primary> |
| 1904 |
</indexterm> |
| 1905 |
|
| 1906 |
<indexterm zone="ddl-priv"> |
| 1907 |
<primary>GRANT</primary> |
| 1908 |
</indexterm> |
| 1909 |
|
| 1910 |
<indexterm zone="ddl-priv"> |
| 1911 |
<primary>REVOKE</primary> |
| 1912 |
</indexterm> |
| 1913 |
|
| 1914 |
<indexterm zone="ddl-priv"> |
| 1915 |
<primary><acronym>ACL</acronym></primary> |
| 1916 |
</indexterm> |
| 1917 |
|
| 1918 |
<indexterm zone="ddl-priv-default"> |
| 1919 |
<primary>privilege</primary> |
| 1920 |
<secondary>default</secondary> |
| 1921 |
</indexterm> |
| 1922 |
|
| 1923 |
<para> |
| 1924 |
When an object is created, it is assigned an owner. The |
| 1925 |
owner is normally the role that executed the creation statement. |
| 1926 |
For most kinds of objects, the initial state is that only the owner |
| 1927 |
(or a superuser) can do anything with the object. To allow |
| 1928 |
other roles to use it, <firstterm>privileges</firstterm> must be |
| 1929 |
granted. |
| 1930 |
</para> |
| 1931 |
|
| 1932 |
<para> |
| 1933 |
There are different kinds of privileges: <literal>SELECT</literal>, |
| 1934 |
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, |
| 1935 |
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>, |
| 1936 |
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>, |
| 1937 |
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>, |
| 1938 |
<literal>ALTER SYSTEM</literal>, and <literal>MAINTAIN</literal>. |
| 1939 |
The privileges applicable to a particular |
| 1940 |
object vary depending on the object's type (table, function, etc.). |
| 1941 |
More detail about the meanings of these privileges appears below. |
| 1942 |
The following sections and chapters will also show you how |
| 1943 |
these privileges are used. |
| 1944 |
</para> |
| 1945 |
|
| 1946 |
<para> |
| 1947 |
The right to modify or destroy an object is inherent in being the |
| 1948 |
object's owner, and cannot be granted or revoked in itself. |
| 1949 |
(However, like all privileges, that right can be inherited by |
| 1950 |
members of the owning role; see <xref linkend="role-membership"/>.) |
| 1951 |
</para> |
| 1952 |
|
| 1953 |
<para> |
| 1954 |
An object can be assigned to a new owner with an <command>ALTER</command> |
| 1955 |
command of the appropriate kind for the object, for example |
| 1956 |
<programlisting> |
| 1957 |
ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owner</replaceable>; |
| 1958 |
</programlisting> |
| 1959 |
Superusers can always do this; ordinary roles can only do it if they are |
| 1960 |
both the current owner of the object (or inherit the privileges of the |
| 1961 |
owning role) and able to <literal>SET ROLE</literal> to the new owning role. |
| 1962 |
All object privileges of the old owner are transferred to the new owner |
| 1963 |
along with the ownership. |
| 1964 |
</para> |
| 1965 |
|
| 1966 |
<para> |
| 1967 |
To assign privileges, the <xref linkend="sql-grant"/> command is |
| 1968 |
used. For example, if <literal>joe</literal> is an existing role, and |
| 1969 |
<literal>accounts</literal> is an existing table, the privilege to |
| 1970 |
update the table can be granted with: |
| 1971 |
<programlisting> |
| 1972 |
GRANT UPDATE ON accounts TO joe; |
| 1973 |
</programlisting> |
| 1974 |
Writing <literal>ALL</literal> in place of a specific privilege grants all |
| 1975 |
privileges that are relevant for the object type. |
| 1976 |
</para> |
| 1977 |
|
| 1978 |
<para> |
| 1979 |
The special <quote>role</quote> name <literal>PUBLIC</literal> can |
| 1980 |
be used to grant a privilege to every role on the system. Also, |
| 1981 |
<quote>group</quote> roles can be set up to help manage privileges when |
| 1982 |
there are many users of a database — for details see |
| 1983 |
<xref linkend="user-manag"/>. |
| 1984 |
</para> |
| 1985 |
|
| 1986 |
<para> |
| 1987 |
To revoke a previously-granted privilege, use the fittingly named |
| 1988 |
<xref linkend="sql-revoke"/> command: |
| 1989 |
<programlisting> |
| 1990 |
REVOKE ALL ON accounts FROM PUBLIC; |
| 1991 |
</programlisting> |
| 1992 |
</para> |
| 1993 |
|
| 1994 |
<para> |
| 1995 |
Ordinarily, only the object's owner (or a superuser) can grant or |
| 1996 |
revoke privileges on an object. However, it is possible to grant a |
| 1997 |
privilege <quote>with grant option</quote>, which gives the recipient |
| 1998 |
the right to grant it in turn to others. If the grant option is |
| 1999 |
subsequently revoked then all who received the privilege from that |
| 2000 |
recipient (directly or through a chain of grants) will lose the |
| 2001 |
privilege. For details see the <xref linkend="sql-grant"/> and |
| 2002 |
<xref linkend="sql-revoke"/> reference pages. |
| 2003 |
</para> |
| 2004 |
|
| 2005 |
<para> |
| 2006 |
An object's owner can choose to revoke their own ordinary privileges, |
| 2007 |
for example to make a table read-only for themselves as well as others. |
| 2008 |
But owners are always treated as holding all grant options, so they |
| 2009 |
can always re-grant their own privileges. |
| 2010 |
</para> |
| 2011 |
|
| 2012 |
<para> |
| 2013 |
The available privileges are: |
| 2014 |
|
| 2015 |
<variablelist> |
| 2016 |
<varlistentry id="ddl-priv-select"> |
| 2017 |
<term><literal>SELECT</literal></term> |
| 2018 |
<listitem> |
| 2019 |
<para> |
| 2020 |
Allows <command>SELECT</command> from |
| 2021 |
any column, or specific column(s), of a table, view, materialized |
| 2022 |
view, or other table-like object. |
| 2023 |
Also allows use of <command>COPY TO</command>. |
| 2024 |
This privilege is also needed to reference existing column values in |
| 2025 |
<command>UPDATE</command>, <command>DELETE</command>, |
| 2026 |
or <command>MERGE</command>. |
| 2027 |
For sequences, this privilege also allows use of the |
| 2028 |
<function>currval</function> function. |
| 2029 |
For large objects, this privilege allows the object to be read. |
| 2030 |
</para> |
| 2031 |
</listitem> |
| 2032 |
</varlistentry> |
| 2033 |
|
| 2034 |
<varlistentry id="ddl-priv-insert"> |
| 2035 |
<term><literal>INSERT</literal></term> |
| 2036 |
<listitem> |
| 2037 |
<para> |
| 2038 |
Allows <command>INSERT</command> of a new row into a table, view, |
| 2039 |
etc. Can be granted on specific column(s), in which case |
| 2040 |
only those columns may be assigned to in the <command>INSERT</command> |
| 2041 |
command (other columns will therefore receive default values). |
| 2042 |
Also allows use of <command>COPY FROM</command>. |
| 2043 |
</para> |
| 2044 |
</listitem> |
| 2045 |
</varlistentry> |
| 2046 |
|
| 2047 |
<varlistentry id="ddl-priv-update"> |
| 2048 |
<term><literal>UPDATE</literal></term> |
| 2049 |
<listitem> |
| 2050 |
<para> |
| 2051 |
Allows <command>UPDATE</command> of any |
| 2052 |
column, or specific column(s), of a table, view, etc. |
| 2053 |
(In practice, any nontrivial <command>UPDATE</command> command will |
| 2054 |
require <literal>SELECT</literal> privilege as well, since it must |
| 2055 |
reference table columns to determine which rows to update, and/or to |
| 2056 |
compute new values for columns.) |
| 2057 |
<literal>SELECT ... FOR UPDATE</literal> |
| 2058 |
and <literal>SELECT ... FOR SHARE</literal> |
| 2059 |
also require this privilege on at least one column, in addition to the |
| 2060 |
<literal>SELECT</literal> privilege. For sequences, this |
| 2061 |
privilege allows use of the <function>nextval</function> and |
| 2062 |
<function>setval</function> functions. |
| 2063 |
For large objects, this privilege allows writing or truncating the |
| 2064 |
object. |
| 2065 |
</para> |
| 2066 |
</listitem> |
| 2067 |
</varlistentry> |
| 2068 |
|
| 2069 |
<varlistentry id="ddl-priv-delete"> |
| 2070 |
<term><literal>DELETE</literal></term> |
| 2071 |
<listitem> |
| 2072 |
<para> |
| 2073 |
Allows <command>DELETE</command> of a row from a table, view, etc. |
| 2074 |
(In practice, any nontrivial <command>DELETE</command> command will |
| 2075 |
require <literal>SELECT</literal> privilege as well, since it must |
| 2076 |
reference table columns to determine which rows to delete.) |
| 2077 |
</para> |
| 2078 |
</listitem> |
| 2079 |
</varlistentry> |
| 2080 |
|
| 2081 |
<varlistentry id="ddl-priv-truncate"> |
| 2082 |
<term><literal>TRUNCATE</literal></term> |
| 2083 |
<listitem> |
| 2084 |
<para> |
| 2085 |
Allows <command>TRUNCATE</command> on a table. |
| 2086 |
</para> |
| 2087 |
</listitem> |
| 2088 |
</varlistentry> |
| 2089 |
|
| 2090 |
<varlistentry id="ddl-priv-references"> |
| 2091 |
<term><literal>REFERENCES</literal></term> |
| 2092 |
<listitem> |
| 2093 |
<para> |
| 2094 |
Allows creation of a foreign key constraint referencing a |
| 2095 |
table, or specific column(s) of a table. Great care should be taken when |
| 2096 |
granting this privilege, since a user who creates a foreign key can arrange |
| 2097 |
for enforcement of that foreign key to call an arbitrary function, such as |
| 2098 |
a cast function, and such functions will be called with the privileges of |
| 2099 |
the table owner. |
| 2100 |
</para> |
| 2101 |
</listitem> |
| 2102 |
</varlistentry> |
| 2103 |
|
| 2104 |
<varlistentry id="ddl-priv-trigger"> |
| 2105 |
<term><literal>TRIGGER</literal></term> |
| 2106 |
<listitem> |
| 2107 |
<para> |
| 2108 |
Allows creation of a trigger on a table, view, etc. Great care should be |
| 2109 |
taken when granting this privilege, since any triggers added to a table |
| 2110 |
or view will be executed with the privileges of users who modify it. |
| 2111 |
</para> |
| 2112 |
</listitem> |
| 2113 |
</varlistentry> |
| 2114 |
|
| 2115 |
<varlistentry id="ddl-priv-create"> |
| 2116 |
<term><literal>CREATE</literal></term> |
| 2117 |
<listitem> |
| 2118 |
<para> |
| 2119 |
For databases, allows new schemas and publications to be created within |
| 2120 |
the database, and allows trusted extensions to be installed within |
| 2121 |
the database. |
| 2122 |
</para> |
| 2123 |
<para> |
| 2124 |
For schemas, allows new objects to be created within the schema. |
| 2125 |
To rename an existing object, you must own the |
| 2126 |
object <emphasis>and</emphasis> have this privilege for the containing |
| 2127 |
schema. |
| 2128 |
</para> |
| 2129 |
<para> |
| 2130 |
For tablespaces, allows tables, indexes, and temporary files to be |
| 2131 |
created within the tablespace, and allows databases to be created that |
| 2132 |
have the tablespace as their default tablespace. |
| 2133 |
</para> |
| 2134 |
<para> |
| 2135 |
Note that revoking this privilege will not alter the existence or |
| 2136 |
location of existing objects. |
| 2137 |
</para> |
| 2138 |
</listitem> |
| 2139 |
</varlistentry> |
| 2140 |
|
| 2141 |
<varlistentry id="ddl-priv-connect"> |
| 2142 |
<term><literal>CONNECT</literal></term> |
| 2143 |
<listitem> |
| 2144 |
<para> |
| 2145 |
Allows the grantee to connect to the database. This |
| 2146 |
privilege is checked at connection startup (in addition to checking |
| 2147 |
any restrictions imposed by <filename>pg_hba.conf</filename>). |
| 2148 |
</para> |
| 2149 |
</listitem> |
| 2150 |
</varlistentry> |
| 2151 |
|
| 2152 |
<varlistentry id="ddl-priv-temporary"> |
| 2153 |
<term><literal>TEMPORARY</literal></term> |
| 2154 |
<listitem> |
| 2155 |
<para> |
| 2156 |
Allows temporary tables to be created while using the database. |
| 2157 |
</para> |
| 2158 |
</listitem> |
| 2159 |
</varlistentry> |
| 2160 |
|
| 2161 |
<varlistentry id="ddl-priv-execute"> |
| 2162 |
<term><literal>EXECUTE</literal></term> |
| 2163 |
<listitem> |
| 2164 |
<para> |
| 2165 |
Allows calling a function or procedure, including use of |
| 2166 |
any operators that are implemented on top of the function. This is the |
| 2167 |
only type of privilege that is applicable to functions and procedures. |
| 2168 |
</para> |
| 2169 |
</listitem> |
| 2170 |
</varlistentry> |
| 2171 |
|
| 2172 |
<varlistentry id="ddl-priv-usage"> |
| 2173 |
<term><literal>USAGE</literal></term> |
| 2174 |
<listitem> |
| 2175 |
<para> |
| 2176 |
For procedural languages, allows use of the language for |
| 2177 |
the creation of functions in that language. This is the only type |
| 2178 |
of privilege that is applicable to procedural languages. |
| 2179 |
</para> |
| 2180 |
<para> |
| 2181 |
For schemas, allows access to objects contained in the |
| 2182 |
schema (assuming that the objects' own privilege requirements are |
| 2183 |
also met). Essentially this allows the grantee to <quote>look up</quote> |
| 2184 |
objects within the schema. Without this permission, it is still |
| 2185 |
possible to see the object names, e.g., by querying system catalogs. |
| 2186 |
Also, after revoking this permission, existing sessions might have |
| 2187 |
statements that have previously performed this lookup, so this is not |
| 2188 |
a completely secure way to prevent object access. |
| 2189 |
</para> |
| 2190 |
<para> |
| 2191 |
For sequences, allows use of the |
| 2192 |
<function>currval</function> and <function>nextval</function> functions. |
| 2193 |
</para> |
| 2194 |
<para> |
| 2195 |
For types and domains, allows use of the type or domain in the |
| 2196 |
creation of tables, functions, and other schema objects. (Note that |
| 2197 |
this privilege does not control all <quote>usage</quote> of the |
| 2198 |
type, such as values of the type appearing in queries. It only |
| 2199 |
prevents objects from being created that depend on the type. The |
| 2200 |
main purpose of this privilege is controlling which users can create |
| 2201 |
dependencies on a type, which could prevent the owner from changing |
| 2202 |
the type later.) |
| 2203 |
</para> |
| 2204 |
<para> |
| 2205 |
For foreign-data wrappers, allows creation of new servers using the |
| 2206 |
foreign-data wrapper. |
| 2207 |
</para> |
| 2208 |
<para> |
| 2209 |
For foreign servers, allows creation of foreign tables using the |
| 2210 |
server. Grantees may also create, alter, or drop their own user |
| 2211 |
mappings associated with that server. |
| 2212 |
</para> |
| 2213 |
</listitem> |
| 2214 |
</varlistentry> |
| 2215 |
|
| 2216 |
<varlistentry id="ddl-priv-set"> |
| 2217 |
<term><literal>SET</literal></term> |
| 2218 |
<listitem> |
| 2219 |
<para> |
| 2220 |
Allows a server configuration parameter to be set to a new value |
| 2221 |
within the current session. (While this privilege can be granted |
| 2222 |
on any parameter, it is meaningless except for parameters that would |
| 2223 |
normally require superuser privilege to set.) |
| 2224 |
</para> |
| 2225 |
</listitem> |
| 2226 |
</varlistentry> |
| 2227 |
|
| 2228 |
<varlistentry id="ddl-priv-alter-system"> |
| 2229 |
<term><literal>ALTER SYSTEM</literal></term> |
| 2230 |
<listitem> |
| 2231 |
<para> |
| 2232 |
Allows a server configuration parameter to be configured to a new |
| 2233 |
value using the <xref linkend="sql-altersystem"/> command. |
| 2234 |
</para> |
| 2235 |
</listitem> |
| 2236 |
</varlistentry> |
| 2237 |
|
| 2238 |
<varlistentry id="ddl-priv-maintain"> |
| 2239 |
<term><literal>MAINTAIN</literal></term> |
| 2240 |
<listitem> |
| 2241 |
<para> |
| 2242 |
Allows <command>VACUUM</command>, <command>ANALYZE</command>, |
| 2243 |
<command>CLUSTER</command>, <command>REFRESH MATERIALIZED VIEW</command>, |
| 2244 |
<command>REINDEX</command>, <command>LOCK TABLE</command>, |
| 2245 |
and database object statistics manipulation functions |
| 2246 |
(see <xref linkend="functions-admin-statsmod"/>) on a relation. |
| 2247 |
</para> |
| 2248 |
</listitem> |
| 2249 |
</varlistentry> |
| 2250 |
</variablelist> |
| 2251 |
|
| 2252 |
The privileges required by other commands are listed on the |
| 2253 |
reference page of the respective command. |
| 2254 |
</para> |
| 2255 |
|
| 2256 |
<para id="ddl-priv-default"> |
| 2257 |
PostgreSQL grants privileges on some types of objects to |
| 2258 |
<literal>PUBLIC</literal> by default when the objects are created. |
| 2259 |
No privileges are granted to <literal>PUBLIC</literal> by default on |
| 2260 |
tables, |
| 2261 |
table columns, |
| 2262 |
sequences, |
| 2263 |
foreign data wrappers, |
| 2264 |
foreign servers, |
| 2265 |
large objects, |
| 2266 |
schemas, |
| 2267 |
tablespaces, |
| 2268 |
or configuration parameters. |
| 2269 |
For other types of objects, the default privileges |
| 2270 |
granted to <literal>PUBLIC</literal> are as follows: |
| 2271 |
<literal>CONNECT</literal> and <literal>TEMPORARY</literal> (create |
| 2272 |
temporary tables) privileges for databases; |
| 2273 |
<literal>EXECUTE</literal> privilege for functions and procedures; and |
| 2274 |
<literal>USAGE</literal> privilege for languages and data types |
| 2275 |
(including domains). |
| 2276 |
The object owner can, of course, <command>REVOKE</command> |
| 2277 |
both default and expressly granted privileges. (For maximum |
| 2278 |
security, issue the <command>REVOKE</command> in the same transaction that |
| 2279 |
creates the object; then there is no window in which another user |
| 2280 |
can use the object.) |
| 2281 |
Also, these default privilege settings can be overridden using the |
| 2282 |
<xref linkend="sql-alterdefaultprivileges"/> command. |
| 2283 |
</para> |
| 2284 |
|
| 2285 |
<para> |
| 2286 |
<xref linkend="privilege-abbrevs-table"/> shows the one-letter |
| 2287 |
abbreviations that are used for these privilege types in |
| 2288 |
<firstterm><acronym>ACL</acronym></firstterm> values. |
| 2289 |
You will see these letters in the output of the <xref linkend="app-psql"/> |
| 2290 |
commands listed below, or when looking at <acronym>ACL</acronym> columns |
| 2291 |
of system catalogs. |
| 2292 |
</para> |
| 2293 |
|
| 2294 |
<table id="privilege-abbrevs-table"> |
| 2295 |
<title><acronym>ACL</acronym> Privilege Abbreviations</title> |
| 2296 |
<tgroup cols="3"> |
| 2297 |
<colspec colname="col1" colwidth="1*"/> |
| 2298 |
<colspec colname="col2" colwidth="1*"/> |
| 2299 |
<colspec colname="col3" colwidth="2*"/> |
| 2300 |
<thead> |
| 2301 |
<row> |
| 2302 |
<entry>Privilege</entry> |
| 2303 |
<entry>Abbreviation</entry> |
| 2304 |
<entry>Applicable Object Types</entry> |
| 2305 |
</row> |
| 2306 |
</thead> |
| 2307 |
<tbody> |
| 2308 |
<row> |
| 2309 |
<entry><literal>SELECT</literal></entry> |
| 2310 |
<entry><literal>r</literal> (<quote>read</quote>)</entry> |
| 2311 |
<entry> |
| 2312 |
<literal>LARGE OBJECT</literal>, |
| 2313 |
<literal>SEQUENCE</literal>, |
| 2314 |
<literal>TABLE</literal> (and table-like objects), |
| 2315 |
table column |
| 2316 |
</entry> |
| 2317 |
</row> |
| 2318 |
<row> |
| 2319 |
<entry><literal>INSERT</literal></entry> |
| 2320 |
<entry><literal>a</literal> (<quote>append</quote>)</entry> |
| 2321 |
<entry><literal>TABLE</literal>, table column</entry> |
| 2322 |
</row> |
| 2323 |
<row> |
| 2324 |
<entry><literal>UPDATE</literal></entry> |
| 2325 |
<entry><literal>w</literal> (<quote>write</quote>)</entry> |
| 2326 |
<entry> |
| 2327 |
<literal>LARGE OBJECT</literal>, |
| 2328 |
<literal>SEQUENCE</literal>, |
| 2329 |
<literal>TABLE</literal>, |
| 2330 |
table column |
| 2331 |
</entry> |
| 2332 |
</row> |
| 2333 |
<row> |
| 2334 |
<entry><literal>DELETE</literal></entry> |
| 2335 |
<entry><literal>d</literal></entry> |
| 2336 |
<entry><literal>TABLE</literal></entry> |
| 2337 |
</row> |
| 2338 |
<row> |
| 2339 |
<entry><literal>TRUNCATE</literal></entry> |
| 2340 |
<entry><literal>D</literal></entry> |
| 2341 |
<entry><literal>TABLE</literal></entry> |
| 2342 |
</row> |
| 2343 |
<row> |
| 2344 |
<entry><literal>REFERENCES</literal></entry> |
| 2345 |
<entry><literal>x</literal></entry> |
| 2346 |
<entry><literal>TABLE</literal>, table column</entry> |
| 2347 |
</row> |
| 2348 |
<row> |
| 2349 |
<entry><literal>TRIGGER</literal></entry> |
| 2350 |
<entry><literal>t</literal></entry> |
| 2351 |
<entry><literal>TABLE</literal></entry> |
| 2352 |
</row> |
| 2353 |
<row> |
| 2354 |
<entry><literal>CREATE</literal></entry> |
| 2355 |
<entry><literal>C</literal></entry> |
| 2356 |
<entry> |
| 2357 |
<literal>DATABASE</literal>, |
| 2358 |
<literal>SCHEMA</literal>, |
| 2359 |
<literal>TABLESPACE</literal> |
| 2360 |
</entry> |
| 2361 |
</row> |
| 2362 |
<row> |
| 2363 |
<entry><literal>CONNECT</literal></entry> |
| 2364 |
<entry><literal>c</literal></entry> |
| 2365 |
<entry><literal>DATABASE</literal></entry> |
| 2366 |
</row> |
| 2367 |
<row> |
| 2368 |
<entry><literal>TEMPORARY</literal></entry> |
| 2369 |
<entry><literal>T</literal></entry> |
| 2370 |
<entry><literal>DATABASE</literal></entry> |
| 2371 |
</row> |
| 2372 |
<row> |
| 2373 |
<entry><literal>EXECUTE</literal></entry> |
| 2374 |
<entry><literal>X</literal></entry> |
| 2375 |
<entry><literal>FUNCTION</literal>, <literal>PROCEDURE</literal></entry> |
| 2376 |
</row> |
| 2377 |
<row> |
| 2378 |
<entry><literal>USAGE</literal></entry> |
| 2379 |
<entry><literal>U</literal></entry> |
| 2380 |
<entry> |
| 2381 |
<literal>DOMAIN</literal>, |
| 2382 |
<literal>FOREIGN DATA WRAPPER</literal>, |
| 2383 |
<literal>FOREIGN SERVER</literal>, |
| 2384 |
<literal>LANGUAGE</literal>, |
| 2385 |
<literal>SCHEMA</literal>, |
| 2386 |
<literal>SEQUENCE</literal>, |
| 2387 |
<literal>TYPE</literal> |
| 2388 |
</entry> |
| 2389 |
</row> |
| 2390 |
<row> |
| 2391 |
<entry><literal>SET</literal></entry> |
| 2392 |
<entry><literal>s</literal></entry> |
| 2393 |
<entry><literal>PARAMETER</literal></entry> |
| 2394 |
</row> |
| 2395 |
<row> |
| 2396 |
<entry><literal>ALTER SYSTEM</literal></entry> |
| 2397 |
<entry><literal>A</literal></entry> |
| 2398 |
<entry><literal>PARAMETER</literal></entry> |
| 2399 |
</row> |
| 2400 |
<row> |
| 2401 |
<entry><literal>MAINTAIN</literal></entry> |
| 2402 |
<entry><literal>m</literal></entry> |
| 2403 |
<entry><literal>TABLE</literal></entry> |
| 2404 |
</row> |
| 2405 |
</tbody> |
| 2406 |
</tgroup> |
| 2407 |
</table> |
| 2408 |
|
| 2409 |
<para> |
| 2410 |
<xref linkend="privileges-summary-table"/> summarizes the privileges |
| 2411 |
available for each type of SQL object, using the abbreviations shown |
| 2412 |
above. |
| 2413 |
It also shows the <application>psql</application> command |
| 2414 |
that can be used to examine privilege settings for each object type. |
| 2415 |
</para> |
| 2416 |
|
| 2417 |
<table id="privileges-summary-table"> |
| 2418 |
<title>Summary of Access Privileges</title> |
| 2419 |
<tgroup cols="4"> |
| 2420 |
<colspec colname="col1" colwidth="2*"/> |
| 2421 |
<colspec colname="col2" colwidth="1*"/> |
| 2422 |
<colspec colname="col3" colwidth="1*"/> |
| 2423 |
<colspec colname="col4" colwidth="1*"/> |
| 2424 |
<thead> |
| 2425 |
<row> |
| 2426 |
<entry>Object Type</entry> |
| 2427 |
<entry>All Privileges</entry> |
| 2428 |
<entry>Default <literal>PUBLIC</literal> Privileges</entry> |
| 2429 |
<entry><application>psql</application> Command</entry> |
| 2430 |
</row> |
| 2431 |
</thead> |
| 2432 |
<tbody> |
| 2433 |
<row> |
| 2434 |
<entry><literal>DATABASE</literal></entry> |
| 2435 |
<entry><literal>CTc</literal></entry> |
| 2436 |
<entry><literal>Tc</literal></entry> |
| 2437 |
<entry><literal>\l</literal></entry> |
| 2438 |
</row> |
| 2439 |
<row> |
| 2440 |
<entry><literal>DOMAIN</literal></entry> |
| 2441 |
<entry><literal>U</literal></entry> |
| 2442 |
<entry><literal>U</literal></entry> |
| 2443 |
<entry><literal>\dD+</literal></entry> |
| 2444 |
</row> |
| 2445 |
<row> |
| 2446 |
<entry><literal>FUNCTION</literal> or <literal>PROCEDURE</literal></entry> |
| 2447 |
<entry><literal>X</literal></entry> |
| 2448 |
<entry><literal>X</literal></entry> |
| 2449 |
<entry><literal>\df+</literal></entry> |
| 2450 |
</row> |
| 2451 |
<row> |
| 2452 |
<entry><literal>FOREIGN DATA WRAPPER</literal></entry> |
| 2453 |
<entry><literal>U</literal></entry> |
| 2454 |
<entry>none</entry> |
| 2455 |
<entry><literal>\dew+</literal></entry> |
| 2456 |
</row> |
| 2457 |
<row> |
| 2458 |
<entry><literal>FOREIGN SERVER</literal></entry> |
| 2459 |
<entry><literal>U</literal></entry> |
| 2460 |
<entry>none</entry> |
| 2461 |
<entry><literal>\des+</literal></entry> |
| 2462 |
</row> |
| 2463 |
<row> |
| 2464 |
<entry><literal>LANGUAGE</literal></entry> |
| 2465 |
<entry><literal>U</literal></entry> |
| 2466 |
<entry><literal>U</literal></entry> |
| 2467 |
<entry><literal>\dL+</literal></entry> |
| 2468 |
</row> |
| 2469 |
<row> |
| 2470 |
<entry><literal>LARGE OBJECT</literal></entry> |
| 2471 |
<entry><literal>rw</literal></entry> |
| 2472 |
<entry>none</entry> |
| 2473 |
<entry><literal>\dl+</literal></entry> |
| 2474 |
</row> |
| 2475 |
<row> |
| 2476 |
<entry><literal>PARAMETER</literal></entry> |
| 2477 |
<entry><literal>sA</literal></entry> |
| 2478 |
<entry>none</entry> |
| 2479 |
<entry><literal>\dconfig+</literal></entry> |
| 2480 |
</row> |
| 2481 |
<row> |
| 2482 |
<entry><literal>SCHEMA</literal></entry> |
| 2483 |
<entry><literal>UC</literal></entry> |
| 2484 |
<entry>none</entry> |
| 2485 |
<entry><literal>\dn+</literal></entry> |
| 2486 |
</row> |
| 2487 |
<row> |
| 2488 |
<entry><literal>SEQUENCE</literal></entry> |
| 2489 |
<entry><literal>rwU</literal></entry> |
| 2490 |
<entry>none</entry> |
| 2491 |
<entry><literal>\dp</literal></entry> |
| 2492 |
</row> |
| 2493 |
<row> |
| 2494 |
<entry><literal>TABLE</literal> (and table-like objects)</entry> |
| 2495 |
<entry><literal>arwdDxtm</literal></entry> |
| 2496 |
<entry>none</entry> |
| 2497 |
<entry><literal>\dp</literal></entry> |
| 2498 |
</row> |
| 2499 |
<row> |
| 2500 |
<entry>Table column</entry> |
| 2501 |
<entry><literal>arwx</literal></entry> |
| 2502 |
<entry>none</entry> |
| 2503 |
<entry><literal>\dp</literal></entry> |
| 2504 |
</row> |
| 2505 |
<row> |
| 2506 |
<entry><literal>TABLESPACE</literal></entry> |
| 2507 |
<entry><literal>C</literal></entry> |
| 2508 |
<entry>none</entry> |
| 2509 |
<entry><literal>\db+</literal></entry> |
| 2510 |
</row> |
| 2511 |
<row> |
| 2512 |
<entry><literal>TYPE</literal></entry> |
| 2513 |
<entry><literal>U</literal></entry> |
| 2514 |
<entry><literal>U</literal></entry> |
| 2515 |
<entry><literal>\dT+</literal></entry> |
| 2516 |
</row> |
| 2517 |
</tbody> |
| 2518 |
</tgroup> |
| 2519 |
</table> |
| 2520 |
|
| 2521 |
<para> |
| 2522 |
<indexterm> |
| 2523 |
<primary><type>aclitem</type></primary> |
| 2524 |
</indexterm> |
| 2525 |
The privileges that have been granted for a particular object are |
| 2526 |
displayed as a list of <type>aclitem</type> entries, each having the |
| 2527 |
format: |
| 2528 |
<synopsis> |
| 2529 |
<replaceable>grantee</replaceable><literal>=</literal><replaceable>privilege-abbreviation</replaceable><optional><literal>*</literal></optional>...<literal>/</literal><replaceable>grantor</replaceable> |
| 2530 |
</synopsis> |
| 2531 |
Each <type>aclitem</type> lists all the permissions of one grantee that |
| 2532 |
have been granted by a particular grantor. Specific privileges are |
| 2533 |
represented by one-letter abbreviations from |
| 2534 |
<xref linkend="privilege-abbrevs-table"/>, with <literal>*</literal> |
| 2535 |
appended if the privilege was granted with grant option. For example, |
| 2536 |
<literal>calvin=r*w/hobbes</literal> specifies that the role |
| 2537 |
<literal>calvin</literal> has the privilege |
| 2538 |
<literal>SELECT</literal> (<literal>r</literal>) with grant option |
| 2539 |
(<literal>*</literal>) as well as the non-grantable |
| 2540 |
privilege <literal>UPDATE</literal> (<literal>w</literal>), both granted |
| 2541 |
by the role <literal>hobbes</literal>. If <literal>calvin</literal> |
| 2542 |
also has some privileges on the same object granted by a different |
| 2543 |
grantor, those would appear as a separate <type>aclitem</type> entry. |
| 2544 |
An empty grantee field in an <type>aclitem</type> stands |
| 2545 |
for <literal>PUBLIC</literal>. |
| 2546 |
</para> |
| 2547 |
|
| 2548 |
<para> |
| 2549 |
As an example, suppose that user <literal>miriam</literal> creates |
| 2550 |
table <literal>mytable</literal> and does: |
| 2551 |
<programlisting> |
| 2552 |
GRANT SELECT ON mytable TO PUBLIC; |
| 2553 |
GRANT SELECT, UPDATE, INSERT ON mytable TO admin; |
| 2554 |
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; |
| 2555 |
</programlisting> |
| 2556 |
Then <application>psql</application>'s <literal>\dp</literal> command |
| 2557 |
would show: |
| 2558 |
<programlisting> |
| 2559 |
=> \dp mytable |
| 2560 |
Access privileges |
| 2561 |
Schema | Name | Type | Access privileges | Column privileges | Policies |
| 2562 |
--------+---------+-------+------------------------+-----------------------+---------- |
| 2563 |
public | mytable | table | miriam=arwdDxtm/miriam+| col1: +| |
| 2564 |
| | | =r/miriam +| miriam_rw=rw/miriam | |
| 2565 |
| | | admin=arw/miriam | | |
| 2566 |
(1 row) |
| 2567 |
</programlisting> |
| 2568 |
</para> |
| 2569 |
|
| 2570 |
<para> |
| 2571 |
If the <quote>Access privileges</quote> column is empty for a given |
| 2572 |
object, it means the object has default privileges (that is, its |
| 2573 |
privileges entry in the relevant system catalog is null). Default |
| 2574 |
privileges always include all privileges for the owner, and can include |
| 2575 |
some privileges for <literal>PUBLIC</literal> depending on the object |
| 2576 |
type, as explained above. The first <command>GRANT</command> |
| 2577 |
or <command>REVOKE</command> on an object will instantiate the default |
| 2578 |
privileges (producing, for |
| 2579 |
example, <literal>miriam=arwdDxt/miriam</literal>) and then modify them |
| 2580 |
per the specified request. Similarly, entries are shown in <quote>Column |
| 2581 |
privileges</quote> only for columns with nondefault privileges. |
| 2582 |
(Note: for this purpose, <quote>default privileges</quote> always means |
| 2583 |
the built-in default privileges for the object's type. An object whose |
| 2584 |
privileges have been affected by an <command>ALTER DEFAULT |
| 2585 |
PRIVILEGES</command> command will always be shown with an explicit |
| 2586 |
privilege entry that includes the effects of |
| 2587 |
the <command>ALTER</command>.) |
| 2588 |
</para> |
| 2589 |
|
| 2590 |
<para> |
| 2591 |
Notice that the owner's implicit grant options are not marked in the |
| 2592 |
access privileges display. A <literal>*</literal> will appear only when |
| 2593 |
grant options have been explicitly granted to someone. |
| 2594 |
</para> |
| 2595 |
|
| 2596 |
<para> |
| 2597 |
The <quote>Access privileges</quote> column |
| 2598 |
shows <literal>(none)</literal> when the object's privileges entry is |
| 2599 |
non-null but empty. This means that no privileges are granted at all, |
| 2600 |
even to the object's owner — a rare situation. (The owner still |
| 2601 |
has implicit grant options in this case, and so could re-grant her own |
| 2602 |
privileges; but she has none at the moment.) |
| 2603 |
</para> |
| 2604 |
</sect1> |
| 2605 |
|
| 2606 |
<sect1 id="ddl-rowsecurity"> |
| 2607 |
<title>Row Security Policies</title> |
| 2608 |
|
| 2609 |
<indexterm zone="ddl-rowsecurity"> |
| 2610 |
<primary>row-level security</primary> |
| 2611 |
</indexterm> |
| 2612 |
|
| 2613 |
<indexterm zone="ddl-rowsecurity"> |
| 2614 |
<primary>policy</primary> |
| 2615 |
</indexterm> |
| 2616 |
|
| 2617 |
<para> |
| 2618 |
In addition to the SQL-standard <link linkend="ddl-priv">privilege |
| 2619 |
system</link> available through <xref linkend="sql-grant"/>, |
| 2620 |
tables can have <firstterm>row security policies</firstterm> that restrict, |
| 2621 |
on a per-user basis, which rows can be returned by normal queries |
| 2622 |
or inserted, updated, or deleted by data modification commands. |
| 2623 |
This feature is also known as <firstterm>Row-Level Security</firstterm>. |
| 2624 |
By default, tables do not have any policies, so that if a user has |
| 2625 |
access privileges to a table according to the SQL privilege system, |
| 2626 |
all rows within it are equally available for querying or updating. |
| 2627 |
</para> |
| 2628 |
|
| 2629 |
<para> |
| 2630 |
When row security is enabled on a table (with |
| 2631 |
<link linkend="sql-altertable">ALTER TABLE ... ENABLE ROW LEVEL |
| 2632 |
SECURITY</link>), all normal access to the table for selecting rows or |
| 2633 |
modifying rows must be allowed by a row security policy. (However, the |
| 2634 |
table's owner is typically not subject to row security policies.) If no |
| 2635 |
policy exists for the table, a default-deny policy is used, meaning that |
| 2636 |
no rows are visible or can be modified. Operations that apply to the |
| 2637 |
whole table, such as <command>TRUNCATE</command> and <literal>REFERENCES</literal>, |
| 2638 |
are not subject to row security. |
| 2639 |
</para> |
| 2640 |
|
| 2641 |
<para> |
| 2642 |
Row security policies can be specific to commands, or to roles, or to |
| 2643 |
both. A policy can be specified to apply to <literal>ALL</literal> |
| 2644 |
commands, or to <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, |
| 2645 |
or <literal>DELETE</literal>. Multiple roles can be assigned to a given |
| 2646 |
policy, and normal role membership and inheritance rules apply. |
| 2647 |
</para> |
| 2648 |
|
| 2649 |
<para> |
| 2650 |
To specify which rows are visible or modifiable according to a policy, |
| 2651 |
an expression is required that returns a Boolean result. This |
| 2652 |
expression will be evaluated for each row prior to any conditions or |
| 2653 |
functions coming from the user's query. (The only exceptions to this |
| 2654 |
rule are <literal>leakproof</literal> functions, which are guaranteed to |
| 2655 |
not leak information; the optimizer may choose to apply such functions |
| 2656 |
ahead of the row-security check.) Rows for which the expression does |
| 2657 |
not return <literal>true</literal> will not be processed. Separate expressions |
| 2658 |
may be specified to provide independent control over the rows which are |
| 2659 |
visible and the rows which are allowed to be modified. Policy |
| 2660 |
expressions are run as part of the query and with the privileges of the |
| 2661 |
user running the query, although security-definer functions can be used |
| 2662 |
to access data not available to the calling user. |
| 2663 |
</para> |
| 2664 |
|
| 2665 |
<para> |
| 2666 |
Superusers and roles with the <literal>BYPASSRLS</literal> attribute always |
| 2667 |
bypass the row security system when accessing a table. Table owners |
| 2668 |
normally bypass row security as well, though a table owner can choose to |
| 2669 |
be subject to row security with <link linkend="sql-altertable">ALTER |
| 2670 |
TABLE ... FORCE ROW LEVEL SECURITY</link>. |
| 2671 |
</para> |
| 2672 |
|
| 2673 |
<para> |
| 2674 |
Enabling and disabling row security, as well as adding policies to a |
| 2675 |
table, is always the privilege of the table owner only. |
| 2676 |
</para> |
| 2677 |
|
| 2678 |
<para> |
| 2679 |
Policies are created using the <xref linkend="sql-createpolicy"/> |
| 2680 |
command, altered using the <xref linkend="sql-alterpolicy"/> command, |
| 2681 |
and dropped using the <xref linkend="sql-droppolicy"/> command. To |
| 2682 |
enable and disable row security for a given table, use the |
| 2683 |
<xref linkend="sql-altertable"/> command. |
| 2684 |
</para> |
| 2685 |
|
| 2686 |
<para> |
| 2687 |
Each policy has a name and multiple policies can be defined for a |
| 2688 |
table. As policies are table-specific, each policy for a table must |
| 2689 |
have a unique name. Different tables may have policies with the |
| 2690 |
same name. |
| 2691 |
</para> |
| 2692 |
|
| 2693 |
<para> |
| 2694 |
When multiple policies apply to a given query, they are combined using |
| 2695 |
either <literal>OR</literal> (for permissive policies, which are the |
| 2696 |
default) or using <literal>AND</literal> (for restrictive policies). |
| 2697 |
The <literal>OR</literal> behavior is similar to the rule that a given |
| 2698 |
role has the privileges |
| 2699 |
of all roles that they are a member of. Permissive vs. restrictive |
| 2700 |
policies are discussed further below. |
| 2701 |
</para> |
| 2702 |
|
| 2703 |
<para> |
| 2704 |
As a simple example, here is how to create a policy on |
| 2705 |
the <literal>account</literal> relation to allow only members of |
| 2706 |
the <literal>managers</literal> role to access rows, and only rows of their |
| 2707 |
accounts: |
| 2708 |
</para> |
| 2709 |
|
| 2710 |
<programlisting> |
| 2711 |
CREATE TABLE accounts (manager text, company text, contact_email text); |
| 2712 |
|
| 2713 |
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; |
| 2714 |
|
| 2715 |
CREATE POLICY account_managers ON accounts TO managers |
| 2716 |
USING (manager = current_user); |
| 2717 |
</programlisting> |
| 2718 |
|
| 2719 |
<para> |
| 2720 |
The policy above implicitly provides a <literal>WITH CHECK</literal> |
| 2721 |
clause identical to its <literal>USING</literal> clause, so that the |
| 2722 |
constraint applies both to rows selected by a command (so a manager |
| 2723 |
cannot <command>SELECT</command>, <command>UPDATE</command>, |
| 2724 |
or <command>DELETE</command> existing rows belonging to a different |
| 2725 |
manager) and to rows modified by a command (so rows belonging to a |
| 2726 |
different manager cannot be created via <command>INSERT</command> |
| 2727 |
or <command>UPDATE</command>). |
| 2728 |
</para> |
| 2729 |
|
| 2730 |
<para> |
| 2731 |
If no role is specified, or the special user name |
| 2732 |
<literal>PUBLIC</literal> is used, then the policy applies to all |
| 2733 |
users on the system. To allow all users to access only their own row in |
| 2734 |
a <literal>users</literal> table, a simple policy can be used: |
| 2735 |
</para> |
| 2736 |
|
| 2737 |
<programlisting> |
| 2738 |
CREATE POLICY user_policy ON users |
| 2739 |
USING (user_name = current_user); |
| 2740 |
</programlisting> |
| 2741 |
|
| 2742 |
<para> |
| 2743 |
This works similarly to the previous example. |
| 2744 |
</para> |
| 2745 |
|
| 2746 |
<para> |
| 2747 |
To use a different policy for rows that are being added to the table |
| 2748 |
compared to those rows that are visible, multiple policies can be |
| 2749 |
combined. This pair of policies would allow all users to view all rows |
| 2750 |
in the <literal>users</literal> table, but only modify their own: |
| 2751 |
</para> |
| 2752 |
|
| 2753 |
<programlisting> |
| 2754 |
CREATE POLICY user_sel_policy ON users |
| 2755 |
FOR SELECT |
| 2756 |
USING (true); |
| 2757 |
CREATE POLICY user_mod_policy ON users |
| 2758 |
USING (user_name = current_user); |
| 2759 |
</programlisting> |
| 2760 |
|
| 2761 |
<para> |
| 2762 |
In a <command>SELECT</command> command, these two policies are combined |
| 2763 |
using <literal>OR</literal>, with the net effect being that all rows |
| 2764 |
can be selected. In other command types, only the second policy applies, |
| 2765 |
so that the effects are the same as before. |
| 2766 |
</para> |
| 2767 |
|
| 2768 |
<para> |
| 2769 |
Row security can also be disabled with the <command>ALTER TABLE</command> |
| 2770 |
command. Disabling row security does not remove any policies that are |
| 2771 |
defined on the table; they are simply ignored. Then all rows in the |
| 2772 |
table are visible and modifiable, subject to the standard SQL privileges |
| 2773 |
system. |
| 2774 |
</para> |
| 2775 |
|
| 2776 |
<para> |
| 2777 |
Below is a larger example of how this feature can be used in production |
| 2778 |
environments. The table <literal>passwd</literal> emulates a Unix password |
| 2779 |
file: |
| 2780 |
</para> |
| 2781 |
|
| 2782 |
<programlisting> |
| 2783 |
-- Simple passwd-file based example |
| 2784 |
CREATE TABLE passwd ( |
| 2785 |
user_name text UNIQUE NOT NULL, |
| 2786 |
pwhash text, |
| 2787 |
uid int PRIMARY KEY, |
| 2788 |
gid int NOT NULL, |
| 2789 |
real_name text NOT NULL, |
| 2790 |
home_phone text, |
| 2791 |
extra_info text, |
| 2792 |
home_dir text NOT NULL, |
| 2793 |
shell text NOT NULL |
| 2794 |
); |
| 2795 |
|
| 2796 |
CREATE ROLE admin; -- Administrator |
| 2797 |
CREATE ROLE bob; -- Normal user |
| 2798 |
CREATE ROLE alice; -- Normal user |
| 2799 |
|
| 2800 |
-- Populate the table |
| 2801 |
INSERT INTO passwd VALUES |
| 2802 |
('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash'); |
| 2803 |
INSERT INTO passwd VALUES |
| 2804 |
('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh'); |
| 2805 |
INSERT INTO passwd VALUES |
| 2806 |
('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh'); |
| 2807 |
|
| 2808 |
-- Be sure to enable row-level security on the table |
| 2809 |
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; |
| 2810 |
|
| 2811 |
-- Create policies |
| 2812 |
-- Administrator can see all rows and add any rows |
| 2813 |
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); |
| 2814 |
-- Normal users can view all rows |
| 2815 |
CREATE POLICY all_view ON passwd FOR SELECT USING (true); |
| 2816 |
-- Normal users can update their own records, but |
| 2817 |
-- limit which shells a normal user is allowed to set |
| 2818 |
CREATE POLICY user_mod ON passwd FOR UPDATE |
| 2819 |
USING (current_user = user_name) |
| 2820 |
WITH CHECK ( |
| 2821 |
current_user = user_name AND |
| 2822 |
shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') |
| 2823 |
); |
| 2824 |
|
| 2825 |
-- Allow admin all normal rights |
| 2826 |
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; |
| 2827 |
-- Users only get select access on public columns |
| 2828 |
GRANT SELECT |
| 2829 |
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) |
| 2830 |
ON passwd TO public; |
| 2831 |
-- Allow users to update certain columns |
| 2832 |
GRANT UPDATE |
| 2833 |
(pwhash, real_name, home_phone, extra_info, shell) |
| 2834 |
ON passwd TO public; |
| 2835 |
</programlisting> |
| 2836 |
|
| 2837 |
<para> |
| 2838 |
As with any security settings, it's important to test and ensure that |
| 2839 |
the system is behaving as expected. Using the example above, this |
| 2840 |
demonstrates that the permission system is working properly. |
| 2841 |
</para> |
| 2842 |
|
| 2843 |
<programlisting> |
| 2844 |
-- admin can view all rows and fields |
| 2845 |
postgres=> set role admin; |
| 2846 |
SET |
| 2847 |
postgres=> table passwd; |
| 2848 |
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell |
| 2849 |
-----------+--------+-----+-----+-----------+--------------+------------+-------------+----------- |
| 2850 |
admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash |
| 2851 |
bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh |
| 2852 |
alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh |
| 2853 |
(3 rows) |
| 2854 |
|
| 2855 |
-- Test what Alice is able to do |
| 2856 |
postgres=> set role alice; |
| 2857 |
SET |
| 2858 |
postgres=> table passwd; |
| 2859 |
ERROR: permission denied for table passwd |
| 2860 |
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd; |
| 2861 |
user_name | real_name | home_phone | extra_info | home_dir | shell |
| 2862 |
-----------+-----------+--------------+------------+-------------+----------- |
| 2863 |
admin | Admin | 111-222-3333 | | /root | /bin/dash |
| 2864 |
bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh |
| 2865 |
alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh |
| 2866 |
(3 rows) |
| 2867 |
|
| 2868 |
postgres=> update passwd set user_name = 'joe'; |
| 2869 |
ERROR: permission denied for table passwd |
| 2870 |
-- Alice is allowed to change her own real_name, but no others |
| 2871 |
postgres=> update passwd set real_name = 'Alice Doe'; |
| 2872 |
UPDATE 1 |
| 2873 |
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin'; |
| 2874 |
UPDATE 0 |
| 2875 |
postgres=> update passwd set shell = '/bin/xx'; |
| 2876 |
ERROR: new row violates WITH CHECK OPTION for "passwd" |
| 2877 |
postgres=> delete from passwd; |
| 2878 |
ERROR: permission denied for table passwd |
| 2879 |
postgres=> insert into passwd (user_name) values ('xxx'); |
| 2880 |
ERROR: permission denied for table passwd |
| 2881 |
-- Alice can change her own password; RLS silently prevents updating other rows |
| 2882 |
postgres=> update passwd set pwhash = 'abc'; |
| 2883 |
UPDATE 1 |
| 2884 |
</programlisting> |
| 2885 |
|
| 2886 |
<para> |
| 2887 |
All of the policies constructed thus far have been permissive policies, |
| 2888 |
meaning that when multiple policies are applied they are combined using |
| 2889 |
the <quote>OR</quote> Boolean operator. While permissive policies can be constructed |
| 2890 |
to only allow access to rows in the intended cases, it can be simpler to |
| 2891 |
combine permissive policies with restrictive policies (which the records |
| 2892 |
must pass and which are combined using the <quote>AND</quote> Boolean operator). |
| 2893 |
Building on the example above, we add a restrictive policy to require |
| 2894 |
the administrator to be connected over a local Unix socket to access the |
| 2895 |
records of the <literal>passwd</literal> table: |
| 2896 |
</para> |
| 2897 |
|
| 2898 |
<programlisting> |
| 2899 |
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin |
| 2900 |
USING (pg_catalog.inet_client_addr() IS NULL); |
| 2901 |
</programlisting> |
| 2902 |
|
| 2903 |
<para> |
| 2904 |
We can then see that an administrator connecting over a network will not |
| 2905 |
see any records, due to the restrictive policy: |
| 2906 |
</para> |
| 2907 |
|
| 2908 |
<programlisting> |
| 2909 |
=> SELECT current_user; |
| 2910 |
current_user |
| 2911 |
-------------- |
| 2912 |
admin |
| 2913 |
(1 row) |
| 2914 |
|
| 2915 |
=> select inet_client_addr(); |
| 2916 |
inet_client_addr |
| 2917 |
------------------ |
| 2918 |
127.0.0.1 |
| 2919 |
(1 row) |
| 2920 |
|
| 2921 |
=> TABLE passwd; |
| 2922 |
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell |
| 2923 |
-----------+--------+-----+-----+-----------+------------+------------+----------+------- |
| 2924 |
(0 rows) |
| 2925 |
|
| 2926 |
=> UPDATE passwd set pwhash = NULL; |
| 2927 |
UPDATE 0 |
| 2928 |
</programlisting> |
| 2929 |
|
| 2930 |
<para> |
| 2931 |
Referential integrity checks, such as unique or primary key constraints |
| 2932 |
and foreign key references, always bypass row security to ensure that |
| 2933 |
data integrity is maintained. Care must be taken when developing |
| 2934 |
schemas and row level policies to avoid <quote>covert channel</quote> leaks of |
| 2935 |
information through such referential integrity checks. |
| 2936 |
</para> |
| 2937 |
|
| 2938 |
<para> |
| 2939 |
In some contexts it is important to be sure that row security is |
| 2940 |
not being applied. For example, when taking a backup, it could be |
| 2941 |
disastrous if row security silently caused some rows to be omitted |
| 2942 |
from the backup. In such a situation, you can set the |
| 2943 |
<xref linkend="guc-row-security"/> configuration parameter |
| 2944 |
to <literal>off</literal>. This does not in itself bypass row security; |
| 2945 |
what it does is throw an error if any query's results would get filtered |
| 2946 |
by a policy. The reason for the error can then be investigated and |
| 2947 |
fixed. |
| 2948 |
</para> |
| 2949 |
|
| 2950 |
<para> |
| 2951 |
In the examples above, the policy expressions consider only the current |
| 2952 |
values in the row to be accessed or updated. This is the simplest and |
| 2953 |
best-performing case; when possible, it's best to design row security |
| 2954 |
applications to work this way. If it is necessary to consult other rows |
| 2955 |
or other tables to make a policy decision, that can be accomplished using |
| 2956 |
sub-<command>SELECT</command>s, or functions that contain <command>SELECT</command>s, |
| 2957 |
in the policy expressions. Be aware however that such accesses can |
| 2958 |
create race conditions that could allow information leakage if care is |
| 2959 |
not taken. As an example, consider the following table design: |
| 2960 |
</para> |
| 2961 |
|
| 2962 |
<programlisting> |
| 2963 |
-- definition of privilege groups |
| 2964 |
CREATE TABLE groups (group_id int PRIMARY KEY, |
| 2965 |
group_name text NOT NULL); |
| 2966 |
|
| 2967 |
INSERT INTO groups VALUES |
| 2968 |
(1, 'low'), |
| 2969 |
(2, 'medium'), |
| 2970 |
(5, 'high'); |
| 2971 |
|
| 2972 |
GRANT ALL ON groups TO alice; -- alice is the administrator |
| 2973 |
GRANT SELECT ON groups TO public; |
| 2974 |
|
| 2975 |
-- definition of users' privilege levels |
| 2976 |
CREATE TABLE users (user_name text PRIMARY KEY, |
| 2977 |
group_id int NOT NULL REFERENCES groups); |
| 2978 |
|
| 2979 |
INSERT INTO users VALUES |
| 2980 |
('alice', 5), |
| 2981 |
('bob', 2), |
| 2982 |
('mallory', 2); |
| 2983 |
|
| 2984 |
GRANT ALL ON users TO alice; |
| 2985 |
GRANT SELECT ON users TO public; |
| 2986 |
|
| 2987 |
-- table holding the information to be protected |
| 2988 |
CREATE TABLE information (info text, |
| 2989 |
group_id int NOT NULL REFERENCES groups); |
| 2990 |
|
| 2991 |
INSERT INTO information VALUES |
| 2992 |
('barely secret', 1), |
| 2993 |
('slightly secret', 2), |
| 2994 |
('very secret', 5); |
| 2995 |
|
| 2996 |
ALTER TABLE information ENABLE ROW LEVEL SECURITY; |
| 2997 |
|
| 2998 |
-- a row should be visible to/updatable by users whose security group_id is |
| 2999 |
-- greater than or equal to the row's group_id |
| 3000 |
CREATE POLICY fp_s ON information FOR SELECT |
| 3001 |
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); |
| 3002 |
CREATE POLICY fp_u ON information FOR UPDATE |
| 3003 |
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); |
| 3004 |
|
| 3005 |
-- we rely only on RLS to protect the information table |
| 3006 |
GRANT ALL ON information TO public; |
| 3007 |
</programlisting> |
| 3008 |
|
| 3009 |
<para> |
| 3010 |
Now suppose that <literal>alice</literal> wishes to change the <quote>slightly |
| 3011 |
secret</quote> information, but decides that <literal>mallory</literal> should not |
| 3012 |
be trusted with the new content of that row, so she does: |
| 3013 |
</para> |
| 3014 |
|
| 3015 |
<programlisting> |
| 3016 |
BEGIN; |
| 3017 |
UPDATE users SET group_id = 1 WHERE user_name = 'mallory'; |
| 3018 |
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2; |
| 3019 |
COMMIT; |
| 3020 |
</programlisting> |
| 3021 |
|
| 3022 |
<para> |
| 3023 |
That looks safe; there is no window wherein <literal>mallory</literal> should be |
| 3024 |
able to see the <quote>secret from mallory</quote> string. However, there is |
| 3025 |
a race condition here. If <literal>mallory</literal> is concurrently doing, |
| 3026 |
say, |
| 3027 |
<programlisting> |
| 3028 |
SELECT * FROM information WHERE group_id = 2 FOR UPDATE; |
| 3029 |
</programlisting> |
| 3030 |
and her transaction is in <literal>READ COMMITTED</literal> mode, it is possible |
| 3031 |
for her to see <quote>secret from mallory</quote>. That happens if her |
| 3032 |
transaction reaches the <structname>information</structname> row just |
| 3033 |
after <literal>alice</literal>'s does. It blocks waiting |
| 3034 |
for <literal>alice</literal>'s transaction to commit, then fetches the updated |
| 3035 |
row contents thanks to the <literal>FOR UPDATE</literal> clause. However, it |
| 3036 |
does <emphasis>not</emphasis> fetch an updated row for the |
| 3037 |
implicit <command>SELECT</command> from <structname>users</structname>, because that |
| 3038 |
sub-<command>SELECT</command> did not have <literal>FOR UPDATE</literal>; instead |
| 3039 |
the <structname>users</structname> row is read with the snapshot taken at the start |
| 3040 |
of the query. Therefore, the policy expression tests the old value |
| 3041 |
of <literal>mallory</literal>'s privilege level and allows her to see the |
| 3042 |
updated row. |
| 3043 |
</para> |
| 3044 |
|
| 3045 |
<para> |
| 3046 |
There are several ways around this problem. One simple answer is to use |
| 3047 |
<literal>SELECT ... FOR SHARE</literal> in sub-<command>SELECT</command>s in row |
| 3048 |
security policies. However, that requires granting <literal>UPDATE</literal> |
| 3049 |
privilege on the referenced table (here <structname>users</structname>) to the |
| 3050 |
affected users, which might be undesirable. (But another row security |
| 3051 |
policy could be applied to prevent them from actually exercising that |
| 3052 |
privilege; or the sub-<command>SELECT</command> could be embedded into a security |
| 3053 |
definer function.) Also, heavy concurrent use of row share locks on the |
| 3054 |
referenced table could pose a performance problem, especially if updates |
| 3055 |
of it are frequent. Another solution, practical if updates of the |
| 3056 |
referenced table are infrequent, is to take an |
| 3057 |
<literal>ACCESS EXCLUSIVE</literal> lock on the |
| 3058 |
referenced table when updating it, so that no concurrent transactions |
| 3059 |
could be examining old row values. Or one could just wait for all |
| 3060 |
concurrent transactions to end after committing an update of the |
| 3061 |
referenced table and before making changes that rely on the new security |
| 3062 |
situation. |
| 3063 |
</para> |
| 3064 |
|
| 3065 |
<para> |
| 3066 |
For additional details see <xref linkend="sql-createpolicy"/> |
| 3067 |
and <xref linkend="sql-altertable"/>. |
| 3068 |
</para> |
| 3069 |
|
| 3070 |
</sect1> |
| 3071 |
|
| 3072 |
<sect1 id="ddl-schemas"> |
| 3073 |
<title>Schemas</title> |
| 3074 |
|
| 3075 |
<indexterm zone="ddl-schemas"> |
| 3076 |
<primary>schema</primary> |
| 3077 |
</indexterm> |
| 3078 |
|
| 3079 |
<para> |
| 3080 |
A <productname>PostgreSQL</productname> database cluster contains |
| 3081 |
one or more named databases. Roles and a few other object types are |
| 3082 |
shared across the entire cluster. A client connection to the server |
| 3083 |
can only access data in a single database, the one specified in the |
| 3084 |
connection request. |
| 3085 |
</para> |
| 3086 |
|
| 3087 |
<note> |
| 3088 |
<para> |
| 3089 |
Users of a cluster do not necessarily have the privilege to access every |
| 3090 |
database in the cluster. Sharing of role names means that there |
| 3091 |
cannot be different roles named, say, <literal>joe</literal> in two databases |
| 3092 |
in the same cluster; but the system can be configured to allow |
| 3093 |
<literal>joe</literal> access to only some of the databases. |
| 3094 |
</para> |
| 3095 |
</note> |
| 3096 |
|
| 3097 |
<para> |
| 3098 |
A database contains one or more named <firstterm>schemas</firstterm>, which |
| 3099 |
in turn contain tables. Schemas also contain other kinds of named |
| 3100 |
objects, including data types, functions, and operators. Within one |
| 3101 |
schema, two objects of the same type cannot have the same name. |
| 3102 |
Furthermore, tables, sequences, indexes, views, materialized views, and |
| 3103 |
foreign tables share the same namespace, so that, for example, an index and |
| 3104 |
a table must have different names if they are in the same schema. The same |
| 3105 |
object name can be used in different schemas without conflict; for |
| 3106 |
example, both <literal>schema1</literal> and <literal>myschema</literal> can |
| 3107 |
contain tables named <literal>mytable</literal>. Unlike databases, |
| 3108 |
schemas are not rigidly separated: a user can access objects in any |
| 3109 |
of the schemas in the database they are connected to, if they have |
| 3110 |
privileges to do so. |
| 3111 |
</para> |
| 3112 |
|
| 3113 |
<para> |
| 3114 |
There are several reasons why one might want to use schemas: |
| 3115 |
|
| 3116 |
<itemizedlist> |
| 3117 |
<listitem> |
| 3118 |
<para> |
| 3119 |
To allow many users to use one database without interfering with |
| 3120 |
each other. |
| 3121 |
</para> |
| 3122 |
</listitem> |
| 3123 |
|
| 3124 |
<listitem> |
| 3125 |
<para> |
| 3126 |
To organize database objects into logical groups to make them |
| 3127 |
more manageable. |
| 3128 |
</para> |
| 3129 |
</listitem> |
| 3130 |
|
| 3131 |
<listitem> |
| 3132 |
<para> |
| 3133 |
Third-party applications can be put into separate schemas so |
| 3134 |
they do not collide with the names of other objects. |
| 3135 |
</para> |
| 3136 |
</listitem> |
| 3137 |
</itemizedlist> |
| 3138 |
|
| 3139 |
Schemas are analogous to directories at the operating system level, |
| 3140 |
except that schemas cannot be nested. |
| 3141 |
</para> |
| 3142 |
|
| 3143 |
<sect2 id="ddl-schemas-create"> |
| 3144 |
<title>Creating a Schema</title> |
| 3145 |
|
| 3146 |
<indexterm zone="ddl-schemas-create"> |
| 3147 |
<primary>schema</primary> |
| 3148 |
<secondary>creating</secondary> |
| 3149 |
</indexterm> |
| 3150 |
|
| 3151 |
<para> |
| 3152 |
To create a schema, use the <xref linkend="sql-createschema"/> |
| 3153 |
command. Give the schema a name |
| 3154 |
of your choice. For example: |
| 3155 |
<programlisting> |
| 3156 |
CREATE SCHEMA myschema; |
| 3157 |
</programlisting> |
| 3158 |
</para> |
| 3159 |
|
| 3160 |
<indexterm> |
| 3161 |
<primary>qualified name</primary> |
| 3162 |
</indexterm> |
| 3163 |
|
| 3164 |
<indexterm> |
| 3165 |
<primary>name</primary> |
| 3166 |
<secondary>qualified</secondary> |
| 3167 |
</indexterm> |
| 3168 |
|
| 3169 |
<para> |
| 3170 |
To create or access objects in a schema, write a |
| 3171 |
<firstterm>qualified name</firstterm> consisting of the schema name and |
| 3172 |
table name separated by a dot: |
| 3173 |
<synopsis> |
| 3174 |
<replaceable>schema</replaceable><literal>.</literal><replaceable>table</replaceable> |
| 3175 |
</synopsis> |
| 3176 |
This works anywhere a table name is expected, including the table |
| 3177 |
modification commands and the data access commands discussed in |
| 3178 |
the following chapters. |
| 3179 |
(For brevity we will speak of tables only, but the same ideas apply |
| 3180 |
to other kinds of named objects, such as types and functions.) |
| 3181 |
</para> |
| 3182 |
|
| 3183 |
<para> |
| 3184 |
Actually, the even more general syntax |
| 3185 |
<synopsis> |
| 3186 |
<replaceable>database</replaceable><literal>.</literal><replaceable>schema</replaceable><literal>.</literal><replaceable>table</replaceable> |
| 3187 |
</synopsis> |
| 3188 |
can be used too, but at present this is just for pro forma |
| 3189 |
compliance with the SQL standard. If you write a database name, |
| 3190 |
it must be the same as the database you are connected to. |
| 3191 |
</para> |
| 3192 |
|
| 3193 |
<para> |
| 3194 |
So to create a table in the new schema, use: |
| 3195 |
<programlisting> |
| 3196 |
CREATE TABLE myschema.mytable ( |
| 3197 |
... |
| 3198 |
); |
| 3199 |
</programlisting> |
| 3200 |
</para> |
| 3201 |
|
| 3202 |
<indexterm> |
| 3203 |
<primary>schema</primary> |
| 3204 |
<secondary>removing</secondary> |
| 3205 |
</indexterm> |
| 3206 |
|
| 3207 |
<para> |
| 3208 |
To drop a schema if it's empty (all objects in it have been |
| 3209 |
dropped), use: |
| 3210 |
<programlisting> |
| 3211 |
DROP SCHEMA myschema; |
| 3212 |
</programlisting> |
| 3213 |
To drop a schema including all contained objects, use: |
| 3214 |
<programlisting> |
| 3215 |
DROP SCHEMA myschema CASCADE; |
| 3216 |
</programlisting> |
| 3217 |
See <xref linkend="ddl-depend"/> for a description of the general |
| 3218 |
mechanism behind this. |
| 3219 |
</para> |
| 3220 |
|
| 3221 |
<para> |
| 3222 |
Often you will want to create a schema owned by someone else |
| 3223 |
(since this is one of the ways to restrict the activities of your |
| 3224 |
users to well-defined namespaces). The syntax for that is: |
| 3225 |
<programlisting> |
| 3226 |
CREATE SCHEMA <replaceable>schema_name</replaceable> AUTHORIZATION <replaceable>user_name</replaceable>; |
| 3227 |
</programlisting> |
| 3228 |
You can even omit the schema name, in which case the schema name |
| 3229 |
will be the same as the user name. See <xref |
| 3230 |
linkend="ddl-schemas-patterns"/> for how this can be useful. |
| 3231 |
</para> |
| 3232 |
|
| 3233 |
<para> |
| 3234 |
Schema names beginning with <literal>pg_</literal> are reserved for |
| 3235 |
system purposes and cannot be created by users. |
| 3236 |
</para> |
| 3237 |
</sect2> |
| 3238 |
|
| 3239 |
<sect2 id="ddl-schemas-public"> |
| 3240 |
<title>The Public Schema</title> |
| 3241 |
|
| 3242 |
<indexterm zone="ddl-schemas-public"> |
| 3243 |
<primary>schema</primary> |
| 3244 |
<secondary>public</secondary> |
| 3245 |
</indexterm> |
| 3246 |
|
| 3247 |
<para> |
| 3248 |
In the previous sections we created tables without specifying any |
| 3249 |
schema names. By default such tables (and other objects) are |
| 3250 |
automatically put into a schema named <quote>public</quote>. Every new |
| 3251 |
database contains such a schema. Thus, the following are equivalent: |
| 3252 |
<programlisting> |
| 3253 |
CREATE TABLE products ( ... ); |
| 3254 |
</programlisting> |
| 3255 |
and: |
| 3256 |
<programlisting> |
| 3257 |
CREATE TABLE public.products ( ... ); |
| 3258 |
</programlisting> |
| 3259 |
</para> |
| 3260 |
</sect2> |
| 3261 |
|
| 3262 |
<sect2 id="ddl-schemas-path"> |
| 3263 |
<title>The Schema Search Path</title> |
| 3264 |
|
| 3265 |
<indexterm> |
| 3266 |
<primary>search path</primary> |
| 3267 |
</indexterm> |
| 3268 |
|
| 3269 |
<indexterm> |
| 3270 |
<primary>unqualified name</primary> |
| 3271 |
</indexterm> |
| 3272 |
|
| 3273 |
<indexterm> |
| 3274 |
<primary>name</primary> |
| 3275 |
<secondary>unqualified</secondary> |
| 3276 |
</indexterm> |
| 3277 |
|
| 3278 |
<para> |
| 3279 |
Qualified names are tedious to write, and it's often best not to |
| 3280 |
wire a particular schema name into applications anyway. Therefore |
| 3281 |
tables are often referred to by <firstterm>unqualified names</firstterm>, |
| 3282 |
which consist of just the table name. The system determines which table |
| 3283 |
is meant by following a <firstterm>search path</firstterm>, which is a list |
| 3284 |
of schemas to look in. The first matching table in the search path |
| 3285 |
is taken to be the one wanted. If there is no match in the search |
| 3286 |
path, an error is reported, even if matching table names exist |
| 3287 |
in other schemas in the database. |
| 3288 |
</para> |
| 3289 |
|
| 3290 |
<para> |
| 3291 |
The ability to create like-named objects in different schemas complicates |
| 3292 |
writing a query that references precisely the same objects every time. It |
| 3293 |
also opens up the potential for users to change the behavior of other |
| 3294 |
users' queries, maliciously or accidentally. Due to the prevalence of |
| 3295 |
unqualified names in queries and their use |
| 3296 |
in <productname>PostgreSQL</productname> internals, adding a schema |
| 3297 |
to <varname>search_path</varname> effectively trusts all users having |
| 3298 |
<literal>CREATE</literal> privilege on that schema. When you run an |
| 3299 |
ordinary query, a malicious user able to create objects in a schema of |
| 3300 |
your search path can take control and execute arbitrary SQL functions as |
| 3301 |
though you executed them. |
| 3302 |
</para> |
| 3303 |
|
| 3304 |
<indexterm> |
| 3305 |
<primary>schema</primary> |
| 3306 |
<secondary>current</secondary> |
| 3307 |
</indexterm> |
| 3308 |
|
| 3309 |
<para> |
| 3310 |
The first schema named in the search path is called the current schema. |
| 3311 |
Aside from being the first schema searched, it is also the schema in |
| 3312 |
which new tables will be created if the <command>CREATE TABLE</command> |
| 3313 |
command does not specify a schema name. |
| 3314 |
</para> |
| 3315 |
|
| 3316 |
<indexterm> |
| 3317 |
<primary><varname>search_path</varname> configuration parameter</primary> |
| 3318 |
</indexterm> |
| 3319 |
|
| 3320 |
<para> |
| 3321 |
To show the current search path, use the following command: |
| 3322 |
<programlisting> |
| 3323 |
SHOW search_path; |
| 3324 |
</programlisting> |
| 3325 |
In the default setup this returns: |
| 3326 |
<screen> |
| 3327 |
search_path |
| 3328 |
-------------- |
| 3329 |
"$user", public |
| 3330 |
</screen> |
| 3331 |
The first element specifies that a schema with the same name as |
| 3332 |
the current user is to be searched. If no such schema exists, |
| 3333 |
the entry is ignored. The second element refers to the |
| 3334 |
public schema that we have seen already. |
| 3335 |
</para> |
| 3336 |
|
| 3337 |
<para> |
| 3338 |
The first schema in the search path that exists is the default |
| 3339 |
location for creating new objects. That is the reason that by |
| 3340 |
default objects are created in the public schema. When objects |
| 3341 |
are referenced in any other context without schema qualification |
| 3342 |
(table modification, data modification, or query commands) the |
| 3343 |
search path is traversed until a matching object is found. |
| 3344 |
Therefore, in the default configuration, any unqualified access |
| 3345 |
again can only refer to the public schema. |
| 3346 |
</para> |
| 3347 |
|
| 3348 |
<para> |
| 3349 |
To put our new schema in the path, we use: |
| 3350 |
<programlisting> |
| 3351 |
SET search_path TO myschema,public; |
| 3352 |
</programlisting> |
| 3353 |
(We omit the <literal>$user</literal> here because we have no |
| 3354 |
immediate need for it.) And then we can access the table without |
| 3355 |
schema qualification: |
| 3356 |
<programlisting> |
| 3357 |
DROP TABLE mytable; |
| 3358 |
</programlisting> |
| 3359 |
Also, since <literal>myschema</literal> is the first element in |
| 3360 |
the path, new objects would by default be created in it. |
| 3361 |
</para> |
| 3362 |
|
| 3363 |
<para> |
| 3364 |
We could also have written: |
| 3365 |
<programlisting> |
| 3366 |
SET search_path TO myschema; |
| 3367 |
</programlisting> |
| 3368 |
Then we no longer have access to the public schema without |
| 3369 |
explicit qualification. There is nothing special about the public |
| 3370 |
schema except that it exists by default. It can be dropped, too. |
| 3371 |
</para> |
| 3372 |
|
| 3373 |
<para> |
| 3374 |
See also <xref linkend="functions-info"/> for other ways to manipulate |
| 3375 |
the schema search path. |
| 3376 |
</para> |
| 3377 |
|
| 3378 |
<para> |
| 3379 |
The search path works in the same way for data type names, function names, |
| 3380 |
and operator names as it does for table names. Data type and function |
| 3381 |
names can be qualified in exactly the same way as table names. If you |
| 3382 |
need to write a qualified operator name in an expression, there is a |
| 3383 |
special provision: you must write |
| 3384 |
<synopsis> |
| 3385 |
<literal>OPERATOR(</literal><replaceable>schema</replaceable><literal>.</literal><replaceable>operator</replaceable><literal>)</literal> |
| 3386 |
</synopsis> |
| 3387 |
This is needed to avoid syntactic ambiguity. An example is: |
| 3388 |
<programlisting> |
| 3389 |
SELECT 3 OPERATOR(pg_catalog.+) 4; |
| 3390 |
</programlisting> |
| 3391 |
In practice one usually relies on the search path for operators, |
| 3392 |
so as not to have to write anything so ugly as that. |
| 3393 |
</para> |
| 3394 |
</sect2> |
| 3395 |
|
| 3396 |
<sect2 id="ddl-schemas-priv"> |
| 3397 |
<title>Schemas and Privileges</title> |
| 3398 |
|
| 3399 |
<indexterm zone="ddl-schemas-priv"> |
| 3400 |
<primary>privilege</primary> |
| 3401 |
<secondary sortas="schemas">for schemas</secondary> |
| 3402 |
</indexterm> |
| 3403 |
|
| 3404 |
<para> |
| 3405 |
By default, users cannot access any objects in schemas they do not |
| 3406 |
own. To allow that, the owner of the schema must grant the |
| 3407 |
<literal>USAGE</literal> privilege on the schema. By default, everyone |
| 3408 |
has that privilege on the schema <literal>public</literal>. To allow |
| 3409 |
users to make use of the objects in a schema, additional privileges might |
| 3410 |
need to be granted, as appropriate for the object. |
| 3411 |
</para> |
| 3412 |
|
| 3413 |
<para> |
| 3414 |
A user can also be allowed to create objects in someone else's schema. To |
| 3415 |
allow that, the <literal>CREATE</literal> privilege on the schema needs to |
| 3416 |
be granted. In databases upgraded from |
| 3417 |
<productname>PostgreSQL</productname> 14 or earlier, everyone has that |
| 3418 |
privilege on the schema <literal>public</literal>. |
| 3419 |
Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for |
| 3420 |
revoking that privilege: |
| 3421 |
<programlisting> |
| 3422 |
REVOKE CREATE ON SCHEMA public FROM PUBLIC; |
| 3423 |
</programlisting> |
| 3424 |
(The first <quote>public</quote> is the schema, the second |
| 3425 |
<quote>public</quote> means <quote>every user</quote>. In the |
| 3426 |
first sense it is an identifier, in the second sense it is a |
| 3427 |
key word, hence the different capitalization; recall the |
| 3428 |
guidelines from <xref linkend="sql-syntax-identifiers"/>.) |
| 3429 |
</para> |
| 3430 |
</sect2> |
| 3431 |
|
| 3432 |
<sect2 id="ddl-schemas-catalog"> |
| 3433 |
<title>The System Catalog Schema</title> |
| 3434 |
|
| 3435 |
<indexterm zone="ddl-schemas-catalog"> |
| 3436 |
<primary>system catalog</primary> |
| 3437 |
<secondary>schema</secondary> |
| 3438 |
</indexterm> |
| 3439 |
|
| 3440 |
<para> |
| 3441 |
In addition to <literal>public</literal> and user-created schemas, each |
| 3442 |
database contains a <literal>pg_catalog</literal> schema, which contains |
| 3443 |
the system tables and all the built-in data types, functions, and |
| 3444 |
operators. <literal>pg_catalog</literal> is always effectively part of |
| 3445 |
the search path. If it is not named explicitly in the path then |
| 3446 |
it is implicitly searched <emphasis>before</emphasis> searching the path's |
| 3447 |
schemas. This ensures that built-in names will always be |
| 3448 |
findable. However, you can explicitly place |
| 3449 |
<literal>pg_catalog</literal> at the end of your search path if you |
| 3450 |
prefer to have user-defined names override built-in names. |
| 3451 |
</para> |
| 3452 |
|
| 3453 |
<para> |
| 3454 |
Since system table names begin with <literal>pg_</literal>, it is best to |
| 3455 |
avoid such names to ensure that you won't suffer a conflict if some |
| 3456 |
future version defines a system table named the same as your |
| 3457 |
table. (With the default search path, an unqualified reference to |
| 3458 |
your table name would then be resolved as the system table instead.) |
| 3459 |
System tables will continue to follow the convention of having |
| 3460 |
names beginning with <literal>pg_</literal>, so that they will not |
| 3461 |
conflict with unqualified user-table names so long as users avoid |
| 3462 |
the <literal>pg_</literal> prefix. |
| 3463 |
</para> |
| 3464 |
</sect2> |
| 3465 |
|
| 3466 |
<sect2 id="ddl-schemas-patterns"> |
| 3467 |
<title>Usage Patterns</title> |
| 3468 |
|
| 3469 |
<para> |
| 3470 |
Schemas can be used to organize your data in many ways. |
| 3471 |
A <firstterm>secure schema usage pattern</firstterm> prevents untrusted |
| 3472 |
users from changing the behavior of other users' queries. When a database |
| 3473 |
does not use a secure schema usage pattern, users wishing to securely |
| 3474 |
query that database would take protective action at the beginning of each |
| 3475 |
session. Specifically, they would begin each session by |
| 3476 |
setting <varname>search_path</varname> to the empty string or otherwise |
| 3477 |
removing schemas that are writable by non-superusers |
| 3478 |
from <varname>search_path</varname>. There are a few usage patterns |
| 3479 |
easily supported by the default configuration: |
| 3480 |
<itemizedlist> |
| 3481 |
<listitem> |
| 3482 |
<para> |
| 3483 |
Constrain ordinary users to user-private schemas. |
| 3484 |
To implement this pattern, first ensure that no schemas have |
| 3485 |
public <literal>CREATE</literal> privileges. Then, for every user |
| 3486 |
needing to create non-temporary objects, create a schema with the |
| 3487 |
same name as that user, for example |
| 3488 |
<literal>CREATE SCHEMA alice AUTHORIZATION alice</literal>. |
| 3489 |
(Recall that the default search path starts |
| 3490 |
with <literal>$user</literal>, which resolves to the user |
| 3491 |
name. Therefore, if each user has a separate schema, they access |
| 3492 |
their own schemas by default.) This pattern is a secure schema |
| 3493 |
usage pattern unless an untrusted user is the database owner or |
| 3494 |
has been granted <literal>ADMIN OPTION</literal> on a relevant role, |
| 3495 |
in which case no secure schema usage pattern exists. |
| 3496 |
</para> |
| 3497 |
<!-- A database owner can attack the database's users via "CREATE SCHEMA |
| 3498 |
trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". --> |
| 3499 |
|
| 3500 |
<para> |
| 3501 |
In <productname>PostgreSQL</productname> 15 and later, the default |
| 3502 |
configuration supports this usage pattern. In prior versions, or |
| 3503 |
when using a database that has been upgraded from a prior version, |
| 3504 |
you will need to remove the public <literal>CREATE</literal> |
| 3505 |
privilege from the <literal>public</literal> schema (issue |
| 3506 |
<literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>). |
| 3507 |
Then consider auditing the <literal>public</literal> schema for |
| 3508 |
objects named like objects in schema <literal>pg_catalog</literal>. |
| 3509 |
</para> |
| 3510 |
<!-- "DROP SCHEMA public" is inferior to this REVOKE, because pg_dump |
| 3511 |
doesn't preserve that DROP. --> |
| 3512 |
</listitem> |
| 3513 |
|
| 3514 |
<listitem> |
| 3515 |
<para> |
| 3516 |
Remove the public schema from the default search path, by modifying |
| 3517 |
<link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link> |
| 3518 |
or by issuing <literal>ALTER ROLE ALL SET search_path = |
| 3519 |
"$user"</literal>. Then, grant privileges to create in the public |
| 3520 |
schema. Only qualified names will choose public schema objects. While |
| 3521 |
qualified table references are fine, calls to functions in the public |
| 3522 |
schema <link linkend="typeconv-func">will be unsafe or |
| 3523 |
unreliable</link>. If you create functions or extensions in the public |
| 3524 |
schema, use the first pattern instead. Otherwise, like the first |
| 3525 |
pattern, this is secure unless an untrusted user is the database owner |
| 3526 |
or has been granted <literal>ADMIN OPTION</literal> on a relevant role. |
| 3527 |
</para> |
| 3528 |
</listitem> |
| 3529 |
|
| 3530 |
<listitem> |
| 3531 |
<para> |
| 3532 |
Keep the default search path, and grant privileges to create in the |
| 3533 |
public schema. All users access the public schema implicitly. This |
| 3534 |
simulates the situation where schemas are not available at all, giving |
| 3535 |
a smooth transition from the non-schema-aware world. However, this is |
| 3536 |
never a secure pattern. It is acceptable only when the database has a |
| 3537 |
single user or a few mutually-trusting users. In databases upgraded |
| 3538 |
from <productname>PostgreSQL</productname> 14 or earlier, this is the |
| 3539 |
default. |
| 3540 |
</para> |
| 3541 |
</listitem> |
| 3542 |
</itemizedlist> |
| 3543 |
</para> |
| 3544 |
|
| 3545 |
<para> |
| 3546 |
For any pattern, to install shared applications (tables to be used by |
| 3547 |
everyone, additional functions provided by third parties, etc.), put them |
| 3548 |
into separate schemas. Remember to grant appropriate privileges to allow |
| 3549 |
the other users to access them. Users can then refer to these additional |
| 3550 |
objects by qualifying the names with a schema name, or they can put the |
| 3551 |
additional schemas into their search path, as they choose. |
| 3552 |
</para> |
| 3553 |
</sect2> |
| 3554 |
|
| 3555 |
<sect2 id="ddl-schemas-portability"> |
| 3556 |
<title>Portability</title> |
| 3557 |
|
| 3558 |
<para> |
| 3559 |
In the SQL standard, the notion of objects in the same schema |
| 3560 |
being owned by different users does not exist. Moreover, some |
| 3561 |
implementations do not allow you to create schemas that have a |
| 3562 |
different name than their owner. In fact, the concepts of schema |
| 3563 |
and user are nearly equivalent in a database system that |
| 3564 |
implements only the basic schema support specified in the |
| 3565 |
standard. Therefore, many users consider qualified names to |
| 3566 |
really consist of |
| 3567 |
<literal><replaceable>user_name</replaceable>.<replaceable>table_name</replaceable></literal>. |
| 3568 |
This is how <productname>PostgreSQL</productname> will effectively |
| 3569 |
behave if you create a per-user schema for every user. |
| 3570 |
</para> |
| 3571 |
|
| 3572 |
<para> |
| 3573 |
Also, there is no concept of a <literal>public</literal> schema in the |
| 3574 |
SQL standard. For maximum conformance to the standard, you should |
| 3575 |
not use the <literal>public</literal> schema. |
| 3576 |
</para> |
| 3577 |
|
| 3578 |
<para> |
| 3579 |
Of course, some SQL database systems might not implement schemas |
| 3580 |
at all, or provide namespace support by allowing (possibly |
| 3581 |
limited) cross-database access. If you need to work with those |
| 3582 |
systems, then maximum portability would be achieved by not using |
| 3583 |
schemas at all. |
| 3584 |
</para> |
| 3585 |
</sect2> |
| 3586 |
</sect1> |
| 3587 |
|
| 3588 |
<sect1 id="ddl-inherit"> |
| 3589 |
<title>Inheritance</title> |
| 3590 |
|
| 3591 |
<indexterm> |
| 3592 |
<primary>inheritance</primary> |
| 3593 |
</indexterm> |
| 3594 |
|
| 3595 |
<indexterm> |
| 3596 |
<primary>table</primary> |
| 3597 |
<secondary>inheritance</secondary> |
| 3598 |
</indexterm> |
| 3599 |
|
| 3600 |
<para> |
| 3601 |
<productname>PostgreSQL</productname> implements table inheritance, |
| 3602 |
which can be a useful tool for database designers. (SQL:1999 and |
| 3603 |
later define a type inheritance feature, which differs in many |
| 3604 |
respects from the features described here.) |
| 3605 |
</para> |
| 3606 |
|
| 3607 |
<para> |
| 3608 |
Let's start with an example: suppose we are trying to build a data |
| 3609 |
model for cities. Each state has many cities, but only one |
| 3610 |
capital. We want to be able to quickly retrieve the capital city |
| 3611 |
for any particular state. This can be done by creating two tables, |
| 3612 |
one for state capitals and one for cities that are not |
| 3613 |
capitals. However, what happens when we want to ask for data about |
| 3614 |
a city, regardless of whether it is a capital or not? The |
| 3615 |
inheritance feature can help to resolve this problem. We define the |
| 3616 |
<structname>capitals</structname> table so that it inherits from |
| 3617 |
<structname>cities</structname>: |
| 3618 |
|
| 3619 |
<programlisting> |
| 3620 |
CREATE TABLE cities ( |
| 3621 |
name text, |
| 3622 |
population float, |
| 3623 |
elevation int -- in feet |
| 3624 |
); |
| 3625 |
|
| 3626 |
CREATE TABLE capitals ( |
| 3627 |
state char(2) |
| 3628 |
) INHERITS (cities); |
| 3629 |
</programlisting> |
| 3630 |
|
| 3631 |
In this case, the <structname>capitals</structname> table <firstterm>inherits</firstterm> |
| 3632 |
all the columns of its parent table, <structname>cities</structname>. State |
| 3633 |
capitals also have an extra column, <structfield>state</structfield>, that shows |
| 3634 |
their state. |
| 3635 |
</para> |
| 3636 |
|
| 3637 |
<para> |
| 3638 |
In <productname>PostgreSQL</productname>, a table can inherit from |
| 3639 |
zero or more other tables, and a query can reference either all |
| 3640 |
rows of a table or all rows of a table plus all of its descendant tables. |
| 3641 |
The latter behavior is the default. |
| 3642 |
For example, the following query finds the names of all cities, |
| 3643 |
including state capitals, that are located at an elevation over |
| 3644 |
500 feet: |
| 3645 |
|
| 3646 |
<programlisting> |
| 3647 |
SELECT name, elevation |
| 3648 |
FROM cities |
| 3649 |
WHERE elevation > 500; |
| 3650 |
</programlisting> |
| 3651 |
|
| 3652 |
Given the sample data from the <productname>PostgreSQL</productname> |
| 3653 |
tutorial (see <xref linkend="tutorial-sql-intro"/>), this returns: |
| 3654 |
|
| 3655 |
<programlisting> |
| 3656 |
name | elevation |
| 3657 |
-----------+----------- |
| 3658 |
Las Vegas | 2174 |
| 3659 |
Mariposa | 1953 |
| 3660 |
Madison | 845 |
| 3661 |
</programlisting> |
| 3662 |
</para> |
| 3663 |
|
| 3664 |
<para> |
| 3665 |
On the other hand, the following query finds all the cities that |
| 3666 |
are not state capitals and are situated at an elevation over 500 feet: |
| 3667 |
|
| 3668 |
<programlisting> |
| 3669 |
SELECT name, elevation |
| 3670 |
FROM ONLY cities |
| 3671 |
WHERE elevation > 500; |
| 3672 |
|
| 3673 |
name | elevation |
| 3674 |
-----------+----------- |
| 3675 |
Las Vegas | 2174 |
| 3676 |
Mariposa | 1953 |
| 3677 |
</programlisting> |
| 3678 |
</para> |
| 3679 |
|
| 3680 |
<para> |
| 3681 |
Here the <literal>ONLY</literal> keyword indicates that the query |
| 3682 |
should apply only to <structname>cities</structname>, and not any tables |
| 3683 |
below <structname>cities</structname> in the inheritance hierarchy. Many |
| 3684 |
of the commands that we have already discussed — |
| 3685 |
<command>SELECT</command>, <command>UPDATE</command> and |
| 3686 |
<command>DELETE</command> — support the |
| 3687 |
<literal>ONLY</literal> keyword. |
| 3688 |
</para> |
| 3689 |
|
| 3690 |
<para> |
| 3691 |
You can also write the table name with a trailing <literal>*</literal> |
| 3692 |
to explicitly specify that descendant tables are included: |
| 3693 |
|
| 3694 |
<programlisting> |
| 3695 |
SELECT name, elevation |
| 3696 |
FROM cities* |
| 3697 |
WHERE elevation > 500; |
| 3698 |
</programlisting> |
| 3699 |
|
| 3700 |
Writing <literal>*</literal> is not necessary, since this behavior is always |
| 3701 |
the default. However, this syntax is still supported for |
| 3702 |
compatibility with older releases where the default could be changed. |
| 3703 |
</para> |
| 3704 |
|
| 3705 |
<para> |
| 3706 |
In some cases you might wish to know which table a particular row |
| 3707 |
originated from. There is a system column called |
| 3708 |
<structfield>tableoid</structfield> in each table which can tell you the |
| 3709 |
originating table: |
| 3710 |
|
| 3711 |
<programlisting> |
| 3712 |
SELECT c.tableoid, c.name, c.elevation |
| 3713 |
FROM cities c |
| 3714 |
WHERE c.elevation > 500; |
| 3715 |
</programlisting> |
| 3716 |
|
| 3717 |
which returns: |
| 3718 |
|
| 3719 |
<programlisting> |
| 3720 |
tableoid | name | elevation |
| 3721 |
----------+-----------+----------- |
| 3722 |
139793 | Las Vegas | 2174 |
| 3723 |
139793 | Mariposa | 1953 |
| 3724 |
139798 | Madison | 845 |
| 3725 |
</programlisting> |
| 3726 |
|
| 3727 |
(If you try to reproduce this example, you will probably get |
| 3728 |
different numeric OIDs.) By doing a join with |
| 3729 |
<structname>pg_class</structname> you can see the actual table names: |
| 3730 |
|
| 3731 |
<programlisting> |
| 3732 |
SELECT p.relname, c.name, c.elevation |
| 3733 |
FROM cities c, pg_class p |
| 3734 |
WHERE c.elevation > 500 AND c.tableoid = p.oid; |
| 3735 |
</programlisting> |
| 3736 |
|
| 3737 |
which returns: |
| 3738 |
|
| 3739 |
<programlisting> |
| 3740 |
relname | name | elevation |
| 3741 |
----------+-----------+----------- |
| 3742 |
cities | Las Vegas | 2174 |
| 3743 |
cities | Mariposa | 1953 |
| 3744 |
capitals | Madison | 845 |
| 3745 |
</programlisting> |
| 3746 |
</para> |
| 3747 |
|
| 3748 |
<para> |
| 3749 |
Another way to get the same effect is to use the <type>regclass</type> |
| 3750 |
alias type, which will print the table OID symbolically: |
| 3751 |
|
| 3752 |
<programlisting> |
| 3753 |
SELECT c.tableoid::regclass, c.name, c.elevation |
| 3754 |
FROM cities c |
| 3755 |
WHERE c.elevation > 500; |
| 3756 |
</programlisting> |
| 3757 |
</para> |
| 3758 |
|
| 3759 |
<para> |
| 3760 |
Inheritance does not automatically propagate data from |
| 3761 |
<command>INSERT</command> or <command>COPY</command> commands to |
| 3762 |
other tables in the inheritance hierarchy. In our example, the |
| 3763 |
following <command>INSERT</command> statement will fail: |
| 3764 |
<programlisting> |
| 3765 |
INSERT INTO cities (name, population, elevation, state) |
| 3766 |
VALUES ('Albany', NULL, NULL, 'NY'); |
| 3767 |
</programlisting> |
| 3768 |
We might hope that the data would somehow be routed to the |
| 3769 |
<structname>capitals</structname> table, but this does not happen: |
| 3770 |
<command>INSERT</command> always inserts into exactly the table |
| 3771 |
specified. In some cases it is possible to redirect the insertion |
| 3772 |
using a rule (see <xref linkend="rules"/>). However that does not |
| 3773 |
help for the above case because the <structname>cities</structname> table |
| 3774 |
does not contain the column <structfield>state</structfield>, and so the |
| 3775 |
command will be rejected before the rule can be applied. |
| 3776 |
</para> |
| 3777 |
|
| 3778 |
<para> |
| 3779 |
All check constraints and not-null constraints on a parent table are |
| 3780 |
automatically inherited by its children, unless explicitly specified |
| 3781 |
otherwise with <literal>NO INHERIT</literal> clauses. Other types of constraints |
| 3782 |
(unique, primary key, and foreign key constraints) are not inherited. |
| 3783 |
</para> |
| 3784 |
|
| 3785 |
<para> |
| 3786 |
A table can inherit from more than one parent table, in which case it has |
| 3787 |
the union of the columns defined by the parent tables. Any columns |
| 3788 |
declared in the child table's definition are added to these. If the |
| 3789 |
same column name appears in multiple parent tables, or in both a parent |
| 3790 |
table and the child's definition, then these columns are <quote>merged</quote> |
| 3791 |
so that there is only one such column in the child table. To be merged, |
| 3792 |
columns must have the same data types, else an error is raised. |
| 3793 |
Inheritable check constraints and not-null constraints are merged in a |
| 3794 |
similar fashion. Thus, for example, a merged column will be marked |
| 3795 |
not-null if any one of the column definitions it came from is marked |
| 3796 |
not-null. Check constraints are merged if they have the same name, |
| 3797 |
and the merge will fail if their conditions are different. |
| 3798 |
</para> |
| 3799 |
|
| 3800 |
<para> |
| 3801 |
Table inheritance is typically established when the child table is |
| 3802 |
created, using the <literal>INHERITS</literal> clause of the |
| 3803 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link> |
| 3804 |
statement. |
| 3805 |
Alternatively, a table which is already defined in a compatible way can |
| 3806 |
have a new parent relationship added, using the <literal>INHERIT</literal> |
| 3807 |
variant of <link linkend="sql-altertable"><command>ALTER TABLE</command></link>. |
| 3808 |
To do this the new child table must already include columns with |
| 3809 |
the same names and types as the columns of the parent. It must also include |
| 3810 |
check constraints with the same names and check expressions as those of the |
| 3811 |
parent. Similarly an inheritance link can be removed from a child using the |
| 3812 |
<literal>NO INHERIT</literal> variant of <command>ALTER TABLE</command>. |
| 3813 |
Dynamically adding and removing inheritance links like this can be useful |
| 3814 |
when the inheritance relationship is being used for table |
| 3815 |
partitioning (see <xref linkend="ddl-partitioning"/>). |
| 3816 |
</para> |
| 3817 |
|
| 3818 |
<para> |
| 3819 |
One convenient way to create a compatible table that will later be made |
| 3820 |
a new child is to use the <literal>LIKE</literal> clause in <command>CREATE |
| 3821 |
TABLE</command>. This creates a new table with the same columns as |
| 3822 |
the source table. If there are any <literal>CHECK</literal> |
| 3823 |
constraints defined on the source table, the <literal>INCLUDING |
| 3824 |
CONSTRAINTS</literal> option to <literal>LIKE</literal> should be |
| 3825 |
specified, as the new child must have constraints matching the parent |
| 3826 |
to be considered compatible. |
| 3827 |
</para> |
| 3828 |
|
| 3829 |
<para> |
| 3830 |
A parent table cannot be dropped while any of its children remain. Neither |
| 3831 |
can columns or check constraints of child tables be dropped or altered |
| 3832 |
if they are inherited |
| 3833 |
from any parent tables. If you wish to remove a table and all of its |
| 3834 |
descendants, one easy way is to drop the parent table with the |
| 3835 |
<literal>CASCADE</literal> option (see <xref linkend="ddl-depend"/>). |
| 3836 |
</para> |
| 3837 |
|
| 3838 |
<para> |
| 3839 |
<command>ALTER TABLE</command> will |
| 3840 |
propagate any changes in column data definitions and check |
| 3841 |
constraints down the inheritance hierarchy. Again, dropping |
| 3842 |
columns that are depended on by other tables is only possible when using |
| 3843 |
the <literal>CASCADE</literal> option. <command>ALTER |
| 3844 |
TABLE</command> follows the same rules for duplicate column merging |
| 3845 |
and rejection that apply during <command>CREATE TABLE</command>. |
| 3846 |
</para> |
| 3847 |
|
| 3848 |
<para> |
| 3849 |
Inherited queries perform access permission checks on the parent table |
| 3850 |
only. Thus, for example, granting <literal>UPDATE</literal> permission on |
| 3851 |
the <structname>cities</structname> table implies permission to update rows in |
| 3852 |
the <structname>capitals</structname> table as well, when they are |
| 3853 |
accessed through <structname>cities</structname>. This preserves the appearance |
| 3854 |
that the data is (also) in the parent table. But |
| 3855 |
the <structname>capitals</structname> table could not be updated directly |
| 3856 |
without an additional grant. In a similar way, the parent table's row |
| 3857 |
security policies (see <xref linkend="ddl-rowsecurity"/>) are applied to |
| 3858 |
rows coming from child tables during an inherited query. A child table's |
| 3859 |
policies, if any, are applied only when it is the table explicitly named |
| 3860 |
in the query; and in that case, any policies attached to its parent(s) are |
| 3861 |
ignored. |
| 3862 |
</para> |
| 3863 |
|
| 3864 |
<para> |
| 3865 |
Foreign tables (see <xref linkend="ddl-foreign-data"/>) can also |
| 3866 |
be part of inheritance hierarchies, either as parent or child |
| 3867 |
tables, just as regular tables can be. If a foreign table is part |
| 3868 |
of an inheritance hierarchy then any operations not supported by |
| 3869 |
the foreign table are not supported on the whole hierarchy either. |
| 3870 |
</para> |
| 3871 |
|
| 3872 |
<sect2 id="ddl-inherit-caveats"> |
| 3873 |
<title>Caveats</title> |
| 3874 |
|
| 3875 |
<para> |
| 3876 |
Note that not all SQL commands are able to work on |
| 3877 |
inheritance hierarchies. Commands that are used for data querying, |
| 3878 |
data modification, or schema modification |
| 3879 |
(e.g., <literal>SELECT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, |
| 3880 |
most variants of <literal>ALTER TABLE</literal>, but |
| 3881 |
not <literal>INSERT</literal> or <literal>ALTER TABLE ... |
| 3882 |
RENAME</literal>) typically default to including child tables and |
| 3883 |
support the <literal>ONLY</literal> notation to exclude them. |
| 3884 |
The majority of commands that do database maintenance and tuning |
| 3885 |
(e.g., <literal>REINDEX</literal>) only work on individual, physical |
| 3886 |
tables and do not support recursing over inheritance hierarchies. |
| 3887 |
However, both <literal>VACUUM</literal> and <literal>ANALYZE</literal> |
| 3888 |
commands default to including child tables and the <literal>ONLY</literal> |
| 3889 |
notation is supported to allow them to be excluded. The respective |
| 3890 |
behavior of each individual command is documented in its reference page |
| 3891 |
(<xref linkend="sql-commands"/>). |
| 3892 |
</para> |
| 3893 |
|
| 3894 |
<para> |
| 3895 |
A serious limitation of the inheritance feature is that indexes (including |
| 3896 |
unique constraints) and foreign key constraints only apply to single |
| 3897 |
tables, not to their inheritance children. This is true on both the |
| 3898 |
referencing and referenced sides of a foreign key constraint. Thus, |
| 3899 |
in the terms of the above example: |
| 3900 |
|
| 3901 |
<itemizedlist> |
| 3902 |
<listitem> |
| 3903 |
<para> |
| 3904 |
If we declared <structname>cities</structname>.<structfield>name</structfield> to be |
| 3905 |
<literal>UNIQUE</literal> or a <literal>PRIMARY KEY</literal>, this would not stop the |
| 3906 |
<structname>capitals</structname> table from having rows with names duplicating |
| 3907 |
rows in <structname>cities</structname>. And those duplicate rows would by |
| 3908 |
default show up in queries from <structname>cities</structname>. In fact, by |
| 3909 |
default <structname>capitals</structname> would have no unique constraint at all, |
| 3910 |
and so could contain multiple rows with the same name. |
| 3911 |
You could add a unique constraint to <structname>capitals</structname>, but this |
| 3912 |
would not prevent duplication compared to <structname>cities</structname>. |
| 3913 |
</para> |
| 3914 |
</listitem> |
| 3915 |
|
| 3916 |
<listitem> |
| 3917 |
<para> |
| 3918 |
Similarly, if we were to specify that |
| 3919 |
<structname>cities</structname>.<structfield>name</structfield> <literal>REFERENCES</literal> some |
| 3920 |
other table, this constraint would not automatically propagate to |
| 3921 |
<structname>capitals</structname>. In this case you could work around it by |
| 3922 |
manually adding the same <literal>REFERENCES</literal> constraint to |
| 3923 |
<structname>capitals</structname>. |
| 3924 |
</para> |
| 3925 |
</listitem> |
| 3926 |
|
| 3927 |
<listitem> |
| 3928 |
<para> |
| 3929 |
Specifying that another table's column <literal>REFERENCES |
| 3930 |
cities(name)</literal> would allow the other table to contain city names, but |
| 3931 |
not capital names. There is no good workaround for this case. |
| 3932 |
</para> |
| 3933 |
</listitem> |
| 3934 |
</itemizedlist> |
| 3935 |
|
| 3936 |
Some functionality not implemented for inheritance hierarchies is |
| 3937 |
implemented for declarative partitioning. |
| 3938 |
Considerable care is needed in deciding whether partitioning with legacy |
| 3939 |
inheritance is useful for your application. |
| 3940 |
</para> |
| 3941 |
|
| 3942 |
</sect2> |
| 3943 |
</sect1> |
| 3944 |
|
| 3945 |
<sect1 id="ddl-partitioning"> |
| 3946 |
<title>Table Partitioning</title> |
| 3947 |
|
| 3948 |
<indexterm> |
| 3949 |
<primary>partitioning</primary> |
| 3950 |
</indexterm> |
| 3951 |
|
| 3952 |
<indexterm> |
| 3953 |
<primary>table</primary> |
| 3954 |
<secondary>partitioning</secondary> |
| 3955 |
</indexterm> |
| 3956 |
|
| 3957 |
<indexterm> |
| 3958 |
<primary>partitioned table</primary> |
| 3959 |
</indexterm> |
| 3960 |
|
| 3961 |
<para> |
| 3962 |
<productname>PostgreSQL</productname> supports basic table |
| 3963 |
partitioning. This section describes why and how to implement |
| 3964 |
partitioning as part of your database design. |
| 3965 |
</para> |
| 3966 |
|
| 3967 |
<sect2 id="ddl-partitioning-overview"> |
| 3968 |
<title>Overview</title> |
| 3969 |
|
| 3970 |
<para> |
| 3971 |
Partitioning refers to splitting what is logically one large table into |
| 3972 |
smaller physical pieces. Partitioning can provide several benefits: |
| 3973 |
<itemizedlist> |
| 3974 |
<listitem> |
| 3975 |
<para> |
| 3976 |
Query performance can be improved dramatically in certain situations, |
| 3977 |
particularly when most of the heavily accessed rows of the table are in a |
| 3978 |
single partition or a small number of partitions. Partitioning |
| 3979 |
effectively substitutes for the upper tree levels of indexes, |
| 3980 |
making it more likely that the heavily-used parts of the indexes |
| 3981 |
fit in memory. |
| 3982 |
</para> |
| 3983 |
</listitem> |
| 3984 |
|
| 3985 |
<listitem> |
| 3986 |
<para> |
| 3987 |
When queries or updates access a large percentage of a single |
| 3988 |
partition, performance can be improved by using a |
| 3989 |
sequential scan of that partition instead of using an |
| 3990 |
index, which would require random-access reads scattered across the |
| 3991 |
whole table. |
| 3992 |
</para> |
| 3993 |
</listitem> |
| 3994 |
|
| 3995 |
<listitem> |
| 3996 |
<para> |
| 3997 |
Bulk loads and deletes can be accomplished by adding or removing |
| 3998 |
partitions, if the usage pattern is accounted for in the |
| 3999 |
partitioning design. Dropping an individual partition |
| 4000 |
using <command>DROP TABLE</command>, or doing <command>ALTER TABLE |
| 4001 |
DETACH PARTITION</command>, is far faster than a bulk |
| 4002 |
operation. These commands also entirely avoid the |
| 4003 |
<command>VACUUM</command> overhead caused by a bulk <command>DELETE</command>. |
| 4004 |
</para> |
| 4005 |
</listitem> |
| 4006 |
|
| 4007 |
<listitem> |
| 4008 |
<para> |
| 4009 |
Seldom-used data can be migrated to cheaper and slower storage media. |
| 4010 |
</para> |
| 4011 |
</listitem> |
| 4012 |
</itemizedlist> |
| 4013 |
|
| 4014 |
These benefits will normally be worthwhile only when a table would |
| 4015 |
otherwise be very large. The exact point at which a table will |
| 4016 |
benefit from partitioning depends on the application, although a |
| 4017 |
rule of thumb is that the size of the table should exceed the physical |
| 4018 |
memory of the database server. |
| 4019 |
</para> |
| 4020 |
|
| 4021 |
<para> |
| 4022 |
<productname>PostgreSQL</productname> offers built-in support for the |
| 4023 |
following forms of partitioning: |
| 4024 |
|
| 4025 |
<variablelist> |
| 4026 |
<varlistentry id="ddl-partitioning-overview-range"> |
| 4027 |
<term>Range Partitioning</term> |
| 4028 |
|
| 4029 |
<listitem> |
| 4030 |
<para> |
| 4031 |
The table is partitioned into <quote>ranges</quote> defined |
| 4032 |
by a key column or set of columns, with no overlap between |
| 4033 |
the ranges of values assigned to different partitions. For |
| 4034 |
example, one might partition by date ranges, or by ranges of |
| 4035 |
identifiers for particular business objects. |
| 4036 |
Each range's bounds are understood as being inclusive at the |
| 4037 |
lower end and exclusive at the upper end. For example, if one |
| 4038 |
partition's range is from <literal>1</literal> |
| 4039 |
to <literal>10</literal>, and the next one's range is |
| 4040 |
from <literal>10</literal> to <literal>20</literal>, then |
| 4041 |
value <literal>10</literal> belongs to the second partition not |
| 4042 |
the first. |
| 4043 |
</para> |
| 4044 |
</listitem> |
| 4045 |
</varlistentry> |
| 4046 |
|
| 4047 |
<varlistentry id="ddl-partitioning-overview-list"> |
| 4048 |
<term>List Partitioning</term> |
| 4049 |
|
| 4050 |
<listitem> |
| 4051 |
<para> |
| 4052 |
The table is partitioned by explicitly listing which key value(s) |
| 4053 |
appear in each partition. |
| 4054 |
</para> |
| 4055 |
</listitem> |
| 4056 |
</varlistentry> |
| 4057 |
|
| 4058 |
<varlistentry id="ddl-partitioning-overview-hash"> |
| 4059 |
<term>Hash Partitioning</term> |
| 4060 |
|
| 4061 |
<listitem> |
| 4062 |
<para> |
| 4063 |
The table is partitioned by specifying a modulus and a remainder for |
| 4064 |
each partition. Each partition will hold the rows for which the hash |
| 4065 |
value of the partition key divided by the specified modulus will |
| 4066 |
produce the specified remainder. |
| 4067 |
</para> |
| 4068 |
</listitem> |
| 4069 |
</varlistentry> |
| 4070 |
</variablelist> |
| 4071 |
|
| 4072 |
If your application needs to use other forms of partitioning not listed |
| 4073 |
above, alternative methods such as inheritance and |
| 4074 |
<literal>UNION ALL</literal> views can be used instead. Such methods |
| 4075 |
offer flexibility but do not have some of the performance benefits |
| 4076 |
of built-in declarative partitioning. |
| 4077 |
</para> |
| 4078 |
</sect2> |
| 4079 |
|
| 4080 |
<sect2 id="ddl-partitioning-declarative"> |
| 4081 |
<title>Declarative Partitioning</title> |
| 4082 |
|
| 4083 |
<para> |
| 4084 |
<productname>PostgreSQL</productname> allows you to declare |
| 4085 |
that a table is divided into partitions. The table that is divided |
| 4086 |
is referred to as a <firstterm>partitioned table</firstterm>. The |
| 4087 |
declaration includes the <firstterm>partitioning method</firstterm> |
| 4088 |
as described above, plus a list of columns or expressions to be used |
| 4089 |
as the <firstterm>partition key</firstterm>. |
| 4090 |
</para> |
| 4091 |
|
| 4092 |
<para> |
| 4093 |
The partitioned table itself is a <quote>virtual</quote> table having |
| 4094 |
no storage of its own. Instead, the storage belongs |
| 4095 |
to <firstterm>partitions</firstterm>, which are otherwise-ordinary |
| 4096 |
tables associated with the partitioned table. |
| 4097 |
Each partition stores a subset of the data as defined by its |
| 4098 |
<firstterm>partition bounds</firstterm>. |
| 4099 |
All rows inserted into a partitioned table will be routed to the |
| 4100 |
appropriate one of the partitions based on the values of the partition |
| 4101 |
key column(s). |
| 4102 |
Updating the partition key of a row will cause it to be moved into a |
| 4103 |
different partition if it no longer satisfies the partition bounds |
| 4104 |
of its original partition. |
| 4105 |
</para> |
| 4106 |
|
| 4107 |
<para> |
| 4108 |
Partitions may themselves be defined as partitioned tables, resulting |
| 4109 |
in <firstterm>sub-partitioning</firstterm>. Although all partitions |
| 4110 |
must have the same columns as their partitioned parent, partitions may |
| 4111 |
have their |
| 4112 |
own indexes, constraints and default values, distinct from those of other |
| 4113 |
partitions. See <xref linkend="sql-createtable"/> for more details on |
| 4114 |
creating partitioned tables and partitions. |
| 4115 |
</para> |
| 4116 |
|
| 4117 |
<para> |
| 4118 |
It is not possible to turn a regular table into a partitioned table or |
| 4119 |
vice versa. However, it is possible to add an existing regular or |
| 4120 |
partitioned table as a partition of a partitioned table, or remove a |
| 4121 |
partition from a partitioned table turning it into a standalone table; |
| 4122 |
this can simplify and speed up many maintenance processes. |
| 4123 |
See <xref linkend="sql-altertable"/> to learn more about the |
| 4124 |
<command>ATTACH PARTITION</command> and <command>DETACH PARTITION</command> |
| 4125 |
sub-commands. |
| 4126 |
</para> |
| 4127 |
|
| 4128 |
<para> |
| 4129 |
Partitions can also be <link linkend="ddl-foreign-data">foreign |
| 4130 |
tables</link>, although considerable care is needed because it is then |
| 4131 |
the user's responsibility that the contents of the foreign table |
| 4132 |
satisfy the partitioning rule. There are some other restrictions as |
| 4133 |
well. See <xref linkend="sql-createforeigntable"/> for more |
| 4134 |
information. |
| 4135 |
</para> |
| 4136 |
|
| 4137 |
<sect3 id="ddl-partitioning-declarative-example"> |
| 4138 |
<title>Example</title> |
| 4139 |
|
| 4140 |
<para> |
| 4141 |
Suppose we are constructing a database for a large ice cream company. |
| 4142 |
The company measures peak temperatures every day as well as ice cream |
| 4143 |
sales in each region. Conceptually, we want a table like: |
| 4144 |
|
| 4145 |
<programlisting> |
| 4146 |
CREATE TABLE measurement ( |
| 4147 |
city_id int not null, |
| 4148 |
logdate date not null, |
| 4149 |
peaktemp int, |
| 4150 |
unitsales int |
| 4151 |
); |
| 4152 |
</programlisting> |
| 4153 |
|
| 4154 |
We know that most queries will access just the last week's, month's or |
| 4155 |
quarter's data, since the main use of this table will be to prepare |
| 4156 |
online reports for management. To reduce the amount of old data that |
| 4157 |
needs to be stored, we decide to keep only the most recent 3 years |
| 4158 |
worth of data. At the beginning of each month we will remove the oldest |
| 4159 |
month's data. In this situation we can use partitioning to help us meet |
| 4160 |
all of our different requirements for the measurements table. |
| 4161 |
</para> |
| 4162 |
|
| 4163 |
<para> |
| 4164 |
To use declarative partitioning in this case, use the following steps: |
| 4165 |
|
| 4166 |
<orderedlist spacing="compact"> |
| 4167 |
<listitem> |
| 4168 |
<para> |
| 4169 |
Create the <structname>measurement</structname> table as a partitioned |
| 4170 |
table by specifying the <literal>PARTITION BY</literal> clause, which |
| 4171 |
includes the partitioning method (<literal>RANGE</literal> in this |
| 4172 |
case) and the list of column(s) to use as the partition key. |
| 4173 |
|
| 4174 |
<programlisting> |
| 4175 |
CREATE TABLE measurement ( |
| 4176 |
city_id int not null, |
| 4177 |
logdate date not null, |
| 4178 |
peaktemp int, |
| 4179 |
unitsales int |
| 4180 |
) PARTITION BY RANGE (logdate); |
| 4181 |
</programlisting> |
| 4182 |
</para> |
| 4183 |
</listitem> |
| 4184 |
|
| 4185 |
<listitem> |
| 4186 |
<para> |
| 4187 |
Create partitions. Each partition's definition must specify bounds |
| 4188 |
that correspond to the partitioning method and partition key of the |
| 4189 |
parent. Note that specifying bounds such that the new partition's |
| 4190 |
values would overlap with those in one or more existing partitions will |
| 4191 |
cause an error. |
| 4192 |
</para> |
| 4193 |
|
| 4194 |
<para> |
| 4195 |
Partitions thus created are in every way normal |
| 4196 |
<productname>PostgreSQL</productname> |
| 4197 |
tables (or, possibly, foreign tables). It is possible to specify a |
| 4198 |
tablespace and storage parameters for each partition separately. |
| 4199 |
</para> |
| 4200 |
|
| 4201 |
<para> |
| 4202 |
For our example, each partition should hold one month's worth of |
| 4203 |
data, to match the requirement of deleting one month's data at a |
| 4204 |
time. So the commands might look like: |
| 4205 |
|
| 4206 |
<programlisting> |
| 4207 |
CREATE TABLE measurement_y2006m02 PARTITION OF measurement |
| 4208 |
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); |
| 4209 |
|
| 4210 |
CREATE TABLE measurement_y2006m03 PARTITION OF measurement |
| 4211 |
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); |
| 4212 |
|
| 4213 |
... |
| 4214 |
CREATE TABLE measurement_y2007m11 PARTITION OF measurement |
| 4215 |
FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'); |
| 4216 |
|
| 4217 |
CREATE TABLE measurement_y2007m12 PARTITION OF measurement |
| 4218 |
FOR VALUES FROM ('2007-12-01') TO ('2008-01-01') |
| 4219 |
TABLESPACE fasttablespace; |
| 4220 |
|
| 4221 |
CREATE TABLE measurement_y2008m01 PARTITION OF measurement |
| 4222 |
FOR VALUES FROM ('2008-01-01') TO ('2008-02-01') |
| 4223 |
WITH (parallel_workers = 4) |
| 4224 |
TABLESPACE fasttablespace; |
| 4225 |
</programlisting> |
| 4226 |
|
| 4227 |
(Recall that adjacent partitions can share a bound value, since |
| 4228 |
range upper bounds are treated as exclusive bounds.) |
| 4229 |
</para> |
| 4230 |
|
| 4231 |
<para> |
| 4232 |
If you wish to implement sub-partitioning, again specify the |
| 4233 |
<literal>PARTITION BY</literal> clause in the commands used to create |
| 4234 |
individual partitions, for example: |
| 4235 |
|
| 4236 |
<programlisting> |
| 4237 |
CREATE TABLE measurement_y2006m02 PARTITION OF measurement |
| 4238 |
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') |
| 4239 |
PARTITION BY RANGE (peaktemp); |
| 4240 |
</programlisting> |
| 4241 |
|
| 4242 |
After creating partitions of <structname>measurement_y2006m02</structname>, |
| 4243 |
any data inserted into <structname>measurement</structname> that is mapped to |
| 4244 |
<structname>measurement_y2006m02</structname> (or data that is |
| 4245 |
directly inserted into <structname>measurement_y2006m02</structname>, |
| 4246 |
which is allowed provided its partition constraint is satisfied) |
| 4247 |
will be further redirected to one of its |
| 4248 |
partitions based on the <structfield>peaktemp</structfield> column. The partition |
| 4249 |
key specified may overlap with the parent's partition key, although |
| 4250 |
care should be taken when specifying the bounds of a sub-partition |
| 4251 |
such that the set of data it accepts constitutes a subset of what |
| 4252 |
the partition's own bounds allow; the system does not try to check |
| 4253 |
whether that's really the case. |
| 4254 |
</para> |
| 4255 |
|
| 4256 |
<para> |
| 4257 |
Inserting data into the parent table that does not map |
| 4258 |
to one of the existing partitions will cause an error; an appropriate |
| 4259 |
partition must be added manually. |
| 4260 |
</para> |
| 4261 |
|
| 4262 |
<para> |
| 4263 |
It is not necessary to manually create table constraints describing |
| 4264 |
the partition boundary conditions for partitions. Such constraints |
| 4265 |
will be created automatically. |
| 4266 |
</para> |
| 4267 |
</listitem> |
| 4268 |
|
| 4269 |
<listitem> |
| 4270 |
<para> |
| 4271 |
Create an index on the key column(s), as well as any other indexes you |
| 4272 |
might want, on the partitioned table. (The key index is not strictly |
| 4273 |
necessary, but in most scenarios it is helpful.) |
| 4274 |
This automatically creates a matching index on each partition, and |
| 4275 |
any partitions you create or attach later will also have such an |
| 4276 |
index. |
| 4277 |
An index or unique constraint declared on a partitioned table |
| 4278 |
is <quote>virtual</quote> in the same way that the partitioned table |
| 4279 |
is: the actual data is in child indexes on the individual partition |
| 4280 |
tables. |
| 4281 |
|
| 4282 |
<programlisting> |
| 4283 |
CREATE INDEX ON measurement (logdate); |
| 4284 |
</programlisting> |
| 4285 |
</para> |
| 4286 |
</listitem> |
| 4287 |
|
| 4288 |
<listitem> |
| 4289 |
<para> |
| 4290 |
Ensure that the <xref linkend="guc-enable-partition-pruning"/> |
| 4291 |
configuration parameter is not disabled in <filename>postgresql.conf</filename>. |
| 4292 |
If it is, queries will not be optimized as desired. |
| 4293 |
</para> |
| 4294 |
</listitem> |
| 4295 |
</orderedlist> |
| 4296 |
</para> |
| 4297 |
|
| 4298 |
<para> |
| 4299 |
In the above example we would be creating a new partition each month, so |
| 4300 |
it might be wise to write a script that generates the required DDL |
| 4301 |
automatically. |
| 4302 |
</para> |
| 4303 |
</sect3> |
| 4304 |
|
| 4305 |
<sect3 id="ddl-partitioning-declarative-maintenance"> |
| 4306 |
<title>Partition Maintenance</title> |
| 4307 |
|
| 4308 |
<para> |
| 4309 |
Normally the set of partitions established when initially defining the |
| 4310 |
table is not intended to remain static. It is common to want to |
| 4311 |
remove partitions holding old data and periodically add new partitions for |
| 4312 |
new data. One of the most important advantages of partitioning is |
| 4313 |
precisely that it allows this otherwise painful task to be executed |
| 4314 |
nearly instantaneously by manipulating the partition structure, rather |
| 4315 |
than physically moving large amounts of data around. |
| 4316 |
</para> |
| 4317 |
|
| 4318 |
<para> |
| 4319 |
The simplest option for removing old data is to drop the partition that |
| 4320 |
is no longer necessary: |
| 4321 |
<programlisting> |
| 4322 |
DROP TABLE measurement_y2006m02; |
| 4323 |
</programlisting> |
| 4324 |
This can very quickly delete millions of records because it doesn't have |
| 4325 |
to individually delete every record. Note however that the above command |
| 4326 |
requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent |
| 4327 |
table. |
| 4328 |
</para> |
| 4329 |
|
| 4330 |
<para> |
| 4331 |
Another option that is often preferable is to remove the partition from |
| 4332 |
the partitioned table but retain access to it as a table in its own |
| 4333 |
right. This has two forms: |
| 4334 |
|
| 4335 |
<programlisting> |
| 4336 |
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; |
| 4337 |
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY; |
| 4338 |
</programlisting> |
| 4339 |
|
| 4340 |
These allow further operations to be performed on the data before |
| 4341 |
it is dropped. For example, this is often a useful time to back up |
| 4342 |
the data using <command>COPY</command>, <application>pg_dump</application>, or |
| 4343 |
similar tools. It might also be a useful time to aggregate data |
| 4344 |
into smaller formats, perform other data manipulations, or run |
| 4345 |
reports. The first form of the command requires an |
| 4346 |
<literal>ACCESS EXCLUSIVE</literal> lock on the parent table. |
| 4347 |
Adding the <literal>CONCURRENTLY</literal> qualifier as in the second |
| 4348 |
form allows the detach operation to require only |
| 4349 |
<literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table, but see |
| 4350 |
<link linkend="sql-altertable-detach-partition"><literal>ALTER TABLE ... DETACH PARTITION</literal></link> |
| 4351 |
for details on the restrictions. |
| 4352 |
</para> |
| 4353 |
|
| 4354 |
<para> |
| 4355 |
Similarly we can add a new partition to handle new data. We can create an |
| 4356 |
empty partition in the partitioned table just as the original partitions |
| 4357 |
were created above: |
| 4358 |
|
| 4359 |
<programlisting> |
| 4360 |
CREATE TABLE measurement_y2008m02 PARTITION OF measurement |
| 4361 |
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01') |
| 4362 |
TABLESPACE fasttablespace; |
| 4363 |
</programlisting> |
| 4364 |
|
| 4365 |
As an alternative to creating a new partition, it is sometimes more |
| 4366 |
convenient to create a new table separate from the partition structure |
| 4367 |
and attach it as a partition later. This allows new data to be loaded, |
| 4368 |
checked, and transformed prior to it appearing in the partitioned table. |
| 4369 |
Moreover, the <literal>ATTACH PARTITION</literal> operation requires |
| 4370 |
only a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the |
| 4371 |
partitioned table rather than the <literal>ACCESS EXCLUSIVE</literal> |
| 4372 |
lock required by <command>CREATE TABLE ... PARTITION OF</command>, |
| 4373 |
so it is more friendly to concurrent operations on the partitioned table; |
| 4374 |
see <link linkend="sql-altertable-attach-partition"><literal>ALTER TABLE ... ATTACH PARTITION</literal></link> |
| 4375 |
for additional details. The |
| 4376 |
<link linkend="sql-createtable-parms-like"><literal>CREATE TABLE ... LIKE</literal></link> |
| 4377 |
option can be helpful to avoid tediously repeating the parent table's |
| 4378 |
definition; for example: |
| 4379 |
|
| 4380 |
<programlisting> |
| 4381 |
CREATE TABLE measurement_y2008m02 |
| 4382 |
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) |
| 4383 |
TABLESPACE fasttablespace; |
| 4384 |
|
| 4385 |
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 |
| 4386 |
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); |
| 4387 |
|
| 4388 |
\copy measurement_y2008m02 from 'measurement_y2008m02' |
| 4389 |
-- possibly some other data preparation work |
| 4390 |
|
| 4391 |
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 |
| 4392 |
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' ); |
| 4393 |
</programlisting> |
| 4394 |
</para> |
| 4395 |
|
| 4396 |
<para> |
| 4397 |
Note that when running the <command>ATTACH PARTITION</command> command, |
| 4398 |
the table will be scanned to validate the partition constraint while |
| 4399 |
holding an <literal>ACCESS EXCLUSIVE</literal> lock on that partition. |
| 4400 |
As shown above, it is recommended to avoid this scan by creating a |
| 4401 |
<literal>CHECK</literal> constraint matching the expected partition |
| 4402 |
constraint on the table prior to attaching it. Once the |
| 4403 |
<command>ATTACH PARTITION</command> is complete, it is recommended to drop |
| 4404 |
the now-redundant <literal>CHECK</literal> constraint. |
| 4405 |
If the table being attached is itself a partitioned table, then each of its |
| 4406 |
sub-partitions will be recursively locked and scanned until either a |
| 4407 |
suitable <literal>CHECK</literal> constraint is encountered or the leaf |
| 4408 |
partitions are reached. |
| 4409 |
</para> |
| 4410 |
|
| 4411 |
<para> |
| 4412 |
Similarly, if the partitioned table has a <literal>DEFAULT</literal> |
| 4413 |
partition, it is recommended to create a <literal>CHECK</literal> |
| 4414 |
constraint which excludes the to-be-attached partition's constraint. If |
| 4415 |
this is not done, the <literal>DEFAULT</literal> partition will be |
| 4416 |
scanned to verify that it contains no records which should be located in |
| 4417 |
the partition being attached. This operation will be performed whilst |
| 4418 |
holding an <literal>ACCESS EXCLUSIVE</literal> lock on the <literal> |
| 4419 |
DEFAULT</literal> partition. If the <literal>DEFAULT</literal> partition |
| 4420 |
is itself a partitioned table, then each of its partitions will be |
| 4421 |
recursively checked in the same way as the table being attached, as |
| 4422 |
mentioned above. |
| 4423 |
</para> |
| 4424 |
|
| 4425 |
<para> |
| 4426 |
As mentioned earlier, it is possible to create indexes on partitioned |
| 4427 |
tables so that they are applied automatically to the entire hierarchy. |
| 4428 |
This can be very convenient as not only will all existing partitions be |
| 4429 |
indexed, but any future partitions will be as well. However, one |
| 4430 |
limitation when creating new indexes on partitioned tables is that it |
| 4431 |
is not possible to use the <literal>CONCURRENTLY</literal> |
| 4432 |
qualifier, which could lead to long lock times. To avoid this, you can |
| 4433 |
use <command>CREATE INDEX ON ONLY</command> the partitioned table, which |
| 4434 |
creates the new index marked as invalid, preventing automatic application |
| 4435 |
to existing partitions. Instead, indexes can then be created individually |
| 4436 |
on each partition using <literal>CONCURRENTLY</literal> and |
| 4437 |
<firstterm>attached</firstterm> to the partitioned index on the parent |
| 4438 |
using <command>ALTER INDEX ... ATTACH PARTITION</command>. Once indexes for |
| 4439 |
all the partitions are attached to the parent index, the parent index will |
| 4440 |
be marked valid automatically. Example: |
| 4441 |
<programlisting> |
| 4442 |
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales); |
| 4443 |
|
| 4444 |
CREATE INDEX CONCURRENTLY measurement_usls_200602_idx |
| 4445 |
ON measurement_y2006m02 (unitsales); |
| 4446 |
ALTER INDEX measurement_usls_idx |
| 4447 |
ATTACH PARTITION measurement_usls_200602_idx; |
| 4448 |
... |
| 4449 |
</programlisting> |
| 4450 |
|
| 4451 |
This technique can be used with <literal>UNIQUE</literal> and |
| 4452 |
<literal>PRIMARY KEY</literal> constraints too; the indexes are created |
| 4453 |
implicitly when the constraint is created. Example: |
| 4454 |
<programlisting> |
| 4455 |
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate); |
| 4456 |
|
| 4457 |
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate); |
| 4458 |
ALTER INDEX measurement_city_id_logdate_key |
| 4459 |
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key; |
| 4460 |
... |
| 4461 |
</programlisting> |
| 4462 |
</para> |
| 4463 |
</sect3> |
| 4464 |
|
| 4465 |
<sect3 id="ddl-partitioning-declarative-limitations"> |
| 4466 |
<title>Limitations</title> |
| 4467 |
|
| 4468 |
<para> |
| 4469 |
The following limitations apply to partitioned tables: |
| 4470 |
<itemizedlist> |
| 4471 |
<listitem> |
| 4472 |
<para> |
| 4473 |
To create a unique or primary key constraint on a partitioned table, |
| 4474 |
the partition keys must not include any expressions or function calls |
| 4475 |
and the constraint's columns must include all of the partition key |
| 4476 |
columns. This limitation exists because the individual indexes making |
| 4477 |
up the constraint can only directly enforce uniqueness within their own |
| 4478 |
partitions; therefore, the partition structure itself must guarantee |
| 4479 |
that there are not duplicates in different partitions. |
| 4480 |
</para> |
| 4481 |
</listitem> |
| 4482 |
|
| 4483 |
<listitem> |
| 4484 |
<para> |
| 4485 |
Similarly an exclusion constraint must include all the |
| 4486 |
partition key columns. Furthermore the constraint must compare those |
| 4487 |
columns for equality (not e.g. <literal>&&</literal>). |
| 4488 |
Again, this limitation stems from not being able to enforce |
| 4489 |
cross-partition restrictions. The constraint may include additional |
| 4490 |
columns that aren't part of the partition key, and it may compare |
| 4491 |
those with any operators you like. |
| 4492 |
</para> |
| 4493 |
</listitem> |
| 4494 |
|
| 4495 |
<listitem> |
| 4496 |
<para> |
| 4497 |
<literal>BEFORE ROW</literal> triggers on <literal>INSERT</literal> |
| 4498 |
cannot change which partition is the final destination for a new row. |
| 4499 |
</para> |
| 4500 |
</listitem> |
| 4501 |
|
| 4502 |
<listitem> |
| 4503 |
<para> |
| 4504 |
Mixing temporary and permanent relations in the same partition tree is |
| 4505 |
not allowed. Hence, if the partitioned table is permanent, so must be |
| 4506 |
its partitions and likewise if the partitioned table is temporary. When |
| 4507 |
using temporary relations, all members of the partition tree have to be |
| 4508 |
from the same session. |
| 4509 |
</para> |
| 4510 |
</listitem> |
| 4511 |
</itemizedlist> |
| 4512 |
</para> |
| 4513 |
|
| 4514 |
<para> |
| 4515 |
Individual partitions are linked to their partitioned table using |
| 4516 |
inheritance behind-the-scenes. However, it is not possible to use |
| 4517 |
all of the generic features of inheritance with declaratively |
| 4518 |
partitioned tables or their partitions, as discussed below. Notably, |
| 4519 |
a partition cannot have any parents other than the partitioned table |
| 4520 |
it is a partition of, nor can a table inherit from both a partitioned |
| 4521 |
table and a regular table. That means partitioned tables and their |
| 4522 |
partitions never share an inheritance hierarchy with regular tables. |
| 4523 |
</para> |
| 4524 |
|
| 4525 |
<para> |
| 4526 |
Since a partition hierarchy consisting of the partitioned table and its |
| 4527 |
partitions is still an inheritance hierarchy, |
| 4528 |
<structfield>tableoid</structfield> and all the normal rules of |
| 4529 |
inheritance apply as described in <xref linkend="ddl-inherit"/>, with |
| 4530 |
a few exceptions: |
| 4531 |
|
| 4532 |
<itemizedlist> |
| 4533 |
<listitem> |
| 4534 |
<para> |
| 4535 |
Partitions cannot have columns that are not present in the parent. It |
| 4536 |
is not possible to specify columns when creating partitions with |
| 4537 |
<command>CREATE TABLE</command>, nor is it possible to add columns to |
| 4538 |
partitions after-the-fact using <command>ALTER TABLE</command>. |
| 4539 |
Tables may be added as a partition with <command>ALTER TABLE |
| 4540 |
... ATTACH PARTITION</command> only if their columns exactly match |
| 4541 |
the parent. |
| 4542 |
</para> |
| 4543 |
</listitem> |
| 4544 |
|
| 4545 |
<listitem> |
| 4546 |
<para> |
| 4547 |
Both <literal>CHECK</literal> and <literal>NOT NULL</literal> |
| 4548 |
constraints of a partitioned table are always inherited by all its |
| 4549 |
partitions; it is not allowed to create <literal>NO INHERIT</literal> |
| 4550 |
constraints of those types. |
| 4551 |
You cannot drop a constraint of those types if the same constraint |
| 4552 |
is present in the parent table. |
| 4553 |
</para> |
| 4554 |
</listitem> |
| 4555 |
|
| 4556 |
<listitem> |
| 4557 |
<para> |
| 4558 |
Using <literal>ONLY</literal> to add or drop a constraint on only |
| 4559 |
the partitioned table is supported as long as there are no |
| 4560 |
partitions. Once partitions exist, using <literal>ONLY</literal> |
| 4561 |
will result in an error for any constraints other than |
| 4562 |
<literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>. |
| 4563 |
Instead, constraints on the partitions |
| 4564 |
themselves can be added and (if they are not present in the parent |
| 4565 |
table) dropped. |
| 4566 |
</para> |
| 4567 |
</listitem> |
| 4568 |
|
| 4569 |
<listitem> |
| 4570 |
<para> |
| 4571 |
As a partitioned table does not have any data itself, attempts to use |
| 4572 |
<command>TRUNCATE</command> <literal>ONLY</literal> on a partitioned |
| 4573 |
table will always return an error. |
| 4574 |
</para> |
| 4575 |
</listitem> |
| 4576 |
</itemizedlist> |
| 4577 |
</para> |
| 4578 |
</sect3> |
| 4579 |
</sect2> |
| 4580 |
|
| 4581 |
<sect2 id="ddl-partitioning-using-inheritance"> |
| 4582 |
<title>Partitioning Using Inheritance</title> |
| 4583 |
|
| 4584 |
<para> |
| 4585 |
While the built-in declarative partitioning is suitable for most |
| 4586 |
common use cases, there are some circumstances where a more flexible |
| 4587 |
approach may be useful. Partitioning can be implemented using table |
| 4588 |
inheritance, which allows for several features not supported |
| 4589 |
by declarative partitioning, such as: |
| 4590 |
|
| 4591 |
<itemizedlist> |
| 4592 |
<listitem> |
| 4593 |
<para> |
| 4594 |
For declarative partitioning, partitions must have exactly the same set |
| 4595 |
of columns as the partitioned table, whereas with table inheritance, |
| 4596 |
child tables may have extra columns not present in the parent. |
| 4597 |
</para> |
| 4598 |
</listitem> |
| 4599 |
|
| 4600 |
<listitem> |
| 4601 |
<para> |
| 4602 |
Table inheritance allows for multiple inheritance. |
| 4603 |
</para> |
| 4604 |
</listitem> |
| 4605 |
|
| 4606 |
<listitem> |
| 4607 |
<para> |
| 4608 |
Declarative partitioning only supports range, list and hash |
| 4609 |
partitioning, whereas table inheritance allows data to be divided in a |
| 4610 |
manner of the user's choosing. (Note, however, that if constraint |
| 4611 |
exclusion is unable to prune child tables effectively, query performance |
| 4612 |
might be poor.) |
| 4613 |
</para> |
| 4614 |
</listitem> |
| 4615 |
</itemizedlist> |
| 4616 |
</para> |
| 4617 |
|
| 4618 |
<sect3 id="ddl-partitioning-inheritance-example"> |
| 4619 |
<title>Example</title> |
| 4620 |
|
| 4621 |
<para> |
| 4622 |
This example builds a partitioning structure equivalent to the |
| 4623 |
declarative partitioning example above. Use |
| 4624 |
the following steps: |
| 4625 |
|
| 4626 |
<orderedlist spacing="compact"> |
| 4627 |
<listitem> |
| 4628 |
<para> |
| 4629 |
Create the <quote>root</quote> table, from which all of the |
| 4630 |
<quote>child</quote> tables will inherit. This table will contain no data. Do not |
| 4631 |
define any check constraints on this table, unless you intend them |
| 4632 |
to be applied equally to all child tables. There is no point in |
| 4633 |
defining any indexes or unique constraints on it, either. For our |
| 4634 |
example, the root table is the <structname>measurement</structname> |
| 4635 |
table as originally defined: |
| 4636 |
|
| 4637 |
<programlisting> |
| 4638 |
CREATE TABLE measurement ( |
| 4639 |
city_id int not null, |
| 4640 |
logdate date not null, |
| 4641 |
peaktemp int, |
| 4642 |
unitsales int |
| 4643 |
); |
| 4644 |
</programlisting> |
| 4645 |
</para> |
| 4646 |
</listitem> |
| 4647 |
|
| 4648 |
<listitem> |
| 4649 |
<para> |
| 4650 |
Create several <quote>child</quote> tables that each inherit from |
| 4651 |
the root table. Normally, these tables will not add any columns |
| 4652 |
to the set inherited from the root. Just as with declarative |
| 4653 |
partitioning, these tables are in every way normal |
| 4654 |
<productname>PostgreSQL</productname> tables (or foreign tables). |
| 4655 |
</para> |
| 4656 |
|
| 4657 |
<para> |
| 4658 |
<programlisting> |
| 4659 |
CREATE TABLE measurement_y2006m02 () INHERITS (measurement); |
| 4660 |
CREATE TABLE measurement_y2006m03 () INHERITS (measurement); |
| 4661 |
... |
| 4662 |
CREATE TABLE measurement_y2007m11 () INHERITS (measurement); |
| 4663 |
CREATE TABLE measurement_y2007m12 () INHERITS (measurement); |
| 4664 |
CREATE TABLE measurement_y2008m01 () INHERITS (measurement); |
| 4665 |
</programlisting> |
| 4666 |
</para> |
| 4667 |
</listitem> |
| 4668 |
|
| 4669 |
<listitem> |
| 4670 |
<para> |
| 4671 |
Add non-overlapping table constraints to the child tables to |
| 4672 |
define the allowed key values in each. |
| 4673 |
</para> |
| 4674 |
|
| 4675 |
<para> |
| 4676 |
Typical examples would be: |
| 4677 |
<programlisting> |
| 4678 |
CHECK ( x = 1 ) |
| 4679 |
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) |
| 4680 |
CHECK ( outletID >= 100 AND outletID < 200 ) |
| 4681 |
</programlisting> |
| 4682 |
Ensure that the constraints guarantee that there is no overlap |
| 4683 |
between the key values permitted in different child tables. A common |
| 4684 |
mistake is to set up range constraints like: |
| 4685 |
<programlisting> |
| 4686 |
CHECK ( outletID BETWEEN 100 AND 200 ) |
| 4687 |
CHECK ( outletID BETWEEN 200 AND 300 ) |
| 4688 |
</programlisting> |
| 4689 |
This is wrong since it is not clear which child table the key |
| 4690 |
value 200 belongs in. |
| 4691 |
Instead, ranges should be defined in this style: |
| 4692 |
|
| 4693 |
<programlisting> |
| 4694 |
CREATE TABLE measurement_y2006m02 ( |
| 4695 |
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) |
| 4696 |
) INHERITS (measurement); |
| 4697 |
|
| 4698 |
CREATE TABLE measurement_y2006m03 ( |
| 4699 |
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) |
| 4700 |
) INHERITS (measurement); |
| 4701 |
|
| 4702 |
... |
| 4703 |
CREATE TABLE measurement_y2007m11 ( |
| 4704 |
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) |
| 4705 |
) INHERITS (measurement); |
| 4706 |
|
| 4707 |
CREATE TABLE measurement_y2007m12 ( |
| 4708 |
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) |
| 4709 |
) INHERITS (measurement); |
| 4710 |
|
| 4711 |
CREATE TABLE measurement_y2008m01 ( |
| 4712 |
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) |
| 4713 |
) INHERITS (measurement); |
| 4714 |
</programlisting> |
| 4715 |
</para> |
| 4716 |
</listitem> |
| 4717 |
|
| 4718 |
<listitem> |
| 4719 |
<para> |
| 4720 |
For each child table, create an index on the key column(s), |
| 4721 |
as well as any other indexes you might want. |
| 4722 |
<programlisting> |
| 4723 |
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); |
| 4724 |
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); |
| 4725 |
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); |
| 4726 |
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); |
| 4727 |
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate); |
| 4728 |
</programlisting> |
| 4729 |
</para> |
| 4730 |
</listitem> |
| 4731 |
|
| 4732 |
<listitem> |
| 4733 |
<para> |
| 4734 |
We want our application to be able to say <literal>INSERT INTO |
| 4735 |
measurement ...</literal> and have the data be redirected into the |
| 4736 |
appropriate child table. We can arrange that by attaching |
| 4737 |
a suitable trigger function to the root table. |
| 4738 |
If data will be added only to the latest child, we can |
| 4739 |
use a very simple trigger function: |
| 4740 |
|
| 4741 |
<programlisting> |
| 4742 |
CREATE OR REPLACE FUNCTION measurement_insert_trigger() |
| 4743 |
RETURNS TRIGGER AS $$ |
| 4744 |
BEGIN |
| 4745 |
INSERT INTO measurement_y2008m01 VALUES (NEW.*); |
| 4746 |
RETURN NULL; |
| 4747 |
END; |
| 4748 |
$$ |
| 4749 |
LANGUAGE plpgsql; |
| 4750 |
</programlisting> |
| 4751 |
</para> |
| 4752 |
|
| 4753 |
<para> |
| 4754 |
After creating the function, we create a trigger which |
| 4755 |
calls the trigger function: |
| 4756 |
|
| 4757 |
<programlisting> |
| 4758 |
CREATE TRIGGER insert_measurement_trigger |
| 4759 |
BEFORE INSERT ON measurement |
| 4760 |
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger(); |
| 4761 |
</programlisting> |
| 4762 |
|
| 4763 |
We must redefine the trigger function each month so that it always |
| 4764 |
inserts into the current child table. The trigger definition does |
| 4765 |
not need to be updated, however. |
| 4766 |
</para> |
| 4767 |
|
| 4768 |
<para> |
| 4769 |
We might want to insert data and have the server automatically |
| 4770 |
locate the child table into which the row should be added. We |
| 4771 |
could do this with a more complex trigger function, for example: |
| 4772 |
|
| 4773 |
<programlisting> |
| 4774 |
CREATE OR REPLACE FUNCTION measurement_insert_trigger() |
| 4775 |
RETURNS TRIGGER AS $$ |
| 4776 |
BEGIN |
| 4777 |
IF ( NEW.logdate >= DATE '2006-02-01' AND |
| 4778 |
NEW.logdate < DATE '2006-03-01' ) THEN |
| 4779 |
INSERT INTO measurement_y2006m02 VALUES (NEW.*); |
| 4780 |
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND |
| 4781 |
NEW.logdate < DATE '2006-04-01' ) THEN |
| 4782 |
INSERT INTO measurement_y2006m03 VALUES (NEW.*); |
| 4783 |
... |
| 4784 |
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND |
| 4785 |
NEW.logdate < DATE '2008-02-01' ) THEN |
| 4786 |
INSERT INTO measurement_y2008m01 VALUES (NEW.*); |
| 4787 |
ELSE |
| 4788 |
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; |
| 4789 |
END IF; |
| 4790 |
RETURN NULL; |
| 4791 |
END; |
| 4792 |
$$ |
| 4793 |
LANGUAGE plpgsql; |
| 4794 |
</programlisting> |
| 4795 |
|
| 4796 |
The trigger definition is the same as before. |
| 4797 |
Note that each <literal>IF</literal> test must exactly match the |
| 4798 |
<literal>CHECK</literal> constraint for its child table. |
| 4799 |
</para> |
| 4800 |
|
| 4801 |
<para> |
| 4802 |
While this function is more complex than the single-month case, |
| 4803 |
it doesn't need to be updated as often, since branches can be |
| 4804 |
added in advance of being needed. |
| 4805 |
</para> |
| 4806 |
|
| 4807 |
<note> |
| 4808 |
<para> |
| 4809 |
In practice, it might be best to check the newest child first, |
| 4810 |
if most inserts go into that child. For simplicity, we have |
| 4811 |
shown the trigger's tests in the same order as in other parts |
| 4812 |
of this example. |
| 4813 |
</para> |
| 4814 |
</note> |
| 4815 |
|
| 4816 |
<para> |
| 4817 |
A different approach to redirecting inserts into the appropriate |
| 4818 |
child table is to set up rules, instead of a trigger, on the |
| 4819 |
root table. For example: |
| 4820 |
|
| 4821 |
<programlisting> |
| 4822 |
CREATE RULE measurement_insert_y2006m02 AS |
| 4823 |
ON INSERT TO measurement WHERE |
| 4824 |
( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) |
| 4825 |
DO INSTEAD |
| 4826 |
INSERT INTO measurement_y2006m02 VALUES (NEW.*); |
| 4827 |
... |
| 4828 |
CREATE RULE measurement_insert_y2008m01 AS |
| 4829 |
ON INSERT TO measurement WHERE |
| 4830 |
( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) |
| 4831 |
DO INSTEAD |
| 4832 |
INSERT INTO measurement_y2008m01 VALUES (NEW.*); |
| 4833 |
</programlisting> |
| 4834 |
|
| 4835 |
A rule has significantly more overhead than a trigger, but the |
| 4836 |
overhead is paid once per query rather than once per row, so this |
| 4837 |
method might be advantageous for bulk-insert situations. In most |
| 4838 |
cases, however, the trigger method will offer better performance. |
| 4839 |
</para> |
| 4840 |
|
| 4841 |
<para> |
| 4842 |
Be aware that <command>COPY</command> ignores rules. If you want to |
| 4843 |
use <command>COPY</command> to insert data, you'll need to copy into the |
| 4844 |
correct child table rather than directly into the root. <command>COPY</command> |
| 4845 |
does fire triggers, so you can use it normally if you use the trigger |
| 4846 |
approach. |
| 4847 |
</para> |
| 4848 |
|
| 4849 |
<para> |
| 4850 |
Another disadvantage of the rule approach is that there is no simple |
| 4851 |
way to force an error if the set of rules doesn't cover the insertion |
| 4852 |
date; the data will silently go into the root table instead. |
| 4853 |
</para> |
| 4854 |
</listitem> |
| 4855 |
|
| 4856 |
<listitem> |
| 4857 |
<para> |
| 4858 |
Ensure that the <xref linkend="guc-constraint-exclusion"/> |
| 4859 |
configuration parameter is not disabled in |
| 4860 |
<filename>postgresql.conf</filename>; otherwise |
| 4861 |
child tables may be accessed unnecessarily. |
| 4862 |
</para> |
| 4863 |
</listitem> |
| 4864 |
</orderedlist> |
| 4865 |
</para> |
| 4866 |
|
| 4867 |
<para> |
| 4868 |
As we can see, a complex table hierarchy could require a |
| 4869 |
substantial amount of DDL. In the above example we would be creating |
| 4870 |
a new child table each month, so it might be wise to write a script that |
| 4871 |
generates the required DDL automatically. |
| 4872 |
</para> |
| 4873 |
</sect3> |
| 4874 |
|
| 4875 |
<sect3 id="ddl-partitioning-inheritance-maintenance"> |
| 4876 |
<title>Maintenance for Inheritance Partitioning</title> |
| 4877 |
<para> |
| 4878 |
To remove old data quickly, simply drop the child table that is no longer |
| 4879 |
necessary: |
| 4880 |
<programlisting> |
| 4881 |
DROP TABLE measurement_y2006m02; |
| 4882 |
</programlisting> |
| 4883 |
</para> |
| 4884 |
|
| 4885 |
<para> |
| 4886 |
To remove the child table from the inheritance hierarchy table but retain access to |
| 4887 |
it as a table in its own right: |
| 4888 |
|
| 4889 |
<programlisting> |
| 4890 |
ALTER TABLE measurement_y2006m02 NO INHERIT measurement; |
| 4891 |
</programlisting> |
| 4892 |
</para> |
| 4893 |
|
| 4894 |
<para> |
| 4895 |
To add a new child table to handle new data, create an empty child table |
| 4896 |
just as the original children were created above: |
| 4897 |
|
| 4898 |
<programlisting> |
| 4899 |
CREATE TABLE measurement_y2008m02 ( |
| 4900 |
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ) |
| 4901 |
) INHERITS (measurement); |
| 4902 |
</programlisting> |
| 4903 |
|
| 4904 |
Alternatively, one may want to create and populate the new child table |
| 4905 |
before adding it to the table hierarchy. This could allow data to be |
| 4906 |
loaded, checked, and transformed before being made visible to queries on |
| 4907 |
the parent table. |
| 4908 |
|
| 4909 |
<programlisting> |
| 4910 |
CREATE TABLE measurement_y2008m02 |
| 4911 |
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); |
| 4912 |
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 |
| 4913 |
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); |
| 4914 |
\copy measurement_y2008m02 from 'measurement_y2008m02' |
| 4915 |
-- possibly some other data preparation work |
| 4916 |
ALTER TABLE measurement_y2008m02 INHERIT measurement; |
| 4917 |
</programlisting> |
| 4918 |
</para> |
| 4919 |
</sect3> |
| 4920 |
|
| 4921 |
<sect3 id="ddl-partitioning-inheritance-caveats"> |
| 4922 |
<title>Caveats</title> |
| 4923 |
|
| 4924 |
<para> |
| 4925 |
The following caveats apply to partitioning implemented using |
| 4926 |
inheritance: |
| 4927 |
<itemizedlist> |
| 4928 |
<listitem> |
| 4929 |
<para> |
| 4930 |
There is no automatic way to verify that all of the |
| 4931 |
<literal>CHECK</literal> constraints are mutually |
| 4932 |
exclusive. It is safer to create code that generates |
| 4933 |
child tables and creates and/or modifies associated objects than |
| 4934 |
to write each by hand. |
| 4935 |
</para> |
| 4936 |
</listitem> |
| 4937 |
|
| 4938 |
<listitem> |
| 4939 |
<para> |
| 4940 |
Indexes and foreign key constraints apply to single tables and not |
| 4941 |
to their inheritance children, hence they have some |
| 4942 |
<link linkend="ddl-inherit-caveats">caveats</link> to be aware of. |
| 4943 |
</para> |
| 4944 |
</listitem> |
| 4945 |
|
| 4946 |
<listitem> |
| 4947 |
<para> |
| 4948 |
The schemes shown here assume that the values of a row's key column(s) |
| 4949 |
never change, or at least do not change enough to require it to move to another partition. |
| 4950 |
An <command>UPDATE</command> that attempts |
| 4951 |
to do that will fail because of the <literal>CHECK</literal> constraints. |
| 4952 |
If you need to handle such cases, you can put suitable update triggers |
| 4953 |
on the child tables, but it makes management of the structure |
| 4954 |
much more complicated. |
| 4955 |
</para> |
| 4956 |
</listitem> |
| 4957 |
|
| 4958 |
<listitem> |
| 4959 |
<para> |
| 4960 |
Manual <command>VACUUM</command> and <command>ANALYZE</command> |
| 4961 |
commands will automatically process all inheritance child tables. If |
| 4962 |
this is undesirable, you can use the <literal>ONLY</literal> keyword. |
| 4963 |
A command like: |
| 4964 |
<programlisting> |
| 4965 |
ANALYZE ONLY measurement; |
| 4966 |
</programlisting> |
| 4967 |
will only process the root table. |
| 4968 |
</para> |
| 4969 |
</listitem> |
| 4970 |
|
| 4971 |
<listitem> |
| 4972 |
<para> |
| 4973 |
<command>INSERT</command> statements with <literal>ON CONFLICT</literal> |
| 4974 |
clauses are unlikely to work as expected, as the <literal>ON CONFLICT</literal> |
| 4975 |
action is only taken in case of unique violations on the specified |
| 4976 |
target relation, not its child relations. |
| 4977 |
</para> |
| 4978 |
</listitem> |
| 4979 |
|
| 4980 |
<listitem> |
| 4981 |
<para> |
| 4982 |
Triggers or rules will be needed to route rows to the desired |
| 4983 |
child table, unless the application is explicitly aware of the |
| 4984 |
partitioning scheme. Triggers may be complicated to write, and will |
| 4985 |
be much slower than the tuple routing performed internally by |
| 4986 |
declarative partitioning. |
| 4987 |
</para> |
| 4988 |
</listitem> |
| 4989 |
</itemizedlist> |
| 4990 |
</para> |
| 4991 |
</sect3> |
| 4992 |
</sect2> |
| 4993 |
|
| 4994 |
<sect2 id="ddl-partition-pruning"> |
| 4995 |
<title>Partition Pruning</title> |
| 4996 |
|
| 4997 |
<indexterm> |
| 4998 |
<primary>partition pruning</primary> |
| 4999 |
</indexterm> |
| 5000 |
|
| 5001 |
<para> |
| 5002 |
<firstterm>Partition pruning</firstterm> is a query optimization technique |
| 5003 |
that improves performance for declaratively partitioned tables. |
| 5004 |
As an example: |
| 5005 |
|
| 5006 |
<programlisting> |
| 5007 |
SET enable_partition_pruning = on; -- the default |
| 5008 |
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; |
| 5009 |
</programlisting> |
| 5010 |
|
| 5011 |
Without partition pruning, the above query would scan each of the |
| 5012 |
partitions of the <structname>measurement</structname> table. With |
| 5013 |
partition pruning enabled, the planner will examine the definition |
| 5014 |
of each partition and prove that the partition need not |
| 5015 |
be scanned because it could not contain any rows meeting the query's |
| 5016 |
<literal>WHERE</literal> clause. When the planner can prove this, it |
| 5017 |
excludes (<firstterm>prunes</firstterm>) the partition from the query |
| 5018 |
plan. |
| 5019 |
</para> |
| 5020 |
|
| 5021 |
<para> |
| 5022 |
By using the EXPLAIN command and the <xref |
| 5023 |
linkend="guc-enable-partition-pruning"/> configuration parameter, it's |
| 5024 |
possible to show the difference between a plan for which partitions have |
| 5025 |
been pruned and one for which they have not. A typical unoptimized |
| 5026 |
plan for this type of table setup is: |
| 5027 |
<programlisting> |
| 5028 |
SET enable_partition_pruning = off; |
| 5029 |
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; |
| 5030 |
QUERY PLAN |
| 5031 |
-------------------------------------------------------------------&zwsp;---------------- |
| 5032 |
Aggregate (cost=188.76..188.77 rows=1 width=8) |
| 5033 |
-> Append (cost=0.00..181.05 rows=3085 width=0) |
| 5034 |
-> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0) |
| 5035 |
Filter: (logdate >= '2008-01-01'::date) |
| 5036 |
-> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0) |
| 5037 |
Filter: (logdate >= '2008-01-01'::date) |
| 5038 |
... |
| 5039 |
-> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0) |
| 5040 |
Filter: (logdate >= '2008-01-01'::date) |
| 5041 |
-> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0) |
| 5042 |
Filter: (logdate >= '2008-01-01'::date) |
| 5043 |
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) |
| 5044 |
Filter: (logdate >= '2008-01-01'::date) |
| 5045 |
</programlisting> |
| 5046 |
|
| 5047 |
Some or all of the partitions might use index scans instead of |
| 5048 |
full-table sequential scans, but the point here is that there |
| 5049 |
is no need to scan the older partitions at all to answer this query. |
| 5050 |
When we enable partition pruning, we get a significantly |
| 5051 |
cheaper plan that will deliver the same answer: |
| 5052 |
<programlisting> |
| 5053 |
SET enable_partition_pruning = on; |
| 5054 |
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; |
| 5055 |
QUERY PLAN |
| 5056 |
-------------------------------------------------------------------&zwsp;---------------- |
| 5057 |
Aggregate (cost=37.75..37.76 rows=1 width=8) |
| 5058 |
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) |
| 5059 |
Filter: (logdate >= '2008-01-01'::date) |
| 5060 |
</programlisting> |
| 5061 |
</para> |
| 5062 |
|
| 5063 |
<para> |
| 5064 |
Note that partition pruning is driven only by the constraints defined |
| 5065 |
implicitly by the partition keys, not by the presence of indexes. |
| 5066 |
Therefore it isn't necessary to define indexes on the key columns. |
| 5067 |
Whether an index needs to be created for a given partition depends on |
| 5068 |
whether you expect that queries that scan the partition will |
| 5069 |
generally scan a large part of the partition or just a small part. |
| 5070 |
An index will be helpful in the latter case but not the former. |
| 5071 |
</para> |
| 5072 |
|
| 5073 |
<para> |
| 5074 |
Partition pruning can be performed not only during the planning of a |
| 5075 |
given query, but also during its execution. This is useful as it can |
| 5076 |
allow more partitions to be pruned when clauses contain expressions |
| 5077 |
whose values are not known at query planning time, for example, |
| 5078 |
parameters defined in a <command>PREPARE</command> statement, using a |
| 5079 |
value obtained from a subquery, or using a parameterized value on the |
| 5080 |
inner side of a nested loop join. Partition pruning during execution |
| 5081 |
can be performed at any of the following times: |
| 5082 |
|
| 5083 |
<itemizedlist> |
| 5084 |
<listitem> |
| 5085 |
<para> |
| 5086 |
During initialization of the query plan. Partition pruning can be |
| 5087 |
performed here for parameter values which are known during the |
| 5088 |
initialization phase of execution. Partitions which are pruned |
| 5089 |
during this stage will not show up in the query's |
| 5090 |
<command>EXPLAIN</command> or <command>EXPLAIN ANALYZE</command>. |
| 5091 |
It is possible to determine the number of partitions which were |
| 5092 |
removed during this phase by observing the |
| 5093 |
<quote>Subplans Removed</quote> property in the |
| 5094 |
<command>EXPLAIN</command> output. It's important to note that any |
| 5095 |
partitions removed by the partition pruning done at this stage are |
| 5096 |
still locked at the beginning of execution. |
| 5097 |
</para> |
| 5098 |
</listitem> |
| 5099 |
|
| 5100 |
<listitem> |
| 5101 |
<para> |
| 5102 |
During actual execution of the query plan. Partition pruning may |
| 5103 |
also be performed here to remove partitions using values which are |
| 5104 |
only known during actual query execution. This includes values |
| 5105 |
from subqueries and values from execution-time parameters such as |
| 5106 |
those from parameterized nested loop joins. Since the value of |
| 5107 |
these parameters may change many times during the execution of the |
| 5108 |
query, partition pruning is performed whenever one of the |
| 5109 |
execution parameters being used by partition pruning changes. |
| 5110 |
Determining if partitions were pruned during this phase requires |
| 5111 |
careful inspection of the <literal>loops</literal> property in |
| 5112 |
the <command>EXPLAIN ANALYZE</command> output. Subplans |
| 5113 |
corresponding to different partitions may have different values |
| 5114 |
for it depending on how many times each of them was pruned during |
| 5115 |
execution. Some may be shown as <literal>(never executed)</literal> |
| 5116 |
if they were pruned every time. |
| 5117 |
</para> |
| 5118 |
</listitem> |
| 5119 |
</itemizedlist> |
| 5120 |
</para> |
| 5121 |
|
| 5122 |
<para> |
| 5123 |
Partition pruning can be disabled using the |
| 5124 |
<xref linkend="guc-enable-partition-pruning"/> setting. |
| 5125 |
</para> |
| 5126 |
</sect2> |
| 5127 |
|
| 5128 |
<sect2 id="ddl-partitioning-constraint-exclusion"> |
| 5129 |
<title>Partitioning and Constraint Exclusion</title> |
| 5130 |
|
| 5131 |
<indexterm> |
| 5132 |
<primary>constraint exclusion</primary> |
| 5133 |
</indexterm> |
| 5134 |
|
| 5135 |
<para> |
| 5136 |
<firstterm>Constraint exclusion</firstterm> is a query optimization |
| 5137 |
technique similar to partition pruning. While it is primarily used |
| 5138 |
for partitioning implemented using the legacy inheritance method, it can be |
| 5139 |
used for other purposes, including with declarative partitioning. |
| 5140 |
</para> |
| 5141 |
|
| 5142 |
<para> |
| 5143 |
Constraint exclusion works in a very similar way to partition |
| 5144 |
pruning, except that it uses each table's <literal>CHECK</literal> |
| 5145 |
constraints — which gives it its name — whereas partition |
| 5146 |
pruning uses the table's partition bounds, which exist only in the |
| 5147 |
case of declarative partitioning. Another difference is that |
| 5148 |
constraint exclusion is only applied at plan time; there is no attempt |
| 5149 |
to remove partitions at execution time. |
| 5150 |
</para> |
| 5151 |
|
| 5152 |
<para> |
| 5153 |
The fact that constraint exclusion uses <literal>CHECK</literal> |
| 5154 |
constraints, which makes it slow compared to partition pruning, can |
| 5155 |
sometimes be used as an advantage: because constraints can be defined |
| 5156 |
even on declaratively-partitioned tables, in addition to their internal |
| 5157 |
partition bounds, constraint exclusion may be able |
| 5158 |
to elide additional partitions from the query plan. |
| 5159 |
</para> |
| 5160 |
|
| 5161 |
<para> |
| 5162 |
The default (and recommended) setting of |
| 5163 |
<xref linkend="guc-constraint-exclusion"/> is neither |
| 5164 |
<literal>on</literal> nor <literal>off</literal>, but an intermediate setting |
| 5165 |
called <literal>partition</literal>, which causes the technique to be |
| 5166 |
applied only to queries that are likely to be working on inheritance partitioned |
| 5167 |
tables. The <literal>on</literal> setting causes the planner to examine |
| 5168 |
<literal>CHECK</literal> constraints in all queries, even simple ones that |
| 5169 |
are unlikely to benefit. |
| 5170 |
</para> |
| 5171 |
|
| 5172 |
<para> |
| 5173 |
The following caveats apply to constraint exclusion: |
| 5174 |
|
| 5175 |
<itemizedlist> |
| 5176 |
<listitem> |
| 5177 |
<para> |
| 5178 |
Constraint exclusion is only applied during query planning, unlike |
| 5179 |
partition pruning, which can also be applied during query execution. |
| 5180 |
</para> |
| 5181 |
</listitem> |
| 5182 |
|
| 5183 |
<listitem> |
| 5184 |
<para> |
| 5185 |
Constraint exclusion only works when the query's <literal>WHERE</literal> |
| 5186 |
clause contains constants (or externally supplied parameters). |
| 5187 |
For example, a comparison against a non-immutable function such as |
| 5188 |
<function>CURRENT_TIMESTAMP</function> cannot be optimized, since the |
| 5189 |
planner cannot know which child table the function's value might fall |
| 5190 |
into at run time. |
| 5191 |
</para> |
| 5192 |
</listitem> |
| 5193 |
|
| 5194 |
<listitem> |
| 5195 |
<para> |
| 5196 |
Keep the partitioning constraints simple, else the planner may not be |
| 5197 |
able to prove that child tables might not need to be visited. Use simple |
| 5198 |
equality conditions for list partitioning, or simple |
| 5199 |
range tests for range partitioning, as illustrated in the preceding |
| 5200 |
examples. A good rule of thumb is that partitioning constraints should |
| 5201 |
contain only comparisons of the partitioning column(s) to constants |
| 5202 |
using B-tree-indexable operators, because only B-tree-indexable |
| 5203 |
column(s) are allowed in the partition key. |
| 5204 |
</para> |
| 5205 |
</listitem> |
| 5206 |
|
| 5207 |
<listitem> |
| 5208 |
<para> |
| 5209 |
All constraints on all children of the parent table are examined |
| 5210 |
during constraint exclusion, so large numbers of children are likely |
| 5211 |
to increase query planning time considerably. So the legacy |
| 5212 |
inheritance based partitioning will work well with up to perhaps a |
| 5213 |
hundred child tables; don't try to use many thousands of children. |
| 5214 |
</para> |
| 5215 |
</listitem> |
| 5216 |
|
| 5217 |
</itemizedlist> |
| 5218 |
</para> |
| 5219 |
</sect2> |
| 5220 |
|
| 5221 |
<sect2 id="ddl-partitioning-declarative-best-practices"> |
| 5222 |
<title>Best Practices for Declarative Partitioning</title> |
| 5223 |
|
| 5224 |
<para> |
| 5225 |
The choice of how to partition a table should be made carefully, as the |
| 5226 |
performance of query planning and execution can be negatively affected by |
| 5227 |
poor design. |
| 5228 |
</para> |
| 5229 |
|
| 5230 |
<para> |
| 5231 |
One of the most critical design decisions will be the column or columns |
| 5232 |
by which you partition your data. Often the best choice will be to |
| 5233 |
partition by the column or set of columns which most commonly appear in |
| 5234 |
<literal>WHERE</literal> clauses of queries being executed on the |
| 5235 |
partitioned table. <literal>WHERE</literal> clauses that are compatible |
| 5236 |
with the partition bound constraints can be used to prune unneeded |
| 5237 |
partitions. However, you may be forced into making other decisions by |
| 5238 |
requirements for the <literal>PRIMARY KEY</literal> or a |
| 5239 |
<literal>UNIQUE</literal> constraint. Removal of unwanted data is also a |
| 5240 |
factor to consider when planning your partitioning strategy. An entire |
| 5241 |
partition can be detached fairly quickly, so it may be beneficial to |
| 5242 |
design the partition strategy in such a way that all data to be removed |
| 5243 |
at once is located in a single partition. |
| 5244 |
</para> |
| 5245 |
|
| 5246 |
<para> |
| 5247 |
Choosing the target number of partitions that the table should be divided |
| 5248 |
into is also a critical decision to make. Not having enough partitions |
| 5249 |
may mean that indexes remain too large and that data locality remains poor |
| 5250 |
which could result in low cache hit ratios. However, dividing the table |
| 5251 |
into too many partitions can also cause issues. Too many partitions can |
| 5252 |
mean longer query planning times and higher memory consumption during both |
| 5253 |
query planning and execution, as further described below. |
| 5254 |
When choosing how to partition your table, |
| 5255 |
it's also important to consider what changes may occur in the future. For |
| 5256 |
example, if you choose to have one partition per customer and you |
| 5257 |
currently have a small number of large customers, consider the |
| 5258 |
implications if in several years you instead find yourself with a large |
| 5259 |
number of small customers. In this case, it may be better to choose to |
| 5260 |
partition by <literal>HASH</literal> and choose a reasonable number of |
| 5261 |
partitions rather than trying to partition by <literal>LIST</literal> and |
| 5262 |
hoping that the number of customers does not increase beyond what it is |
| 5263 |
practical to partition the data by. |
| 5264 |
</para> |
| 5265 |
|
| 5266 |
<para> |
| 5267 |
Sub-partitioning can be useful to further divide partitions that are |
| 5268 |
expected to become larger than other partitions. |
| 5269 |
Another option is to use range partitioning with multiple columns in |
| 5270 |
the partition key. |
| 5271 |
Either of these can easily lead to excessive numbers of partitions, |
| 5272 |
so restraint is advisable. |
| 5273 |
</para> |
| 5274 |
|
| 5275 |
<para> |
| 5276 |
It is important to consider the overhead of partitioning during |
| 5277 |
query planning and execution. The query planner is generally able to |
| 5278 |
handle partition hierarchies with up to a few thousand partitions fairly |
| 5279 |
well, provided that typical queries allow the query planner to prune all |
| 5280 |
but a small number of partitions. Planning times become longer and memory |
| 5281 |
consumption becomes higher when more partitions remain after the planner |
| 5282 |
performs partition pruning. Another |
| 5283 |
reason to be concerned about having a large number of partitions is that |
| 5284 |
the server's memory consumption may grow significantly over |
| 5285 |
time, especially if many sessions touch large numbers of partitions. |
| 5286 |
That's because each partition requires its metadata to be loaded into the |
| 5287 |
local memory of each session that touches it. |
| 5288 |
</para> |
| 5289 |
|
| 5290 |
<para> |
| 5291 |
With data warehouse type workloads, it can make sense to use a larger |
| 5292 |
number of partitions than with an <acronym>OLTP</acronym> type workload. |
| 5293 |
Generally, in data warehouses, query planning time is less of a concern as |
| 5294 |
the majority of processing time is spent during query execution. With |
| 5295 |
either of these two types of workload, it is important to make the right |
| 5296 |
decisions early, as re-partitioning large quantities of data can be |
| 5297 |
painfully slow. Simulations of the intended workload are often beneficial |
| 5298 |
for optimizing the partitioning strategy. Never just assume that more |
| 5299 |
partitions are better than fewer partitions, nor vice-versa. |
| 5300 |
</para> |
| 5301 |
</sect2> |
| 5302 |
|
| 5303 |
</sect1> |
| 5304 |
|
| 5305 |
<sect1 id="ddl-foreign-data"> |
| 5306 |
<title>Foreign Data</title> |
| 5307 |
|
| 5308 |
<indexterm> |
| 5309 |
<primary>foreign data</primary> |
| 5310 |
</indexterm> |
| 5311 |
<indexterm> |
| 5312 |
<primary>foreign table</primary> |
| 5313 |
</indexterm> |
| 5314 |
<indexterm> |
| 5315 |
<primary>user mapping</primary> |
| 5316 |
</indexterm> |
| 5317 |
|
| 5318 |
<para> |
| 5319 |
<productname>PostgreSQL</productname> implements portions of the SQL/MED |
| 5320 |
specification, allowing you to access data that resides outside |
| 5321 |
PostgreSQL using regular SQL queries. Such data is referred to as |
| 5322 |
<firstterm>foreign data</firstterm>. (Note that this usage is not to be confused |
| 5323 |
with foreign keys, which are a type of constraint within the database.) |
| 5324 |
</para> |
| 5325 |
|
| 5326 |
<para> |
| 5327 |
Foreign data is accessed with help from a |
| 5328 |
<firstterm>foreign data wrapper</firstterm>. A foreign data wrapper is a |
| 5329 |
library that can communicate with an external data source, hiding the |
| 5330 |
details of connecting to the data source and obtaining data from it. |
| 5331 |
There are some foreign data wrappers available as <filename>contrib</filename> |
| 5332 |
modules; see <xref linkend="contrib"/>. Other kinds of foreign data |
| 5333 |
wrappers might be found as third party products. If none of the existing |
| 5334 |
foreign data wrappers suit your needs, you can write your own; see <xref |
| 5335 |
linkend="fdwhandler"/>. |
| 5336 |
</para> |
| 5337 |
|
| 5338 |
<para> |
| 5339 |
To access foreign data, you need to create a <firstterm>foreign server</firstterm> |
| 5340 |
object, which defines how to connect to a particular external data source |
| 5341 |
according to the set of options used by its supporting foreign data |
| 5342 |
wrapper. Then you need to create one or more <firstterm>foreign |
| 5343 |
tables</firstterm>, which define the structure of the remote data. A |
| 5344 |
foreign table can be used in queries just like a normal table, but a |
| 5345 |
foreign table has no storage in the PostgreSQL server. Whenever it is |
| 5346 |
used, <productname>PostgreSQL</productname> asks the foreign data wrapper |
| 5347 |
to fetch data from the external source, or transmit data to the external |
| 5348 |
source in the case of update commands. |
| 5349 |
</para> |
| 5350 |
|
| 5351 |
<para> |
| 5352 |
Accessing remote data may require authenticating to the external |
| 5353 |
data source. This information can be provided by a |
| 5354 |
<firstterm>user mapping</firstterm>, which can provide additional data |
| 5355 |
such as user names and passwords based |
| 5356 |
on the current <productname>PostgreSQL</productname> role. |
| 5357 |
</para> |
| 5358 |
|
| 5359 |
<para> |
| 5360 |
For additional information, see |
| 5361 |
<xref linkend="sql-createforeigndatawrapper"/>, |
| 5362 |
<xref linkend="sql-createserver"/>, |
| 5363 |
<xref linkend="sql-createusermapping"/>, |
| 5364 |
<xref linkend="sql-createforeigntable"/>, and |
| 5365 |
<xref linkend="sql-importforeignschema"/>. |
| 5366 |
</para> |
| 5367 |
</sect1> |
| 5368 |
|
| 5369 |
<sect1 id="ddl-others"> |
| 5370 |
<title>Other Database Objects</title> |
| 5371 |
|
| 5372 |
<para> |
| 5373 |
Tables are the central objects in a relational database structure, |
| 5374 |
because they hold your data. But they are not the only objects |
| 5375 |
that exist in a database. Many other kinds of objects can be |
| 5376 |
created to make the use and management of the data more efficient |
| 5377 |
or convenient. They are not discussed in this chapter, but we give |
| 5378 |
you a list here so that you are aware of what is possible: |
| 5379 |
</para> |
| 5380 |
|
| 5381 |
<itemizedlist> |
| 5382 |
<listitem> |
| 5383 |
<para> |
| 5384 |
Views |
| 5385 |
</para> |
| 5386 |
</listitem> |
| 5387 |
|
| 5388 |
<listitem> |
| 5389 |
<para> |
| 5390 |
Functions, procedures, and operators |
| 5391 |
</para> |
| 5392 |
</listitem> |
| 5393 |
|
| 5394 |
<listitem> |
| 5395 |
<para> |
| 5396 |
Data types and domains |
| 5397 |
</para> |
| 5398 |
</listitem> |
| 5399 |
|
| 5400 |
<listitem> |
| 5401 |
<para> |
| 5402 |
Triggers and rewrite rules |
| 5403 |
</para> |
| 5404 |
</listitem> |
| 5405 |
</itemizedlist> |
| 5406 |
|
| 5407 |
<para> |
| 5408 |
Detailed information on |
| 5409 |
these topics appears in <xref linkend="server-programming"/>. |
| 5410 |
</para> |
| 5411 |
</sect1> |
| 5412 |
|
| 5413 |
<sect1 id="ddl-depend"> |
| 5414 |
<title>Dependency Tracking</title> |
| 5415 |
|
| 5416 |
<indexterm zone="ddl-depend"> |
| 5417 |
<primary>CASCADE</primary> |
| 5418 |
<secondary sortas="DROP">with DROP</secondary> |
| 5419 |
</indexterm> |
| 5420 |
|
| 5421 |
<indexterm zone="ddl-depend"> |
| 5422 |
<primary>RESTRICT</primary> |
| 5423 |
<secondary sortas="DROP">with DROP</secondary> |
| 5424 |
</indexterm> |
| 5425 |
|
| 5426 |
<para> |
| 5427 |
When you create complex database structures involving many tables |
| 5428 |
with foreign key constraints, views, triggers, functions, etc. you |
| 5429 |
implicitly create a net of dependencies between the objects. |
| 5430 |
For instance, a table with a foreign key constraint depends on the |
| 5431 |
table it references. |
| 5432 |
</para> |
| 5433 |
|
| 5434 |
<para> |
| 5435 |
To ensure the integrity of the entire database structure, |
| 5436 |
<productname>PostgreSQL</productname> makes sure that you cannot |
| 5437 |
drop objects that other objects still depend on. For example, |
| 5438 |
attempting to drop the products table we considered in <xref |
| 5439 |
linkend="ddl-constraints-fk"/>, with the orders table depending on |
| 5440 |
it, would result in an error message like this: |
| 5441 |
<screen> |
| 5442 |
DROP TABLE products; |
| 5443 |
|
| 5444 |
ERROR: cannot drop table products because other objects depend on it |
| 5445 |
DETAIL: constraint orders_product_no_fkey on table orders depends on table products |
| 5446 |
HINT: Use DROP ... CASCADE to drop the dependent objects too. |
| 5447 |
</screen> |
| 5448 |
The error message contains a useful hint: if you do not want to |
| 5449 |
bother deleting all the dependent objects individually, you can run: |
| 5450 |
<screen> |
| 5451 |
DROP TABLE products CASCADE; |
| 5452 |
</screen> |
| 5453 |
and all the dependent objects will be removed, as will any objects |
| 5454 |
that depend on them, recursively. In this case, it doesn't remove |
| 5455 |
the orders table, it only removes the foreign key constraint. |
| 5456 |
It stops there because nothing depends on the foreign key constraint. |
| 5457 |
(If you want to check what <command>DROP ... CASCADE</command> will do, |
| 5458 |
run <command>DROP</command> without <literal>CASCADE</literal> and read the |
| 5459 |
<literal>DETAIL</literal> output.) |
| 5460 |
</para> |
| 5461 |
|
| 5462 |
<para> |
| 5463 |
Almost all <command>DROP</command> commands in <productname>PostgreSQL</productname> support |
| 5464 |
specifying <literal>CASCADE</literal>. Of course, the nature of |
| 5465 |
the possible dependencies varies with the type of the object. You |
| 5466 |
can also write <literal>RESTRICT</literal> instead of |
| 5467 |
<literal>CASCADE</literal> to get the default behavior, which is to |
| 5468 |
prevent dropping objects that any other objects depend on. |
| 5469 |
</para> |
| 5470 |
|
| 5471 |
<note> |
| 5472 |
<para> |
| 5473 |
According to the SQL standard, specifying either |
| 5474 |
<literal>RESTRICT</literal> or <literal>CASCADE</literal> is |
| 5475 |
required in a <command>DROP</command> command. No database system actually |
| 5476 |
enforces that rule, but whether the default behavior |
| 5477 |
is <literal>RESTRICT</literal> or <literal>CASCADE</literal> varies |
| 5478 |
across systems. |
| 5479 |
</para> |
| 5480 |
</note> |
| 5481 |
|
| 5482 |
<para> |
| 5483 |
If a <command>DROP</command> command lists multiple |
| 5484 |
objects, <literal>CASCADE</literal> is only required when there are |
| 5485 |
dependencies outside the specified group. For example, when saying |
| 5486 |
<literal>DROP TABLE tab1, tab2</literal> the existence of a foreign |
| 5487 |
key referencing <literal>tab1</literal> from <literal>tab2</literal> would not mean |
| 5488 |
that <literal>CASCADE</literal> is needed to succeed. |
| 5489 |
</para> |
| 5490 |
|
| 5491 |
<para> |
| 5492 |
For a user-defined function or procedure whose body is defined as a string |
| 5493 |
literal, <productname>PostgreSQL</productname> tracks |
| 5494 |
dependencies associated with the function's externally-visible properties, |
| 5495 |
such as its argument and result types, but <emphasis>not</emphasis> dependencies |
| 5496 |
that could only be known by examining the function body. As an example, |
| 5497 |
consider this situation: |
| 5498 |
|
| 5499 |
<programlisting> |
| 5500 |
CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', |
| 5501 |
'green', 'blue', 'purple'); |
| 5502 |
|
| 5503 |
CREATE TABLE my_colors (color rainbow, note text); |
| 5504 |
|
| 5505 |
CREATE FUNCTION get_color_note (rainbow) RETURNS text AS |
| 5506 |
'SELECT note FROM my_colors WHERE color = $1' |
| 5507 |
LANGUAGE SQL; |
| 5508 |
</programlisting> |
| 5509 |
|
| 5510 |
(See <xref linkend="xfunc-sql"/> for an explanation of SQL-language |
| 5511 |
functions.) <productname>PostgreSQL</productname> will be aware that |
| 5512 |
the <function>get_color_note</function> function depends on the <type>rainbow</type> |
| 5513 |
type: dropping the type would force dropping the function, because its |
| 5514 |
argument type would no longer be defined. But <productname>PostgreSQL</productname> |
| 5515 |
will not consider <function>get_color_note</function> to depend on |
| 5516 |
the <structname>my_colors</structname> table, and so will not drop the function if |
| 5517 |
the table is dropped. While there are disadvantages to this approach, |
| 5518 |
there are also benefits. The function is still valid in some sense if the |
| 5519 |
table is missing, though executing it would cause an error; creating a new |
| 5520 |
table of the same name would allow the function to work again. |
| 5521 |
</para> |
| 5522 |
|
| 5523 |
<para> |
| 5524 |
On the other hand, for an SQL-language function or procedure whose body |
| 5525 |
is written in SQL-standard style, the body is parsed at function |
| 5526 |
definition time and all dependencies recognized by the parser are |
| 5527 |
stored. Thus, if we write the function above as |
| 5528 |
|
| 5529 |
<programlisting> |
| 5530 |
CREATE FUNCTION get_color_note (rainbow) RETURNS text |
| 5531 |
BEGIN ATOMIC |
| 5532 |
SELECT note FROM my_colors WHERE color = $1; |
| 5533 |
END; |
| 5534 |
</programlisting> |
| 5535 |
|
| 5536 |
then the function's dependency on the <structname>my_colors</structname> |
| 5537 |
table will be known and enforced by <command>DROP</command>. |
| 5538 |
</para> |
| 5539 |
</sect1> |
| 5540 |
|
| 5541 |
</chapter> |